Using automation when executing SQL scripts stored in separate files

When working on a big projects, sometimes there is a need to update database from the sql files scattered around multiple files or folders especially when using source control for maintaining database changes. Also when using auto builds via CI server this functionality seems to be useful.

In this article I will show you how to automate the process of updating multiple database sql files so you can do it in one go – like that:

  //this is command line executed by TeamCity build (change params with your build configuration values)
 c:\\UpdateScripts.exe "\scripts\Procedures" %Server% %Database% %ToBeReplacedSelectUser% %DatabaseSelectUser% "exec dbo.proc_GrantExecutePermissionsOnProcedures '%DatabaseUser%'"

Lets create C# console application first. Once that is done we can implement basic validation rules for parameters being passed in. In our example we will support 6 parameters: Server, Database, StringToFind, StringToReplace, DatabaseUser, FinalQuery (we will use windows integrated database connection).

    if (args.Length == 0)
    {
        Console.WriteLine("No arguments passed!");
        Console.WriteLine(Environment.NewLine);
        Console.WriteLine(getHelp());
        Console.ReadLine();
        return 1;
    }

    if (args.Length < 3)
    {
        if (!silent)
        {
            Console.WriteLine("Not all required arguments passed!");
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine(getHelp());
            Console.ReadLine();
        }
        return 1;
    } 
    ///////////////
    var scriptPath = args[0];
    var server = args[1];
    var database = args[2];
    var findString = args[3];
    var replaceString = args[4];
    var endQuery = args.Length >= 5 ? args[5] : string.Empty;
    /////////////

    if (!Directory.Exists(scriptPath) || Directory.GetFiles(scriptPath).Length == 0)
    {
        if (!silent)
        {
            Console.WriteLine("Directory does not exist or is empty!");
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine(getHelp());
            Console.ReadLine();
        }
        return 1;
    }

The code below will get all .sql files from the directory and execute containing queries. If an file has “GO” statements inside then it needs to be executed in one block to avoid errors. Normally when executing such a scripts, Sql Management Studio cares about that. In our case we simply need to split content by “GO” blocks.

Console.WriteLine("params to find: " + findString);
Console.WriteLine("replace string: " + replaceString);

using (var con = new SqlConnection("Server=" + server + ";Database=" + database + ";Trusted_Connection=True;"))
{
    con.Open();

    var files = Directory.GetFiles(scriptPath).Where(f => f.ToLower().EndsWith(".sql"));

    foreach (var filePath in files)
    {
        #region execute query from file
        var query = File.ReadAllText(filePath);

        if (findString.Length > 0)
        {
            query = query.Replace(findString, replaceString);
        }
        Console.WriteLine("Executing: " + Path.GetFileName(filePath));

        query = query.Replace("go\r\n", "GO\r\n");
        query = query.Replace("go\t", "GO\t");
        query = query.Replace("\ngo", "\nGO");

        //divide into GO groups to avoid errors
        var itemQuery = query.Split(new string[] { "GO\r\n", "GO\t", "\nGO" }, StringSplitOptions.None);

        foreach (var queryItem in itemQuery)
        {
            if (queryItem.Trim().Length < 3) { continue; }

            using (var cmd = new SqlCommand(queryItem, con))
            {
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    if (!silent)
                    {
                        Console.WriteLine("Error: " + ex.Message);
                    }
                    exitResult = 1;
                }
            }
        }
        #endregion
    }

At the end we can execute final sql statement. It is useful when for example there is a need to assign database permissions after the update is performed.

 //////////////////////execute final query (e.g permission update)
if (endQuery.Trim().Length > 3)
{
    using (var cmd = new SqlCommand(endQuery, con))
    {
        try
        {
            Console.WriteLine("Executing final query: " + endQuery);
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
            return 1;
        }
    }
}

The Console.WriteLine output will also be picked up by the TeamCity build log, so you can see all update information when needed. I have attached solution files, feel free to adjust it to your needs 🙂
UpdateDBScripts