Running embedded SQL file from C# gets errors, but running from SSMS does not

Question

I have a simple .sql file that looks like this:

    USE [master]
    GO

    CREATE DATABASE [NEW_DATABASE]
    GO

When I run it from SSMS, it works fine, the NEW_DATABASE database is created. However, I am trying to run this command by using it as an embedded resource in a C# program. The final version of this .sql file will be a lot more complex but, for now, I'm just trying to get this simple script to work.

Here are the steps that I've taken:

1) I added a resource file (named SqlFiles) to my project.

2) I opened the resource file within VS and added a new file
resource, "create.sql".

3) I saved the resource file.

Then, in my code, I have the following:

// If database doesn't exist, run the script to create it.
if (dbExists == false)
{
    using (var connection = new SqlConnection("Data Source=" + computerName + "\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True"))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = SqlFiles.create;
        command.ExecuteNonQuery();
    }  
}

When I run the program, I get the following error when the command is executed:

Incorrect syntax near 'GO'.

Yet, as I stated, if I open this same file in SSMS and execute it, it works without issue.

I do notice that when I hover over the command variable in debug, the value of the CommandText appears to have formatting text in it (return characters, rather):

USE [master]\r\nGO\r\nCREATE DATABASE [NEW_DATABASE]\r\nGO\r\n

Could this be this issue?

Answer 1

The GO keyword is not T-SQL, or in other words the method of how your c# is interacting with the database. Go is a a Sql Server Managent Studio keyword that tells the SQL script to be executed as separate commands. So SSMS runs in the script in batches separated by the GO's.

You need to separate and run the batches individually on your own and tell each to execute. Alternatively, if the script is not generated, I store them as a stored procedure and pass my arguments to that.