CODEWITHSUNDEEP
c#sql-serverentity-framework-coreef-core-2.0ef-core-2.2
LP13
LP13

Reputation: 34149

Using EF Core to invoke stored procedure and closing connection

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

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?

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): 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 ---

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

Upvotes: 2

Views: 2346

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205779

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

Upvotes: 2

mohammadyasin parsi
mohammadyasin parsi

Reputation: 29

The connection is closed automatically when the code exits the using block but If the Sql Connection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if Pooling is set to false or no, the underlying connection to the server is actually closed.

Upvotes: 0

Related Questions