[轉帖]C#執行SQL指令碼，讀取XML檔案

[轉帖]C#執行SQL指令碼，讀取XML檔案

需要添加如下引用：

using System.IO;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;

GoSql類如下：

    class GoSql
    {
        private static string ConStr = "";
        private static string ConString//這裡讀取XML設定檔可以借鑒
        {
            get
            {
                if (ConStr == "")
                {
                    try
                    {
                        XmlDocument doc = new XmlDocument();
                        doc.Load("SqlScripts\\ServerConfig.xml");//這裡是所執行的SQL伺服器配置XML路徑
                        string userid = doc.SelectSingleNode("ServerConfig/UserId").InnerText;
                        string password = doc.SelectSingleNode("ServerConfig/PassWord").InnerText;
                        string servername = doc.SelectSingleNode("ServerConfig/ServerName").InnerText;
                        string database = doc.SelectSingleNode("ServerConfig/DataBase").InnerText;
                        ConStr = "server = " + servername + ";uid = "
                         + userid + ";pwd = " + password + ";database = " + database;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
                return ConStr;
            }
        }
        private static SqlConnection Con;
        public static SqlConnection MyConnection
        {
            get
            {
                if (Con == null)
                {
                    Con = new SqlConnection(ConString);
                }
                return Con;
            }
        }
        public static bool ExecuteSqlFile(string varFileName)
        {
            if (!File.Exists(varFileName))
            {
                return false;
            }
            StreamReader rs = new StreamReader(varFileName, System.Text.Encoding.Default);//注意編碼
            ArrayList alSql = new ArrayList();
            string commandText = "";
            string varLine = "";
            while (rs.Peek() > -1)
            {
                varLine = rs.ReadLine();
                if (varLine == "")
                {
                    continue;
                }
                if (varLine != "GO")
                {
                    commandText += varLine;
                    commandText += "\r\n";
                }
                else
                {
                     commandText += "";
                }
            }
           alSql.Add(commandText);
            rs.Close();
            try
            {
                ExecuteCommand(alSql);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private static void ExecuteCommand(ArrayList varSqlList)
        {
            MyConnection.Open();
            SqlTransaction varTrans = MyConnection.BeginTransaction();
            SqlCommand command = new SqlCommand();
            command.Connection = MyConnection;
            command.Transaction = varTrans;
            try
            {
                foreach (string varcommandText in varSqlList)
                {
                    command.CommandText = varcommandText;
                    command.ExecuteNonQuery();
                }
                varTrans.Commit();
            }
            catch (Exception ex)
            {
                varTrans.Rollback();
                throw ex;
            }
            finally
            {
                MyConnection.Close();
            }
        }
    }

      設定檔ServerConfig.xml內容如下：

<?xml version="1.0" encoding="utf-8" ?>
<ServerConfig>
 <ServerName>192.168.0.68</ServerName>
 <DataBase>lanxi_stgylmis</DataBase>
 <UserId>sa</UserId>
 <PassWord>sa</PassWord>
</ServerConfig>

     調用方法如下：

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {//告訴檔案路徑即可
                if (GoSql.ExecuteSqlFile(Application.StartupPath.ToString() + "\\SqlScripts\\080417.sql"))
                {
                    MessageBox.Show("SQL語句被執行！");
                }
                else
                {
                    MessageBox.Show("操作失敗！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }

轉自：http://www.smartgz.com/blog/Article/1114.asp