我确定这个问题已经得到解答,但我无法使用搜索工具找到答案.
使用c#我想运行一个.sql文件.sql文件包含多个sql语句,其中一些语句分为多行.我尝试在文件中读取并尝试使用ODP.NET执行文件...但是我不认为ExecuteNonQuery真的是为了这样做而设计的.
所以我尝试通过生成一个进程来使用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永远不会返回....除非我将UseShellExecute设置为true.UseShellExecute的副作用是您无法捕获重定向的输出.
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);
}
}
我尝试使用Microsoft.SqlServer.Management这个解决方案,但是它与.NET 4.0不兼容,所以我只使用.NET libs框架编写了另一个解决方案.
string script = File.ReadAllText(@"E:\someSqlScript.sql"); // split script on GO command IEnumerablecommandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase); Connection.Open(); foreach (string commandString in commandStrings) { if (commandString.Trim() != "") { using(var command = new SqlCommand(commandString, Connection)) { command.ExecuteNonQuery(); } } } Connection.Close();
这适用于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 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;
}
}
将命令执行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
这对我有用：
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 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();
}
}
京公网安备 11010802040832号 | 京ICP备19059560号-6 