/// <summary>
/// 执行一段 SQL 脚本( for MS SQL200X )
/// </summary>
public static void ExecuteSqlScript(string sScript, ScriptExecuteHandler handler, SqlInfoMessageEventHandler imHandler = null)
{
string[] statements = System.Text.RegularExpressions.Regex.Split(sScript, "\\sGO\\s", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
using (var conn = new SqlConnection(ConnectString)) {
if (imHandler != null)
{
conn.InfoMessage += imHandler;
}
conn.Open();
try {
using (var trans = conn.BeginTransaction()) {
foreach (var sql0 in statements)
{
var sql = sql0.Trim();
try {
if (sql.ToLower().IndexOf("setuser") >= 0)
{
continue;
}
if (sql.Length > 0)
{
using (SqlCommand cmd = new SqlCommand()) {
cmd.Transaction = trans;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql.Trim();
var o = cmd.ExecuteScalar();
if (handler != null)
{
handler(new EventSQLScriptArgs(cmd.CommandText, o));
}
}
}
} catch (Exception x) {
trans.Rollback();
throw new Exception(String.Format("ERROR:\n{1}\n\nSTATEMENT:\n{0}", sql, x.Message));
}
}
trans.Commit();
}
} finally {
if (imHandler != null)
{
conn.InfoMessage -= imHandler;
}
conn.Close();
conn.Dispose();
}
}
}
/// <summary>
/// 执行一个 SQL 脚本文件( for MS SQL200X )
/// </summary>
public static void ExecuteSqlScriptFile(string fileName, ScriptExecuteHandler handler, SqlInfoMessageEventHandler imHandler = null)
{
string sScript = null;
try {
using (var file = new System.IO.StreamReader(fileName)) {
sScript = file.ReadToEnd() + Environment.NewLine;
file.Close();
}
} catch (System.IO.FileNotFoundException) {
return;
} catch (Exception x) {
throw new Exception("Failed to read " + fileName, x);
}
ExecuteSqlScript(sScript, handler, imHandler);
}
