UkrGuru.SqlJson Your Link Between SQL Server and .NET 5

• Download source - 9.1 KB

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)
{
    // DbService Init
    services.AddScoped<DbService>();

    // DbHelper Init
    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;
    }

    // GET: api/<ContactController>
    [HttpGet]
    public async Task<List<Contact>> Get()
    {
        return await _db.FromProcAsync<List<Contact>>("Contacts_List");
    }

    // GET api/<ContactController>/1
    [HttpGet("{id}")]
    public async Task<Contact> Get(int id)
    {
        return await _db.FromProcAsync<Contact>("Contacts_Item", new { Id = id });
    }

    // POST api/<ContactController>
    [HttpPost]
    public async Task<int> Post([FromBody] Contact item)
    {
        return await _db.FromProcAsync<int>("Contacts_Ins", item);
    }

    // PUT api/<ContactController>/5
    [HttpPut("{id}")]
    public async Task Put(int id, [FromBody] Contact item)
    {
        await _db.ExecProcAsync("Contacts_Upd", item);
    }

    // DELETE api/<ContactController>/5
    [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
{
    // DbHelper Demo
    // POST api/<ContactController>/PostGet
    [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

• 18^th February, 2021: Initial version