Using Dapper ORM with ComponentOne Blazor

March 30 2021
0 Comments

What is Dapper?

Micro-ORMs are lightweight object-relational mappers (ORM), and Dapper, also known as the King of Micro ORM, is the most popular micro-ORM. It is a simple object mapping tool that lets you work with data using business objects in a .NET application without writing code to map query results from ADO.NET data readers to instances of objects. It is an open-source, lightweight ORM developed by the Stack Overflow team and is very fast compared to other ORMs primarily because of its lightweight. It provides support for both static and dynamic object binding using SQL Query, transactions, and stored procedures.

How Dapper Works

Install Dapper using Nuget Package Manager.
Create the POCO classes for database tables.
Create an IDbConnection Object.
Write a SQL query to perform either of the CRUD operations.
Passe Query as Parameter in the Execute Method to fetch the desired results.

ComponentOne Blazor Edition is a set of blazor UI controls provided by ComponentOne. For details, refer to demos and documentation.

This article will demonstrate how to perform CRUD operations on FlexGrid control from the ComponentOne Blazor Edition using Dapper.

Now, let's integrate the above steps to create a Blazor application that would use Dapper to perform CRUD operations in FlexGrid.

Download Now!

Step 1: Configure Blazor Application

In Visual Studio 2019, create a new project, selecting the Blazor App template.
In the Configure your new project window, provide the project name and location. Click Create.
In the Create a new Blazor app dialog, select Blazor Server App template and click Create. A new server-side Blazor app is created.

CreatenewBlazorApp

Note: Blazor Client-side app or WebAssembly app can be created using the Blazor WebAssembly App template. For details, check the Blazor WebAssembly documentation topic here.

Step 2: Install Nuget Packages

We would need to install the following three packages to work with Dapper, SQL Database, and ComponentOne Blazor FlexGrid:

Dapper
System.Data.SqlClient
C1.Blazor.Grid

ThreePackages

Step 3: Add Database & Client-side Resources

We will be working with SQL database using SQL database file, namely C1Nwind.mdf. Download C1NWind.mdf database file and add it to the project's Data folder.
To work with ComponentOne Blazor controls, you must register client side resources for these controls in the application. Navigate to the Pages folder, open _Host.cshtml file and register the client resources by adding the following lines of code to the head tag.

<link rel="stylesheet" href="/_content/C1.Blazor.Core/styles.css"/>

<link rel="stylesheet" href="/_content/C1.Blazor.Grid/styles.css"/>

<link rel="stylesheet" href="/_content/C1.Blazor.ListView/styles.css"/>

<link rel="stylesheet" href="/_content/C1.Blazor.Input/styles.css"/>

<link rel="stylesheet" href="/_content/C1.Blazor.DataPager/styles.css"/>


<script src="/_content/C1.Blazor.Core/scripts.js"></script>

<script src="/_content/C1.Blazor.Input/scripts.js"></script>

<script src="/_content/C1.Blazor.Grid/scripts.js"></script>

Step 4: Configure Connection String

We will save the connection string for connecting to SQL database file, by adding the connection string in appsettings.json file using following code:

{
  “Logging”: {
    “LogLevel”: {
      “Default”: “Information”,
      “Microsoft”: “Warning”,
      “Microsoft.Hosting.Lifetime”: “Information”
    }
  },
  “AllowedHosts”: “*”,
  “ConnectionStrings”: {
    "SqlDbContext": "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename= <Project location>\\Data\\C1NWind.mdf;Integrated Security=True;Connect Timeout=30"
  }
}

Create a new class “SqlConnectionConfiguration" under "Data" folder to fetch SQL connection string from appsettings.json configuration file by using the following code:

public class SqlConnectionConfiguration
{
   public SqlConnectionConfiguration(string value) => Value = value;
   public string Value { get; }
}

Step 5: Add POCO Classes

A POCO entity is a class that doesn't depend on any framework-specific base class. It is like any other normal. NET CLR class, which is why it is called "Plain Old CLR Objects."

To map database tables to .Net business objects, we must create a POCO class for each database table. For this article, we will be creating a POCO class each for the Products table and Employees table. We will be using the Employees table to showcase all the CRUD operations being performed on ComponentOne FlexGrid and Products table to showcase invoking of a stored procedure using Dapper.

A POCO class is defined by adding a property corresponding to each column/field in the database table.

Create and add Employee class for Employees table in the project's Data folder using the following code:

public class Employee
{
   public int EmployeeId { get; set; }
   public string LastName { get; set; }
   public string FirstName { get; set; }
   public string Title { get; set; }
   public string TitleOfCourtesy { get; set; }
   public string BirthDate { get; set; }
   public string HireDate { get; set; }
   public string Address { get; set; }
   public string City { get; set; }
   public string Region { get; set; }
   public string PostalCode { get; set; }
   public string Country { get; set; }
   public string HomePhone { get; set; }
   public string Extension { get; set; }
   public string Notes { get; set; }
   public string ReportsTo { get; set; }
}

Similarly, create and add Product class for Products table in the project's Data folder using the following code:

public class Product
{
   public int ProductId { get; set; }
   public string ProductName { get; set; }
   public int SupplierId { get; set; }
   public int CategoryId { get; set; }
   public string QuantityPerUnit { get; set; }
   public string UnitPrice { get; set; }
   public string UnitsInStock { get; set; }
   public string UnitsOnOrder { get; set; }
   public int ReorderLevel { get; set; }
   public bool Discontinued { get; set; }
}

You can define classes for all the other database tables you would like to use Dapper.

Step 6: Define Data Access Interfaces

Dapper API consists of various versions of the Query and Execute method, which are used to execute SQL commands and return appropriate results depending on the method's return type.

We would utilize these methods by defining an interface that would contain all the methods that we intend to implement to perform CRUD operations using Dapper.

Create and add IEmployeeService interface in the project's Data folder to define the methods used to perform CRUD operations on the Employees table:

interface IEmployeeService
{
   Task<IEnumerable<Employee>> GetEmployees();
   Task<bool> CreateEmployee(Employee employee);
   Task<bool> EditEmployee(int EmpId, Employee employee);
   Task<Employee> SingleEmployee(int EmpId);
   Task<bool> DeleteEmployee(int EmpId);
}

Similarly, create and add IProductService interface in the project's Data folder to define the methods used to perform CRUD operations on the Products table:

interface IProductService
{
   Task<IEnumerable<Product>> GetProducts();
}

Step 7: Create Classes Implementing CRUD Operations

The interfaces defined above must be implemented through classes to add expected definitions to the methods. The method definitions would define SQL queries for Create, Update, Read, Delete operations, and execute them using appropriate methods from Dapper API.

Create and add EmployeeService class to project's Data folder. This class implements the IEmployeeService interface.

In the code below, the QueryAsync, QueryFirstOrDefaultAsync methods from Dapper API have been used to perform the Read operation, and the ExecuteAsync method has been used to perform other operations, including Create, Update and Delete on the Employees table.

public class EmployeeService: IEmployeeService
{
   private readonly SqlConnectionConfiguration _configuration;
   public EmployeeService(SqlConnectionConfiguration configuration)
   {
      _configuration = configuration;
   }
 //Create Operation
   public async Task<bool> CreateEmployee(Employee employee)
   {
      var parameters = new DynamicParameters();
      parameters.Add("FirstName", employee.FirstName, DbType.String);
      parameters.Add("LastName", employee.LastName, DbType.String);
      parameters.Add("Title", employee.Title, DbType.String);
      parameters.Add("HireDate", employee.HireDate, DbType.String);
      parameters.Add("Country", employee.Country, DbType.String);

      using (IDbConnection conn = new SqlConnection(_configuration.Value))
      {
         try
         {
            string insertSql = "INSERT INTO Employees (FirstName, LastName, Title, HireDate, Country) VALUES (@FirstName, @LastName, @Title, @HireDate, @Country)";
            await conn.ExecuteAsync(insertSql, parameters);
         }
         catch (Exception ex)
         {
             throw ex;
         }
       }
     return true;
   }

   //Read Operation
   public async Task<IEnumerable<Employee>> GetEmployees()
   {
      IEnumerable<Employee> employees;

      using (IDbConnection conn = new SqlConnection(_configuration.Value))
      {
         try
         {
            var readSql = "Select EmployeeId, FirstName, LastName, Title, HireDate, Country from Employees";
            employees = await conn.QueryAsync<Employee>(readSql);
         }
         catch (Exception ex)
         {
            throw ex;
         }
       }
      return employees;
}

    //Read Operation using WHERE clause
    public async Task<Employee> SingleEmployee(int EmpId)
    {
       var parameters = new DynamicParameters();
       parameters.Add("EId", EmpId, DbType.Int32);

       Employee employee = new Employee();

       using (IDbConnection conn = new SqlConnection(_configuration.Value))
       {
          try
          {
             string uSql = "Select * from Employees WHERE EmployeeId = @EId";
             employee = await conn.QueryFirstOrDefaultAsync<Employee>(uSql, parameters);
          }
          catch (Exception ex)
          {
             throw ex;
          }
       }
      return employee;
    }

    //Update Operation
    public async Task<bool> EditEmployee(int EmpId, Employee employee)
    {
       var parameters = new DynamicParameters();
       parameters.Add("EmpId", EmpId);
       parameters.Add("FirstName", employee.FirstName, DbType.String);
       parameters.Add("LastName", employee.LastName, DbType.String);
       parameters.Add("Title", employee.Title, DbType.String);
       parameters.Add("HireDate", employee.HireDate, DbType.String);
       parameters.Add("Country", employee.Country, DbType.String);

       using (IDbConnection conn = new SqlConnection(_configuration.Value))
       {
          try
          {
             string updateSql = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, Title = @Title, HireDate = @HireDate, Country = @Country WHERE EmployeeId = @EmpId";
             await conn.ExecuteAsync(updateSql, parameters);
          }
          catch (Exception ex)
          {
            throw ex;
          }
        }
       return true;
     }

     //Delete Operation
     public async Task<bool> DeleteEmployee(int EmpId)
     {
        var parameters = new DynamicParameters();
        parameters.Add("EId", EmpId, DbType.Int32);

        using (IDbConnection conn = new SqlConnection(_configuration.Value))
        {
            try
            {
              string deleteSql = "DELETE FROM Employees WHERE EmployeeId = @EId";
              await conn.ExecuteAsync(deleteSql, parameters);
            }
            catch (Exception ex)
            {
               throw ex;
            }
          }
        return true;
       }
    }
}

Similarly, create and add ProductService class to the project's Data folder. This class implements IProductService Interface.

In the code below, the QueryAsync method of Dapper API has been used to execute the stored procedure 'Alphabetical List of Products and get the resulting records from the Products table.

public class ProductService: IProductService
{
   private readonly SqlConnectionConfiguration _configuration;
   public ProductService(SqlConnectionConfiguration configuration)
   {
      _configuration = configuration;
   }

   //Executing Stored Procedure
   public async Task<IEnumerable<Product>> GetProducts()
   {
       IEnumerable<Product> products;

       using (IDbConnection conn = new SqlConnection(_configuration.Value))
       {
          try
          {
             products = await conn.QueryAsync<Product>("[Alphabetical List of Products]", commandType: CommandType.StoredProcedure);
          }
          catch (Exception ex)
          {
             throw ex;
          }
        }
      return products;
    }
 }

Step 8: Register Data Access Classes

To inject and access the interfaces and their methods defined above in razor components, we must register them in the startup.cs file by adding the following code to the ConfigureServices method:

public void ConfigureServices(IServiceCollection services)
{
   services.AddRazorPages();
   services.AddServerSideBlazor();

   //Register Employee Service/Product Service
   services.AddScoped<IEmployeeService, EmployeeService>();
   services.AddScoped<IProductService, ProductService>();

   //Register SQL Connection Configuration Service
   var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDbContext"));
   services.AddSingleton(sqlConnectionConfiguration);
}

Step 9: Creating Razor Components to Perform CRUD Operations

Read Operation:

Create and add a razor component named ListEmployees in the Pages folder.

This razor component will perform the Read operation by specifying the appropriate SQL query and invoking the GetEmployees method from the EmployeeService class. The code also initializes the ComponentOne Blazor FlexGrid control and binds the fetched data to FlexGrid. The page will render three hyperlinks at the top to perform the Create, Update and Delete operations on FlexGrid by navigating to the designated Razor pages for performing the specified operations.

A navigation route to this page must be added to the Navigation menu of the Blazor application to let the user explore the CRUD operations in action.

@page "/listemployees"

@using C1.Blazor.Grid
@using C1.Blazor.Core
@using TestBlazorDapper.Data
@inject IEmployeeService EmployeeService

<h2>Employee Details</h2>

<br />
<p>
    <a href="/addemployee">Create New Employee</a> |
    <a href="/editemployee/@selRow">Edit existing Employee</a> |
    <a href="/deleteemployee/@selRow">Delete Employee</a>
</p>

<br />
<FlexGrid @ref="flexgrid" ItemsSource="employees" AutoGenerateColumns="false" SelectionChanged="OnSelectionChanged" SelectionMode="GridSelectionMode.Row">
    <FlexGridColumns>
        <GridColumn Binding="EmployeeId" Width="120" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center" />
        <GridColumn Binding="FirstName" Width="GridLength.Star" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center" />
        <GridColumn Binding="LastName" Width="GridLength.Star" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center" />
        <GridColumn Binding="Title" Width="GridLength.Star" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center" />
        <GridColumn Binding="HireDate" Width="GridLength.Star" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center" />
        <GridColumn Binding="Country" Width="GridLength.Star" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center" />
    </FlexGridColumns>
</FlexGrid>

@code {
    [Parameter]
    public int selRow { get; set; }

    FlexGrid flexgrid;

    IEnumerable<Employee> employees;

    protected override async Task OnInitializedAsync()
    {
        employees = await EmployeeService.GetEmployees();
    }

    public void OnSelectionChanged(object sender, GridCellRangeEventArgs e)
    {
        selRow = (int)flexgrid[e.CellRange.Row, 0];
        StateHasChanged();
    }   
}

Here is a quick view of ListEmployees razor component, showcasing FlexGrid displaying fetched data along with three links to perform other CRUD operations:

EmployeeDetails

Create Operation:

Create and add a razor component named AddEmployee in the Pages folder.

This razor component will perform the Create operation by specifying the appropriate SQL query and invoking the CreateEmployee method from the EmployeeService class. This code will append a new record in the database and navigate to the ListEmployees. It will be a razor page to display the updated data fetched from the database.

@page "/addemployee"

@using TestBlazorDapper.Data
@inject NavigationManager NavigationManager
@inject IEmployeeService EmployeeService

<h2>Create Employee</h2>
<hr />
<form>
    <div class="row">
        <div class="col-md-8">
            <div class="form-group">
                <label for="FirstName" class="control-label">Name</label>
                <input for="FirstName" class="form-control" @bind="@employee.FirstName" />
            </div>
            <div class="form-group">
                <label for="LastName" class="control-label">LastName</label>
                <input for="LastName" class="form-control" @bind="@employee.LastName" />
            </div>
            <div class="form-group">
                <label for="Title" class="control-label">Title</label>
                <input for="Title" class="form-control" @bind="@employee.Title" />
            </div>
            <div class="form-group">
                <label for="HireDate" class="control-label">HireDate</label>
                <input for="HireDate" class="form-control" @bind="@employee.HireDate" />
            </div>
            <div class="form-group">
                <label for="Country" class="control-label">Country</label>
                <input for="Country" class="form-control" @bind="@employee.Country" />                               
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-4">
            <div class="form-group">
                <input type="button" class="btn btn-primary" @onclick="@CreateEmployee" value="Save" />
                <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />
            </div>
        </div>
    </div>
</form>

@code {

    Employee employee = new Employee();       

    protected async Task CreateEmployee()
    {
        await EmployeeService.CreateEmployee(employee);
        NavigationManager.NavigateTo("listemployees");
    }

    void Cancel()
    {
        NavigationManager.NavigateTo("listemployees");
    }
}

Here is a quick view of the AddEmployee razor component:

CreateEmployee

Update Operation:

Create and add razor component named EditEmployee in the Pages folder.

This razor component will perform the Update operation by specifying the appropriate SQL query and invoking the EditEmployee method from the EmployeeService class. This code will update the selected record in the database and navigate back to ListEmployees.razor page to display the updated data fetched from database.

@page "/editemployee/{id:int}"

@using TestBlazorDapper.Data  
@inject IJSRuntime JsRuntime  
@inject NavigationManager NavigationManager  
@inject IEmployeeService EmployeeService

<h2>Edit Employee</h2>  
<hr />  
<form>  
    <div class="row">  
        <div class="col-md-8">  
            <div class="form-group">  
                <label for="FirstName" class="control-label">FirstName</label>  
                <input for="FirstName" class="form-control" @bind="@employee.FirstName" />  
            </div>  
            <div class="form-group">  
                <label for="LastName" class="control-label">LastName</label>  
                <input for="LastName" class="form-control" @bind="@employee.LastName" />  
            </div>  
            <div class="form-group">  
                <label for="Title" class="control-label">Title</label>  
                <input for="Title" class="form-control" @bind="@employee.Title" />  
            </div>  
            <div class="form-group">  
                <label for="HireDate" class="control-label">HireDate</label>  
                <input for="HireDate" class="form-control" @bind="@employee.HireDate" />  
            </div>  
            <div class="form-group">  
                <label for="Country" class="control-label">Country</label>  
                <input for="Country" class="form-control" @bind="@employee.Country" />  
            </div>  
        </div>  
    </div>  
    <div class="row">  
        <div class="form-group">  
            <input type="button" class="btn btn-primary" @onclick="@UpdateEmployee" value="Update" />  
            <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
        </div>  
    </div>  
</form>

@code {

    [Parameter]  
    public int id { get; set; }

    Employee employee = new Employee();

    protected override async Task OnInitializedAsync()  
    {  
        if (id > 0)  
        {  
            employee = await EmployeeService.SingleEmployee(id);  
        }  
        else  
        {  
            await JsRuntime.InvokeVoidAsync("alert", "Please select a record to edit!");  
            NavigationManager.NavigateTo("listemployees");  
        }  
    }

    protected async Task UpdateEmployee()  
    {        
       await EmployeeService.EditEmployee(id, employee);  
       NavigationManager.NavigateTo("listemployees");        
    }

    void Cancel()  
    {  
        NavigationManager.NavigateTo("listemployees");  
    }  
}

Delete Operation:

Create and add razor component named DeleteEmployee in the Pages folder.

This razor component will perform the Delete operation by specifying the appropriate SQL query and invoking the DeleteEmployee method from the EmployeeService class. This code will delete the selected record from the database and navigate back to ListEmployees.razor page to display the updated data fetched from database.

@page "/deleteemployee/{id:int}"

@using TestBlazorDapper.Data  
@inject IJSRuntime JsRuntime  
@inject NavigationManager NavigationManager  
@inject IEmployeeService EmployeeService

<h2>Confirm Delete</h2>  
<p>Are you sure you want to delete this Employee with Id :<b> @id</b></p>  
<br />  
<div class="col-md-4">  
    <table class="table">  
        <tr>  
            <td>FirstName</td>  
            <td>@employee.FirstName</td>  
        </tr>  
        <tr>  
            <td>LastName</td>  
            <td>@employee.LastName</td>  
        </tr>  
        <tr>  
            <td>Title</td>  
            <td>@employee.Title</td>  
        </tr>  
        <tr>  
            <td>HireDate</td>  
            <td>@employee.HireDate</td>  
        </tr>  
        <tr>  
            <td>Country</td>  
            <td>@employee.Country</td>  
        </tr>  
    </table>  
    <div class="form-group">  
        <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />  
        <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />  
    </div>  
</div>

@code {

    [Parameter]  
    public int id { get; set; }  
    Employee employee = new Employee();

    protected override async Task OnInitializedAsync()  
    {  
        if (id > 0)  
        {  
            employee = await EmployeeService.SingleEmployee(id);  
        }  
        else  
        {  
            await JsRuntime.InvokeVoidAsync("alert", "Please select a record to delete!");  
            NavigationManager.NavigateTo("listemployees");  
        }  
    }

    protected async Task Delete()  
    {  
        await EmployeeService.DeleteEmployee(id);  
        NavigationManager.NavigateTo("listemployees");  
    }

    void Cancel()  
    {  
        NavigationManager.NavigateTo("listemployees");  
    }  
}

Executing Stored Procedure:

Create and add razor component named ListProducts in the Pages folder.

This razor component will execute the stored procedure "Alphabetical List of Products" by invoking the GetProducts method from the ProductService class. This code will display the alphabetical list of Products along with other columns from the Products table.

A navigation route to this page must be added to the Navigation menu of the Blazor application to let user observe the Stored Procedure execution in action.

@page "/listproducts"

@using C1.Blazor.Core  
@using C1.Blazor.Grid  
@using TestBlazorDapper.Data  
@inject IProductService ProductService

<h3>Alphabetical List of Products</h3>

<br/>

<FlexGrid ItemsSource="products" AutoGenerateColumns="false">  
    <FlexGridColumns>         
        <GridColumn Binding="ProductName" Width="300" HorizontalAlignment="C1HorizontalAlignment.Left" HeaderHorizontalAlignment="C1HorizontalAlignment.Center"/>  
        <GridColumn Binding="QuantityPerUnit" Width="300" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center"/>  
        <GridColumn Binding="UnitPrice" Width="150" Format="n2" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center"/>  
        <GridColumn Binding="ReorderLevel" Width="150" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center"/>  
        <GridColumn Binding="Discontinued" Width="150" HorizontalAlignment="C1HorizontalAlignment.Center" HeaderHorizontalAlignment="C1HorizontalAlignment.Center"/>  
    </FlexGridColumns>  
</FlexGrid>

@code {      
    IEnumerable<Product> products;

    protected override async Task OnInitializedAsync()  
    {  
        products = await ProductService.GetProducts();  
    }  
}

Append the following code to NavMenu.razor file to add routes for ListEmployees.razor and ListProducts.razor pages:

<li class="nav-item">  
    <NavLink class="nav-link" href="listemployees">  
        <span class="oi oi-list-rich" aria-hidden="true"></span> CRUD Operations Demo  
    </NavLink>  
</li>  
<li class="nav-item">  
    <NavLink class="nav-link" href="listproducts">  
        <span class="oi oi-list-rich" aria-hidden="true"></span> Stored Procedure Demo  
    </NavLink>  
</li>

Step 11: Dapper in Action

We are done implementing the integration of Dapper with ComponentOne Blazor FlexGrid. Execute the application to perform CRUD operations and see Dapper in action, the GIF below depicts the same:

Execution