Create Blazor CRUD Application with PostgreSQL and Dapper
Tags: csharp, dotnet, webdev, blazor
Published: 2022-05-11 12:22:34

---
title: Create Blazor CRUD Application with PostgreSQL and Dapper
published: true
date: 2022-05-11 11:45:36 UTC
tags: csharp, dotnet, webdev, blazor
canonical_url: https://www.syncfusion.com/blogs/post/create-blazor-crud-application-with-postgresql-and-dapper.aspx
cover_image: https://www.syncfusion.com/blogs/wp-content/uploads/2022/05/Create-Blazor-CRUD-Application-with-PostgreSQL-and-Dapper-1-672x372.png
---

In this blog, we will create a Blazor CRUD application with [Dapper](https://en.wikipedia.org/wiki/Dapper_ORM "Wikipedia page for Dapper") and the [PostgreSQL](https://en.wikipedia.org/wiki/PostgreSQL "Wikipedia page for PostgreSQL") database for Blazor server-side apps. We will use the Syncfusion [Blazor DataGrid](https://www.syncfusion.com/blazor-components/blazor-datagrid "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](https://docs.microsoft.com/en-us/dotnet/api/system.data.idbconnection "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](https://www.nuget.org/packages/Dapper/ "Dapper 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](https://en.wikipedia.org/wiki/Oracle_Database "Wikiepedia page for Oracle Database") and [SQL Server](https://en.wikipedia.org/wiki/Microsoft_SQL_Server "Wikipedia page for Microsoft SQL Server"), are supported by PostgreSQL.

## Prerequisites

- [Visual Studio 2022](https://visualstudio.microsoft.com/vs/ "Visual Studio 2022")
- [PostgreSQL 13.0 with PgAdmin](https://www.postgresql.org/download/ "PostgreSQL 13.0 with PgAdmin")
- [.NET SDK 5.0](https://dotnet.microsoft.com/en-us/download/visual-studio-sdks ".NET SDK 5.0") or later (I used .NET 6.0)

## 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 database](https://www.syncfusion.com/blogs/wp-content/uploads/2022/05/Create-Blazor-CRUD-Application-with-PostgreSQL-and-Dapper.png)Step 2: Create the Blazor server-side application.

Open Visual Studio 2022 and [create a new Blazor server-side project](https://docs.microsoft.com/en-us/aspnet/core/blazor/tooling?pivots=windows&view=aspnetcore-6.0 "Tooling for ASP.NET Core Blazor documentation").

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.

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

```

## Step 4: Install Dapper package in the project.

Open the NuGet package manager, search for Dapper, and install the [dapper package](https://www.nuget.org/packages/Dapper/ "Dapper NuGet Package").

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

```csharp
Using Dapper

DefaultTypeMap.MatchNamesWithUnderscores = true;
```

## 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.

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

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

```csharp
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);
}
```

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.

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

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.

```csharp
public interface IEmployeeService
{
  Task<bool> CreateEmployee(Employee employee);
  Task<List<Employee>> GetEmployeeList();
  Task<Employee> UpdateEmployee(Employee employee);
  Task<bool> DeleteEmployee(int key);
}
```

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

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

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](https://blazor.syncfusion.com/documentation/datagrid/getting-started "Getting Started with Blazor DataGrid Component").

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

```js
@using Syncfusion.Blazor
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Data
```

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

```csharp
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.
```

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

```html
<head>
  <link href="_content/Syncfusion.Blazor.Themes/bootstrap5.css" rel="stylesheet" />
</head>
```

Open **\_Layout.cshtml** file inside the pages directory and then add the script reference inside the **<head>** tag.

```html
<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>
```

## Step 8: Create the custom data adapter.

The DataGrid component’s comprehensive data-binding capability using the [**SfDataManager**](https://blazor.syncfusion.com/documentation/data/getting-started "Getting Started with Blazor DataManager") 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.

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

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.

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

## Step 10: Register the services and adapters.

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

```csharp
builder.Services.AddScoped<IDbService, DbService>();
builder.Services.AddScoped<IEmployeeService, EmployeeService>();
builder.Services.AddScoped<EmployeeAdapter>();
```

## 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](https://www.syncfusion.com/blogs/wp-content/uploads/2022/05/Run-the-Blazor-CRUD-application..png)

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 app](https://www.syncfusion.com/blogs/wp-content/uploads/2022/05/Run-the-Blazor-CRUD-application.2.png)Double-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](https://www.syncfusion.com/blogs/wp-content/uploads/2022/05/Run-the-Blazor-CRUD-application.3.png)  
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](https://www.syncfusion.com/blogs/wp-content/uploads/2022/05/Run-the-Blazor-CRUD-application.4.png)

## Resource

For more information, refer to the project [Blazor CRUD application with PostgreSQL and Dapper demo](https://github.com/SyncfusionExamples/blazor-crud-with-postgresql-and-dapper "Blazor CRUD app with PostgreSQL and Dapper GitHub 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](https://www.syncfusion.com/blazor-components/blazor-datagrid "Blazor DataGrid") component using the Syncfusion [DataManager](https://blazor.syncfusion.com/documentation/data/getting-started "Getting Started with Blazor 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](https://www.syncfusion.com/blazor-components "Blazor UI components library") 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](https://www.syncfusion.com/forums "Syncfusion support forums"), [support portal](https://support.syncfusion.com/ "Syncfusion support portal"), or [feedback portal](https://www.syncfusion.com/feedback/ "Syncfusion feedback portal"). We are always happy to assist you!

## Related blogs

- [Exploring Syncfusion Blazor Components on Cross-Platform Desktop Apps Using Electron](https://dev.to/syncfusion/exploring-syncfusion-blazor-components-on-cross-platform-desktop-apps-using-electron-1g9k "Blog: Exploring Syncfusion Blazor Components on Cross-Platform Desktop Apps Using Electron")
- [Build a Blazor CRUD Application with Dapper](https://dev.to/syncfusion/how-to-build-a-blazor-crud-application-with-dapper-3m44 "Blog: Build a Blazor CRUD Application with Dapper")