.net 如何使用EF/SQL Server调用API中的存储过程，并在处理ResultSet之前检查RetVal？

9avjhtql 于 2023-02-06 发布在 .NET

关注(0)|答案(2)|浏览(142)

我已经将Equipment模型的.NET Core 6 API缩减为两列进行测试：Equipment_Id和Description。在POST（Add）期间，SQL Server存储过程将在成功时返回值0或2，并且一切正常。
如果返回值为1（error），并显示错误消息（例如：“Equipment already exists”），转换到Equipment失败，因为没有结果集，并且发生异常。我想在生成异常时检查返回值（dbParam）。
如何执行存储过程，检查返回值，然后将结果集转换为List<Equipment>？

try
{
    // snipped dbParameters creation
    string CommandText = "EXEC @ReturnValue=sp_API_Model_Equipment_WRITE @Equipment_Id=@Equipment_Id OUTPUT,@Description=@Description,@ErrMsg=@ErrMsg OUTPUT";

    var results = await _DbContext.Equipment.FromSqlRaw(CommandText, dbParameters.ToArray()).ToListAsync(); 

    // If the stored procedure detects a problem and returns value 1 (error), 
    // there is no resultset and an exception is generated:
    //
    // Microsoft.EntityFrameworkCore.Query: Error: 
    // An exception occurred while iterating over the results of a query for context type 'myAPI.Data.DBContext'.
    // System.InvalidOperationException: The required column 'Equipment_Id' was not present in the results of a 'FromSql' operation.
    // at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.BuildIndexMap(IReadOnlyList`1 columnNames, DbDataReader dataReader)
    // at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
    // at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
    // at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
    //

    int ReturnValue = (int)paramReturnValue.Value;

    switch (ReturnValue)
    {
        case 0: // Success
            if (results.Count == 0)
                return NotFound();  // 404

            return CreatedAtAction("GetEquipmentById", new { Equipment_Id = results[0].SerialNum }, results[0]);  // 201 Created

        case 1: // Error
            return BadRequest(paramErrMsg.Value);

        case 2: // Warning (Already Exists)
            if (results.Count == 0)
                return NotFound();  // 404 (Internal Failure)

            return Ok(results[0]);  // 200 OK

        case 99: // Exception
            return StatusCode(StatusCodes.Status500InternalServerError, "Internal exception occurred");
    }

    return BadRequest($"Unknown ReturnValue ${ReturnValue}.");
}
catch (Exception ex)
{
    clsDebug.WriteDebugExc(ex);

    return StatusCode(StatusCodes.Status500InternalServerError, "Internal exception occurred.");
}

.net

来源：https://stackoverflow.com/questions/73790185/how-to-call-stored-procedure-in-api-using-ef-sql-server-and-check-retval-before

2条答案

按热度按时间

bmp9r5qi1#

20/20后见之明。我太努力地想让EF/LINQ（FromSqlRaw/ToListAsync）方法工作，这时一个使用常用Microsoft.Data.SqlClient的更干净的解决方案正盯着我的脸。
我现在可以调用proc，查看ReturnValue，只有当有数据行时才将它们转换为'List。
我在这里发布了修改后的代码：

try
{
    using (var cmd = _DbContext.Database.GetDbConnection().CreateCommand())
    {
        var paramReturnValue = new SqlParameter();

        paramReturnValue.ParameterName = "@ReturnValue";
        paramReturnValue.DbType = DbType.Int32;
        paramReturnValue.Value = 0;
        paramReturnValue.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(paramReturnValue);

        //----------------------------------------------------------------------------------------
        var paramErrMsg = new SqlParameter();

        paramErrMsg.ParameterName = "@ErrMsg";
        paramErrMsg.DbType = DbType.String;
        paramErrMsg.Value = "";
        paramErrMsg.Size = 256;
        paramErrMsg.Direction = ParameterDirection.Output;

        cmd.Parameters.Add(paramErrMsg);

        //----------------------------------------------------------------------------------------

        cmd.CommandText = "sp_API_Model_Equipment_WRITE";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        if (cmd.Connection.State == ConnectionState.Closed)
            await cmd.Connection.OpenAsync();

        await cmd.ExecuteScalarAsync(); // Execute the Proc without Throwing Exc on No Data Rows

        int ReturnValue = (int)paramReturnValue.Value; // Get Return Value from Proc

        switch (ReturnValue)
        {
            case int n when (n == 0 || n == 2): // Success or Already Exists -- Data Rows Exists
                DataTable dt = new DataTable();
                SqlDataAdapter dataAdapter = new SqlDataAdapter((SqlCommand)cmd);  // dbCommand inherits from 
                dataAdapter.Fill(dt);

                var dv = dt.ToDataList<Equipment>();  // create new List<T> from DataTable

                if (dv.Count == 0)
                    return NotFound();  // 404

                if(ReturnValue == 2)
                    return Ok(dv[0]);  // 200 OK (Already Exists)

                return CreatedAtAction("GetEquipmentById", new { Equipment_Id = dv[0].SerialNum }, dv[0]);  // 201 Created

            case 1: // Error
                return BadRequest(paramErrMsg.Value); // User Error Message created by Proc

            case 99: // Exception (SQL Catch)
                clsDebug.WriteDebugError($"SQL Failure: {ReturnValue} -- {paramErrMsg.Value}"); // Log System Message

                return StatusCode(StatusCodes.Status500InternalServerError, "Internal exception occurred"); // Return User Message
        }

        clsDebug.WriteDebugError($"SQL Failure: Unknown Return Value {ReturnValue} -- {paramErrMsg.Value}"); // Log System Message

        return BadRequest($"Unknown ReturnValue ${ReturnValue}."); // Return User Message
    }
}
catch (Exception ex)
{
    clsDebug.WriteDebugExc(ex); // Log Exception

    return StatusCode(StatusCodes.Status500InternalServerError, "Internal exception occurred."); // Return Neutered Message
}

赞(0）回复(0）举报 2023-02-06

u7up0aaq2#

在这种情况下，您可以尝试返回一个空结果集，类似于：

SET NOCOUNT ON 
EXEC @ReturnValue=sp_API_Model_Equipment_WRITE @Equipment_Id=@Equipment_Id OUTPUT,@Description=@Description,@ErrMsg=@ErrMsg OUTPUT";

if @ReturnValue = 1
begin
  select * from equipment where 1=0;
end

赞(0）回复(0）举报 2023-02-06

我来回答

.net 如何使用EF/SQL Server调用API中的存储过程，并在处理ResultSet之前检查RetVal？

2条答案

相关问题

热门标签

最新问答