0

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

Option 1:

We can't open large script files in SQL management studio because of the issue of out of memory space, so Initially I used sqlcmd to execute 160 mb SQL script file on remote host, after 55 minutes some rows were effected with this error, TCP Provider: An existing connection was forcibly closed by the remote host. , communication link failure.

Option 2:

Now I am trying using this example, the file size is 160 MB with lot of insert statements, but Visual Studio crashes

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:

enter image description here

Community
  • 1
  • 1
Shaiju T
  • 6,201
  • 20
  • 104
  • 196
  • 1
    Does your script contains `GO` separators? Or there are just `INSERT...` lines? – Dennis Dec 08 '15 at 07:12
  • 2
    Why is *Visual Studio* reading the file at all? It's not clear why you're executing your code within the Visual Studio executable. (Additionally, please format your code to make it more readable.) – Jon Skeet Dec 08 '15 at 07:12
  • @Dennis, yes it contains `GO` separators because the script file was generated with Generate script wizard in Sql management studio. – Shaiju T Dec 08 '15 at 07:16
  • If there are only `INSERT`s and `GO`s, then it's rather easy to parse this file manually. Just read file line-by-line, skip `GO` statements, and execute one `INSERT` statement per `SqlCommand`. – Dennis Dec 08 '15 at 07:20
  • @Jon Skeet, I have made the changes , thanks. – Shaiju T Dec 08 '15 at 07:26
  • @Dennis, i am migrating to new database with `schema and data` , so it contains all , create, insert, alter, hope you understand. – Shaiju T Dec 08 '15 at 07:28
  • Have you explored other options, like with the Import and Export wizard? If you still have access to the legacy database, that could be a viable alternative. – Matthew Haugen Dec 08 '15 at 07:31
  • @Matthew Haugen i have tried `import export wizard` in sql, here are the [issues](http://www.sqlmatters.com/Articles/Common%20Issues%20with%20the%20SQL%20Server%20Import%20and%20Export%20Wizard.aspx) with it, and i can break script into smaller parts but again its time consuming, i can run script file which is 20 mb in sql management studio, i just posted this question to know weather we can read large file in c#. – Shaiju T Dec 08 '15 at 07:35

1 Answers1

0

I would suggest executing the insert statements line by line 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 log file of the database.

RePierre
  • 9,358
  • 2
  • 20
  • 37