How can I execute a .sql from C#?
- by J. Pablo Fernández
For some integration tests I want to connect to the database and run a .sql file that has the schema needed for the tests to actually run, including GO statements. How can I execute the .sql file? (or is this totally the wrong way to go?)
I've found a post in the MSDN forum showing this code:
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 sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
FileInfo file = new FileInfo("C:\\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
but on the last line I'm getting this error:
System.Reflection.TargetInvocationException:
Exception has been thrown by the
target of an invocation. ---
System.TypeInitializationException:
The type initializer for ''
threw an exception. ---
.ModuleLoadException:
The C++ module failed to load during
appdomain initialization. ---
System.DllNotFoundException: Unable to
load DLL 'MSVCR80.dll': The specified
module could not be found. (Exception
from HRESULT: 0x8007007E).
I was told to go and download that DLL from somewhere, but that sounds very hacky. Is there a cleaner way to? Is there another way to do it? What am I doing wrong?
I'm doing this with Visual Studio 2008, SQL Server 2008, .Net 3.5SP1 and C# 3.0.
