Click here to Skip to main content
13,193,048 members (64,609 online)
Rate this:
Please Sign up or sign in to vote.
I have 90 scripts in C:\SQL Scripts\*sql

how could i execute by command ,i saw many things on net but could execute it successfully,,,
I am using sql 2008 R2 , not a default instance
Posted 16-Feb-13 3:48am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

first write a class which will execute script as follows
public class ScriptExecutor
    public static void ExecuteScript(string connectionString, string directory)
        string[] files = Directory.GetFiles(directory, "*.sql", SearchOption.AllDirectories);
            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
                        foreach (string fileFullName in files)
                            string content = File.ReadAllText(fileFullName);
                            //remove go statement from script because it is not tsql statement.
                            content = content.ToLower().Replace("\r\ngo", "\r\n");
                            cmd.CommandText = content;
                            if (conn.State == System.Data.ConnectionState.Closed)
                            //throw new ApplicationException("Exception!!!");
        catch (Exception ex)
            throw ex;

How to execute this code
string scriptDirectory = @"D:\ScriptDir";
string connString = "Data Source=HABIB-PC; Initial Catalog=**; user Id=**; Password=***;";
ScriptExecutor.ExecuteScript(connString, scriptDirectory);
Maciej Los 16-Feb-13 12:58pm
Nice, +4! ;)
Why 4? Because of no errors handler. I'll re-vote, if you change it.
S. M. Ahasan Habib 16-Feb-13 22:05pm
thanks for your comment. Added exception handling and transaction block.
Maciej Los 17-Feb-13 6:24am

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.171017.2 | Last Updated 16 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100