Click here to Skip to main content
15,868,141 members
Articles / Programming Languages / SQL

SQL Scripts Runner

Rate me:
Please Sign up or sign in to vote.
4.69/5 (13 votes)
4 Nov 2013CPOL4 min read 36K   1.6K   30   10
A simple console application, which executes all SQL files added by developers working with decentralised development environment.

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? 

Image 1

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.

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).

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;
   }
 }
} 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Softopia
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionPrerrequisites Pin
kiquenet.com3-Dec-13 20:22
professionalkiquenet.com3-Dec-13 20:22 
MS SQL Management Studio is required or only two assemblies Microsoft.Data.Schema.ScriptDom; Microsoft.Data.Schema.ScriptDom.Sql ?

Maybe, if I want use it in a Windows Server 2008, and I don't want install MS SQL Management Studio.

Any suggestions?
kiquenet.com

AnswerRe: Prerrequisites Pin
Lyubomir Rumenov Velchev5-Dec-13 4:19
Lyubomir Rumenov Velchev5-Dec-13 4:19 
SuggestionVS 2010 Version Pin
Jaime Premy12-Nov-13 4:04
professionalJaime Premy12-Nov-13 4:04 
GeneralRe: VS 2010 Version Pin
Lyubomir Rumenov Velchev13-Nov-13 5:11
Lyubomir Rumenov Velchev13-Nov-13 5:11 
SuggestionPlease mention program versions Pin
RickZeeland8-Nov-13 8:14
mveRickZeeland8-Nov-13 8:14 
GeneralRe: Please mention program versions Pin
Lyubomir Rumenov Velchev11-Nov-13 3:51
Lyubomir Rumenov Velchev11-Nov-13 3:51 
GeneralMy vote of 5 Pin
M Rayhan4-Nov-13 22:00
M Rayhan4-Nov-13 22:00 
QuestionFigure 1: Confusing Pin
BloodBaz4-Nov-13 5:22
BloodBaz4-Nov-13 5:22 
AnswerRe: Figure 1: Confusing Pin
Lyubomir Rumenov Velchev4-Nov-13 10:46
Lyubomir Rumenov Velchev4-Nov-13 10:46 
GeneralRe: Figure 1: Confusing Pin
BloodBaz4-Nov-13 14:11
BloodBaz4-Nov-13 14:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.