使用EF Core调用存储过程并关闭连接 [英] Using EF Core to invoke stored procedure and closing connection

问题描述

我有一个使用EF Core的ASP.NET Core 2.2应用程序。我有服务类，通常对任何CRUD操作使用 DbContext 。但是，在其中一种方法（下面的 Assign 方法）中，我需要使用存储过程。所以我正在使用以下代码。请注意，将 DbContext 作为 Scoped 实例注入。

I have an ASP.NET Core 2.2 application using EF Core. I have service class which typically use a DbContext for any CRUD operations. However in one of the method (Assign method below) I need to use stored procedure. So I am using the following code. Note that DbContext is injected as Scoped instance.

public class MyService : IMyService
{
   private readonly MyDbContext _dbContext;

   public MyService(MyDbContext dbContext)
   {
      _dbContext = dbContext;
   }

   public async Task<Employee> GetByID(int id)
   {
      return await _dbContext.Employees.FindById(id);
   }

   public async Task<int?> Assign(int itemID, int userID)
   {
        using (var cmd = _dbContext.Database.GetDbConnection().CreateCommand())
        {
            var p1 = new SqlParameter("@ItemId", SqlDbType.Int);
            p1.Value = itemID;

            var p2 = new SqlParameter("@UserID", SqlDbType.Int);
            p2.Value = userID;

            cmd.CommandText = "dbo.prcAssign";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);

            await _dbContext.Database.OpenConnectionAsync();

            var result = await cmd.ExecuteScalarAsync();

            if (result != null)
            {
                return Convert.ToInt32(result);
            }

            return null;                
        }
    }
}

存储过程正在使用内部SQL事务。我想知道是否需要在 Assign 方法中显式关闭数据库连接，否则在请求结束时容器将自动关闭该连接？

The stored procedure is using a SQL transaction internally. I wanted to know if I need to explicitly close the DB connection in the Assign method or the connection will be automatically get closed on request end by the container?

问题在于，由于存储过程正在使用事务，因此将读锁定置于表上（我们实际上希望对表进行读锁定以避免弄脏数据）。该存储过程执行需要几毫秒的时间，而我们几乎没有50个用户。但是，我们经常在 Employee 表上陷入僵局：

Issue is since the stored procedure is using a transaction, it's putting read lock on the table (we actually want the read lock on the table to avoid getting dirty data). The stored procedure takes a few milliseconds to execute and we hardly have 50 users. However very often we get deadlock on Employee table:

System.Data.SqlClient .SqlException（0x80131904）：事务（进程ID 59）在锁资源上与另一个进程死锁，并被选择为死锁受害者。重新运行事务。

，位于System.Data.SqlClient.SqlConnection.OnError（SqlException异常，布尔值breakConnection，操作 1 wrapCloseInAction）
，位于System.Data。 SqlClient.SqlInternalConnection.OnError（SqlException异常，布尔值breakConnection，操作 1 wrapCloseInAction）

在System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject> stateObj，布尔值调用程序HasConnectionLock，布尔值asyncClose ）

在System.Data.SqlClient.TdsParser.TryRun（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，布尔值和DataReady）

在System.Data。 SqlClient.SqlDataReader.TryConsumeMetaData（）

在System.Data.SqlClient.SqlDataReader.get_MetaData（）

在System.Data.SqlClient.SqlCommand.FinishExecuteReader（SqlDataReader ds，RunBehavior runBehavior，字符串resetOptionsString）

，位于System.Data.SqlClient。 SqlCommand.CompleteAsyncExecuteReader（）在System.Data.SqlClient.SqlCommand.InternalEndExecuteReader（IAsyncResult，String endMethod）处
在System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal（IAsyncResult asyncResult）中的

System.Data.SqlClient.SqlCommand.EndExecuteReader（IAsyncResult asyncResult）上的
> System.Threading.Tasks.TaskFactory 1.FromAsyncCoreLogic（IAsyncResult iar，Func 2 endFunction，操作 1 endAction，任务 1 promise，布尔值requireSynchronization）

---从上一个引发异常的位置开始的堆栈结束跟踪- -

System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReader(IAsyncResult asyncResult) > at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---

在xxxxx.Services.MyService.Assign（Int32 itemID，Int32 userID）在D：\xxxx\Services\MyService.cs：line 84

at xxxxx.Services.MyService.Assign(Int32 itemID, Int32 userID) in D:\xxxx\Services\MyService.cs:line 84

推荐答案

我想知道是否需要显式关闭 Assign 方法中的数据库连接，否则连接将自动关闭n请求是通过容器结束的吗？

I wanted to know if I need to explicitly close the DB connection in the Assign method or the connection will be automatically get closed on request end by the container?

是否稍后会自动关闭连接都没有关系。按照良好的编程习惯，应该释放分配的资源（创建->处置，打开->关闭等）。

It doesn't matter if the connection will be closed automatically at some later point or not. Following the good programming practices, one should release the allocated resources (Create -> Dispose, Open -> Close etc.).

EF Core内部为需要开放连接的每个操作执行此操作，因此最好这样做，例如像这样的

EF Core internally is doing that for each operation which needs open connection, so you'd better do the same, e.g. something like this

await _dbContext.Database.OpenConnectionAsync();
try
{
    var result = await cmd.ExecuteScalarAsync();

    if (result != null)
    {
        return Convert.ToInt32(result);
    }

    return null;
}
finally
{
    _dbContext.Database.CloseConnection();
}

这篇关于使用EF Core调用存储过程并关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！