SQL文件執行

        private void button1_Click(object sender, EventArgs e)
        {
 
            SqlConnection conn = new SqlConnection("Persist Security Info=false;User ID=sa;Password=1;Initial Catalog=hrs_mis;Server=.");
            conn.Open();
            SqlTransaction stran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = stran;
            try
            {
                StreamReader reader = null;
                string str = "";
                string pathToScriptFile = @"D:\EHRS Installation\SqlSciptTool\bin\Debug\新增文字文件.sql";
                if (!File.Exists(pathToScriptFile))
                {
                    throw new Exception("文檔 " + pathToScriptFile + " 不存在！");
                }
                reader = new StreamReader(File.OpenRead(pathToScriptFile), Encoding.Default);
                while ( !string.IsNullOrEmpty(str = ReadNextStatementFromStream(reader)))
                {
                    MessageBox.Show(str);
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                reader.Close();

                stran.Commit();
                MessageBox.Show("You got it!");
            }
            catch (Exception exception)
            {
                stran.Rollback();
                MessageBox.Show(exception.Message);

            }
            finally
            {
                conn.Close();
            }
        }

        private static string ReadNextStatementFromStream(StreamReader _reader)
        {
            string strOut = null;
            try
            {
                string strTemp;
                StringBuilder builder = new StringBuilder();

                while (null != (strTemp = _reader.ReadLine()))
                {
                    if (strTemp.Trim().ToUpper() == "GO")
                    {
                        strOut = builder.ToString();
                        return strOut;
                    }
                    else
                    {
                        builder.Append(strTemp.Trim() + Environment.NewLine);
                    }
                }
                strOut = builder.ToString();
            }
            catch
            {
                strOut = null;
            }
            return strOut;
        }