c# - 如何通过C#代码部署存储过程？

Question

我有一个像这样的存储过程

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

以及部署它的 C# 方法:

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;
    }
}

一般来说，它将存储过程脚本读取为字符串，并尝试像通常的 SQL 查询一样执行它。一切顺利，直到到达

await insertProcedureCommand.ExecuteNonQueryAsync();

并抛出异常

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

我注意到如果存储过程没有这部分

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

不会引发任何异常，并且该过程将成功部署。所以问题可以表述为:如何通过 C# 代码部署包含 (IF EXISTS-DROP) 逻辑的存储过程？

PS。我知道我可以通过 c# 在另一个 SQL 脚本中删除存储过程，但我想在一个脚本中执行此操作。另请注意，我有 SQL Server 2014，而不是 2016 等较新版本(因为我的公司，我知道它很糟糕)

Answer 1

使用动态 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

<小时/>

不幸的是，您必须将存储过程中的每个 ' 加倍。 T-SQL 不支持here-strings i T-SQL 还没有。