如何使用 c# 执行 .SQL 脚本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/650098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute an .SQL script file using c#
提问by Rich
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.
使用 c# 我想运行一个 .sql 文件。sql 文件包含多个 sql 语句，其中一些语句被分成多行。我尝试读入文件并尝试使用 ODP.NET 执行文件......但是我认为 ExecuteNonQuery 并不是真的设计来做到这一点。
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.
所以我尝试通过生成一个进程来使用 sqlplus ......但是，除非我在 UseShellExecute 设置为 true 的情况下生成该进程，否则 sqlplus 会挂起并且永远不会退出。这是不起作用的代码。
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.
WaitForExit 永远不会返回......除非我将 UseShellExecute 设置为 true。UseShellExecute 的一个副作用是您无法捕获重定向的输出。
采纳答案by Rich
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);
}
}
回答by Binoj Antony
Put the command to execute the sql script into a batch file then run the below code
将执行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 )
in the batch file write something like this (sample for sql server)
在批处理文件中写这样的东西（sql server的示例）
osql -E -i %1
回答by Rich
I managed to work out the answer by reading the manual :)
我设法通过阅读手册找出答案:)
This extract from the MSDN
这个摘自 MSDN
The code example avoids a deadlock condition by calling p.StandardOutput.ReadToEnd before p.WaitForExit. A deadlock condition can result if the parent process calls p.WaitForExit before p.StandardOutput.ReadToEnd and the child process writes enough text to fill the redirected stream. The parent process would wait indefinitely for the child process to exit. The child process would wait indefinitely for the parent to read from the full StandardOutput stream.
There is a similar issue when you read all text from both the standard output and standard error streams. For example, the following C# code performs a read operation on both streams.
该代码示例通过在 p.WaitForExit 之前调用 p.StandardOutput.ReadToEnd 来避免死锁情况。如果父进程在 p.StandardOutput.ReadToEnd 之前调用 p.WaitForExit 并且子进程写入足够的文本来填充重定向的流，则会导致死锁情况。父进程将无限期地等待子进程退出。子进程将无限期地等待父进程从完整的 StandardOutput 流中读取。
当您从标准输出和标准错误流中读取所有文本时，也会出现类似的问题。例如，以下 C# 代码对两个流执行读取操作。
Turns the code into this;
把代码变成这样；
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;
}
Which now exits correctly.
现在正确退出。
回答by Hacko
I tried this solution with Microsoft.SqlServer.Management but it didn't work well with .NET 4.0 so I wrote another solution using .NET libs framework only.
我用 Microsoft.SqlServer.Management 尝试了这个解决方案，但它在 .NET 4.0 上运行得不好，所以我只使用 .NET libs 框架编写了另一个解决方案。
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();
回答by PussInBoots
Added additional improvements to surajits answer:
添加了对 surajits 答案的额外改进：
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);
}
}
}
}
Also, I had to add the following references to my project:
另外，我必须在我的项目中添加以下引用：
C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dllC:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dllC:\Program Files\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
I have no idea if those are the right dll:s to use since there are several folders in C:\Program Files\Microsoft SQL Server but in my application these two work.
我不知道这些是否是正确的 dll:s 使用，因为 C:\Program Files\Microsoft SQL Server 中有几个文件夹，但在我的应用程序中这两个工作。
回答by StefanG
There are two points to considerate.
有两点需要考虑。
1) This source code worked for me:
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;
}
I set the working directory to the script directory, so that sub scripts within the script also work.
我将工作目录设置为脚本目录，以便脚本中的子脚本也能工作。
Call it e.g. as Execute("usr/pwd@service", "c:\myscripts", "script.sql")
将其称为例如 Execute("usr/pwd@service", "c:\myscripts", "script.sql")
2) You have to finalize your SQL script with the statement EXIT;
2) 你必须用语句完成你的 SQL 脚本 EXIT;
回答by Neelam saini
This works for me:
这对我有用：
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();
}
}
回答by Xtian11
This Works on Framework 4.0 or Higher. Supports "GO". Also show the error message, line, and sql command.
这适用于 Framework 4.0 或更高版本。支持“GO”。还显示错误消息、行和 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;
}
}
}
}
connection.Close();
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return false;
}
}
回答by martinoss
Using EntityFramework, you can go with a solution like this. I use this code to initialize e2e tests. De prevent sql injection attacks, make sure not to generate this script based on user input or use command parameters for this (see overload of ExecuteSqlCommand that accepts parameters).
使用 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);
}
}
}
}
回答by Muhammad Salman
I couldn't find any exact and valid way to do this. So after a whole day, I came with this mixed code achieved from different sources and trying to get the job done.
我找不到任何准确有效的方法来做到这一点。所以一整天后，我带着这个从不同来源获得的混合代码并试图完成工作。
But it is still generating an exception ExecuteNonQuery: CommandText property has not been Initializedeven though it successfully runs the script file - in my case, it successfully creates the database and inserts data on the first startup.
但是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
{
}
}
}
