C# 利用*.SQL文件自动建库建表等的类

 /// <summary> 
    /// 自动建库建表
    /// </summary>
    public class OperationSqlFile
    {
        SqlConnection sqlCon;
        SqlCommand sqlCom;
        String _varFileName = "";
        String _conn = "";
        String _database = "";
        /// <summary> 
        /// 实例化 ExecuteSqlFile FileName:SQL文件路径 conn:连接数据源字符串 database:数据库名
        /// </summary> 
        /// <param name="FileName">SQL文件路径</param> 
        /// <param name="conn">连接数据源字符串</param> 
        /// <param name="database">数据库名</param> 
        /// <returns></returns> 
        public OperationSqlFile(String FileName, String Fonn, String Satabase)
        {
            _varFileName = FileName;
            _conn = Conn;
            _database = Database;
        }
        /// <summary> 
        /// 开始执行
        /// </summary> 
        public void Start()
        {
            ExecuteSql(_conn, _database, "create database " + _database);//建库
            String Conn = _conn + ";Initial Catalog=" + _database;
            ExecuteSqlFile(_varFileName, Conn);
        }
        /// <summary> 
        /// 执行Sql语句 
        /// </summary> 
        /// <param name="connString">连接字符串</param> 
        /// <param name="DatabaseName">数据库名</param> 
        /// <param name="sql">SQL语句</param> 
        /// <returns></returns> 
        private void ExecuteSql(string connString, string DatabaseName, string sql)
        {
            sqlCon = new SqlConnection(connString);
            sqlCon.Open();
            sqlCom = new SqlCommand(sql, sqlCon);
            try
            {
                sqlCom.ExecuteNonQuery();
            }
            catch (Exception e)
            {
            }
            finally
            {
                sqlCon.Close();
            }
        }
        /// <summary> 
        /// 执行Sql文件 
        /// </summary> 
        /// <param name="varFileName"></param> 
        /// <param name="Conn"></param> 
        /// <returns></returns> 
        private bool ExecuteSqlFile(string varFileName, String Conn)
        {
            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 += "\r\n";
                }
                else
                {
                    alSql.Add(commandText);
                    commandText = "";
                }
            }
            sr.Close();
            try
            {
                ExecuteCommand(alSql, Conn);
            }
            catch
            {
                return false;
            }
            return true;
        }
        private void ExecuteCommand(ArrayList varSqlList, String Conn)
        {
            sqlCon = new SqlConnection(Conn);
            sqlCon.Open();
            SqlTransaction varTrans = sqlCon.BeginTransaction();
            sqlCom = new SqlCommand();
            sqlCom.Connection = sqlCon;
            sqlCom.Transaction = varTrans;
            try
            {
                foreach (string varcommandText in varSqlList)
                {
                    sqlCom.CommandText = varcommandText;
                    try
                    {
                        sqlCom.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                    }
                }
                varTrans.Commit();
            }
            catch (Exception ex)
            {
                
            }
            finally
            {
                sqlCon.Close();
            }
        } 
    }
执行
            string FNADD = C:/mySql.sql;//SQL文件地址
            String Conn = "Data Source=(local);Integrated Security=True";//连接数据库字符串
            String DataB = "MyDATA";//要创建的数据库名
            OperationSqlFile Ex = new OperationSqlFile(FNADD, Conn , DataB );
            Ex.Start();

 

转载于:https://www.cnblogs.com/caoyinglai/p/3672844.html