在项目中关于执行SQL语句的底层方法的通用类很多,但是很少有提供执行SQL脚本的的方法,曾经在项目中有功能需要能直接执行SQL脚本的方法,

经过项目的实践检验,方法比较实用,现在将代码贴出来给大家分享下:

/// <summary>
        /// 执行SQL语句脚本文件(带注释,带Go)
        /// </summary>
        /// <param name="sqlFileName">sql脚本文件路径</param>
        public static int ExecuteSQLFile(String sqlFileName)
        {
            int icount = 0;
            using (SqlConnection connecction = new SqlConnection(connectionString))
            {
            FileStream stream = new FileStream(sqlFileName, FileMode.Open);
            StreamReader reader = new StreamReader(stream, Encoding.GetEncoding("gb2312"));
                try
                {
                    SqlCommand command = connecction.CreateCommand();
                    connecction.Open();
                    //读取文件
                   
                    StringBuilder builder = new StringBuilder();
                    String strLine = "";
                    while ((strLine = reader.ReadLine()) != null)
                    {
                        if (strLine.Trim().ToUpper() != @"GO")
                        {
                            builder.AppendLine(strLine);
                        }
                        else
                        {
                            command.CommandText = builder.ToString();
                            icount = command.ExecuteNonQuery();
                            builder.Remove(0, builder.Length);
                        }
                    }
                    command.CommandText = builder.ToString();
                    icount = command.ExecuteNonQuery();
                    builder.Remove(0, builder.Length);
                    reader.Close();
                    stream.Close();
                    return icount;
                }
                catch
                {
                    reader.Close();
                    stream.Close();
                    icount = 0;
                    return icount;
                }
            }
        }