Dapper with .NET Core - Nested Lifetime / SqlConnection Area

Question

I use .NET Dependency Injection to create an object SqlConnectionduring application startup, which I then plan to insert into my repository. This one SqlConnectionwill be used by Dapper to read / write data from the database in my repository implementation. I am going to use calls asyncwith Dapper.

Question: should I embed SqlConnectionas a transition or as a singleton? Given the fact that I want to use async, my idea would be to use a transient process if Dapper would not implement some isolated containers inside, and my singleton area would still be wrapped within any area used by Dapper inside.

Are there any recommendations / recommendations regarding the lifetime of the SqlConnection object when working with Dapper? Are there any reservations that I may skip?

Thanks in advance.

Answer 1

If you provide the SQL connection as a singleton, you cannot serve multiple queries at the same time unless you enable MARS, which also has limitations. Best practice is to use a transient SQL connection and ensure its proper location.

IDbConnectionFactory , using. , .

Answer 2

@ , , .

factory.

- UnitOfWork.

DalSession UnitOfWork . .
BaseDal . Repository ( BaseRepository).

• UnitOfWork .
• DalSession .
• UnitOfWork BaseDal.

- / SqlConnection Dapper?

, , , . :

• .
  . using . , . , .
  , /. using . - UnitOfWork, .
• .
  . . - " " .
  , ( ) , . , .
  , ( DAL) , . , , , DAL, .

Answer 3

, . , , . .

, , , , , , . , , , !

, , , DbService . , , DbService, , , "". .

EDIT: , Dapper GitHub

:

/*
 * Db Service
 */
public interface IDbService
{
    ISomeRepo SomeRepo { get; }
}

public class DbService : IDbService
{
    readonly string connStr;
    ISomeRepo someRepo;

    public DbService(string connStr)
    {
        this.connStr = connStr;
    }

    public ISomeRepo SomeRepo
    {
        get
        {
            if (someRepo == null)
            {
                someRepo = new SomeRepo(this.connStr);
            }

            return someRepo;
        }
    }
}

:

/*
 * Mock Repo
 */
public interface ISomeRepo
{
    IEnumerable<SomeModel> List();
}

public class SomeRepo : ISomeRepo
{
    readonly string connStr;

    public SomeRepo(string connStr)
    {
        this.connStr = connStr;
    }

    public IEnumerable<SomeModel> List()
    {
        //work to return list of SomeModel 
    }
}

:

/*
 * Startup.cs
 */
public IConfigurationRoot Configuration { get; }

public void ConfigureServices(IServiceCollection services)
{
    //...rest of services

    services.AddSingleton<IDbService, DbService>();

    //...rest of services
}

, , :

public SomeController : Controller 
{
    IDbService dbService;

    public SomeController(IDbService dbService)
    {
        this.dbService = dbService;
    }

    public IActionResult Index()
    {
        return View(dbService.SomeRepo.List());
    }
}