布署程式時執行SQL文件來創建DB及相關物件

最新推荐文章于 2021-08-04 11:52:55 发布

xndiy07

最新推荐文章于 2021-08-04 11:52:55 发布

阅读量460

点赞数

分类专栏： Dot Net 文章标签： sql database server null go

本文链接：https://blog.csdn.net/xndiy07/article/details/3490797

版权

Dot Net 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

DBAccess.cs

using System;
using System.Xml;
using System.Data;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using System.Diagnostics;
namespace ExecuteSqlFile
{
    /// <summary>
    /// DBAccess 的摘要说明。
    /// </summary>
    public class DBAccess
    {
        public DBAccess()
        {
        }
        #region 属性
        //server=TEST;uid=sa;pwd=1234567890;database=DB
        private static string ConStr = "";
        public static string ConString
        {
            get
            {
                 return ConStr;
            }
            set
            {
                ConStr = value;
            }
        }
        private static SqlConnection Con;
        public static SqlConnection MyConnection
        {
            get
            {
                if (Con == null)
                {
                    Con = new SqlConnection(ConString);
                }
                return Con;
            }
        }
        #endregion
        /// <summary>
        /// 执行Sql文件
        /// </summary>
        /// <param name="varFileName"></param>
        /// <returns></returns>
        public static bool ExecuteSqlFile(string varFileName)
        {
            if (!File.Exists(varFileName))
            {
                return false;
            }
            StreamReader sr = File.OpenText(varFileName);
            ArrayList alSql = new ArrayList();
            string commandText = "";
            string varLine = "";
            while (sr.Peek() > -1)
            {
                varLine = sr.ReadLine();
                if (varLine == "")
                {
                    continue;
                }
                if (varLine != "GO")
                {
                    commandText += varLine;
                    commandText += " ";
                }
                else
                {
                    alSql.Add(commandText);
                    commandText = "";
                }
            }
            sr.Close();
            try
            {
                ExecuteCommand(alSql);
            }
            catch(Exception ex)
            {
                //return false;
                throw ex;
            }
            return true;
        }
        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();
            }
        }
        public static bool ExecOSQL(string sServer,string sUser,string sPwd,string sSqlFile)
        {
            try
            {
                Process   p   =   new   Process();
                p.StartInfo.FileName   =   "cmd.exe ";
                p.StartInfo.UseShellExecute   =   false;
                p.StartInfo.RedirectStandardInput   =   true;
                p.StartInfo.RedirectStandardOutput   =   false;
                p.StartInfo.RedirectStandardError   =   true;
                p.StartInfo.CreateNoWindow   =   true;
                //string   Path   =   Application.StartupPath.ToString();
                string   Parameter   =   "osql.exe   -U " + sUser  + " -P "+ sPwd + " -S " +  sServer + " -i " + sSqlFile;

                //MessageBox.Show(Parameter);
                //this.Cursor   =   System.Windows.Forms.Cursors.WaitCursor;
                p.Start();
                p.StandardInput.WriteLine(Parameter);
                p.StandardInput.WriteLine( "exit ");
                p.StandardInput.WriteLine( "exit ");
                p.WaitForExit();
                p.Close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return true;
        }
        //...
    }
}

Call it

 
 protected void btnExecSQL_Click(object sender, EventArgs e)
        {
            try
            {
                ExecuteSqlFile.DBAccess.ConString = "server=TEST;uid=sa;pwd=1234567890;database=master";
                if(ExecuteSqlFile.DBAccess.ExecOSQL("TEST","sa","1234567890",Server.MapPath("CreaDB.sql")))
                {
                    lbInfo.Text += "Create DB OK!";
                    //if (ExecuteSqlFile.DBAccess.ExecOSQL("TEST", "sa", "1234567890", Server.MapPath("iTest.sql"))) 
                    if (ExecuteSqlFile.DBAccess.ExecuteSqlFile(Server.MapPath("iTest.sql")))
                    {
                        lbInfo.Text += "Create Table OK!";
                    }
                    else
                    {
                        lbInfo.Text += "Create Table Err!";
                    }
                }
                else
                {
                    lbInfo.Text += "Create DB Err!";
                }
            }
            catch(Exception ex)
            {
                lbInfo.Text += ex.Message;
            }
        }
 