Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

How to execute multiple database script files from a specific directory

0.00/5 (No votes)
18 Feb 2013 1  
I will explain very simple way to execute multiple script files (*.sql) from a directory.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here