I have ASP.NET Core Web API. The app has three functionalities that connects to DB. EF DbContext, Hangfire and Serilog Logging. All 3 reads the connection string from appsettings.json
{
"Logging": {
"LogLevel": {
"Default": "Error"
}
},
"Serilog": {
"Using": [ "Serilog.Sinks.MSSqlServer"],
"WriteTo": [
{
"Name": "MSSqlServer",
"Args": {
"connectionString": "Server=serverip;Database=MyDb;Integrated Security=True;",
"tableName": "Logs",
"schemaName": "logging",
"autoCreateSqlTable": false
}
}
]
},
"ConnectionStrings": {
"DefaultConnection": "Server=serverip;Database=MyDb;Integrated Security=True;"
}
}
in Programs.cs I have configured Serilog that auto reads the connectionString from appsettings
public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
WebHost.CreateDefaultBuilder(args)
.UseUrls("http://*:30000")
.UseStartup<Startup>()
.ConfigureLogging((hostingContext, logging) =>
{
if (!hostingContext.HostingEnvironment.IsLocal())
{
logging.ClearProviders();
}
Log.Logger = new LoggerConfiguration()
.ReadFrom.Configuration(hostingContext.Configuration)
.CreateLogger();
logging.AddSerilog();
});
in Startup.cs we have Hangfire and EF DbContext using the same connection value
public void ConfigureServices(IServiceCollection services)
{
//dbContext
var connection = configuration.GetConnectionString("DefaultConnection");
services.AddDbContext<MyDBContext>(options => options.UseSqlServer(connection));
services.AddHangfire(config => config.UseSqlServerStorage(connection));
}
Serilog, has separate entry in appsettings for connection string but the value for the connection string is same. Each funcationality has its own SQL schema. DbContext -> dbo schema, Hangfire->hangfire schema, Serilog ->logging schema
As per the documentation
A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default).
Since the value of the connection string is same, does that mean all these 3 funcationalities sharing the same connection pool?
UPDATE 1
The reason I asked this question because we were seeing error
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Here is my implementation of service. Since service is registered with Scope lifetime, the DI container should dispose service at the end of request and that should dispose the dbContext as well.
public interface IBaseService : IDisposable
{
}
public abstract class BaseService : IBaseService
{
private bool _disposed = false;
protected readonly MyDBContext _dbContext;
protected BaseService(MyDBContext dbContext)
{
_dbContext = dbContext;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
/// <summary>
/// Releases unmanaged and - optionally - managed resources.
/// </summary>
/// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
protected virtual void Dispose(bool disposing)
{
if (_disposed)
return;
if (disposing)
{
if (_dbContext != null)
{
_dbContext.Dispose();
}
// Free any other managed objects here.
}
// Free any unmanaged objects here.
_disposed = true;
}
}
public interface IOrderService : IBaseService
{
Task<Order> Create(Order order);
}
public class OrderService:BaseService,IOrderService
{
public OrderService():base(MyDBContext dbContext)
{
}
public async Task<Order> Create(Order order)
{
_dbContext.Orders.Add(order);
await _dbContext.SaveChangesAsync();
// at this point I am guessing the SQL connection will be closed by the DBContext
}
}
and in Startup.cs it service is regiered with Scope lifetime
services.AddScoped<IOrderService, OrderService>();