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!
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 ...
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; }
}
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:
public void ConfigureServices(IServiceCollection services)
{
services.AddScoped<DbService>();
DbHelper.ConnString = Configuration.GetConnectionString("SqlJsonConnection");
}
Supply DbService for Use in Pages
Please replace DbContext
with UkrGuru.SqlJson.DbService
in your pages like below:
private readonly UkrGuru.SqlJson.DbService _db;
public CreateModel(UkrGuru.SqlJson.DbService db)
{
_db = db;
}
Index Page
Update your OnGetAsync
on the Index page:
public async Task OnGetAsync()
{
Contact = await _db.FromProcAsync<List<Contact>>("Contacts_List");
}
Create Page
Update your OnPostAsync
on the Create Page:
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:
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:
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:
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.
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