How to deploy stored procedure via C# code?

Question

I have a stored procedure like

IF (OBJECT_ID('sp_InsertDevice', 'P') IS NOT NULL) 
    DROP PROCEDURE sp_InsertDevice 
GO

CREATE PROCEDURE sp_InsertDevice
    @serialNumber NVARCHAR(8),
    @modelName NVARCHAR(40),
    @userId INT
AS
BEGIN
    INSERT INTO Device (SerialNumber, ModelName, UserID)
    VALUES (@serialNumber, @modelName, @userId)

    SELECT CAST(SCOPE_IDENTITY() AS INT);
END

and a C# method to deploy it:

protected virtual async Task<bool> DeployStoredProcedure(string storedProcedureName)
{
    try
    {
        var dir = Directory.GetFiles(this.StoredProceduresPath);
        string storedProceduresPath = Directory.GetFiles(this.StoredProceduresPath).Where(x => x.Contains(storedProcedureName)).First();
        string storedProcedureScriptFull = File.ReadAllText(storedProceduresPath);

        SqlCommand insertProcedureCommand = new SqlCommand(storedProcedureScriptFull, this.SqlConnection)
                {
                    CommandType = CommandType.Text,
                    CommandTimeout = this.CommandTimeout
                };

        await this.EnsureConnectionOpened();
        await insertProcedureCommand.ExecuteNonQueryAsync();

        return true;
    }
    catch (Exception exception)
    {
        this.SqlConnection.Close();
        ExceptionDispatchInfo.Capture(exception).Throw();
        return false;
    }
}

In general words, it reads the stored procedure script to a string and tries to execute it like a usual SQL query. Everything goes OK until it reaches the

await insertProcedureCommand.ExecuteNonQueryAsync();

and throws an exception

Incorrect syntax near 'GO'
CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

I noticed that if the stored procedure was without this part

IF (OBJECT_ID('sp_InsertDevice', 'P') IS NOT NULL) 
    DROP PROCEDURE sp_InsertDevice 
GO

no exception would be thrown and the procedure would have deployed successfully. So the question can be stated as: how to deploy stored procedure which contains (IF EXISTS-DROP) logic via C# code?

PS. I know that I can drop the stored procedure via c# in another SQL script but I would like to do it in one script. Also notice that I have SQL Server 2014, not the newer versions like 2016 and so on (because of my company, I know it sucks)

Answer 1

Workaround using dynamic SQL:

IF (OBJECT_ID('sp_InsertDevice', 'P') IS NOT NULL) 
    DROP PROCEDURE sp_InsertDevice 

EXEC(
'CREATE PROCEDURE sp_InsertDevice
    @serialNumber nvarchar(8),
    @modelName nvarchar(40),
    @userId int
AS
BEGIN
    INSERT INTO Device (SerialNumber, ModelName, UserID)
    VALUES (@serialNumber, @modelName, @userId)

    SELECT CAST(SCOPE_IDENTITY() AS INT);
END');

db<>fiddle demo

---

Unfortunately you will have to double every ' inside stored procedure. T-SQL does not support here-strings i T-SQL yet.

Answer 2

GO is not a valid SQL command, and is used to split the SQL into parts that are sequentially executed on the server by the SQL Server manager.

It is however, very easy to use the scripts generated by SQL Server for the distribution of the database scheme.

So I split the scripts generated by SQL myself on the "GO" keyword, and execute them one-by-one.

Something like this (this is a copy from some "very old" code, so you should clean it up a bit by using var etc.):

                Regex regex = new Regex("GO\r\n",RegexOptions.Singleline);
                ArrayList updateCommands = new ArrayList(regex.Split(updateScript));

                using (SqlConnection con = GetNewConnection()) {
                    con.Open();
                    foreach(string commandText in updateCommands) {
                        if (string.IsNullOrWhiteSpace(commandText)) continue;
                        using (SqlCommand cmd = new SqlCommand(commandText, con)) {
                            cmd.ExecuteNonQuery();
                        }
                    } // foreach
                }