Introduction
In this article, we will discuss how to integrate Dapper with the Blazor Web Server application. As many of us may know, Dapper is a micro ORM framework that helps us map object-oriented domain models to a relational database. Using Dapper, it is very easy to execute an SQL query against database and get the result mapped to C# domain class.
Table of Contents
- What is Dapper and its purpose
- Dapper NuGet Package
- Building a Blazor Web Server Application with Dapper
- Demo
- Conclusion
What is Blazor
Dapper is a Micro ORM (Object Relational Mapping) for .NET technologies which can be installed using a NuGet package. ORM creates a virtual database in terms of classes and provides methods to work with those classes. Dapper was created by Stack Overflow and its implemented on Stack Overflow’s production.
Purpose of Dapper
- Dapper is a NuGet library, can be used with any .NET project.
- Lightweight and high performance.
- Reduces the database access code.
- Focus on getting database tasks done instead of being full-on ORM.
- Work with any database – Eg: SQL Server, Oracle, SQLite, MySQL, PostgreSQL, etc.
- For an existing database, using Dapper is an optimal choice.
Installing Dapper NuGet Packages
As mentioned before, Dapper is a NuGet package, which can be installed in two ways,
- Package Manager Console
- Installing using Package Solutions
Package Manager Console
Install-Package Dapper -Version 2.0.90
Installing using Package Solutions
After creating the project, browse to Tools, then choose NuGet Package Manager and choose Manage NuGet Packages for Solutions. In the search, enter Dapper and search. Install the highlighted NuGet Package as shown,
Building a Blazor Web Server Application with Dapper – Basic CRUD Operations
Now, lets build a simple Blazor Server Application which performs the basic CRUD operations using Dapper. Prerequisite knowledge in Blazor is an added advantage.
- Open Visual Studio 2019 and choose Create a new project to create a new project.
- Enter the project name and choose the location of the project.
- Next, choose Blazor Server App as our project template.
- First, lets install the required packages, Dapper and Microsoft.Data.SqlClient
- Next, we shall create our Tables and required Stored Procedures. For this demo, I am using SQL Server as my Database.
- Table Name as DemoWorks.Sales and required Stored Procedures as
- [DemoWorks].[GetSalesDetails]
- [DemoWorks].[SaveSalesDetails]
- [DemoWorks].[GetSalesById]
- [DemoWorks].[UpdateSales]
- [DemoWorks].[DeleteSales]
Run the below mentioned T-SQL scripts.
DemoWorks.Sales
CREATE TABLE [DemoWorks].[Sales](
[SalesId] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](100) NULL,
[Quantity] [int] NULL,
[CreatedDt] [datetime2](7) NULL,
[UpdatedDt] [datetime2](7) NULL,
PRIMARY KEY CLUSTERED
(
[SalesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [DemoWorks].[Sales] ADD DEFAULT (getdate()) FOR [CreatedDt]
GO
[DemoWorks].[GetSalesDetails]
CREATE ORALTER PROCEDURE [DemoWorks].[GetSalesDetails]
AS
BEGIN
SELECT
SalesId,
ProductName,
Quantity
FROM DemoWorks.Sales
END
[DemoWorks].[SaveSalesDetails]
CREATE OR ALTER PROCEDURE [DemoWorks].[SaveSalesDetails]
(
@ProductName NVARCHAR(100),
@Quantity INT
)
AS
BEGIN
INSERT INTO DemoWorks.Sales
(
ProductName,
Quantity
)
VALUES
(
@ProductName,
@Quantity
)
END
[DemoWorks].[GetSalesById]
CREATE OR ALTER PROCEDURE [DemoWorks].[GetSalesById]
(
@SalesId INT
)
AS
BEGIN
SELECT
SalesId,
ProductName,
Quantity
FROM DemoWorks.Sales
WHERE SalesId=@SalesId
END
[DemoWorks].[UpdateSales]
CREATE OR ALTER PROCEDURE [DemoWorks].[UpdateSales]
(
@ProductName NVARCHAR(100),
@Quantity INT,
@SalesId INT
)
AS
BEGIN
UPDATE DemoWorks.Sales SET
ProductName=@ProductName,
Quantity=@Quantity
WHERE SalesId=@SalesId
END
[DemoWorks].[DeleteSales]
CREATE OR ALTER PROCEDURE [DemoWorks].[DeleteSales]
(
@SalesId INT
)
AS
BEGIN
DELETE FROM DemoWorks.Sales
WHERE SalesId=@SalesId
END
Now we have created our (database) Table and required Stored procedures to perform CRUD operations. Now lets build Blazor Application.
Switch to Visual Studio (Blazor application) and open appsettings.json file to add connection string.
appsettings.json
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DevDB": "Server=THOUGHTSIQ;Database=DapperDemo;Trusted_Connection=True;MultipleActiveResultSets=true"
}
}
From the solution explorer, under the Data folder, add new class as SalesDto.cs and add the properties.
SalesDto.cs
public class SalesDto
{
public int SalesId { get; set; }
[Required(ErrorMessage = "Please Product Name"), MaxLength(50)]
public string ProductName { get; set; }
[Required(ErrorMessage = "Please Product Quantity")]
public int Quantity { get; set; }
public bool IsUpdate { get; set; } = false;
}
Next, we need to create two folders as IServices and Services. In IServices folder, lets create ISalesService.cs interface and declare the methods which are required for CRUD operations.
ISalesService.cs
public interface ISalesService
{
Task<IEnumerable<SalesDto>> GetSales();
Task<bool> SaveSalesDetails(SalesDto sales);
Task<SalesDto> GetSalesById(int id);
Task<bool> DeleteSales(int id);
}
And in the Services folder, lets create SalesServices.cs Class and inherit the above interface and implement those methods.
SalesServices.cs
public class SalesServices : ISalesService
{
public IConfiguration _configuration { get; }
public string _connectionString { get; }
public SalesServices(IConfiguration configuration)
{
_configuration = configuration;
_connectionString = _configuration.GetConnectionString("DevDB");
}
public async Task<bool> DeleteSales(int id)
{
var parameters = new DynamicParameters();
parameters.Add("SalesId", id, DbType.Int32);
using (var conn = new SqlConnection(_connectionString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
await conn.ExecuteAsync("DemoWorks.DeleteSales", parameters, commandType: CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
return true;
}
public async Task<IEnumerable<SalesDto>> GetSales()
{
IEnumerable<SalesDto> salesEntries;
using (var conn = new SqlConnection(_connectionString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
salesEntries = await conn.QueryAsync<SalesDto>("DemoWorks.GetSalesDetails", commandType: CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
return salesEntries;
}
public async Task<SalesDto> GetSalesById(int id)
{
var parameters = new DynamicParameters();
parameters.Add("SalesId", id, DbType.Int32);
SalesDto sales = new SalesDto();
using (var conn = new SqlConnection(_connectionString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
sales = await conn.QueryFirstOrDefaultAsync<SalesDto>("DemoWorks.GetSalesById", parameters, commandType: CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
return sales;
}
public async Task<bool> SaveSalesDetails(SalesDto sales)
{
var parameters = new DynamicParameters();
parameters.Add("ProductName", sales.ProductName, DbType.String);
parameters.Add("Quantity", sales.Quantity, DbType.Int32);
using (var conn = new SqlConnection(_connectionString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
try
{
if (sales.IsUpdate)
{
parameters.Add("SalesId", sales.SalesId, DbType.Int32);
await conn.ExecuteAsync("DemoWorks.UpdateSales", parameters, commandType: CommandType.StoredProcedure);
}
else
await conn.ExecuteAsync("DemoWorks.SaveSalesDetails", parameters, commandType: CommandType.StoredProcedure);
}
catch (Exception)
{
throw;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
return true;
}
}
We have added all the logic for Sales entity in above class. We have used stored procedures for CURD operations with Dapper.
Startup.cs
Now we have to register our Interface, open the Startup.cs class. In that class, inside the ConfigureServices we have to register ISalesService.cs interface as shown below.
public void ConfigureServices(IServiceCollection services)
{
services.AddRazorPages();
services.AddServerSideBlazor();
services.AddSingleton<WeatherForecastService>();
services.AddSingleton<ISalesService, SalesServices>();
}
Blazor Components for CRUD Operation
For Front-end, lets use the default component Index.razor. I am following partial files approach, so that we can separate UI and C# code. So lets create another class and name it as Index.razor.cs under the Pages folder.
Index.razor
@page "/"
<h2>Sales Entries</h2>
<hr />
<form>
<div class="row">
<div class="col-md-8">
<div class="form-group">
<label for="ProductName" class="control-label">Product Name</label>
<input for="ProductName" class="form-control" @bind="@sales.ProductName" />
</div>
<div class="form-group">
<label for="Quantity" class="control-label">Quantity</label>
<input for="Quantity" class="form-control" @bind="@sales.Quantity" />
</div>
</div>
</div>
@if (sales.IsUpdate)
{
<div class="row">
<div class="col-md-4">
<div class="form-group">
<input type="button" class="btn btn-primary" @onclick="@UpdateSales" value="Update" />
</div>
</div>
</div>
}
else
{
<div class="row">
<div class="col-md-4">
<div class="form-group">
<input type="button" class="btn btn-primary" @onclick="@CreateSales" value="Save" />
</div>
</div>
</div>
}
@if (salesEntries == null)
{
<h3>Please wait, Loading the data.</h3>
}
else
{
<table class='table'>
<thead>
<tr>
<th>Product Name</th>
<th>Quantity</th>
<th>Options</th>
</tr>
</thead>
<tbody>
@foreach (var sales in salesEntries)
{
<tr>
<td>@sales.ProductName</td>
<td>@sales.Quantity</td>
<td>
<input type="button" value="Edit" @onclick="() => GetSalesById(sales.SalesId)" class="btn btn-primary" />
<input type="button" value="Delete" @onclick="() => DeleteSales(sales.SalesId)" class="btn btn-danger" />
</td>
</tr>
}
</tbody>
</table>
}
</form>
Now paste the below code in the Partial class
Index.razor.cs
public partial class Index
{
IEnumerable<SalesDto> salesEntries;
[Inject]
public ISalesService SalesServices { get; set; }
SalesDto sales = new SalesDto();
protected override async Task OnInitializedAsync()
{
await this.GetSalesDetails();
}
protected async Task GetSalesDetails()
{
salesEntries = await SalesServices.GetSales();
}
protected async Task CreateSales()
{
await SalesServices.SaveSalesDetails(sales);
await this.GetSalesDetails();
this.ClearAll();
}
protected async Task UpdateSales()
{
await SalesServices.SaveSalesDetails(sales);
await this.GetSalesDetails();
this.ClearAll();
}
protected async Task GetSalesById(int SalesId)
{
sales = await SalesServices.GetSalesById(SalesId);
sales.IsUpdate = true;
await this.GetSalesDetails();
}
protected async Task DeleteSales(int SalesId)
{
await SalesServices.DeleteSales(SalesId);
await this.GetSalesDetails();
}
public void ClearAll()
{
sales.ProductName = string.Empty;
sales.Quantity = 0;
}
}
Demo
Now we have implemented all the required coding to perform the CRUD operation using Blazor with Dapper. Now lets run the application and perform the CRUD Operation.
Before Adding Item
Add New Item
Edit Item
Select added items
Delete item
Thus we have performed the basic CRUD operation in Blazor with Dapper. The sample project is added into the GitHub repo, please feel free to use this sample demo application here
Conclusion
In this simple article, we discussed what is Dapper and its purpose and also implemented the Blazor application with Dapper which performs basic CRUD operations. I hope you all found this article much useful. Please feel free to share the feedback in the comment section.
Learn With Sundar 
Leave a Reply