C# 读取文件中的sql语句 创建数据库以及表结构

大概思路是: 读取文件 根据文件中行内容为GO 作为分割  一条条放到list中 然后在程序中逐条执行sql语句;

值得一提的是 创建数据库的语句是不允许放到程序事务中执行的 所以目前我是分了两个文本文件  先执行创建数据库的语句 把数据库创建完成 再通过事务执行建表 存储过程等的语句;

目前没有找到可以一起执行创建的方法 如果有朋友有解决的方法还望能够指教一下

 

代码贴上:

首先创建数据库:

　　　　       string con = System.Configuration.ConfigurationManager.AppSettings["create"];　　　　 
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="str1"></param>
        /// <param name="str2"></param>
        /// <param name="dbname"></param>
        /// <param name="partschemeName"></param>
        /// <returns></returns>
        public bool CreateBase(string fileName, string str1, string str2, string dbname, string partschemeName)
        {
            ArrayList list = GetSqlListFromFile(fileName, str1, str2, dbname, partschemeName);
            return ExecuteCommand4CreateDataBase(list, con);
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="list"></param>
        /// <param name="connString"></param>
        /// <returns></returns>
        public bool ExecuteCommand4CreateDataBase(ArrayList list, string connString)
        {
            SqlConnection MyConnection = new SqlConnection(connString);
            MyConnection.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = MyConnection;
            try
            {
                foreach (string commandText in list)
                {
                    command.CommandText = commandText;
                    command.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                MyConnection.Close();
            }
        }

　　　   /// <summary>
        /// 获取文件内的sql语句list
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="str1">文件中数据库名字</param>
        /// <param name="str2">文件中数据库分区方案名字</param>
        /// <param name="dbname">新创建的数据库名字</param>
        /// <param name="partschemeName">新创建的数据库分区方案名称</param>
        /// <returns></returns>
        public ArrayList GetSqlListFromFile(string fileName, string str1, string str2, string dbname, string partschemeName)
        {
            ArrayList sqlList = new ArrayList();
            if (!File.Exists(fileName))
            {
                return sqlList;
            }
            StreamReader rs = new StreamReader(fileName, System.Text.Encoding.Default);
            string commandText = "";
            string line = "";
            while (rs.Peek() > -1)
            {
                line = rs.ReadLine();
                if (line == "")
                {
                    continue;
                }
                if (line != "GO" && line != "go")
                {
                    commandText += line;
                    commandText = commandText.Replace(str1, dbname);
                    commandText = commandText.Replace(str2, partschemeName);
                    commandText += "\r\n";
                }
                else
                {
                    sqlList.Add(commandText);
                    commandText = "";
                }
            }

            rs.Close();
            return sqlList;
        }

然后通过事务创建表以及存储过程等:

　　　　 
        /// <summary>
        /// 创建表 存储过程等
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="str1"></param>
        /// <param name="str2"></param>
        /// <param name="dbname"></param>
        /// <param name="partschemeName"></param>
        /// <returns></returns>
        public bool CreateTable(string fileName, string str1, string str2, string dbname, string partschemeName)
        {
            ArrayList list = GetSqlListFromFile(fileName, str1, str2, dbname, partschemeName);
            return ExecuteCommand(list, con);
        }

　　　　 /// <summary>
        /// 创建数据表 存储过程等(事务)
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="connString"></param>
        /// <returns></returns>
        public bool ExecuteCommand(ArrayList sqlList, string connString)
        {

            SqlConnection MyConnection = new SqlConnection(connString);
            MyConnection.Open();
            SqlTransaction trans = MyConnection.BeginTransaction();
            SqlCommand command = new SqlCommand();
            command.Connection = MyConnection;
            command.Transaction = trans;
            try
            {
                foreach (string commandText in sqlList)
                {
                    command.CommandText = commandText;
                    command.ExecuteNonQuery();
                }
                trans.Commit();
                return true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                MyConnection.Close();
            }
        }

　　以上。