Click here to Skip to main content
13,254,754 members (52,469 online)
Click here to Skip to main content
Add your own
alternative version


8 bookmarked
Posted 18 Feb 2013

How to execute multiple database script files from a specific directory

, 18 Feb 2013
Rate this:
Please Sign up or sign in to vote.
I will explain very simple way to execute multiple script files (*.sql) from a directory.


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)
                    catch (SqlException ex)
                        return new OperationResult(false, "File Name: " + fileFullName  + "  Error Message:" + ex.Message);
        if (result.Success)
    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");
    Console.WriteLine("Failed to execute:" + result.Error);



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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

S. M. Ahasan Habib
Bangladesh Bangladesh
I believe in and stay with living-learning culture. I believe learning is fun. I feel coding is like poetry. I am work with various technologies in my professional career (AngularJS, ASP.NET MVC/WebAPI, Scala-Play Framework, Nodejs, SharePoint, Redshift).

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 18 Feb 2013
Article Copyright 2013 by S. M. Ahasan Habib
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid