Implementing Dapper In Blazor Server Application

Introduction

This article 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, executing an SQL query against a database is quite easy, as is getting the result mapped to the C# domain class.

Table of Contents

• What Dapper is and its purpose
• Dapper NuGet Package
• Building a Blazor Web Server Application with Dapper
• Demo
• Conclusion

What is Dapper?

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 is implemented on Stack Overflow’s production.

Purpose of Dapper

• Dapper is a NuGet library, that 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. 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, let's build a simple Blazor Server Application that performs the basic CRUD operations using Dapper. Prerequisite knowledge in Blazor is an added advantage.

• Open Visual Studio 2019 and choose 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, let's install the required packages, Dapper and Microsoft.Data.SqlClient.
• Next, we'll create our Tables and the 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 OR ALTER 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

We have created our (database) Table and the required stored procedures to perform CRUD operations. Now let's build the Blazor Application.

Switch to the Visual Studio (Blazor application) and open the appsettings.json file to add the 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 a 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 the IServices folder, let's create ISalesService.cs interface and declare the methods that 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);
}

Next, in the Services folder, let's create a 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;
    }
}

public void ConfigureServices(IServiceCollection services) {
    services.AddRazorPages();
    services.AddServerSideBlazor();
    services.AddSingleton < WeatherForecastService > ();
    services.AddSingleton < ISalesService, SalesServices > ();
}

@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>

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;
    }
}