如何使用 c # 执行.SQL 脚本文件

小开

将执行 sql 脚本的命令放入一个批处理文件中，然后运行下面的代码

string batchFileName = @"c:\batosql.bat";
string sqlFileName = @"c:\MySqlScripts.sql";
Process proc = new Process();
proc.StartInfo.FileName = batchFileName;
proc.StartInfo.Arguments = sqlFileName;
proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
proc.StartInfo.ErrorDialog = false;
proc.StartInfo.WorkingDirectory = Path.GetDirectoryName(batchFileName);
proc.Start();
proc.WaitForExit();
if ( proc.ExitCode!= 0 )

在批处理文件中编写如下代码(sql server 的示例)

osql -E -i %1

小开

我通过阅读手册得出了答案:)

这个摘录自 MSDN

该代码示例避免了死锁通过调用之前的标准输出等待退出。一个死锁条件如果父进程调用之前等待退出标准输出子进程写入足够的文本填充重定向流过程将无限期地等待要退出的子进程过程将无限期地等待父母要从头读起标准输出流。

当你阅读的时候也有类似的问题标准输出的所有文本和标准错误流例如，下面的 C # 代码对两者执行读操作溪流。

把代码变成这样

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


bool started = p.Start();
// important ... read stream input before waiting for exit.
// this avoids deadlock.
string output = p.StandardOutput.ReadToEnd();


p.WaitForExit();


Console.WriteLine(output);


if (p.ExitCode != 0)
{
Console.WriteLine( string.Format("*** Failed : {0} - {1}",s,p.ExitCode));
break;
}

现在正确退出。

小开

最佳答案

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);
}
}

小开

我在微软尝试过这个解决方案。SqlServer.管理，但它没有很好地工作与。NET 4.0，所以我写了另一个解决方案使用。NET 库框架。

string script = File.ReadAllText(@"E:\someSqlScript.sql");


// split script on GO command
IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);


Connection.Open();
foreach (string commandString in commandStrings)
{
if (!string.IsNullOrWhiteSpace(commandString.Trim()))
{
using(var command = new SqlCommand(commandString, Connection))
{
command.ExecuteNonQuery();
}
}
}
Connection.Close();

小开

对苏拉吉的回答增加了额外的改进:

using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.SqlClient;


namespace MyNamespace
{
public partial class RunSqlScript : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
var connectionString = @"your-connection-string";
var pathToScriptFile = Server.MapPath("~/sql-scripts/") + "sql-script.sql";
var sqlScript = File.ReadAllText(pathToScriptFile);


using (var connection = new SqlConnection(connectionString))
{
var server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(sqlScript);
}
}
}
}

此外，我还必须为我的项目添加以下参考资料:

C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

我不知道这些 dll: s 是否正确，因为在 c: Program Files Microsoft SQL Server 中有几个文件夹，但在我的应用程序中，这两个都可以使用。

小开

有两点需要考虑。

1)这个源代码对我很有用:

private static string Execute(string credentials, string scriptDir, string scriptFilename)
{
Process process = new Process();
process.StartInfo.UseShellExecute = false;
process.StartInfo.WorkingDirectory = scriptDir;
process.StartInfo.RedirectStandardOutput = true;
process.StartInfo.FileName = "sqlplus";
process.StartInfo.Arguments = string.Format("{0} @{1}", credentials, scriptFilename);
process.StartInfo.CreateNoWindow = true;


process.Start();
string output = process.StandardOutput.ReadToEnd();
process.WaitForExit();


return output;
}

我将工作目录设置为 script 目录，这样脚本中的子脚本也可以工作。

称之为 Execute("usr/pwd@service", "c:\myscripts", "script.sql")

2)必须使用 EXIT;语句完成 SQL 脚本

小开

这对我有用:

public void updatedatabase()
{


SqlConnection conn = new SqlConnection("Data Source=" + txtserver.Text.Trim() + ";Initial Catalog=" + txtdatabase.Text.Trim() + ";User ID=" + txtuserid.Text.Trim() + ";Password=" + txtpwd.Text.Trim() + "");
try
{


conn.Open();


string script = File.ReadAllText(Server.MapPath("~/Script/DatingDemo.sql"));


// split script on GO command
IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
foreach (string commandString in commandStrings)
{
if (commandString.Trim() != "")
{
new SqlCommand(commandString, conn).ExecuteNonQuery();
}
}
lblmsg.Text = "Database updated successfully.";


}
catch (SqlException er)
{
lblmsg.Text = er.Message;
lblmsg.ForeColor = Color.Red;
}
finally
{
conn.Close();
}
}

小开

此命令可在 Framework4.0或更高版本上运行。支持“ GO”。还可以显示错误消息、 line 和 sql 命令。

using System.Data.SqlClient;


private bool runSqlScriptFile(string pathStoreProceduresFile, string connectionString)
{
try
{
string script = File.ReadAllText(pathStoreProceduresFile);


// split script on GO command
System.Collections.Generic.IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",
RegexOptions.Multiline | RegexOptions.IgnoreCase);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
foreach (string commandString in commandStrings)
{
if (commandString.Trim() != "")
{
using (var command = new SqlCommand(commandString, connection))
{
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
string spError = commandString.Length > 100 ? commandString.Substring(0, 100) + " ...\n..." : commandString;
MessageBox.Show(string.Format("Please check the SqlServer script.\nFile: {0} \nLine: {1} \nError: {2} \nSQL Command: \n{3}", pathStoreProceduresFile, ex.LineNumber, ex.Message, spError), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
}
}
}
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
}

小开

使用 EntityFramework，您可以使用这样的解决方案。我使用这段代码来初始化 e2e 测试。防止 sql 注入攻击，确保不要根据用户输入生成此脚本，或为此使用命令参数(请参见接受参数的 ExecuteSqlCommand 的过载)。

public static void ExecuteSqlScript(string sqlScript)
{
using (MyEntities dataModel = new MyEntities())
{
// split script on GO commands
IEnumerable<string> commands =
Regex.Split(
sqlScript,
@"^\s*GO\s*$",
RegexOptions.Multiline | RegexOptions.IgnoreCase);


foreach (string command in commands)
{
if (command.Trim() != string.Empty)
{
dataModel.Database.ExecuteSqlCommand(command);
}
}
}
}

小开

我找不到任何确切有效的方法来做这件事。因此，一整天之后，我带来了这个混合代码，它来自不同的来源，并试图完成工作。

但是它仍然生成异常 ExecuteNonQuery: CommandText property has not been Initialized，即使它成功地运行了脚本文件——在我的例子中，它成功地创建了数据库并在第一次启动时插入了数据。

public partial class Form1 : MetroForm
{
SqlConnection cn;
SqlCommand cm;
public Form1()
{
InitializeComponent();
}


private void Form1_Load(object sender, EventArgs e)
{
if (!CheckDatabaseExist())
{
GenerateDatabase();
}
}


private bool CheckDatabaseExist()
{
SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=SalmanTradersDB;Integrated Security=true");
try
{
con.Open();
return true;
}
catch
{
return false;
}
}


private void GenerateDatabase()
{


try
{
cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("drop databse {0}", "SalmanTradersDB"));
cm = new SqlCommand(sb.ToString() , cn);
cn.Open();
cm.ExecuteNonQuery();
cn.Close();
}
catch
{


}
try
{
//Application.StartupPath is the location where the application is Installed
//Here File Path Can Be Provided Via OpenFileDialog
if (File.Exists(Application.StartupPath + "\\script.sql"))
{
string script = null;
script = File.ReadAllText(Application.StartupPath + "\\script.sql");
string[] ScriptSplitter = script.Split(new string[] { "GO" }, StringSplitOptions.None);
using (cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True"))
{
cn.Open();
foreach (string str in ScriptSplitter)
{
using (cm = cn.CreateCommand())
{
cm.CommandText = str;
cm.ExecuteNonQuery();
}
}
}
}
}
catch
{


}


}


}

小开

使用 Microsoft.SqlServer.DacFx 为 dotnet 提供新的解决方案

批量返回的所有结果完全可访问

为 fSharp 互动写的

用途:

Dotnet fsi —— exec file.fsx connect-string sql-batches-file

// https://github.com/dotnet/SqlClient/issues/1397
// https://github.com/dotnet/fsharp/issues/12703
#r "nuget: Microsoft.Data.SqlClient, 3.0"


#r "nuget: Microsoft.SqlServer.DacFx"


open System
open System.IO
open System.Text
open System.Collections.Generic
open Microsoft.Data.SqlClient
open Microsoft.SqlServer.TransactSql.ScriptDom


let tokens (f:TSqlFragment) = seq {
for i = f.FirstTokenIndex to f.LastTokenIndex do yield f.ScriptTokenStream[i]
}


let fragToString (f:TSqlFragment) =
let append (b:StringBuilder) (t:TSqlParserToken) = b.Append(t.Text)
(Seq.fold append (StringBuilder()) (tokens f)).ToString()


let parse file =
let parser = TSql150Parser(false)
use textReader = File.OpenText(file) :> TextReader
let mutable errors : IList<_> = Unchecked.defaultof<IList<_>>
let res = parser.Parse(textReader, &errors)
match errors.Count with
| 0 -> res
| _ -> failwith $"parse error in file {file}"


let cs = SqlConnectionStringBuilder(fsi.CommandLineArgs[1])
let dbConn = new SqlConnection(cs.ConnectionString)
dbConn.Open()


let visitor = {
new TSqlFragmentVisitor() with
override this.Visit(statement:TSqlBatch) =
let sql = fragToString statement
printfn $"running batch:\n{sql}"
let cmd = new SqlCommand(sql, dbConn)
cmd.ExecuteNonQuery() |> ignore
}
let parsed = parse fsi.CommandLineArgs[2]
parsed.Accept(visitor)