Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server / SQL Server 2016
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
24 Jun 2021CPOL2 min read 7.3K   137   5   4
Minimally simple UkrGuru.SqlJson package for modern data manipulation
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.

Image 1

Image 2

Database Setup

First, we need to initialize database and stored procedures in my style coding. All scripts are included in the source archive.

Image 3

Then, you need to update ConnectionString in appsettings.json file.

C#
"ConnectionStrings": {
  "SqlJsonConnection": "Server=localhost;Database=SqlJsonDemo;Integrated Security=SSPI"
}

Services Setup

Then, we need to initialize DbService and ConnString in your ConfigureServices function:

C#
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 ...

C#
[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 ...

C#
[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

SQL
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

 
 
C#
[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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Ukraine Ukraine
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionMessage Closed Pin
26-Jun-21 11:28
Member 1526393826-Jun-21 11:28 
QuestionMessage Closed Pin
26-Jun-21 11:26
Member 1526393826-Jun-21 11:26 
QuestionLooks cool, but .. Pin
LightTempler19-Feb-21 10:35
LightTempler19-Feb-21 10:35 
AnswerRe: Looks cool, but .. Pin
Oleksandr Viktor (UkrGuru)19-Feb-21 21:45
Oleksandr Viktor (UkrGuru)19-Feb-21 21:45 
GeneralRe: Looks cool, but .. Pin
LightTempler20-Feb-21 7:45
LightTempler20-Feb-21 7:45 
GeneralRe: Looks cool, but .. Pin
Oleksandr Viktor (UkrGuru)3-Mar-21 23:42
Oleksandr Viktor (UkrGuru)3-Mar-21 23:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.