Read a large SQL script file in C #

Question

I am trying to read a large script, so far I have tried two options:

Option 1:

We cannot open large script files in the SQL management studio due to a lack of memory problem, so first I used sqlcmd to execute a 160 MB SQL script file on the remote host after 55 minutes some lines were executed with this error, TCP Provider: An existing connection was forcibly closed by the remote host. , communication link failure. TCP Provider: An existing connection was forcibly closed by the remote host. , communication link failure.

Option 2:

Now I'm trying to use this example, the file size is 160 MB with many insert statements, but it crashes in Visual Studio

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

Screen shot:

Answer 1

I would suggest executing the insert statements in turn, optionally wrapped in a transaction:

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

Keep in mind that this will put some pressure on the database log file.