.Net Core, Stored Procedures and Connection Pooling

.net-core asp.net-core asp.net-core-webapi connection-pooling entity-framework-core

Question

I have a .NET Core Web API service that will call two stored procedures (and nothing else). I am wondering how to accomplish these calls in .NET Core.

At first I was thinking about using Entity Framework Core for my database connection. I was considering EF Core because I know it handles a lot of hard bits about connecting to the database (for example connection pooling).

But this service has some fairly strict performance requirements. I need it to be fast and be able to handle a fairly high amount of call traffic. To be honest, EF Core can probably handle it. But adding EF Core feels like I am adding a lot of complexity to something very simple.

So I thought I would ask, are there any other options in .NET Core for connecting to a SQL Server database in a safe, pooled, high traffic tolerant way?

A few side notes in case it is relevant:

  • My service will have two operations
  • Each operation will take 3 strings and an integer and call one of the two stored procedures
  • It will return only true or false to both types of calls (returned from the stored procedure as a bit)
1
0
2/14/2020 4:56:15 AM

Popular Answer

This is how I am calling procedure in .NET Core

try
{
    using (var db = new DBEntities())
    {
        var result = db.Database.SqlQuery<EmployeesData>("exec uspDataOfEmployee {0} , {1}", 
            EmployeeId , Convert.ToDateTime(month).ToString("yyyy-MM-dd")).ToList();
        return result;
    }
}
catch (Exception ex)
{
    Logger.Instance.Error(ex, "Exceptio while calling procedureName( method.", EmployeeId.ToString());
    throw;
}

List of benefits if you are using above code...

  1. Easy to make this call async.
  2. Easy to pass the parameters
  3. Easy to map with your custom model
  4. No need to generate the Complex Types to register the procedure and it's return type in EDMX file.
  5. Obvious , fast response time.
0
2/14/2020 5:06:22 AM


Related Questions





Related

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