Implementing Dapper in Blazor Server Application

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,

Fig.1 Installing Dapper NuGet Package

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.
Fig. 2 Choose Blazor Server App as 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
Fig. 3 Before Adding Items
Add New Item
Fig. 4 Added New Item
Edit Item
Fig. 5 Edit the item
Select added items
Fig. 6 Select added items
Delete item
Fig. 7 Delete items

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: