Click here to Skip to main content
Rate this: bad
good
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 4:48am
Ali_1001.3K

1 solution

Rate this: bad
good
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);
        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
string scriptDirectory = @"D:\ScriptDir";
string connString = "Data Source=HABIB-PC; Initial Catalog=**; user Id=**; Password=***;";
ScriptExecutor.ExecuteScript(connString, scriptDirectory);
  Permalink  
v4
Comments
Maciej Los at 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 at 16-Feb-13 22:05pm
   
thanks for your comment. Added exception handling and transaction block.
Maciej Los at 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)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 535
1 Kornfeld Eliyahu Peter 407
2 Maciej Los 369
3 DamithSL 221
4 OriginalGriff 218
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,476
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,917


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 16 Feb 2013
Copyright © CodeProject, 1999-2014
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