SQL Scripts Runner


A simple console application, which executes all SQL files added by developers working with decentralised development environment.

For downloading the source code: Codeproject Article


How do you manage SQL scripts in a decentralised development environment?

The Figure below kind of describes the what I had in mind when I refer to a decentralised development environment?

Figure 1 
Decentralised Development Environment is the one with the local storage - the dashed box
Centralised Development Environment has only centralised data storage. 

Usually there is a main database and every developer has his own copy locally.

This type of development environment allows for independent work without the need of a network connection; if there is an error with the main storage, the others will still be able to work with their local database copies.

However a great disadvantage is that during development each developer has to maintain the main database and it’s local one. This means creating a script constantly with the necessary updates. Another option is to create a backup and restore the main database locally (this would take a lot of time and is not considerate as good practice).

So how can one keep track of the Database changes?
In this article I propose two solutions.
  1. Store the script in a common storage available for all developers – for example RedMine (http://www.redmine.org).
  2. Add all the SQL scripts in the project source control.

This first option separates the DB scripts from the concrete project, but does not provide for versioning. 
The second option however allows for versioning and when the latest version of the SQL scripts have been retrieved from the source control the developer can run it as a console executable manually. (It would be ideal if there were a way to do this automatically, this I leave to the general public.). 

What the executable should do?
  • Should be configurable – this should specify the SQL scripts folder.
  • The executable should run the scripts in order.
  • If there is a syntactic error with the script, the application should display it before even running the script - validation.
  • If there is a runtime error with the script all the scripts should roll back. The file and the line at which the error occurred should be displayed.
  • When there is no error then all the scripts should be executed and the changes applied.
  • Should have a way to test the results – simple Unit Testing.

One very important thing to be mentioned is that the SQL script should be written in a way that it can be executed many times (this means having check conditions or a drop and create statement).


Returning a list of SQL files

All the files from a directory and its subdirectories should be returned but in the right order – this means that the files from folder Ver11.0 should be not after Ver1.0 if there is folder with name Ver2.0. This means that the sort order should be based on the values of the versions, but not by the string values.
That’s very easily implemented by a LINQ expression:
foreach (var d in Directory.GetDirectories(dir).OrderBy(x => float.Parse(Path.GetFileName(x).Substring(Configuration.Configuration.Pproperties.ScriptVersionPrefix.Length))))

Display the execution percentage

When \r is used the cursor goes back to the beginning of the current line and then can be rewritten. 

Console.Write("\r{0}{1}% complete", message, percent);

How to find SQL compile time errors?

The parsing capabilities of SQL Server Management Studio were used for this. I have used the two assemblies: Microsoft.Data.Schema.ScriptDom; Microsoft.Data.Schema.ScriptDom.Sql;

These two assemblies can be found in: C:\Windows\Microsoft.NET\assembly\GAC_MSIL
If you have installed MS SQL Management Studio
public static bool ParseSqlFile(string file)
     IList errors = new List();
     bool hasErros = false;
     using (TextReader reader = File.OpenText(file))
         var parser = new TSql100Parser(true);
         var script = parser.Parse(reader, out errors) as TSqlScript;
         hasErros = errors.Count > 0;
         foreach (var parseError in errors)
              Errors.ProcessErrors(file, parseError);
      return hasErros;

Main method of the application

All comes together in the main method. Here after the connection to the database is open, the SQL files are returned in a collection, then for every file its SQL script is parsed and executed as part of transaction so that when the execution is unsuccessful the data can be rolled back.

Please note:
//TODO: remove

This should be removed when it is used in practice. It is good for demo purposes so that the execution percentage can be seen.

The next section is not important for the current project and can be skipped. If after you get the source code you do not understand something about it you can get back to it.

Getting the configuration in OOP way

In “Configuration” folder can be found three files, which are responsible for getting the configuration settings from the App.config in OOP way. A custom attribute defines the key of the setting and after that the attribute is applied to a property, which is set by reflection when the console application starts.

The custom attribute  class:
[AttributeUsage(AttributeTargets.Property, Inherited = false)]
class ConfigAttribute : Attribute

     /// The key

 private readonly string key;
Class with properties which have attributes of the custom attrubute type
public class ConfigProperties

   /// Gets or sets a value indicating whether [use AU s_ AIS].

   ///   true if [use AU s_ AIS]; otherwise, false.

public string SqlDir { get; set; }
In the “Configuration.cs” file is done the reading of the attributes by reflection.
static Configuration()
   properties = new ConfigProperties();
   var props = typeof(ConfigProperties).GetProperties();
   foreach (PropertyInfo prop in props)
       string auth = string.Empty;
       object[] attrs = prop.GetCustomAttributes(true);
       foreach (object attr in attrs)
          var authAttr = attr as ConfigAttribute;
          if (authAttr != null)
             string propName = prop.Name;
             auth = authAttr.Key;
    var value = Convert.ChangeType(ConfigurationManager.AppSettings.Get(auth), prop.PropertyType);
       prop.SetValue(properties, value, null);
   catch (Exception ex)
    throw ex;