Use MS SQL Server Management Studio library to import a large SQL file in C#

Suppose you have a SQL File that needs to be imported into a MS SQL Database Server using C#, firstly import the following libraries as references in your C# project from the folder "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies"

• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc
• Microsoft.SqlServer.SqlEnum

Next use the following C# codes to import the SQL file

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;

private static void RunUT_ImportSQLFile()
{
 string script = "";

 string filepath = @"D:\temp\mixertruck.sql";

 if (!File.Exists(filepath))
 {
  Console.WriteLine("File [{0}] does not exist!", filepath);
  return;
 }

 StringBuilder script_sb = new StringBuilder();
 using (StreamReader reader = new StreamReader(filepath))
 {
  script = reader.ReadToEnd();
 }

 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
 sb.DataSource = @".\SQLEXPRESS";
 sb.InitialCatalog = "mixertruck";
 sb.UserID = "[username]";
 sb.Password = "[password]";
 string connection_string = sb.ToString();
 Console.WriteLine(connection_string);
 try
 {
  Console.WriteLine("Start importing data from: {0}", filepath);
  using (SqlConnection conn = new SqlConnection(connection_string))
  {
   Server server = new Server(new ServerConnection(conn));
   server.ConnectionContext.ExecuteNonQuery(script);
  }
  Console.WriteLine("Completed importing data from: {0}", filepath);
 }
 catch (Exception ex)
 {
  Console.WriteLine(ex.ToString());
 }
}

If the SQL file is really large the line "script=reader.ReadToEnd()" will throw memory exception, an alternative is as follows:

public static void RunUT_ImportLargeSQLFile()
{
 string filepath = @"D:\temp\mixertruck.sql";

 if (!File.Exists(filepath))
 {
  Console.WriteLine("File [{0}] does not exist!", filepath);
  return;
 }

 StringBuilder script_sb = new StringBuilder();
 string line = null;
 string query = null;
 

 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
 sb.DataSource = @".\SQLEXPRESS";
 sb.InitialCatalog = "mixertruck";
 sb.UserID = "[username]";
 sb.Password = "[password]";
 string connection_string = sb.ToString();
 int query_count=queries.Count;
 try
 {

  using (SqlConnection conn = new SqlConnection(connection_string))
  {
   Server server = new Server(new ServerConnection(conn));
   
   using (StreamReader reader = new StreamReader(filepath))
   {
    while ((line = reader.ReadLine())!= null)
    {
     script_sb.AppendLine(line);
     if (line.Contains(";"))
     {
      query = script_sb.ToString();
      script_sb.Clear();
      server.ConnectionContext.ExecuteNonQuery(query);
     }
    }
   }
  }

 }
 catch (Exception ex)
 {
  Console.WriteLine("Error executing: {0}\r\n{1}", query, ex.ToString());
 }
}