ukrguru/sqljson

This framework is fast and easy access to Sql Server databases only through stored procedures...

This framework is fast and easy access to Sql Server databases only through stored procedures and EF Core is not involved in this

This framework is fast and easy access to Sql Server databases only through stored procedures...

UkrGuru.SqlJson

.

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

Installation

1. Install the nuget package UkrGuru.SqlJson in Visual Studio

Package Manager:

Install-Package UkrGuru.SqlJson

or the dotnet command line:

dotnet add package UkrGuru.SqlJson

2. Add a new SqlJsonConnection to your database in appsettings.json

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

3. Open the ~/Startup.cs file and register the UkrGuru SqlJson service:

public void ConfigureServices(IServiceCollection services)
{
  // more code may be present here
  services.AddSqlJson(Configuration.GetConnectionString("SqlJsonConnection"));
  // more code may be present here
}

Samples of code

DbService: how to use?

[ApiController]
[Route("api/[controller]")]
public class ContactController : ControllerBase
{
    private readonly DbService _db;
    public ContactController(DbService db) => _db = db;

    [HttpGet]
    public async Task<List<Contact>> Get() => await _db.FromProcAsync<List<Contact>>("Contacts_List");

    [HttpGet("{id}")]
    public async Task<Contact> Get(int id) => await _db.FromProcAsync<Contact>("Contacts_Item", id);

    [HttpPost]
    public async Task<Contact> Post([FromBody] Contact item) => await _db.FromProcAsync<Contact>("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", id);
}

DbHelper: how to use?

[HttpPost("Post")]
public async Task<Contact> Post([FromBody] Contact item)
{
    return await DbHelper.FromProcAsync<Contact>("Contacts_Ins", item);
}

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 or with "FOR JSON PATH, WITHOUT_ARRAY_WRAPPER" for TEntity
CREATE PROCEDURE [dbo].[Contacts_List] 
AS
SELECT Id, FullName, Email, Notes
FROM Contacts
FOR JSON PATH
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

License

The UkrGuru.SqlJson package is an open source product licensed under:

  • MIT license

All source code is © Oleksandr Viktor (UkrGuru), regardless of changes made to them. Any source code modifications must leave the original copyright code headers intact if present.

There's no charge to use, integrate or modify the code for this project. You are free to use it in personal, commercial, government and any other type of application and you are free to modify the code for use in your own projects.

Donate

If you find this library useful, consider making a small donation. Contact with me by email ([email protected]) for Payoneer Invoice ...

Library Stats (Sep 19, 2022)

Subscribers: 1
Stars: 3
Forks: 0
Issues: 0

Json (also known as JSON

NET) is a highly performant JSON serialization library

Json (also known as JSON

Fake JSON Server

is a Fake REST API that can be used as a Back End for prototyping or as a template for a CRUD Back End

Fake JSON Server
JSON

318

JSON Flat File Data Store

Simple data store that saves the data in JSON format to a single file

JSON Flat File Data Store

A fast JSON (de)serializer, built on Sigil with a number of somewhat crazy optimization tricks

A fast JSON (de)serializer, built on Releases are available on Nuget in addition to this repository

A fast JSON (de)serializer, built on Sigil with a number of somewhat crazy optimization tricks

dotnet-jwk is a JSON Web Key manager for dotnet

It allow to generate, encrypt, decrypt, convert and check JWK

dotnet-jwk is a JSON Web Key manager for dotnet

Kiota Json Serialization Library for dotnet

The Json Serialization Library for dotnet is the dotnet JSON serialization library implementation with System

Kiota Json Serialization Library for dotnet

A JSON serializer/deserializer (with JsonSchema support) library written in pure C#

VJson is a JSON serializer/deserializer (with JsonSchema support) library written in pure C#

A JSON serializer/deserializer (with JsonSchema support) library written in pure C#
JSON

148

A high-performance async/non-blocking redis client components for dotnet core,default support json and protobuf data format

A high-performance async/non-blocking redis client components for dotnet core,default support json and protobuf data format

A high-performance async/non-blocking redis client components for dotnet core,default support json and protobuf data format
A minimalist JSON library designed to easily encode and decode JSON messages

JSON Document Transfoms

JSON Document Transforms (JDT) library

JSON Document Transfoms

Open Source Json Database Manager Made in WinForms that copies CastleDB's UI structure

This Database Manager also has features which are unique to it for the purpose of further automating database management, features like: adding additional restrictions to...

Open Source Json Database Manager Made in WinForms that copies CastleDB's UI structure