Click here to Skip to main content
11,796,275 members (76,459 online)
Click here to Skip to main content

How to execute multiple database script files from a specific directory

, 18 Feb 2013 CPOL 8.5K 397 7
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
IXORA Solution Ltd.
Bangladesh Bangladesh
Mostly I work with MS technologies (ASP.NET MVC, WPF, C#, SQL Server, SSRS, SharePoint, Entity Framework, MSTest, Enterprise Library, MEF, WCF, WebAPI, MS Excel, IIS).
Non MS technologies which I love and use (Resharper, NHiberNet, JQuery, AngularJS, KnockoutJS, NodeJS, Python, MSpec, RihnoMock, Crystal Report, Subversion, Crome)

You may also be interested in...

Comments and Discussions

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