Sql Merge/Consolidator

In this article I will showcase a cool application that would merge/consolidate all the sql files in a folder into a single SQL file that is executable. This tool has been very useful to me to roll out releases into stage and production environments.

This makes life simpler in the sense that you have to run only one sql file as opposed to running multiple SQL script files for every database release.

The two main steps to merge the SQL files are as follows:

1. Use the GetFiles method of the DirectoryInfo class to fetch all the names of SQL files of the folder in where this application executed.

string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
DirectoryInfo dir = new DirectoryInfo(path);
FileInfo[] sqlfiles = dir.GetFiles("*.sql");

2. Loop through the file list, read the content of each file and write it into the consolidation file.

foreach (FileInfo f in sqlfiles)
{
  fileContent = ReadFromFile(f.Name);
  temp_arraylist.Add(fileContent);
}

WriteToFile(temp_arraylist, "AllInOne.sql");

Tips for a successful database release:

1. Make sure that the SQL scripts always successfully pass through the development, QA and stage environments before reaching the production/live environment.

2. Always take a backup of the database before executing a SQL script in production/Stage environment. This might add some overhead, but trust me this habit will help you in the long run. A rollback on an serious error while script execution can be handled efficiently with a backup copy.

3. Make sure that every Sql file has a “GO” at the end, so that the variables in one scripts do not get messed up.

4. Prefix the file names with numbers to define the order of execution. I usually name my files as follows:

• Script_001_firstFile.sql
• Script_002_secondFile.sql
• Script_003_anotherfile.sql …

5. It is good practice to sandwich your code in between a Transaction and Rollback/Commit block. Caution has to be exercised while working with huge amounts of data; you don’t want to follow this practice because, the transaction log could grow and runs out of space causing a crashing of your SQL Server.

Following is the C# code for the Merge tool:

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Collections;

namespace SQLMerge
{
    class Program
    {
        static void Main(string[] args)
        {
            ArrayList al = new ArrayList();

            FileInfo[] sqlfiles = null;
            sqlfiles = GetFileNames();
            string fileContent = string.Empty;
            foreach (FileInfo f in sqlfiles)
            {
                Console.WriteLine(f.Name);
                fileContent = ReadFromFile(f.Name);
                al.Add(fileContent);
            }

            WriteToFile(al, "AllInOne.sql");

            Console.Read();
        }

        static string ReadFromFile(string filename)
        {
            string s = String.Empty;

            try
            {
                // Specify file, instructions, and privelegdes
                FileStream file = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.Read);

                // Create a new stream to read from a file
                StreamReader sr = new StreamReader(file);

                // Read contents of file into a string
                s = "  print 'Begin Executing: " + filename + "' ";
                s = s + sr.ReadToEnd();
                s = s + "  print 'Done Executing: " + filename + "' ";

                // Close StreamReader
                sr.Close();

                // Close file
                file.Close();
            }
            catch (Exception ex)
            {
                Console.Write("Reading of file: " + filename + " has failed because: " + ex.ToString());
                MessageBox.Show("Reading of file: " + filename + " has failed because: " + ex.ToString());
            }

            return s;
        }

        static void WriteToFile(ArrayList al, string filename)
        {
            try
            {
                // *** Write to file ***

                // Specify file, instructions, and privelegdes
                FileStream file = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.Write);

                // Create a new stream to write to the file
                StreamWriter sw = new StreamWriter(file);

                foreach (string s in al)
                {
                    // Write a string to the file
                    sw.WriteLine(s);
                    sw.WriteLine("\n GO \n\n");
                }

                // Close StreamWriter
                sw.Close();

                // Close file
                file.Close();
            }
            catch (Exception ex)
            {
                Console.Write("Writing to file: " + filename + " has failed because: " + ex.ToString());
                MessageBox.Show("Writing to file: " + filename + " has failed because: " + ex.ToString());
            }
        }

        static FileInfo[] GetFileNames()
        {
            string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
            DirectoryInfo dir = new DirectoryInfo(path);
            FileInfo[] sqlfiles = dir.GetFiles("*.sql");
            Console.WriteLine("Total number of sql files to be merged: {0}", sqlfiles.Length);

            return sqlfiles;
        }
    }
}

PS: Dare to Dream and Strive to Achieve