Click here to Skip to main content
15,889,876 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

1 solution

first write a class which will execute script as follows
C#
public class ScriptExecutor
{
    public static void ExecuteScript(string connectionString, string directory)
    {
        string[] files = Directory.GetFiles(directory, "*.sql", SearchOption.AllDirectories);
        try
        {
            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)
                                conn.Open();

                            cmd.ExecuteNonQuery();

                            //throw new ApplicationException("Exception!!!");
                        }
                    }
                }
                scope.Complete();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}


How to execute this code
C#
string scriptDirectory = @"D:\ScriptDir";
string connString = "Data Source=HABIB-PC; Initial Catalog=**; user Id=**; Password=***;";
ScriptExecutor.ExecuteScript(connString, scriptDirectory);
 
Share this answer
 
v4
Comments
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    
+5!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900