Alternative easy way to run SQL Server procedures without using Microsoft Entity Framework Core
Background
I am an old software developer for over 20 years and have written a lot of unique code in my life. In my practice of working with data, I usually use SQL Server and stored procedures to execute queries of any complexity. For the past year, I tried to use the Microsoft EF Core framework, but I always lacked the power that I had when using procedures earlier. In the end, my patience ran out and I created a minimally simple UkrGuru.SqlJson
package for modern data manipulation, and now I want to share this knowledge with you...
Introduction
At the moment, the UkrGuru.SqlJson
package has two classes: DbHelper
and DbService
.
Database Setup
First, we need to initialize database and stored procedures in my style coding. All scripts are included in the source archive.
Then, you need to update ConnectionString
in appsettings.json file.
"ConnectionStrings": {
"SqlJsonConnection": "Server=localhost;Database=SqlJsonDemo;Integrated Security=SSPI"
}
Services Setup
Then, we need to initialize DbService
and ConnString
in your ConfigureServices
function:
public void ConfigureServices(IServiceCollection services)
{
services.AddScoped<DbService>();
DbHelper.ConnString = Configuration.GetConnectionString("SqlJsonConnection");
}
Example using DbService
Below is an example of using DbService
a simple controller in the code, but take my word for it, this can be used anywhere in your program and with procedures of any complexity ...
[Route("api/[controller]")]
[ApiController]
public class ContactController : ControllerBase
{
private readonly DbService _db;
public ContactController(DbService db)
{
_db = db;
}
[HttpGet]
public async Task<List<Contact>> Get()
{
return await _db.FromProcAsync<List<Contact>>("Contacts_List");
}
[HttpGet("{id}")]
public async Task<Contact> Get(int id)
{
return await _db.FromProcAsync<Contact>("Contacts_Item", new { Id = id });
}
[HttpPost]
public async Task<int> Post([FromBody] Contact item)
{
return await _db.FromProcAsync<int>("Contacts_Ins", item);
}
[HttpPut("{id}")]
public async Task Put(int id, [FromBody] Contact item)
{
await _db.ExecProcAsync("Contacts_Upd", item);
}
[HttpDelete("{id}")]
public async Task Delete(int id)
{
await _db.ExecProcAsync("Contacts_Del", new { Id = id });
}
}
Example using DbHelper
Below is an example of using DbHelper
, a simple controller in the code, but take my word for it, this can be used anywhere in your program and with procedures of any complexity ...
[Route("api/[controller]")]
[ApiController]
public class ContactController : ControllerBase
{
[HttpPost("PostGet")]
public async Task<Contact> PostGet([FromBody] Contact item)
{
using SqlConnection connection = new SqlConnection(DbHelper.ConnString);
await connection.OpenAsync();
var id = await connection.FromProcAsync<int>("Contacts_Ins", item);
return await connection.FromProcAsync<Contact>("Contacts_Item", new { Id = id });
}
}
Standard for procedures
UkrGuru.SqlJson
will automatically serialize C# input parameters list to json and deserialize result in object.
So you must follow the next requirements:
1. You can use procedures without parameters or with 1 specific parameter (@Data varchar)
2. If used FromProcAsync then you need prepare result in json format with "FOR JSON PATH" for List<TEntity>
or with "FOR JSON PATH, WITHOUT_ARRAY_WRAPPER" for TEntity
ALTER PROCEDURE [dbo].[Contacts_Ins]
@Data nvarchar(max)
AS
INSERT INTO Contacts (FullName, Email, Notes)
SELECT * FROM OPENJSON(@Data)
WITH (FullName nvarchar(50), Email nvarchar(100), Notes nvarchar(max))
DECLARE @Id int = SCOPE_IDENTITY()
SELECT Id, FullName, Email, Notes
FROM Contacts
WHERE Id = @Id
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
DbController - universal API
This is another best example of using UkrGuru.SqlJson
[ApiController]
[Route("api/[controller]")]
public class DbController : ControllerBase
{
private readonly string _prefix = "api.";
private readonly DbService _db;
public DbController(DbService db) => _db = db;
[HttpGet("{proc}")]
public async Task<string> Get(string proc, string data = null)
{
try
{
return await _db.FromProcAsync($"{_prefix}{proc}", data);
}
catch (Exception ex)
{
return await Task.FromResult($"Error: {ex.Message}");
}
}
[HttpPost("{proc}")]
public async Task<dynamic> Post(string proc, [FromBody] dynamic data = null)
{
try
{
return await _db.FromProcAsync<dynamic>($"{_prefix}{proc}",
(object)data == null ? null : data);
}
catch (Exception ex)
{
return await Task.FromResult($"Error: {ex.Message}");
}
}
}
Points of Interest
The speed of UkrGuru.SqlJson
package turned out to be fantastically fast, easy to use, and left Microsoft EF Core far behind...
More
For more usage examples see in UkrGuru/WebJobs: Starter library for running background tasks under your ASP.NET Core website, supports cron and trigger rules, allows extensibility for any custom action. (github.com)
History
- 18th February, 2021: Initial version