Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Tip/Trick

How to execute multiple database script files from a specific directory

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
18 Feb 2013CPOL 17.8K   621   8  
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. 

C#
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 

C#
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Bangladesh Bangladesh
How do I describe myself to you? How can I explain that this is true?
I am who I am because of you! My work I love you !!

Comments and Discussions

 
-- There are no messages in this forum --