如何通过C#代码部署存储过程?-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

如何通过C#代码部署存储过程?

祖安文状元 2020-01-05 14:13:06 156

我有一个类似的存储过程

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();
存储过通过
分享到
取消 提交回答
全部回答(1)
  • 祖安文状元
    2020-01-05 14:13:17

    使用动态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');
    
    0 0
开发与运维
使用钉钉扫一扫加入圈子
+ 订阅

集结各类场景实战经验,助你开发运维畅行无忧

相似问题
最新问题