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
- Microsoft.SqlServer.ConnectionInfo.dll
- Microsoft.SqlServer.Management.Sdk.Sfc.dll
- Microsoft.SqlServer.Smo.dll
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
}
}
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
}
}
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>
<requiredRuntime version="v4.0" />
</startup>
