in .NET Core and Dapper, can you specify a lifetime/scope to a SqlConnection?

asp.net-core c# dapper dependency-injection

Question

To create a new object, I'm using.NET Core Dependency Injection.SqlConnection object during application launch and intend to inject it into my repository afterwards. ThisSqlConnection will be used by Dapper to read and write information from the database within my repository implementation. I'm going to employasync calling Dapper.

Should I administer the injection?SqlConnection as a singleton or as a transient? Taking into account that I want to utilizeasync Transient would be my choice until Dapper supports isolation containers internally, in which case my singleton's scope would still be contained to the scope that Dapper employs internally.

Are there any suggestions or best practices for how long the SqlConnection object should last while using Dapper? Are there any warnings that I could be overlooking?

I appreciate it.

1
13
3/21/2017 8:54:12 PM

Accepted Answer

If you supply a single SQL connection, you cannot fulfill several requests concurrently unless you activate MARS, which has its own restrictions. Using temporary SQL connections and making sure they are appropriately disposed of is recommended.

In my applications, I provide uniqueIDbConnectionFactory to the repositories used to establish connections withinusing statement. In this situation, the repository itself may be a singleton to save memory allocations.

7
3/21/2017 9:00:46 PM

Popular Answer

In both the response and the reply, I agree with @Andrii Litvinov.

In this case I would go with approach of data-source specific connection factory.

I'll discuss a another method using the same methodology: UnitOfWork.

Refer DalSession and UnitOfWork respond from zzz-21 zzz. This deals with connections.
Refer BaseDal respond from zzz-31 zzz. This is how I've done it.Repository (actually BaseRepository ).

  • UnitOfWork is administered as a brief period.
  • Separate databases might be created to handle various data sources.DalSession every data source.
  • UnitOfWork is administeredBaseDal .

Are there any recommendations/best practices regarding the lifetime of the SqlConnection object when working with Dapper?

The majority of developers agree that connections should be made as quickly as feasible. Here, I see two strategies:

  1. per-action connection
    Of course, the lifespan of this link will be the shortest. You seal off a link.using for each action, a block. As long as you don't wish to group the activities, this strategy is good. Most of the time, you can utilize transactions even when you wish to aggregate the operations.
    The issue arises when you wish to combine actions from several classes and methods. You cannot useusing block this. The UnitOfWork solution is as follows.
  2. Connection for every work unit.
    Define your work unit. This will vary depending on the application. "Connection per request" is a popular strategy in web applications.
    This makes more sense since, in general, there are groups of activities we wish to carry out collectively most of the time. Two of the sites I posted above clarify this.
    This method also has the benefit of giving the application (using DAL) greater control over how the connection is utilized. And from what I gather, the application is more knowledgeable about connection use than the DAL.


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow