在 C# 中读取大型 SQL 脚本文件

我正在尝试阅读一个大脚本，到目前为止我已经尝试了两种选择：

选项1：

由于内存空间不足的问题，我们无法在SQL Management Studio中打开大型脚本文件，所以最初我使用sqlcmd在远程主机上执行 160 mb SQL 脚本文件，55 分钟后某些行受到此错误的影响，TCP Provider: An existing connection was forcibly closed by the remote host. , communication link failure.

选项2：

现在我正在尝试使用this https://stackoverflow.com/a/40830/2218697例如，文件大小为 160 MB，包含大量插入语句，但 Visual Studio 崩溃

Code:

public ActionResult Index()
{
   string scriptDirectory = "e:\\";
   string sqlConnectionString = "Integrated Security=SSPI;" +
   "Persist Security Info=True;Initial Catalog=TestDB;Data Source=localhost\\SQLEXPRESS";
   DirectoryInfo di = new DirectoryInfo(scriptDirectory);
   FileInfo[] rgFiles = di.GetFiles("*.sql");
   foreach (FileInfo fi in rgFiles)
   {
        FileInfo fileInfo = new FileInfo(fi.FullName);
        string script = fileInfo.OpenText().ReadToEnd(); // here visual studio crashes
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        Server server = new Server(new ServerConnection(connection));
        server.ConnectionContext.ExecuteNonQuery(script);
   }

       return View();
}

截屏：

我建议逐行执行插入语句（可选）包含在事务中：

public ActionResult Index()
{
    string scriptDirectory = "e:\\";
    string sqlConnectionString = "Integrated Security=SSPI;" +
        "Persist Security Info=True;Initial Catalog=TestDB;Data Source=localhost\\SQLEXPRESS";

    using(var connection = new SqlConnection(sqlConnectionString))
    {
        var transaction = connection.BeginTransaction();
        using(var command = connection.CreateCommand())
        {
            ProcessFiles(command, scriptDirectory);
        }
        transaction.Commit();
    }
    return View();
}

private void ProcessFiles(SqlCommand command, string scriptDirectory)
{
    foreach(var file in Directory.GetFiles(scriptDirectory,"*.sql"))
    {
        using(var reader = new StreamReader(file))
        {
            while(!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                if(!line.StartsWith("GO"))
                {
                    command.CommandText = line;
                    command.ExecuteNonQuery();
                }
            }
        }
    }        
}

请记住，这会给数据库的日志文件带来一些压力。