ASP.NET Core Blazor and EF Core Raw SQL Queries for Stored Procedure

• Download source - 405 KB

Introduction

In this article, we will see in detail how to use Entity Framework Core Raw SQL for executing Stored Procedures in our ASP.NET Core Blazor Applications.

Raw SQL queries are most commonly useful for all the relational database as it can be useful to return the regular entity types or keyless entity types. As if we work with large database with lots of relational tables and many pages, we need to bind and show the table details by joining from many tables, for example, if we want to show the sales report, in that case, we need to join multiple tables like Company master, Plant master, customer Master, Item master, Production results and Sales results. In this case, the best option will be make it as a Stored procedure and also sometimes, it will be hard to define the entity for several join in one Stored Procedure. In those cases, we can use this Raw SQL Queries and without defining the entity, we can directly execute the Stored Procedure and bind the result in our Blazor applications.

In this article, we will see how to create a Stored Procedure to search and bind the Customer details in our Blazor application using the EF Core Raw SQL Queries for executing our Stored procedure with search parameter.

Background

Prerequisites

• Visual Studio 2019 16.3 or later
• .NET Core 3.0 SDK

Using the Code

Create a Database and a Table

We will be using our SQL Server database for our Raw SQL Queries example with Stored Procedure. First, we will create a database named CustDB and a table as CustMasterTBL. Here is the SQL script to create a database table and sample record insert query in our table. Search Stored Procedure.

-- =============================================    
-- Author : Shanu    
-- Create date : 2020-10-15  
-- Description :  
-- Latest    
-- Modifier : Shanu    
-- Modify date : 2020-10-15  
-- =============================================   
--Script to create DB,Table , sample Insert data   and Stored Procedure 
USE MASTER   
GO   
-- 1) Check for the Database Exists .If the database is exist then drop and create new DB   
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'CustDB' )   
DROP DATABASE CustDB   
GO   
   
CREATE DATABASE CustDB   
GO   
   
USE CustDB   
GO   

  CREATE TABLE [dbo].[CustMasterTBL](
    [CustCd] [varchar](20) NOT NULL,
    [CustName] [varchar](100) NOT NULL,
    [Email] [nvarchar](100) NOT NULL,
    [PhoneNo] [varchar](100) NOT NULL,
    [InsertBy] [varchar](100) NOT NULL,
    [UpdateBy] [varchar](100) NOT NULL
) ON [PRIMARY]

GO
select * from CustMasterTBL

INSERT INTO [dbo].[CustMasterTBL]
           ([CustCd],[CustName],[Email],[PhoneNo],[InsertBy],[UpdateBy])
     VALUES
           ( 'C001','ACompany','acompany@gmail.com','55555','Shanun','Shanu')

INSERT INTO [dbo].[CustMasterTBL]
           ([CustCd],[CustName],[Email],[PhoneNo],[InsertBy],[UpdateBy])
     VALUES
           ( 'C002','BCompany','bcompany@gmail.com','2323123123','Afraz','Afraz')

INSERT INTO [dbo].[CustMasterTBL]
           ([CustCd],[CustName],[Email],[PhoneNo],[InsertBy],[UpdateBy])
     VALUES
           ( 'C003','CCompany','ccompany@gmail.com','786786','Afreen','Afreen')
--Stored procedure

Create PROCEDURE [dbo].[usp_CustDetails]
        (  
        @CustCd                VARCHAR(52)        = '' 
        ,@CustName                VARCHAR(52)        = '' 
        )
AS 

SET NOCOUNT ON 
 
        SELECT      CustCd 
                  ,CustName
                  ,Email
                  ,PhoneNo
                  ,InsertBy
                  ,UpdateBy
        FROM  CustMasterTBL
         Where  
                    CustCd like @CustCd + '%'
                AND CustName like @CustName + '%'

Step 1: Create ASP.NET Core Blazor Server Application

After installing all the prerequisites listed above, click Start >> Programs >> Visual Studio 2019 >> Visual Studio 2019 on your desktop. Click New >> Project.

Select Blazor App and click Next button.

Select your project folder and enter your Project name and then click Create button.

Select Blazor Server App:

After creating ASP.NET Core Blazor Server Application, wait for a few seconds. You will see the below structure in solution explorer:

In the Data folder, we can add all our Model, DBContext Class, Services and Controller, we will see that in this article.

In the Pages folder, we can add all our component files.component file all should have the .razor extension with the file name.

In the Shared folder, we can add all left menu form NavMenu.razor file and change the main content from the MainLayout.razor file.

In the _Imports.razor file, we can see all set of imports have been added in order to use in all component pages.

In the App.razor file, we will add our main component to be displayed by default when run in browser.Appsertings.json can be used to add the connection string.

Startup.cs file is an important file where we add all our endpoints example like Controller end points, HTTP Client, add services and dbcontext to be used in startup Configuration method.

Run to Test the Application

When we run the application, we can see that the left side has navigation and the right side contains the data. We can see as the default sample pages and menus will be displayed in our Blazor web site. We can use the pages or remove it and start with our own page.

Step 2: Install the Packages

In order to use the Entity Framework Core and Raw SQL, install the below packages:

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer

Right click the solution and click on the Manage Nuget package. Search for all the packages and install all the needed packages like the below image:

After installing the package, we can confirm it from Dependencies Packages.

Connection String

Open the appsettings.json file and the SQL connection string. Note add your SQL server ID details.

{
  "ConnectionStrings": {

    "DefaultConnection": "Server= YourServer ID;Database=CustDB;
     user id= SQL UID;password=Sql Password;Trusted_Connection=True;
     MultipleActiveResultSets=true"
}

Step 3: Create Model Class

Next, we need to create the Model class for using in our application for binding the Customer Details.

Right click the Data Folder and create new class file as “CustMasterTBL.cs”:

In the class, we add the property field name which is the same as the below code:

public class CustMasterTBL
    {
        public string CustCd { get; set; }
        public string CustName { get; set; }
        public string Email { get; set; }
        public string PhoneNo { get; set; }
        public string InsertBy { get; set; }
        public string UpdateBy { get; set; }
    }

Creating DBContext CLASS

Next, we create DBContext class to using in our Service for connecting our SQL server and retrieve the data using the connection string. We create the DBContext class as CustManagementContext.cs.

We add the below code in the DBContext class:

public partial class CustManagementContext : DbContext
    {
        public CustManagementContext(DbContextOptions<CustManagementContext> options)
           : base(options)
        {
        }
        public DbSet<ShanuBlazorRawSQL.Data.CustMasterTBL> CustMasterTBL { get; set; }
    }

Creating Service Class

Next, we create the CustMasterTBLService.cs class in order to bind the result by executing the Stored Procedure by using the Raw SQL Queries.

First, we need to import the:

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

Next, we create the object for our DbContext in order to execute the stored procedure.

private readonly CustManagementContext _context;
      public CustMasterTBLService(CustManagementContext context)
      {
          _context = context;
      }

Next, we create the async method to return the customer details as array, also we can see in this method we use the FromSqlRaw to execute the Stored Procedure with CustCD and CustName as parameter.

public async Task<CustMasterTBL[]> GetCustomerAsync(String CustCD, String CustName)
        {
            CustMasterTBL[] custsObjs;
            var CustCDs = CustCD;
            var CustNames = CustName;
            SqlParameter param1 = new SqlParameter("@CustCd", CustCD);
            SqlParameter param2 = new SqlParameter("@CustName", CustName);

            custsObjs = _context.CustMasterTBL.FromSqlRaw
                        ("EXECUTE dbo.usp_CustDetails @CustCd,@CustName", param1, param2)
                .ToArray();
            return custsObjs;
        }

Step 4: Add the Service and DbContext to the Startup.cs

We need to add the services created by us to the Startup.cs ConfigureServices method.

services.AddSingleton<ItemMasterService>();

Step 5: Working with Client Project

First, we need to add the Razor Component page.

Add Razor Component

To add the Razor Component page, right click the Pages folder from the Client project. Click on Add >> New Item >> Select Razor Component >> Enter your component name, here we have given the name as Customer.razor.

Note all the component files need to have the extensions as .razor.

In Razor Component Page, we have three parts of code as first is the Import part where we import all the references and models for using in the component, HTML design and data bind part and finally we have the function part to call all the web API to bind in our HTML page and also to perform client-side business logic to be displayed in Component page.

Import Part

First, we import all the needed support files and references in our Razor View page. Here, we have first imported our Model and service class to be used in our view.

@page "/Customer"
@using ShanuBlazorRawSQL.Data
@inject CustMasterTBLService CustomerService

HTML Design and Data Bind Part

In design part, we bind the result in table and also we design a search part with button.

@if (custDetails == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>Customer Code</th>
                <th>Customer Name</th>
                <th>Email</th>
                <th>Phone No</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var forecast in custDetails)
            {
                <tr>
                    <td>@forecast.CustCd</td>
                    <td>@forecast.CustName</td>
                    <td>@forecast.Email</td>
                    <td>@forecast.PhoneNo</td>
                </tr>
            }
        </tbody>
    </table>
}

Function Part

Function part to get the Service result and bind the result in array and we have created function to search and bind the result when button clicked.

Here, first we declare the customer Array to get bind the result and declared variables for search.

In OnInitializedAsync, we get the CustomerService result and bind the result in the ItemsArrays.

@code {
    CustMasterTBL[] custDetails;
      String CustCD = "";
        String Custname = "";

    protected override async Task OnInitializedAsync()
    {
        custDetails = await CustomerService.GetCustomerAsync(CustCD,Custname);
    }

    //SearchCustomer
    async Task searchDetails()
    {
        custDetails = await CustomerService.GetCustomerAsync(CustCD,Custname);
    }
}

Navigation Menu

Now, we need to add this newly added DrawingSample Razor page to our left Navigation. For adding this, open the Shared Folder and open the NavMenu.cshtml page and add the menu.

<li class="nav-item px-3">
            <NavLink class="nav-link" href="Customer">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Customer
            </NavLink>

</li>

Build and Run the Application

Points of Interest

In this article, we have seen how to use the Stored procedure in ASP.NET Core Blazor application using the Entity FrameWork Core Raw SQL queries. In the next article, we will see how to use CRUD functionality using the Stored Procedure and Entity FrameWork Core Raw SQL queries.

History

• 15^th October, 2020: Initial version