Execute SQL script file in C#

I was faced with the challenged of executing an SQL batch script from my C#code.

As is always the case, I started by using the SqlCommand object to run it, to which I received an exception on the ‘GO’ statements in my script. The next step was to parse the script and remove the ‘Go’ keyword which didn’t work because the script was at some point creating Stored procedures and executing them.

Here is how I fixed my problems..

Since Sqlserver 2005, it is possible to  reference SMO library in your project.
Refence the following files in the your project

1. Microsoft.SqlServer.ConnectionInfo.dll
2. Microsoft.SqlServer.Management.Sdk.Sfc.dll
3. Microsoft.SqlServer.Smo.dll

Copy paste following code snipet to your project. Edit it to fit your intended use

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

public class SQLEngine
{
public static void ExecuteScript()
{
LogFile logFile;
SqlConnection Sqlconnection;
Server server=null;
string SQLscript;
FileInfo file;
try
{

file = new FileInfo("C:\\script.sql");
SQLscript = file.OpenText().ReadToEnd();

Sqlconnection = "Data Source=(local);Initial Catalog=[Catalog];Integrated Security=True
ServerConnection serverConnection = new ServerConnection(Sqlconnection);
server = new Server(serverConnection);

server.ConnectionContext.InfoMessage+=new SqlInfoMessageEventHandler(myConnection_InfoMessage);

server.ConnectionContext.BeginTransaction();

server.ConnectionContext.ExecuteNonQuery(SQLscript); // hangs here for about 7 -15 seconds

server.ConnectionContext.CommitTransaction();

server.ConnectionContext.InfoMessage -= new SqlInfoMessageEventHandler(myConnection_InfoMessage);

}
catch (Exception ex)
{
//Handle Exception
}

}


private static void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
//Add code to manage the messages
}

}

Incase you are using .NET 2 version of Microsoft.SqlServer…dll, while your project targets a higher version. You are likely to get this error;

System.IO.FileLoadException was unhandled HResult=-2146232799 Message=Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

Change the app.config file and add the following to allow loading of .net 2 assemblies

<startup useLegacyV2RuntimeActivationPolicy="true">

<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
<requiredRuntime version="v4.0" />
</startup>