Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / CSS

UkrGuru.SqlJson is Perfect for your ASP.NET Core Web Application!

4.00/5 (1 vote)
28 Jul 2021CPOL2 min read 9.8K   84  
Minimally simple UkrGuru.SqlJson package for your ASP.NET Core Web Application
In this tip, you will learn about an alternative easy way to run SQL Server procedures without using Microsoft EF Core in your ASP.NET Core Web application.

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 in ASP.NET Core Web Application, and now I want to share this knowledge with you...

Introduction

In this article, I will tell how to replace Microsoft EF Core with my UkrGuru.Json package in your ASP.NET Core Web Application. UkrGuru.Json package is easy to learn, easy to use and fast as lightning!

Image 1

If you don't know my UkrGuru.SqlJson package yet, then I recommend that you read my other articles, UkrGuru.SqlJson Your Link Between SQL Server and .NET 5 and ASP.NET Core Razor Pages Using EntityFramework Core first...

Using this knowledge, I first created a Contact model and then generated Scaffolded items ...

C#
public class Contact
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string FullName { get; set; }

    [Required]
    public string Email { get; set; }

    public string Notes { get; set; }
}

Image 2

Image 3

Using the Code

Now I will tell you what you need to do to apply my package to use.

Services Setup

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");
}

Supply DbService for Use in Pages

Please replace DbContext with UkrGuru.SqlJson.DbService in your pages like below:

C#
private readonly UkrGuru.SqlJson.DbService _db;

public CreateModel(UkrGuru.SqlJson.DbService db)
{
    _db = db;
}

Index Page

Update your OnGetAsync on the Index page:

C#
public async Task OnGetAsync()
{
    Contact = await _db.FromProcAsync<List<Contact>>("Contacts_List");
}

Create Page

Update your OnPostAsync on the Create Page:

C#
public async Task<IActionResult> OnPostAsync()
{
    if (!ModelState.IsValid)
    {
        return Page();
    }

    await _db.ExecProcAsync("Contacts_Ins", Contact);

    return RedirectToPage("./Index");
}

Edit Page

Update your OnGetAsync and OnPostAsync on the Edit page:

C#
public async Task<IActionResult> OnGetAsync(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    Contact = await _db.FromProcAsync<Contact>("Contacts_Item", new { Id = id });

    if (Contact.Id == 0)
    {
        return NotFound();
    }
    return Page();
}

public async Task<IActionResult> OnPostAsync()
{
    if (!ModelState.IsValid)
    {
        return Page();
    }

    await _db.ExecProcAsync("Contacts_Upd", Contact);

    return RedirectToPage("./Index");
}

Details Page

Update your OnGetAsync on the Details page:

C#
public async Task<IActionResult> OnGetAsync(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    Contact = await _db.FromProcAsync<Contact>("Contacts_Item", new { Id = id });

    if (Contact.Id == 0)
    {
        return NotFound();
    }
    return Page();
}

Delete Page

Update your OnGetAsync and OnPostAsync on the Delete page:

C#
public async Task<IActionResult> OnGetAsync(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    Contact = await _db.FromProcAsync<Contact>("Contacts_Item", new { Id = id });

    if (Contact.Id == 0)
    {
        return NotFound();
    }
    return Page();
}

public async Task<IActionResult> OnPostAsync(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    await _db.FromProcAsync<Contact>("Contacts_Del", new { Id = id });

    return RedirectToPage("./Index");
}

And voila, your code is ready to run.

Image 4

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

  • 27th February, 2021: Initial version

License

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