How can I execute a .sql from C#?

Question

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.

Answer 1

Have you tried running this with a very, very basic script in the .sql file? Maybe something that just inserts one row or creates an arbitrary table? Something that is very easy to verify? Essentially, this code is like hard coding the sql, except you're reading it from a file. If you can get it to work with a very simple file, then I would say that there is likely something wrong with the file structure itself. The post alluded to the fact that there are some stipulations regarding what can and cannot be in the file. If nothing else, it's a good place to start troubleshooting.

Answer 2

MSVCR80 is the Visual C++ 2005 runtime. You may need to install the runtime package. See http://www.microsoft.com/downloads/details.aspx?FamilyID=200b2fd9-ae1a-4a14-984d-389c36f85647&displaylang=en for more details.

In addition to resolving the DLL issue and Matt Brunell's answer (which I feel is more appropriate for what you're trying to do), you can use the SQLCMD command line tool (from the SQL Client tools installation) to execute these SQL scripts. Just be sure it's on your path so you don't struggle with path locations.

This would play out like so:

Actual command:

SQLCMD -S myServer -D myDatabase -U myUser -P myPassword -i myfile.sql

Parameters (case matters):

S: server
d: database
U: User name, only necessary if you don't want to use Windows authentication
P: Password, only necessary if you don't want to use Windows authentication
i: File to run

Code to execute SQL files:

var startInfo = new ProcessStartInfo();
startInfo.FileName = "SQLCMD.EXE";
startInfo.Arguments = String.Format("-S {0} -d {1}, -U {2} -P {3} -i {4}",
                                    server,
                                    database,
                                    user,
                                    password,
                                    file);
Process.Start(startInfo);

See http://msdn.microsoft.com/en-us/library/ms162773.aspx for more information on the SQLCMD tool.

Answer 3

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

You shouldn't need SMO to execute queries. Try using the SqlCommand object instead. Remove these using statements. Use this code to execute the query:

 SqlConnection conn = new SqlConnection(sqlConnectionString);
 SqlCommand cmd = new SqlCommand(script, conn);
 cmd.ExecuteNonQuery();

Also, remove the project reference to SMO. Note: you will want to clean up resources properly.

Update:

The ADO.NET libraries do not support the 'GO' keyword. It looks like your options are:

1. Parse the script. Remove the 'GO' keywords and split the script into separate batches. Execute each batch as its own SqlCommand.
2. Send the script to SQLCMD in the shell (David Andres's answer).
3. Use SMO like the code from the blog post.

Actually, in this case, I think that SMO may be the best option, but you will need to track down why the dll wasn't found.

Answer 4

You may be interested in this: http://geekswithblogs.net/thomasweller/archive/2009/09/08/automating-database-script-execution.aspx

It presents a general-purpose 'test fixture' to automatically execute sql-scripts. There is also sample code available, and there are no dependencies to any unusual assemblies whatsoever...

Answer 5

If you add following references in your project, then original code will work fine.

I use SQL 2008 Express.

Path: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\

Files: microsoft.sqlserver.smo.dll, microsoft.sqlserver.connectioninfo.dll and Microsoft.SqlServer.Management.Sdk.Sfc.dll

Answer 6

Why not try a semi-colon instead of the "GO" keyword?