none
How to run a sql script file on Sql server Express edition 2005 using C# code

    Question

  • Hi all,
                I have to run a group of sql commands which are stored in a file (sqlscript.sql) on Sql Server Expression Edition 2005 using my C# code. 

    That sql script contains the creation of Logins to the Sql Server and creating the Users for those logins to the specific database.

    How can I do this. Please help me out.

    Here is the sql script that I want to run on Sql Server Express Editon 2005

    --delete the User [SmartCarLocalSqlServer] from UpLink_Helo

    USE [Master]

    GO

    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'SmartCarLocalSqlServer')

    DROP USER [SmartCarLocalSqlServer]

    GO

    --delete the user [SmartCarUser] from UpLink_Helo

    USE [Master]

    GO

    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'SmartCarUser')

    DROP USER [SmartCarUser]

    --delete the Login [SmartCarLocalSqlServer]

    IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SmartCarLocalSqlServer')

    DROP LOGIN [SmartCarLocalSqlServer]

    --delete the Login [SmartCarUser]

    IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SmartCarUser')

    DROP LOGIN [SmartCarUser]

    --create the Login [SmartCarLocalSqlServer]

    CREATE LOGIN [SmartCarLocalSqlServer] WITH PASSWORD=N'ClubCar2008', DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON,

    CHECK_POLICY=ON

    GO

    --create the Login [SmartCarUser]

    CREATE LOGIN [SmartCarUser] WITH PASSWORD=N'ClubCar2008', DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

    GO

    --Create the User [SmartCarLocalSqlServer] for login [SmartCarLocalSqlServer]

    -- for Master database

    USE [Master]

    GO

    CREATE USER [SmartCarLocalSqlServer] FOR LOGIN [SmartCarLocalSqlServer] WITH DEFAULT_SCHEMA=[dbo]

    Go

    --create the User [SmartCarUser] for Login [SmartCarUser]

    --for Master database

    USE [Master]

    GO

    CREATE USER [SmartCarUser] FOR LOGIN [SmartCarUser] WITH DEFAULT_SCHEMA=[dbo]

    GO

     
    Thursday, December 11, 2008 11:27 AM

Answers

  •         private string[] GetScriptStatements(string p)  
            {  
                string[] statements = p.Split(new string[] { "GO\r\n" }, StringSplitOptions.RemoveEmptyEntries);  
                return statements;  
            }  
     
            private void ExecuteStatements(string[] statements, string connectionString)  
            {  
                if (statements.Length > 0)  
                {  
                    using (SqlConnection conn = new SqlConnection())  
                    {  
                        conn.ConnectionString = connectionString;  
                        conn.Open();  
                        using (SqlCommand command = new SqlCommand(string.Empty, conn))  
                        {  
                            foreach (string statement in statements)  
                            {  
                                command.CommandText = statement;  
                                command.ExecuteNonQuery();  
                            }  
                        }  
                    }  
                }  
            }  
     
            private void RunScripts(string connectionString)  
            {  
                //Drop  
                string[] dropStatements = GetScriptStatements(File.ReadAllText("YOUR FILE", new System.Text.UTF8Encoding()));  
                ExecuteStatements(dropStatements, connectionString);  
            }  
     
    Hi,

      Above is code sample, hope this help.   
    Pongsathon.Keng
    • Proposed as answer by Guo Surfer Monday, December 15, 2008 7:20 AM
    • Marked as answer by Guo Surfer Tuesday, December 16, 2008 9:47 AM
    Thursday, December 11, 2008 2:36 PM
  • You can instantiate a SqlCommand object and a SqlConnection object and submit your script via the SqlCommand.ExecuteNonQuery() method.

    Beware I have tried to submit scripts in the past and the "GO" in the script will cause issues. You can only use the "GO" from inside the Sql Management console ( someone correct me if I am wrong ) and it will cause an exception when you execute the NonQuery call.

    This should get you started. Add your own exception handling etc... Either take the "GO's" out before processing or add string handler to do it in your code.

     
    using (SqlConnection conn = new SqlConnection(yourConnectionString)) 
        using (SqlCommand cmd = new SqlCommand(conn)) 
        { 
             
             cmd.CommandText = File.ReadAllText(yourScriptFileName); 
             cmd.CommandType = CommandType.Text; 
             conn.Open(); 
             cmd.ExecuteNonQuery(); 
             conn.Close(); 
              
        } 

    • Proposed as answer by Guo Surfer Monday, December 15, 2008 7:20 AM
    • Marked as answer by Guo Surfer Tuesday, December 16, 2008 9:47 AM
    Thursday, December 11, 2008 2:36 PM

All replies

  •         private string[] GetScriptStatements(string p)  
            {  
                string[] statements = p.Split(new string[] { "GO\r\n" }, StringSplitOptions.RemoveEmptyEntries);  
                return statements;  
            }  
     
            private void ExecuteStatements(string[] statements, string connectionString)  
            {  
                if (statements.Length > 0)  
                {  
                    using (SqlConnection conn = new SqlConnection())  
                    {  
                        conn.ConnectionString = connectionString;  
                        conn.Open();  
                        using (SqlCommand command = new SqlCommand(string.Empty, conn))  
                        {  
                            foreach (string statement in statements)  
                            {  
                                command.CommandText = statement;  
                                command.ExecuteNonQuery();  
                            }  
                        }  
                    }  
                }  
            }  
     
            private void RunScripts(string connectionString)  
            {  
                //Drop  
                string[] dropStatements = GetScriptStatements(File.ReadAllText("YOUR FILE", new System.Text.UTF8Encoding()));  
                ExecuteStatements(dropStatements, connectionString);  
            }  
     
    Hi,

      Above is code sample, hope this help.   
    Pongsathon.Keng
    • Proposed as answer by Guo Surfer Monday, December 15, 2008 7:20 AM
    • Marked as answer by Guo Surfer Tuesday, December 16, 2008 9:47 AM
    Thursday, December 11, 2008 2:36 PM
  • You can instantiate a SqlCommand object and a SqlConnection object and submit your script via the SqlCommand.ExecuteNonQuery() method.

    Beware I have tried to submit scripts in the past and the "GO" in the script will cause issues. You can only use the "GO" from inside the Sql Management console ( someone correct me if I am wrong ) and it will cause an exception when you execute the NonQuery call.

    This should get you started. Add your own exception handling etc... Either take the "GO's" out before processing or add string handler to do it in your code.

     
    using (SqlConnection conn = new SqlConnection(yourConnectionString)) 
        using (SqlCommand cmd = new SqlCommand(conn)) 
        { 
             
             cmd.CommandText = File.ReadAllText(yourScriptFileName); 
             cmd.CommandType = CommandType.Text; 
             conn.Open(); 
             cmd.ExecuteNonQuery(); 
             conn.Close(); 
              
        } 

    • Proposed as answer by Guo Surfer Monday, December 15, 2008 7:20 AM
    • Marked as answer by Guo Surfer Tuesday, December 16, 2008 9:47 AM
    Thursday, December 11, 2008 2:36 PM
  • This is pretty  strong  code, Thanks for posting it:

     private void ExecuteStatements(string[] statements, string connectionString)  
            {  
                if (statements.Length > 0)  
                {  
                    using (SqlConnection conn = new SqlConnection())  
                    {  
                        conn.ConnectionString = connectionString;  
                        conn.Open();  
                        using (SqlCommand command = new SqlCommand(string.Empty, conn))  
                        {  
                            foreach (string statement in statements)  
                            {  
                                command.CommandText = statement;  
                                command.ExecuteNonQuery();  
                            }  
                        }  
                    }  
                }  
            }  
    Wednesday, June 22, 2011 4:29 PM