.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)
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...
- Easy to make this call async.
- Easy to pass the parameters
- Easy to map with your custom model
- No need to generate the Complex Types to register the procedure and it's return type in EDMX file.
- Obvious , fast response time.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow