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");

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