How to Build a Blazor CRUD Application with Dapper
Tags: blazor, webdev, csharp, dotnet
Published: 2021-03-10 11:46:54

---
title: How to Build a Blazor CRUD Application with Dapper
published: true
date: 2021-03-10 11:30:54 UTC
tags: blazor, webdev, csharp, dotnet
canonical_url: https://www.syncfusion.com/blogs/post/build-blazor-crud-application-with-dapper.aspx
cover_image: https://www.syncfusion.com/blogs/wp-content/uploads/2021/03/How-to-build-a-Blazor-CRUD-application-with-Dapper-672x372.png
---

In this blog post, we are going to discuss how to bind the Syncfusion [Blazor DataGrid](https://www.syncfusion.com/blazor-components/blazor-datagrid "Link to Blazor DataGrid") with a database using [Dapper](https://github.com/StackExchange/Dapper "Link to StackExchange database for Dapper demo on GitHub") and perform CRUD operations. To demonstrate this, we are going to create a bug tracker database table in [MS SQL Server](https://www.microsoft.com/en-us/sql-server "Link to Microsoft SQL Server") and perform CRUD operations in that table in a Blazor server-side application.

## Dapper

Dapper is a simple object mapper for .NET that owns the title of **King of Micro ORM** in terms of speed, as it’s virtually as fast as using a raw ADO.NET data reader. It is available as a NuGet package. An ORM (object-relational mapper) is responsible for mapping databases and programming languages.

Dapper is database independent. It does not contain any database-specific implementation. Arguably, it is chosen by people who want to write their own SQL query. It simply provides extension methods for the **IDbConnection** interface which helps users to query and perform CRUD operations in databases.

Dapper is in production use at [Stack Overflow](https://stackoverflow.com/ "Link to Stack Overflow").

## Creating the database

First things first, create a database named **BugTracker** and a table named **Bugs** to hold the list of bugs. The process to do this is explained in detail as follows:

1. Open SQL Server 2017.
2. Create a new database named **BugTracker**.
3. Right-click on the created database and select **New Query**.
4. Use the following SQL query to create a table named **Bugs**.
```sql
Create Table Bugs(
Id BigInt Identity(1,1) Primary Key Not Null,
Summary Varchar(400) Not Null,
BugPriority Varchar(100) Not Null,
Assignee Varchar(100),
BugStatus Varchar(100) Not Null)
```

The table design will look like the following screenshot.

![Bugs Table](https://www.syncfusion.com/blogs/wp-content/uploads/2020/12/Bugs-Table.png)<figcaption>Bugs Table</figcaption>

## Creating the Blazor server-side application

Open Visual Studio 2019, select **Create a New Project** , select **Blazor App** , and then click **Next**. ![Choose the Blazor App option from the list](https://www.syncfusion.com/blogs/wp-content/uploads/2021/03/Choose-the-Blazor-App-option-from-the-list-1.png)

In the next window, provide the project name and click **Create**. In this example, I am using **Dapper.CRUD** as the Blazor project name.  
 ![Provide the name as Dapper.CRUD for the Blazor app](https://www.syncfusion.com/blogs/wp-content/uploads/2021/03/Provide-the-name-as-Dapper.CRUD-for-the-Blazor-app.png)

Now select the **Blazor Server App** project template and click **Create** to create the Blazor server-side application.  
 ![Choose the Blazor Server App option from the list](https://www.syncfusion.com/blogs/wp-content/uploads/2021/03/Choose-the-Blazor-Server-App-option-from-the-list.png)

## Adding the Dapper package and creating a model class

To use Dapper and access the database in our Blazor application and perform CRUD operations, we need to install a few NuGet packages. Run the following commands in the **Package Manager Console** to install the necessary NuGet packages:

- **Install-Package Dapper -Version 2.0.35** : Enables us to use Dapper in our Blazor application.
- **Install-Package Microsoft.Data.SqlClient -Version 2.0.1** : Provides database access classes such as SqlConnection, SqlCommand, and so on. Also provides the data provider for MS SQL Server.

Most ORMs provide scaffolding options to create model classes, but Dapper doesn’t have any in-built scaffolding option. So, we need to create the model class manually. Here we are creating a class named **Bug.cs** in the **Data** folder as follows.

![Creating the Model Class](https://www.syncfusion.com/blogs/wp-content/uploads/2020/12/Creating-the-Model-Class.png)<figcaption>Structure of the Model Class</figcaption>

## Creating data access layer

Before creating the data access layer, we need to set the connection string of our database in the **appsettings.json** file as illustrated in the following screenshot.

![Setting the Connection String](https://www.syncfusion.com/blogs/wp-content/uploads/2020/12/Setting-the-Connection-String.png)<figcaption>Setting the Connection String to the Database</figcaption>

Now right-click on the **Data** folder and select **Class** to create a new class. Here I am naming it **BugDataAccessLayer.cs**. Now replace the code in this class with the following code, which contains code to handle CRUD operations in the **Bugs** table.

```csharp
public class BugDataAccessLayer
{
    public IConfiguration Configuration;
    private const string BUGTRACKER_DATABASE = "BugTrackerDatabase";
    private const string SELECT_BUG = "select * from bugs";
    public BugDataAccessLayer(IConfiguration configuration)
    {
       Configuration = configuration; //Inject configuration to access Connection string from appsettings.json.
    }

    public async Task<List<Bug>> GetBugsAsync()
    {
       using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(BUGTRACKER_DATABASE)))
       {
           db.Open();
           IEnumerable<Bug> result = await db.QueryAsync<Bug>(SELECT_BUG);
           return result.ToList();
       }
    }

    public async Task<int> GetBugCountAsync()
    {
       using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(BUGTRACKER_DATABASE)))
       {
            db.Open();
            int result = await db.ExecuteScalarAsync<int>("select count(*) from bugs");
            return result;
       }
    }

    public async Task AddBugAsync(Bug bug)
    {
       using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(BUGTRACKER_DATABASE)))
       {
            db.Open();
            await db.ExecuteAsync("insert into bugs (Summary, BugPriority, Assignee, BugStatus) values (@Summary, @BugPriority, @Assignee, @BugStatus)", bug);
       }
    }

    public async Task UpdateBugAsync(Bug bug)
    {
       using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(BUGTRACKER_DATABASE)))
       {
            db.Open();
            await db.ExecuteAsync("update bugs set Summary=@Summary, BugPriority=@BugPriority, Assignee=@Assignee, BugStatus=@BugStatus where id=@Id", bug);
       }
    }

    public async Task RemoveBugAsync(int bugid)
    {
       using (IDbConnection db = new SqlConnection(Configuration.GetConnectionString(BUGTRACKER_DATABASE)))
       {
            db.Open();
            await db.ExecuteAsync("delete from bugs Where id=@BugId", new { BugId = bugid });
       }
    }
}
```

In the above code example:

- In the constructor of the **BugDataAccessLayer** , we have injected **IConfiguration** , which help us to get the connection string provided in **appsettings.json**.
- **GetBugsAsync** method performs the select operation and returns the list of bugs from the Bugs table.
- **AddBugAsync** method inserts a new bug into the Bugs table.
- **UpdateBugAsync** method updates the given bug object in the table.
- **RemoveBugAsync** method removes the given bug by its ID.

Now register **BugDataAccessLayer** as a scoped service in the **Startup.cs** file as follows.

![Registering the BugDataAccessLayer](https://www.syncfusion.com/blogs/wp-content/uploads/2021/03/Registering-the-BugDataAccessLayer.png)<figcaption>Registering the BugDataAccessLayer</figcaption>

## Adding the Syncfusion Blazor component

To add a Syncfusion Blazor component to our application, run the following command in the **Package Manager Console:**

| `Install-Package Syncfusion.Blazor -Version 18.3.0.50` |

Once the Syncfusion Blazor package is added to our dependencies section successfully, open the **\_Import.razor** file and add the following namespaces, which are required to use Syncfusion Blazor components in this application.

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

Open the **Startup.cs** file and register the Syncfusion service in the **ConfigureService** method as follows.

```csharp
using Syncfusion.Blazor;

namespace Dapper.CRUD
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddSingleton<WeatherForecastService>();
            services.AddScoped<BugDataAccessLayer>();
            services.AddSyncfusionBlazor();
        }

       . . . . . . 
    }
}
```

Syncfusion Blazor provides different themes. They are:

- Bootstrap4
- Material
- Office 365
- Bootstrap
- High Contrast

In this demo application, I am going to use the Bootstrap4 theme. To add the theme, open the **Pages/\_Host.cshtml** file and add the following CSS reference code.

```html
<link href="_content/Syncfusion.Blazor/styles/bootstrap4.css" rel="stylesheet" />
```

## **Creating the custom data adapter**

One of the notable features in the **DataGrid** is its flexible data-binding capability using the **SfDataManager** component. It acts as an interface between the data source and the DataGrid to handle data requests and responses. **DataManager** provides various in-built data adapters to interact with data sources such as OData services, web APIs, and so on. If the built-in adapters provided don’t match our need, then we can simply use our own custom adapter to handle the data operations manually.

To bind the **Bug** table to the Syncfusion Blazor DataGrid, we are going to use the [custom data binding feature](https://blazor.syncfusion.com/documentation/datagrid/custom-binding/ "Link to custom databinding feature in Syncfusion Blazor DataGrid documentation").

The following points must be considered for creating a custom adapter:

- Our custom adapter must extend the **DataAdaptor ** class.
- Override available CRUD methods to handle data querying and manipulation.
- Register our custom adapter class as service in the **Startup.cs** file.

Now, let’s create a new class named **BugDataAdaptor.cs** in the **Data** folder and replace the code in that class with the following.

```csharp
public class BugDataAdaptor: DataAdaptor
{
   private BugDataAccessLayer _dataLayer;
   public BugDataAdaptor(BugDataAccessLayer bugDataAccessLayer)
   {
      _dataLayer = bugDataAccessLayer;
   }

   public override async Task<object> ReadAsync(DataManagerRequest dataManagerRequest, string key = null)
   {
       List<Bug> bugs = await _dataLayer.GetBugsAsync();
       int count = await _dataLayer.GetBugCountAsync();
       return dataManagerRequest.RequiresCounts ? new DataResult() { Result = bugs, Count = count } : count;
   }

   public override async Task<object> InsertAsync(DataManager dataManager, object data, string key)
   {
       await _dataLayer.AddBugAsync(data as Bug);
       return data;
    }

    public override async Task<object> UpdateAsync(DataManager dataManager, object data, string keyField, string key)
    {
        await _dataLayer.UpdateBugAsync(data as Bug);
        return data;
    }

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

```

In the above code example, we:

- Extended the **BugDataAdaptor** class from the **DataAdaptor ** class.
- Injected a **BugDataAccessLayer** instance to perform data operations.
- Handled the CRUD methods.

## Adding the Syncfusion Blazor DataGrid component

Now that the application is configured to use Syncfusion Blazor components, let’s include the following DataGrid component code in the **Index.razor** page.

```html
@using Dapper.CRUD.Data

<SfGrid TValue="Bug" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel" })">
    <SfDataManager AdaptorInstance="typeof(BugDataAdaptor)" Adaptor="Adaptors.CustomAdaptor"></SfDataManager>
    <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>
    <GridColumns>
        <GridColumn Field="@nameof(Bug.Id)" IsPrimaryKey="true" Visible="false"></GridColumn>
        <GridColumn Field="@nameof(Bug.Summary)" Width="100"></GridColumn>
        <GridColumn Field="@nameof(Bug.BugPriority)" HeaderText="Priority" Width="100"></GridColumn>
        <GridColumn Field="@nameof(Bug.Assignee)" Width="100"></GridColumn>
        <GridColumn Field="@nameof(Bug.BugStatus)" HeaderText="Status" Width="100"></GridColumn>
    </GridColumns>
</SfGrid>
```

In the above code example, we:

- Defined the **SfDataManager** component to provide a data source to the grid. You can notice that we have specified the **AdaptorInstance** property with the type of the custom adapter we created in previous step and mentioned **Adaptor** property as **Adaptors.CustomAdaptor**.
- Specified **TValue** as **Bug ** class.
- Enabled editing using the **GridEditSettings ** component.
- Mentioned the **Toolbar** property with required toolbar options.
- Mentioned the **Id** column as a primary key column using **IsPrimaryKey** property and made it hidden.

## Create a row

To create a new row in Blazor app, click the **Add** toolbar button. The new record edit form will look similar to the following screenshot.

![Providing Record Details for a New Row](https://www.syncfusion.com/blogs/wp-content/uploads/2020/12/Providing-Record-Details-for-a-New-Row.png)<figcaption>Providing details to create a new row</figcaption>

Clicking the **Update** toolbar button will call the **InsertAsync** method of our **BugDataAdaptor** to insert the record in the **Bug** table. A successfully inserted record in the grid will look similar to the following screenshot.

![Bug Table with New Row](https://www.syncfusion.com/blogs/wp-content/uploads/2020/12/Bug-Table-with-New-Row.png)<figcaption>Bug table with the new row</figcaption>

## Update a row

To edit a row in Blazor app, select any row and the click the **Edit** toolbar button. The edit form will look similar to the following screenshot.

![Updating a Row](https://www.syncfusion.com/blogs/wp-content/uploads/2020/12/Updating-a-Row.png)<figcaption>Updating a value in an existing row</figcaption>

Here we are updating the value of the **Status** field from **Not started** to **In progress**. Clicking the **Update** toolbar button will call the **UpdateAsync** method of our **BugDataAdaptor** to update the record in the **Bug** table. We have now successfully updated the record. Now the grid will look similar to the following screenshot.

![Updated Row](https://www.syncfusion.com/blogs/wp-content/uploads/2020/12/Updated-Row.png)

## Delete a row

To delete a row in Blazor app, select any row and  click the **Delete** toolbar button. Clicking the **Delete** button will call the **RemoveAsync** method of our **BugDataAdaptor** to delete the record from the **Bug** table.

## Resource

For more information, you can check out the [Blazor DataGrid CRUD application using Dapper demo](https://github.com/SyncfusionExamples/blazor-datagrid-dapper-crud "LInk to Blazor DataGrid CRUD application using Dapper demo on GitHub") .

## Conclusion

In this blog, we have learned the procedure to create a Blazor server-side application, include the Syncfusion [Blazor DataGrid](https://www.syncfusion.com/blazor-components/blazor-datagrid "Link to the Syncfusion DataGrid") component in it and perform CRUD operations using Dapper ORM.

If you have any questions, please let us know in the comments section below. You can also contact us through our [support forums](https://www.syncfusion.com/forums "Link to the Syncfusion support forum"), [Direct-Trac](https://www.syncfusion.com/account/login "Link to the Syncfusion support system Direct Trac"), or [feedback portal](https://www.syncfusion.com/feedback/blazor-components "Link to Syncfusion Feedback Portal"). We are always happy to assist you!

If you like this blog post, we think you’ll also like the following articles too:

- [Send Emails and Reminders for Events in Blazor Scheduler](https://dev.to/syncfusion/how-to-send-emails-and-reminders-for-events-in-blazor-scheduler-1095 "Link to the article How to Send Emails and Reminders for Events in Blazor Scheduler") [Blog]
- [Perform CRUD Operations Using Blazor Preview 9 and Entity Framework Core 3.0](https://dev.to/karthickramasamy08/how-to-perform-crud-operations-using-blazor-preview-9-and-entity-framework-core-3-0-40bl-temp-slug-4497346 "Link to the article How to Perform CRUD Operations Using Blazor Preview 9 and Entity Framework Core 3.0") [Blog]
- [Build a Blazor WebAssembly CRUD Application with Entity Framework](https://dev.to/syncfusion/how-to-build-a-blazor-wasm-crud-application-with-entity-framework-2lm3 "Link to the article How to Build a Blazor WebAssembly CRUD Application with Entity Framework") [Blog]
- [_Blazor Succinctly_](https://www.syncfusion.com/ebooks/blazor-succinctly "Link to the eBook Blazor Succinctly") [eBook]
- _[Blazor WebAssembly Succinctly](https://www.syncfusion.com/ebooks/blazor_webassembly_succinctly "Link to the eBook Blazor WebAssembly Succinctly")_ [eBook]