How to execute multiple database script files from a specific directory

• Download ScriptExecutor.zip  

Introduction 

Many times we need to execute multiple script files from a specific directory/subdirectory. I will explain very simple way to execute multiple script files (*.sql) from a directory.

Using the code 

I use a simple ADO.NET connection and command object. I also use System.Transactions.TranscationScope object to manage transaction. Just one thing you should remember is the GO statement. Actually it is not a TSQL statement. So I need to remove that. But if you use osql, Enterprise Manager etc., then they support Go statement as a separator. 

public static OperationResult ExecuteScript(string connectionString, string directory, string scriptFileExtension)
{
    string[] files = Directory.GetFiles(directory, scriptFileExtension, SearchOption.AllDirectories);
    if (null == files || files.Length == 0)
        return new OperationResult(false, "no script file found to directory " + directory);
    var result = new OperationResult(true, null);
    var to = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted };
    using (var scope = new TransactionScope(TransactionScopeOption.Required, to))
    {
        using (var conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandTimeout = 0;//unlimited
                var regex = new Regex(Environment.NewLine + "go", RegexOptions.IgnoreCase);
                foreach (string fileFullName in files)
                {
                    string content = File.ReadAllText(fileFullName);
                    //remove go statement from script because it is not tsql statement.
                    content = regex.Replace(content, string.Empty);
                    cmd.CommandText = content;
                    if (conn.State == System.Data.ConnectionState.Closed)
                        conn.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        return new OperationResult(false, "File Name: " + fileFullName  + "  Error Message:" + ex.Message);
                    }
                }
            }
        }
        if (result.Success)
            scope.Complete();
    }
    return result;
}  

Client code 

string scriptDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ScriptFiles");
bool exists = Directory.Exists(scriptDirectory);
string connString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
OperationResult result = ScriptExecutor.ExecuteScript(connString, scriptDirectory, "*.sql");

if (result.Success)
    Console.WriteLine("Scripts executed successfully");
else
    Console.WriteLine("Failed to execute:" + result.Error);

Console.ReadKey(); 

Conclusion 

I have shown this with a console application. You can build any tool with window/WPF technology or anything else that you might be easily reusable and disputable. I uploaded source code that is developed by Visual Studio 2012 with .NET Framework 4.5 But no such framework feature I used there. So with any framework version you can use it.