如何使用C＃来执行.SQL脚本文件 [英] How to execute an .SQL script file using c#

问题描述

我敢肯定，这个问题已经已经回答了，但我无法用搜索工具找到答案。

使用C＃我想要运行.sql文件。 SQL文件中包含多个SQL语句，其中一些被打破多行。我试图读取文件，并试图执行文件使用ODP.NET ... ...但是我不认为的ExecuteNonQuery真的是专门做这个。

所以，我试图通过产卵的过程用sqlplus ...但是除非我产生了这个过程与UseShellExecute设置为true sqlplus中会挂起，永远不会退出。这里的code不起作用。

 进程p =新工艺（）;
p.StartInfo.UseShellExecute = FALSE;
p.StartInfo.RedirectStandardOutput = TRUE;
p.StartInfo.FileName =sqlplus的;
p.StartInfo.Arguments =的String.Format（XX / XX @ {0} @ {1}，in_database，S）;
p.StartInfo.CreateNoWindow = TRUE;布尔开始= p.Start（）;
p.WaitForExit（）;
 

WaitForExit不会返回....除非我设置UseShellExecute为true。 UseShellExecute的副作用是你可以不捕获重定向的输出。

解决方案

 使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用的System.Web;
使用System.Web.UI程序;
使用System.Web.UI.WebControls;
使用Microsoft.SqlServer.Management.Smo;
使用Microsoft.SqlServer.Management.Common;
使用System.IO;
使用System.Data.SqlClient的;公共部分类ExcuteScript：System.Web.UI.Page
{
    保护无效的Page_Load（对象发件人，EventArgs的发送）
    {
    字符串sqlConnectionString = @集成安全性= SSPI;坚持安全信息=假;初始目录= ccwebgrity;数据源= SURAJIT \\ SQLEX $ P $干燥综合征    字符串脚本= File.ReadAllText（@E：\\项目文档\\ MX462-PD \\ MX756_ModMappings1.sql）;    康涅狄格州的SqlConnection =新的SqlConnection（sqlConnectionString）;    Server服务器=新服务器（新ServerConnection（康涅狄格州））;    server.ConnectionContext.ExecuteNonQuery（脚本）;
    }
}
 

I'm sure this question has been answered already, however I was unable to find an answer using the search tool.

Using c# I'd like to run a .sql file. The sql file contains multiple sql statements, some of which are broken over multiple lines. I tried reading in the file and tried executing the file using ODP.NET ... however I don't think ExecuteNonQuery is really designed to do this.

So I tried using sqlplus via spawning a process ... however unless I spawned the process with UseShellExecute set to true sqlplus would hang and never exit. Here's the code that DOESN'T WORK.

Process p = new Process();
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = "sqlplus";
p.StartInfo.Arguments = string.Format("xx/xx@{0} @{1}", in_database, s);
p.StartInfo.CreateNoWindow = true;

bool started = p.Start();
p.WaitForExit();

WaitForExit never returns .... Unless I set UseShellExecute to true. A side effect of UseShellExecute is that you can no capture the redirected output.

解决方案

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;

public partial class ExcuteScript : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

    string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

    SqlConnection conn = new SqlConnection(sqlConnectionString);

    Server server = new Server(new ServerConnection(conn));

    server.ConnectionContext.ExecuteNonQuery(script);
    }
}

这篇关于如何使用C＃来执行.SQL脚本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！