Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (151).NET Core  (25)Angular  (49)ASP.NET  (48)ASP.NET Core  (64)ASP.NET MVC  (86)Azure  (28)Blazor  (96)DocIO  (20)Essential JS 2  (71)Essential Studio  (163)File Formats  (20)Flutter  (109)JavaScript  (148)Microsoft  (101)PDF  (59)React  (33)Succinctly series  (122)Syncfusion  (479)TypeScript  (30)Uno Platform  (3)UWP  (4)Vue  (26)Webinar  (25)Windows Forms  (55)WinUI  (20)WPF  (121)Xamarin  (130)XlsIO  (24)Other CategoriesBarcode  (4)BI  (29)Bold BI  (3)Build conference  (6)Business intelligence  (53)Button  (4)C#  (107)Chart  (50)Cloud  (9)Company  (445)Dashboard  (6)Data Science  (3)Data Validation  (5)DataGrid  (47)Development  (257)Doc  (7)DockingManager  (1)eBook  (93)Enterprise  (22)Entity Framework  (5)Essential Tools  (14)Excel  (20)Extensions  (13)File Manager  (4)Gantt  (9)Gauge  (6)Git  (4)Grid  (27)HTML  (9)Installer  (2)Knockout  (2)LINQPad  (1)Linux  (2)M-Commerce  (1)Metro Studio  (11)Mobile  (225)Mobile MVC  (9)OLAP server  (1)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (22)Performance  (6)PHP  (1)PivotGrid  (4)Predictive Analytics  (6)Report Server  (3)Reporting  (10)Reporting / Back Office  (11)Rich Text Editor  (5)Road Map  (9)Scheduler  (26)SfDataGrid  (9)Silverlight  (21)Sneak Peek  (19)Solution Services  (2)Spreadsheet  (9)SQL  (7)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (11)Tips and Tricks  (59)UI  (128)Uncategorized  (68)Unix  (2)User interface  (68)Visual State Manager  (1)Visual Studio  (19)Visual Studio Code  (11)Web  (212)What's new  (138)Windows 8  (19)Windows App  (1)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)
Share on twitter
Share on facebook
Share on linkedin
How to build a Blazor CRUD application with Dapper

How to Build a Blazor CRUD Application with Dapper

In this blog post, we are going to discuss how to bind the Syncfusion Blazor DataGrid with a database using Dapper and perform CRUD operations. To demonstrate this, we are going to create a bug tracker database table in MS 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.

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.
    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
Bugs Table

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

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

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

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
Structure of the Model Class

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
Setting the Connection String to the Database

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.

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
Registering the BugDataAccessLayer

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.

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

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.

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

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.

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.

@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
Providing details to create a new row

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
Bug table with the new row

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
Updating a value in an existing row

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
Updated Row

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 .

Conclusion

In this blog, we have learned the procedure to create a Blazor server-side application, include the Syncfusion Blazor 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, Direct-Trac, or 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:

Tags:

Share this post:

Share on twitter
Share on facebook
Share on linkedin

Comments (14)

Dapper isn’t really doing much here: wouldn’t using scaffold with EFCore be easier? – you wouldn’t have to build the object classes manually.

If you are looking to use Entity Framework instead of Dapper then we would like to recommend you to take look at the below post which explains configuring and consuming data from Entity framework with CRUD functionality enabled.
https://www.syncfusion.com/blogs/post/how-to-build-a-blazor-wasm-crud-application-with-entity-framework.aspx

Is there post that is Blazor Server + EF + DataGrid?

I believe the below documentation sections would be helpful for you.
https://blazor.syncfusion.com/documentation/datagrid/data-binding/#entity-framework
https://blazor.syncfusion.com/documentation/datagrid/editing/#entity-framework
Grid configuration will be the same for both WASM and Server flavor applications. The only difference between them how and where you are going to add the Entity Framework and Web API controller.

Yes, it is doing in class BugDataAccessLayer. In that class you can call stored procedure on server or you can define sql functions as he did (select * from —).
I would be happyer if example would be more complicated. For example some master detail insert where all pass or nothing. I am not sure can we use User Defined Table Types in Dapper, such example is interesting.

Johann Alexander Quintero Cortés

Thanks for sharing.

As a basic example it is still a good way to get to know the capabilities of the SfGrid

Is it possible we can have an example of the same but with some of the other controls such as SfChart please?

Very descriptive post!!!

ps.: I think you forgot to register the ‘BugDataAdaptor’

Thanks for the appreciation. Yes, I see that I have missed that point. I will update the content. However, it has been added to the GitHub sample.
https://github.com/SyncfusionExamples/blazor-datagrid-dapper-crud/blob/main/Dapper.CRUD/Startup.cs#L35
Thanks for noticing !!

Lukasz Pomianowski

In the server side isn’t a mistake to use scoped connection ? The docs for EF mention DBContextFactory

Madhu Sudhanan P

Yes for using Ef Core in the Blazor server-side host model, the below approach should be followed.
https://docs.microsoft.com/en-us/aspnet/core/blazor/blazor-server-ef-core?view=aspnetcore-5.0

Don’t you have this example as a Blazor Web assembly app? I don’t get the BugDataAdapter resolved in the client project.

Madhu Sudhanan P

Please ensure whether you have added BugDataAdaptor in the service collection as follows.
public void ConfigureServices(IServiceCollection services)
{
. . . .
services.AddScoped();
services.AddScoped();
}
With the above code, you can inject BugDataAccessLayer within BugDataAdaptor using constructor injection.
https://blazor.syncfusion.com/documentation/datagrid/custom-binding/#inject-service-into-custom-adaptor

Hello,

Are you able to build crud operations with dapper for the scheduler component?

Comments are closed.

Popular Now

Be the first to get updates

Subscribe RSS feed
Scroll To Top