SQL Scripts Runner

• Download SQLScriptDBTool.zip - 3.2 MB
• Download DBScript.zip - 1.1 KB

Introduction

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

Background

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

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

Figure 1

Centralised Development Environment has only centralised data storage.

Decentralised Development Environment is the one with the local storage - the dashed box.

In the decentralised environment 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.

Using the code - Implementation 

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<ParseError> errors = new List<ParseError>();
     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
Thread.Sleep(500);

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
{
     /// <summary>
     /// The key
     /// </summary>
 private readonly string key;
Class with properties which have attributes of the custom attrubute type
public class ConfigProperties
{
   /// <summary>
   /// Gets or sets a value indicating whether [use AU s_ AIS].
   /// </summary>
   /// <value>
   ///   <c>true</c> if [use AU s_ AIS]; otherwise, <c>false</c>.
   /// </value>
   [Config("SQLDir")]
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;
          }
   try
   {
   	var value = Convert.ChangeType(ConfigurationManager.AppSettings.Get(auth), prop.PropertyType);
       prop.SetValue(properties, value, null);
   }
   catch (Exception ex)
   {
   	throw ex;
   }
   break;
   }
 }
}