Execute sql file on a SQL Server using C#

c#, sql, sql-server

Solution

This is how we do it:

    protected virtual void ExecuteScript(SqlConnection connection, string script)
    {
        string[] commandTextArray = System.Text.RegularExpressions.Regex.Split(script, "\r\n[\t ]*GO");

        SqlCommand _cmd = new SqlCommand(String.Empty, connection);

        foreach (string commandText in commandTextArray)
        {
            if (commandText.Trim() == string.Empty) continue;
            if ((commandText.Length >= 3) && (commandText.Substring(0, 3).ToUpper() == "USE"))
            {
                throw new Exception("Create-script contains USE-statement. Please provide non-database specific create-scripts!");
            }

            _cmd.CommandText = commandText;
            _cmd.ExecuteNonQuery();
        }

    }

Load the contents of your script using some file-reading function.

Problem

I and trying to create a method to run a .sql file on an SQL Server database. The code i have is: ``` SqlConnection dbCon = new SqlConnection(connstr); FileInfo file = new FileInfo(Server.MapPath("~/Installer/JobTraksDB.sql")); StreamReader fileRead = file.OpenText(); string script = fileRead.ReadToEnd(); fileRead.Close(); SqlCommand command = new SqlCommand(script, dbCon); try { dbCon.Open(); command.ExecuteNonQuery(); dbCon.Close(); } catch (Exception ex) { throw new Exception("Failed to Update the Database, check your Permissions."); } ``` But i keep getting errors about "incorrect syntax near keyword 'GO'" My SQL File starts like this: (Generated from SQL Management Studio) ``` SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Job_Types]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_JobTypes] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ``` How should i be executing this script?

Original source