Execute and Run SQL script from C#

[tweetmeme source=”mradwan06” only_single=false http://www.URL.com]
I work in a project that used an integration testing that need to check for a specific data in the DB so as the best practice I just want to insert my test data into the DB and then run my test and at the end of the test remove this data to restate the DB to the original state so I search for a way to execute SQL script or run SQL script quickly and efficiency that may need DDL and DML so I use the following code
[sourcecode language=”csharp”]
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Data Source=.;Initial
Catalog=MyDB;Integrated Security=True";
FileInfo file = new FileInfo("D:\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection sqlConnection = new
SqlConnection(connectionString);
Server server = new Server(new ServerConnection(sqlConnection ));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
[/sourcecode]

Note you have to reference the following DLLs Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Management.Sdk.Sfc
My first problem was where is the Microsoft.SqlServer.Management.Smo? and where is the Microsoft.SqlServer.Management.Common? you will find them “C:Program Files (x86)Microsoft SQL Server100SDKAssemblies” and if you can’t find them you can go to the following link so you can download them and it’s related DLLs click here

The second problem was that I have the following error
“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”

so you have to add the following underline text in the app.config so it will look like the following

[sourcecode language=”csharp”]
<?xml version="1.0"?>
<configuration>

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

<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>

</configuration>
[/sourcecode]

That’s it

Share This: