Running MS SQL scripts from .Net

I was trying to have some C# code execute a database script to set up an MS SQL database, but the script was failing. Google helped me out (a bit lazy this sunday evening) by providing this blog post: How to run a SQL database installer from your .net application, by David Szabo. However, the script still failed me (much later in the script, but still). This time it was more obvious. David's solution to the problem was simply to split the string containing the script on the word "GO". In my case though, the script included the creation of a stored procedure containing a GOTO statement. Since I was splitting the string on the characters "GO", GOTO lost its verb, and the remaining part got to start a new element in my string array of commands to execute. So, I altered the solution slightly to include the carriage-return and line feed characters before and after the GO statement in the script:

        // Runs the given database script on the given connection. The script
        // is divided into commands by splitting the incoming script string
        // on the "GO" word, including the surrounding carriage-return and
        // newline characters, in order to avoid splitting on other occurrances
        // of the characters "GO"
        private static void RunScript(SqlConnection connection, string script)
        {
            string[] commands = script.Split(
                new string[] { "\r\nGO\r\n" },
                StringSplitOptions.RemoveEmptyEntries);

            for (int i = 0; i < commands.Length; i++)
            {
                SqlCommand command = new SqlCommand(commands[i], connection);
                command.ExecuteNonQuery();
                command.Dispose();
            }

        }

The script worked, and I was happy and smiling.

posted on Sunday, December 02, 2007 6:12 PM