Create Blazor CRUD Application with PostgreSQL and Dapper

Suresh Mohan - May 11 '22 - - Dev Community

In this blog, we will create a Blazor CRUD application with Dapper and the PostgreSQL database for Blazor server-side apps. We will use the Syncfusion Blazor DataGrid component to perform CRUD operations in this app.

What is Dapper?

Dapper is an open-source and micro ORM (object-relational mapping) product developed by the StackOverflow team. It is very lightweight and straightforward to use with a project. It supports PostgreSQL, MySQL, SQL Server, and other databases.

Dapper extends the IDbConnection interface, which provides an open data source connection supported by the .NET Framework. We also have to write queries when using Dapper. It’s simple to execute a SQL query on a database and have the results mapped to a C# domain class.

Dapper can be configured and is available as a NuGet package.

What is PostgreSQL?

PostgreSQL is an open-source database management system for enterprises. It supports both SQL and JSON for relational and nonrelational queries for flexibility and SQL compliance. In addition, advanced data types and performance optimization capabilities, which are only accessible in expensive production databases such as Oracle and SQL Server, are supported by PostgreSQL.

Prerequisites

Step 1: Create the database.

Use the PgAdmin to create the database using PostgreSQL. In the database, create a table named Employee to manage employee details like in the following figure.

Create the databaseStep 2: Create the Blazor server-side application.

Open Visual Studio 2022 and create a new Blazor server-side project.

Make sure to select .NET 6.0 as the framework for this project and set the authenticate type to none.

Step 3: Install and configure PostgreSQL package.

Now, open the NuGet package manager of the project solution, search for the package Npsql 6.0.3 and install it.

This package connects and interacts with the PostgreSQL server in the .NET environment.

Now, add the connection string in the project to connect it with the PostgreSQL server. Use the appsettings.json file to add the connection string like in the following code example.

{
    "Logging": {
        "LogLevel": {
            "Default": "Information"
            "Microsoft.AspNetCore": "Warning"
        }
    }
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "Employeedb": Host=localhost;Port=5432;Database=testdb;User Id= *******;Password=******* ;"
    }
}
Enter fullscreen mode Exit fullscreen mode

Step 4: Install Dapper package in the project.

Open the NuGet package manager, search for Dapper, and install the dapper package.

Then, add the following configuration in the Program.cs to map the column name with underscores to the model class.

Using Dapper

DefaultTypeMap.MatchNamesWithUnderscores = true;
Enter fullscreen mode Exit fullscreen mode

Step 5: Create a database service.

Next, create a model class to interact with the database using Dapper.

Then, create a directory named Models in the root directory. Create an Employee model class inside the Model directory with the following code.

public class Employee

{
  public int Id { get; set; }
  public string Name { get; set; }
  public int Age { get; set; }
  public string Address { get; set; }
  public string MobileNumber { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

Create a directory named Services in the root directory and then create an interface named IDbService inside the Services folder, with the following code.

public interface IDbService
{
  Task<T> GetAsync<T>(string command, object parms);
  Task<List<T>> GetAll<T>(string command, object parms);
  Task<T> Insert<T>(string command, object parms);
  Task<T> Update<T>(string command, object parms);
  Task<T> Delete<T>(string command, object parms);
}
Enter fullscreen mode Exit fullscreen mode

Here, T refers to the type of parameter. We have used the T type parameter for the return type of the methods. Also, the methods accept two parameters. The command parameter is the query command, and params parameter refers to the object used for operation with PostgreSQL query.

Now, create the DbService class inherited from the IDbservice inside the Services folder, like in the following code example.

using Dapper;
using Npgsql;

public class DbService : IDbService
{
  private readonly IDbConnection _db;

  public DbService(IConfiguration config)
  {
    _db = new NpgsqlConnection(config.GetConnectionString("Employeedb"));
  }

  public async Task<T> GetAsync<T>(string command, object parms)
  {
    T result;
    result = (await _db.QueryAsync<T>(command, parms).ConfigureAwait(false)).FirstOrDefault();
  return result; 
  }

  public async Task<List<T>> GetAll<T>(string command, object parms)
  {
    List<T> result = new List<T>();
    result = (await _db.QueryAsync<T>(command, parms)).ToList();
    return result;
  } 

  public async Task<T> Insert<T>(string command, object parms)
  {
    T result;
    result = _db.Query<T>(command, parms).FirstOrDefault();
    return result;
  }

  public async Task<T> Update<T>(string command, object parms)
  {
    T result;
    result = _db.Query<T>(command, parms).FirstOrDefault();
    return result;
  }

  public async Task<T> Delete<T>(string command, object parms)
  {
    T result;
    result = _db.Query<T>(command, parms).FirstOrDefault();
    return result;
  }
}
Enter fullscreen mode Exit fullscreen mode

We have used IConfiguration to access the appsettings.json file and get the connection string. We then used the connection string to establish the connection with the database through the NpgsqlConnection method.

We implemented the IDbService interface in the DbService class, and it includes the implementation for the CRUD operations.

Step 6: Creating the Employee Service.

Now, create an IEmployeeService interface inside the Services directory in the project with the following code.

public interface IEmployeeService
{
  Task<bool> CreateEmployee(Employee employee);
  Task<List<Employee>> GetEmployeeList();
  Task<Employee> UpdateEmployee(Employee employee);
  Task<bool> DeleteEmployee(int key);
}
Enter fullscreen mode Exit fullscreen mode

Now, create the EmployeeService class inherited from IEmployeeService inside the Service directory.

public class EmployeeService : IEmployeeService
{
  private readonly IDbService _dbService;

  public EmployeeService(IDbService dbService)
  {
    _dbService = dbService;
  }

  public async Task<bool> CreateEmployee(Employee employee)
  {
    var result = await _dbService.Insert<int>("INSERT INTO public.employee (id,name, age, address, mobile_number) VALUES (@Id, @Name, @Age, @Address, @MobileNumber)", employee);
    return true;
  }

  public async Task<List<Employee>> GetEmployeeList()
  {
    var employeeList = await _dbService.GetAll<Employee>("SELECT * FROM public.employee", new { });
    return employeeList;
  }

  public async Task<Employee> UpdateEmployee(Employee employee)
  {
    var updateEmployee = await _dbService.Update<int>("Update public.employee SET name=@Name, age=@Age, address=@Address, mobile_number=@MobileNumber WHERE id=@Id", employee);
    return employee;
  }

  public async Task<bool> DeleteEmployee(int key)
  {
    var deleteEmployee = await _dbService.Delete<int>("DELETE FROM public.employee WHERE id=@Id", new { id = key });
    return true;
  }
}
Enter fullscreen mode Exit fullscreen mode

Here is an implementation of the IEmployeeService interface. In this case, we create the CRUD operations for employees. Also, we use queries for each operation with database services.

Step 7: Installing and configuring the Syncfusion Blazor DataGrid component.

First, you add two NuGet packages to your app for the Syncfusion Blazor DataGrid component. To do so, open the NuGet package manager and install:

  • Syncfusion.Blazor.Grid: This package adds the Syncfusion Blazor DataGrid component and works with the Blazor application.
  • Syncfusion.Blazor.Themes: This package adds and configures Blazor component themes as static web assets in the application.

Note: If you are new to using the Syncfusion Blazor DataGrid control, we highly recommend you go through the getting started documentation.

After adding the packages, add the following namespaces in the _Import.razor file.

@using Syncfusion.Blazor
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
Enter fullscreen mode Exit fullscreen mode

Then, configure and register the Blazor components in your application. To do so, open the program.cs file and add the following services.

var builder = WebApplication.CreateBuilder(args);

 // Add services to the container.
 builder.Services.AddRazorPages();
 builder.Services.AddServerSideBlazor();
 builder.Services.AddSyncfusionBlazor();

var app = builder.Build();
// Configure the HTTP request pipeline.
Enter fullscreen mode Exit fullscreen mode

Next, open the _Layout.cshtml file inside the pages folder and add the Syncfusion Bootstrap 5 theme inside the tag as follows.

<head>
  <link href="_content/Syncfusion.Blazor.Themes/bootstrap5.css" rel="stylesheet" />
</head>
Enter fullscreen mode Exit fullscreen mode

Open _Layout.cshtml file inside the pages directory and then add the script reference inside the tag.

<head>
  <link href="_content/Syncfusion.Blazor.Themes/bootstrap5.css" rel="stylesheet" /><script src="_content/Syncfusion.Blazor/scripts/syncfusion-blazor.min.js" type="text/javascript"></script>
</head>
Enter fullscreen mode Exit fullscreen mode

Step 8: Create the custom data adapter.

The DataGrid component’s comprehensive data-binding capability using the SfDataManager component is one of its most significant features. The SfDataManager handles data requests and responses, and it serves as an interface between the data source and the DataGrid. The SfDataManager has a few types of built-in adapters, and you are free to create your own custom adapters.

For our requirement, we’ll create a custom adapter for the Employee table to operate with the DataGrid. First, create a directory named Adapters in the root directory and then create a class named EmployeeAdapater, which inherits DataAdapter. Refer to the following code example.

public class EmployeeAdapter : DataAdapter
{
  private readonly IEmployeeService _employeeService;

  public EmployeeAdapter(IEmployeeService employeeService)
  {
    _employeeService = employeeService;
  }

  public override async Task<object> ReadAsync(DataManagerRequest dataManagerRequest, string key = null)
  {
    List<Employee> employees = await _employeeService.GetEmployeeList();
    int count = employees.Count;
    return dataManagerRequest.RequiresCounts ? new DataResult() { Result = employees, Count = count } : count;
  }

  public override async Task<object> InsertAsync(DataManager dataManager, object data, string key)
  {
    await _employeeService.CreateEmployee(data as Employee);
    return data;
  }

  public override async Task<object> RemoveAsync(DataManager dataManager, object data, string keyField, string key)
  {
    await _employeeService.DeleteEmployee(Convert.ToInt32(data));
    return keyField;
  }

  public override async Task<object> UpdateAsync(DataManager dataManager, object data, string keyField, string key)
  {
    await _employeeService.UpdateEmployee(data as Employee);
    return data;
  }
}
Enter fullscreen mode Exit fullscreen mode

We also injected an employee service using the IEmployeeService interface in order to use employee CRUD operations.

Step 9: Add the Syncfusion Blazor DataGrid component.

Add the Syncfusion Blazor DataGrid to the Index.razor page inside the page folder. Refer to the following code example.

@page "/"
@using BlazorCRUD.Adapters
@using BlazorCRUD.Models

<SfGrid TValue="Employee" ID="Grid" Toolbar="@(new List<string>() {"Add", "Delete", "Update", "Cancel"})">
 <SfDataManager AdapterInstance="typeof(EmployeeAdapter)" Adapter="Adapters.CustomAdapter"></SfDataManager>
 <GridEditSettings AllowEditing="true" AllowDeleting="true" AllowAdding="true" Mode="@EditMode.Normal"></GridEditSettings>
 <GridColumns>
   <GridColumn Field="@nameof(Employee.Id)" Width="50" IsPrimaryKey="true"></GridColumn>
   <GridColumn Field="@nameof(Employee.Name)" Width="100"></GridColumn>
   <GridColumn Field="@nameof(Employee.Age)"  Width="100"></GridColumn>
   <GridColumn Field="@nameof(Employee.Address)" Width="100"></GridColumn>
   <GridColumn Field="@nameof(Employee.MobileNumber)"  Width="100" HeaderText="Mobile Number"></GridColumn>
 </GridColumns>
</SfGrid>
Enter fullscreen mode Exit fullscreen mode

Step 10: Register the services and adapters.

Now, register the services and adapters in the program.cs file as follows.

builder.Services.AddScoped<IDbService, DbService>();
builder.Services.AddScoped<IEmployeeService, EmployeeService>();
builder.Services.AddScoped<EmployeeAdapter>();
Enter fullscreen mode Exit fullscreen mode

Step 11: Run the Blazor CRUD application.

Run the application. You will see a screen similar to the following screenshot. Click Add and type in the values of the fields. Then, click Update to add the data into the Employee table of the database.

Updating a new record in the Blazor app

After successfully adding a new record, the app will invoke the ReadAsync method in the EmployeeAdapter to fetch the records and display them in the app. Refer to the following screenshot.

Fetching and displaying the newly added record in the Blazor appDouble-clicking the table row will enable the edit mode of that particular row. Make the necessary changes and click Update to update the data in the database table.

Updating a row record in the Blazor app

To delete a row from the table, click the row to select it, and then click Delete. This action will call the RemoveAsync method of the EmployeeAdapter to remove the record from the table. Deleting a record from the Blazor app

Resource

For more information, refer to the project Blazor CRUD application with PostgreSQL and Dapper demo on GitHub.

Conclusion

In this blog, we learned how to create a Blazor CRUD application with the Dapper ORM tool and PostgreSQL. We demonstrated how to create a custom adapter for the Syncfusion Blazor DataGrid component using the Syncfusion DataManager and perform CRUD operations.

I hope this article will assist you in your next Blazor project. Please share if you have any concerns in the comments section below. Thank you for reading!

Syncfusion’s Blazor component suite offers over 70 UI components. They work with both server-side and client-side (WebAssembly) hosting models seamlessly. Use them to build marvelous applications!

If you have any questions or comments, you can contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!

Related blogs

Image
qr maker free | url shortening service- InShorten
Image
You Are Responsible For A Getting A ADHD Diagnosis Budget? 12 Top Notch Ways To Spend Your Money
Image
5 Killer Quora Answers To How Much Is An ADHD Assessment
Image
Are You Making The Most From Your Chesterfield Corner Leather Sofa?
Image
Should You Include Personal Interests on Your Resume?
Image
7 Principper for Microservices

microservices
Image
Cat Flap Installation Near Me
Image
Three Greatest Moments In Ultra Lightweight Folding Wheelchairs History
Image
Folding Powered Wheelchair: The Evolution Of Folding Powered Wheelchair
Image
Gamified Trading: Engaging the Next Generation of Trader

cryptocurrency
Image
The 12 Worst Types Of Accounts You Follow On Twitter
Image
The Top Online Assessment For ADHD Experts Have Been Doing Three Things
Image
Cat Flap Installation Cost
Image
15 Reasons You Must Love Folding Powered Wheelchair
Image
Magia Mebli do Sypialni: Stwórz Idealną Przestrzeń do Wypoczynku
Image
15 Amazing Facts About 2 Seater Chesterfield Couch That You Never Known
Image
Everything You Need to Know About Online Toto Sites
Image
المسح الأمني للكود باستخدام Amazon Q Developer

aws, developer, genai
Image
Tokenized Assets: The Future of Ownership and Trading

blockchain, cryptocurrency
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .