I have used SMO, older versions of SMO, to run SQL in C#, but I wanted to do it a new application I’m writing to help with seeding database for tests. Actually, it was pretty easy and you may ask why not just use ADO or sqlcmd. Well the SQL I want to run has TSQL statements that ADO can’t work with. The sqlcmd tool is awesome from the command line, but I wanted a C# solution and SMO allowed me to have less ceremony to get everything up and running.

First you have to get the SMO DLLs necessary to connect with SQL Server and execute the scripts. I am using the files for SQL Server 2012 and I found the DLLs in C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\. You will need 3 of them:

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.Smo.dll

You can copy them to a common folder in your solution and reference them in the project you will use to code up your SQL file runner. If you are into all the Ninja code stuff, you will probably host them on a private NuGet server. Next, all you need is a little code:

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

public class ExcuteSqlScript
{
	public void FromFile(string filePath, string connectionString)
	{
		FileInfo file = new FileInfo(filePath);
		string script = file.OpenText().ReadToEnd();
		this.FromString(script, connectionString);
		file.OpenText().Close();
	}

	public void FromString(string script, string connectionString)
	{
		SqlConnection connection = new SqlConnection(connectionString);
		Server server = new Server(new ServerConnection(connection));
		server.ConnectionContext.ExecuteNonQuery(script);
	}

	//Finally figured out how to display formatted code, yay!
}

I basically have two methods. One loads the SQL from a file path to the SQL file and the other accepts a string with the SQL you want to run. They are both pretty self explanatory. You just need to supply the file path or SQL string and a connection string and it will execute. You should add some error handling and perhaps tweak the file read security and performance for your situation (see refs below). One thing I will be adding is a method to run all SQL files in a directory or iterate over a config file containing the paths to SQL files that need to be ran.

Anyway, this gives a basis to create a more robust solution. If you need more advanced interaction, like transactions, take a closer look at the API for ServerConnection and read the docs, it wasn’t too hard to get through it as the API is simple.

References

SQL Server Management Objects (SMO) Programming Guide – http://technet.microsoft.com/en-us/library/ms162169.aspx

C# .Net: Fastest Way to Read Text Files – http://blogs.davelozinski.com/curiousconsultant/csharp-net-fastest-way-to-read-text-files

Happy Coding!