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