简单的脚本批量发布（带事务）程序

来源：互联网发布：中国美术馆知乎编辑：程序博客网时间：2024/06/07 14:35
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data;namespace ConsoleApplication8{    class Program    {        static void Main(string[] args)        {            string connString = @"Data Source=(local)\sqlserver2014;Initial Catalog=tempdb;Integrated Security=True";            //正确SQL            string sql_1 =@"IF OBJECT_ID('t') IS NOT NULL DROP TABLE tCREATE TABLE t(id INT IDENTITY(1,1) PRIMARY KEY)GOALTER TABLE t ADD c1 INTgoINSERT INTO t(c1) VALUES(1)";            //有错误的SQL            string sql_2 =@"INSERT INTO t(c1) VALUES(2)GOif object_id('proc_test') is not null drop proc proc_testgoCREATE PROC Proc_TESTASBegin    set nocount on    select 1endgo123 -- 注：此处有误";            string errMsg = string.Empty;            bool r1 = ExecuteNonQueryWithConnAndGO(connString, sql_1, ref errMsg);            Console.WriteLine("SQL1 结果：{0}, 错误信息:{1}", r1 ? "成功":"失败", string.IsNullOrEmpty(errMsg)?"无":errMsg);            errMsg = string.Empty;            bool r2 = ExecuteNonQueryWithConnAndGO(connString, sql_2, ref errMsg);            Console.WriteLine("SQL2 结果：{0}, 错误信息:{1}", r2 ? "成功" : "失败", string.IsNullOrEmpty(errMsg) ? "无" : errMsg);            //注：第2个SQL有问题，所以第2个SQL全部回滚了，只有第一个SQL的记录            Print(connString, "select * from t");            Console.Read();        }        #region [ 执行带Go语句 ]        /// <summary>          /// 执行带"GO"的SQL，返回最后一条SQL的受影响行数          /// </summary>          /// <param name="connString">连接串</param>          /// <param name="sql">sql语句</param>          /// <returns>是否成功</returns>        public static bool ExecuteNonQueryWithConnAndGO(string connString, string sql, ref string errMsg)        {            bool result = true;            string[] arr = System.Text.RegularExpressions.Regex.Split(sql, @"\bGO\b", System.Text.RegularExpressions.RegexOptions.IgnoreCase);            using (SqlConnection conn = new SqlConnection(connString))            {                conn.Open();                SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;                SqlTransaction tx = conn.BeginTransaction();                cmd.Transaction = tx;                try                {                    for (int n = 0; n < arr.Length; n++)                    {                        string strsql = arr[n];                        if (strsql.Trim().Length > 1 && strsql.Trim().Replace(";", "") != "")                        {                            cmd.CommandText = strsql;                            cmd.ExecuteNonQuery();                        }                    }                    tx.Commit();                }                catch (System.Data.SqlClient.SqlException ex)                {                    tx.Rollback();                    result = false;                    errMsg = ex.Message;                }            }            return result;        }        #endregion        public static void Print(string connString, string sql)         {            Console.WriteLine("\r\n------- 输出信息: {0} --------", sql);            DataTable dt = new DataTable();            using (SqlConnection conn = new SqlConnection(connString))             {                conn.Open();                SqlCommand cmd = new SqlCommand(sql, conn);                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                adapter.Fill(dt);            }            foreach (DataRow dr in dt.Rows)             {                foreach (DataColumn dc in dt.Columns)                 {                    Console.Write("{0}: {1}\t",dc.ColumnName,dr[dc.ColumnName].ToString());                }                Console.WriteLine();            }        }    }}
阅读全文
0 0