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)

FYI, you should avoid using the sp_ prefix; it's reserved by Microsoft for Special procedures, so your SP could suddenly stop working one day (If Microsoft introduce a Special Procedure with the name your SP), and using sp_ comes with a performance hit: Is the sp_ prefix still a no-no? — Thom A
– Thom A ♦, Commented Dec 23, 2019 at 11:10
It's much better if you create a database project in Visual Studio and instead deploy the resulting DACPAC rather than attempting to do so via code. It's essentially the same as avoiding a brute-force technology like say FTP to deploy a website by instead using MSDeploy. Besides, deploying the contents of an unknown string is arguably just as dangerous (if not more) as embedded SQL statements in code — user585968
– user585968, Commented Dec 23, 2019 at 11:15

Lukasz Szozda · Accepted Answer · 2019-12-23 11:19:04Z

4

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.

edited Dec 23, 2019 at 11:19

answered Dec 23, 2019 at 11:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marlonchosky Over a year ago

Thank you very much!!! Wrapping around the code was a clever idea!

GvS · Accepted Answer · 2019-12-23 11:16:07Z

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
                }

Dan Guzman · Accepted Answer · 2019-12-23 12:02:56Z

Consider using SQL Server Management Objects. Unlike SqlClient, the SMO includes methods that regognize GO batch separators. SMO is available as a NuGet package.

Below is an example console app that executes a script with GO batch separators with the Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery method.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;

class Example
{

    static void Main(string[] args)
    {
            var script = @"
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
";
        try
        {
            using (var connection = new SqlConnection("Data Source=YourServer;Integrated Security=SSPI;Initial Catalog=YourDatabase"))
            {
                var serverConnection = new ServerConnection(connection);
                connection.Open();

                serverConnection.ExecuteNonQuery(script);
            }
        }
        catch
        {
            throw;
        }
    }
}

Collectives™ on Stack Overflow

How to deploy stored procedure via C# code?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related