Executing multiple SQL statements with SqlCommand
August 15, 2009 | In Development |If you try and execute a number of sql statements in one go using the SqlCommand object, you will receive a SqlException: “Incorrect syntax near…” when you call the ExecuteNonQuery().
This is because the “GO” statements cannot be executed within the script. The simple way around this is to break the script into parts by splitting the string on the “GO” statements. Then you can execute the individual commands.
Some sample code:
using(SqlConnection connection = new SqlConnection(connectionString))
{
string[] commands = sql.Split(new string[]{"GO\r\n", "GO ", "GO\t"},
StringSplitOptions.RemoveEmptyEntries );
foreach (string c in commands)
{
command = new SqlCommand(c, connection);
command.ExecuteNonQuery();
}}