C#读取.sql文件并执行文件中的sql脚本

有些时候我们需要在程序中编写读取sql脚本文件并执行这些sql语句，但是我们在有些时候会遇到读出来的sql语句不能执行，其实不能执行并不是你的sql脚本文件有错误，而是去执行sql语句的时候，C#代码里面执行sql语句的代码对sql里面的一些标志字符不识别罢了,

  不能有Go标识符，还有不能执行创建视图的语句，如果有上述语句C#代码执行sql语句的时候就会出错，只要过滤掉“GO“等标志字符。

        // 读取文本文件
        public static ArrayList ReadSqlFile(string filePath)
        {
            ArrayList alSql = new ArrayList();

            try
            {
                if (!File.Exists(filePath))
                {
                    return null;
                }
                StreamReader sr = File.OpenText(filePath);

                string commandText = string.Empty;
                string varLine = string.Empty;
                while (sr.Peek() > -1)
                {
                    varLine = sr.ReadLine();
                    if (varLine == string.Empty)
                    {
                        continue;
                    }
                    if (varLine != "GO" || varLine != "go" || varLine.Substring(0, 2) != "--" || varLine.Substring(0, 2) != @"/*" || varLine.Substring(0, 2) != @"*/")
                    {
                        commandText += varLine;
                        //commandText += "/r/n";
                    }
                    else
                    {
                        alSql.Add(commandText);
                        commandText = string.Empty;
                    }
                }
                alSql.Add(commandText);
                commandText = string.Empty;
                sr.Close();
                return alSql;
            }
            catch (Exception ex)
            {
                Logger.Instance.DoSomesting = "【业务模块-文件读写】";
                Logger.Instance.Info($"读取文件(.sql)异常:{ex.Message}");

                return alSql;
            }
        }

调用执行（此处执行方法我用封装类方法，后面给一个直接执行方法参考）

  ArrayList commandTextArrayList = null;

  string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);// 获取桌面路径
  OpenFileDialog openFileDialog = new OpenFileDialog();     //以打开的方式
  openFileDialog.Multiselect = false;                       //该值确定是否可以选择多个文件
  openFileDialog.Title = "请选择文件";                       //标题
  openFileDialog.InitialDirectory = desktopPath;//@"C:\";                 //默认打开C:\路径（可更改）
                                                            //限制只显示文件夹及后缀为sql的文件（可根据需求更改）
  openFileDialog.Filter = "SQL文件(*.sql)|*.sql";
  string sqlFileName = "";
  if (openFileDialog.ShowDialog() == DialogResult.OK)
  {
      sqlFileName = openFileDialog.FileName;

      commandTextArrayList = FileRWUtil.ReadSqlFile(sqlFileName); //调用ExecuteSqlFile()方法，反回 ArrayList对象;
      isSucess = bllDatabase.ExecuteSQLFile(commandTextArrayList);
  }

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();
   }
  }