How do I run my .sql script file through ADO.NET?

Question

I want to run my .sql script file using my ASP.NET website through ADO.NET. How it could be it is not working?

When I try

'dbScript is a string and contains contents of the .sql file'
Dim cmd As New SqlCommand(dbScript, con)
Try
    con.Open()
    cmd.ExecuteNonQuery()
Catch ex As Exception
Finally
    con.Close()
    cmd.Dispose()
End Try

I get exceptions when GO statement executed in script. How can I fix this problem?

Answer 1

See my blog post about Handling GO Separators in SQL - The Easy Way. The trick is to use SMO's ExecuteNonQuery() method. For example, here's some code that will run all scripts in a directory, regardless of GO separators:

    using System;
    using System.IO;
    using System.Data.SqlClient;
    using System.Collections.Generic;

    //Microsoft.SqlServer.Smo.dll
    using Microsoft.SqlServer.Management.Smo;
    //Microsoft.SqlServer.ConnectionInfo.dll
    using Microsoft.SqlServer.Management.Common;

    public class RunAllSqlSriptsInDirectory
    {
        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();
                SqlConnection connection = new SqlConnection(sqlConnectionString);
                Server server = new Server(new ServerConnection(connection));
                server.ConnectionContext.ExecuteNonQuery(script);
            }
        }
    }

Answer 2

GO is not a Transact-SQL statement, is a tools batch delimiter. The server rightfully complain of a syntax error when GO is encountered in a batch. You need to split the file into batches and then execute individual batches. Use a regular expression that splits the file inot batches and recognizes GO case insensitive on a single line.

Answer 3

There is one minor problem with using splitting method to execute batches. The problem is comments. Say you have no power over the content of the files. You just need to execute it. GO within a multi-line comment will be the problem in every solution example here that is splitting the sql code using "GO" as the separator. Example:

[some sql code]
GO

/* start of commented out sql code ***********
[some sql code]
GO
end of commented out sql code ****************/

[some sql code]
GO

This will require some more complicated parsing than just a split. This won't work anymore:

Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
string[] lines = regex.Split(sql);

This code also ignores that spaces may lead the GO.

Answer 4

It's because GO isn't actually a native TSQL statement, it's used in Management Studio/Enterprise Manager to divide the script into batches.

You either need to:
1) split it into multiple individual scripts on each GO statement
2) use the Server class within SQL Management Objects, as exampled here