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.
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
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.
USE MASTER
GO
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')
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);
}
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
- 15th October, 2020: Initial version
