Execute a large SQL script (with GO commands)

I need to execute a large set of SQL statements (creating a bunch of tables, views and stored procedures) from within a C# program.

These statements need to be separated by GO statements, but SqlCommand.ExecuteNonQuery() does not like GO statements. My solution, which I suppose I'll post for reference, was to split the SQL string on GO lines, and execute each batch separately.

Is there an easier/better way?

回答

Use SQL Server Management Objects (SMO) which understands GO separators. See my blog post here: http://weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way

Sample code:

public static void Main()    
{        
  string scriptDirectory = "c:\\temp\\sqltest\\";
  string sqlConnectionString = "Integrated Security=SSPI;" +
  "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
  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();
        using (SqlConnection connection = new SqlConnection(sqlConnectionString))
        {
            Server server = new Server(new ServerConnection(connection));
            server.ConnectionContext.ExecuteNonQuery(script);
        }
   }
}

If that won't work for you, see Phil Haack's library which handles that: http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators-and.aspx

This solutions caused a failure your code when some of SQL Dlls are not installed on the machine. .NET uses some dll's built in Windows. The absentence of some SQL feature packs (including Managment Objects) may prevent a errors something like 'Microsoft.SqlServer.SqlClrProvider.dll' not found. Fixing it(it is not easy work) next error will be 'Microsoft.SqlServer.BathParser.dll' e.t.c Find other solution to ensure flexibility for your application. – Alexandr Sargsyan May 27 '16 at 6:35

How to execute an .SQL script file using c#

回答1

 protected void Page_Load(object sender, EventArgs e)
    {
        string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS";

        string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql");

        SqlConnection conn = new SqlConnection(sqlConnectionString);

        Server server = new Server(new ServerConnection(conn));

        server.ConnectionContext.ExecuteNonQuery(script);
    }

回答2

I tried this solution with Microsoft.SqlServer.Management but it didn't work well with .NET 4.0 so I wrote another solution using .NET libs framework only.

string script = File.ReadAllText(@"E:\someSqlScript.sql");

// split script on GO command
IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);

Connection.Open();
foreach (string commandString in commandStrings)
{
    if (!string.IsNullOrWhiteSpace(commandString.Trim()))
    {
        using(var command = new SqlCommand(commandString, Connection))
        {
            command.ExecuteNonQuery();
        }
    }
}     
Connection.Close();

需要注意的是using SqlConnection = Microsoft.Data.SqlClient.SqlConnection; 命令空间不一样

 private void ExecuteSqlFile(string connectionString, string content)
        {
            using (Microsoft.Data.SqlClient.SqlConnection connection = new Microsoft.Data.SqlClient.SqlConnection(connectionString))
            {
                Server server = new Server(new ServerConnection(connection));
                server.ConnectionContext.ExecuteNonQuery(content);
            }
        }