Execute SQL Batch queries using C# and SMO Magic

Hello World,

Let me quickly summarize the objective of this post. We have a development database that is restored every night with masked data and schema from its production version. During the course of development, we need to alter the schema and/or modify data that C# code uses and these database changes get overwritten every night (there are good reasons for the nightly restore, but I would not touch upon those here). In essence, I needed a way to schedule the deployment of our yet to be released database changes post the nightly restore.

SQL Server Management Objects

There are (as always) many ways of doing this, one of which is to use sqlcmd. I wanted a better solution which could be more intuitive to use and extend. My search led me to SQL Server Management Objects (SMO). If you’ve not heard of them before, SQL server provides an SDK (Software Development Kit) that consists of APIs that can be used to pretty much do everything that you can do using SQL Server Management Studio.

A programming guide can be found here and installation instructions can be found here.

I’m going to demonstrate a small sample that can scan a windows directory for sql scripts, read database connection information from a config file and execute scripts against the configured database.

The Basic Solution using SMO

Prerequisites: Install the SQL Server SDK from the link above. You should now have a directory containing the assemblies that expose the API under “C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies” [Assuming you are on Windows 7 64 bit, other versions would place the assemblies in a similar path].

Step 1: Create a helper class that encapsulates the functionality of SMO

Lets create a new C# solution of type “console application” and add a class library project to it. In this class library project, add references to the following:

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

What we are trying to do is obtain an instance of Microsoft.SqlServer.Management.Smo.Server class and use the ConnectionContext object’s ExecuteNonQuery method to run a batch SQL script delimited by GO statements. Following is the code for our class which should be easily understandable:

public class SqlConnectionHelper
 {
      private readonly string _connectionString;
      private readonly SqlConnectionStringBuilder _connectionStringBuilder;</pre>
      /// <summary> Initializes a new instance of the <see cref="SqlConnectionHelper"/> class. </summary>
      /// <param name="connectionString">The connection string.</param>
      public SqlConnectionHelper(string connectionString)
      {
         _connectionString = connectionString;
         _connectionStringBuilder = new SqlConnectionStringBuilder(_connectionString);
       }

       /// <summary> Initializes a new instance of the <see cref="SqlConnectionHelper"/> class. </summary>
       /// <param name="sqlConnectionStringBuilder">The SQL connection string builder.</param>
       public SqlConnectionHelper(SqlConnectionStringBuilder sqlConnectionStringBuilder)
       {
         _connectionString = sqlConnectionStringBuilder.ToString();
         _connectionStringBuilder = sqlConnectionStringBuilder;
       }

       /// <summary> Gets the connection string. </summary>
       /// <value> The connection string. </value>
       public string ConnectionString
       {
         get { return _connectionString; }
       }

       /// <summary> Gets the name of the database server (instance name). </summary>
       /// <value> The name of the server. </value>
       public string ServerName
       {
         get { return _connectionStringBuilder.DataSource; }
       }

       /// <summary> Gets the name of the database. </summary>
       /// <value> The name of the database. </value>
       public string DatabaseName
       {
         get { return _connectionStringBuilder.InitialCatalog; }
       }

       /// <summary> Gets a new <see cref="Server"/> connection. </summary>
       /// <returns> A new instance of Microsoft.SqlServer.Management.Smo.Server</returns>
       public Server GetServer()
       {
         var sqlConnection = new SqlConnection(_connectionString);
         return new Server(new ServerConnection(sqlConnection));
       }

       /// <summary> Executes the SQL script. </summary>
       /// <param name="scriptText">The script text.</param>
       public void ExecuteScript(string scriptText)
       {
         var server = GetServer();
         server.ConnectionContext.ExecuteNonQuery(scriptText);
       }
 }

Step 2: The Console app

Now in the Console application project, let’s modify the code to look like this:

class Program
 {
     static void Main(string[] args)
     {
        Console.WriteLine("***************************************************************************************");
        Console.WriteLine("Script Directory: {0}", SqlFileDirectory);
        Console.WriteLine("Connection String: {0}", SqlConnectionString);
        Console.WriteLine("***************************************************************************************");</pre>
        var sqlConnectionHelper = new SqlConnectionHelper(SqlConnectionString);
        var directoryInfo = new DirectoryInfo(SqlFileDirectory);
        FileInfo[] scriptFiles = directoryInfo.GetFiles();</pre>
        foreach (var scriptFile in scriptFiles)
        {
           var fileInfo = new FileInfo(scriptFile.FullName);
           Console.WriteLine("Executing: {0}", scriptFile.FullName);
           string script = fileInfo.OpenText().ReadToEnd();
           try
           {
              sqlConnectionHelper.ExecuteScript(script);
           }
              catch (Exception exception)
           {
             Console.WriteLine("Could not execute {0} due to error {1}", scriptFile.FullName, exception.Message);
           }
         }
         Console.WriteLine("Press any key to exit");
         Console.ReadKey();
     }

     private static string SqlFileDirectory
     {
        get
          {
            var appSettings = ConfigurationManager.AppSettings;
            string sqlDirectory = appSettings.AllKeys.Contains("sqlFileDirectory")
                                  ? appSettings["sqlFileDirectory"]
                                  : @"D:\SqlBatch";
            return sqlDirectory;
          }
      }

     private static string SqlConnectionString
     {
        get
        {
           return ConfigurationManager.ConnectionStrings["currentConnection"].ConnectionString;
        }
     }
  }
}

As can be seen, it is pretty simple to execute scripts programmatically. This program can be scheduled via windows task scheduler and we should be good!

The Gotchas

There are always gotchas! The important ones I found were that the SQL Server libraries are compiled against version 2.0 of the .NET framework. What that means is that if you are targeting .NET framework 4 or higher, you must activate legacy V2 runtime activation policy or be ready for surprising ugly error messages. I ran into one of these which complained about a missing assembly. As you can see, the real issue is not easily guessable from the exception. The solution is to have this section in your app.config: (note the useLegacyV2RuntimeActivationPolicy=”true”)

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

Postscript

Now (as one would expect) there are other super developers who’ve run into the same scenario and devised a solution. One such gentleman is Phil Haack. He is kind enough to share his SQL library with us mere mortals. You can find information about his work here.

There’s also a good stackoverflow discussion around the general topic of this post.

Happy Coding

Advertisements
Tagged with: , , , ,
Posted in .NET, C#, MSSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Comic for March 28, 2017
    Dilbert readers - Please visit Dilbert.com to read this feature. Due to changes with our feeds, we are now making this RSS feed a link to Dilbert.com.
%d bloggers like this: