Combine ADO.NET, EF Core And Dapper In Same Blazor App

Introduction

Blazor is a new framework built by Microsoft for creating interactive client-side web UI with a .NET codebase. We can write both client-side and server-side code in C#.NET itself. I have already written four articles about the Blazor server on C# Corner. Please refer to the below articles for more basics about the Blazor framework.

• Create A Simple Blazor Server Application With .NET Core 3.0
• Create a Single Page App with Blazor Server and Entity Framework Core 3.0
• Combine SQL-DB and MongoDB in the Same Blazor App
• Create a Blazor Server SPA With Dapper

We will see how to use ADO.NET, EF Core, and Dapper with the Blazor application. I have combined these three different approaches in the same Blazor application. So that, a person can get an idea about these different methods from one place. Please note, that this is an experimental effort from my side to combine different methodologies in a single post.

ADO.NET provides consistent access to data sources such as SQL Server and XML and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain.

Entity Framework is an open-source ORM framework for .NET applications supported by Microsoft. It enables developers to work with data using objects of domain-specific classes without focusing on the underlying database tables and columns where this data is stored. With the Entity Framework, developers can work at a higher level of abstraction when they deal with data and can create and maintain data-oriented applications with less code compared with traditional applications.

Dapper is a micro ORM (Object Relational Mapper) that helps to map the native query output to a domain class. It is a high-performance data access system built by the StackOverflow team and released as open source. If your project prefers writing stored procedures or writing raw SQL queries instead of using full-fledged ORM tools like EntityFramework, then Dapper is a better choice for you. Using Dapper, it is very easy to execute an SQL query against a database and get the result mapped to the C# domain class.

We will create a Blazor application in Visual Studio 2019 and create three different services for ADO.NET, EF Core, and Dapper. We will use a single interface for all these services. We will register these services in the Startup class one by one and inject them inside the razor components. We will create four razor components for CRUD operations. We can see all the CRUD actions with ADO.NET, EF Core, and Dapper by registering in the Startup class. We will create a simple employee application to see these actions. I will explain all the actions step by step.

Create a database and table in SQL Server

Use below SQL script below to create a new database and table.

USE master

CREATE DATABASE SarathlalDb;

GO

USE SarathlalDb

CREATE TABLE [dbo].[Employees] (
    [Id]          NVARCHAR (250) NOT NULL,
    [Name]        NVARCHAR (250) NULL,
    [Department]  NVARCHAR (250) NULL,
    [Designation] NVARCHAR (250) NULL,
    [Company]     NVARCHAR (250) NULL,
    [City]        NVARCHAR (250) NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO

Create Blazor application in Visual Studio 2019

Choose the Blazor template from Visual Studio 2019 and create a Blazor application.

We must install the below libraries in our project.

• “Microsoft.Data.SqlClient”,
• “Microsoft.EntityFrameworkCore.SqlServer” and
• “Dapper”

We can create an “Employee” model class with the below properties. We will create all C# classes and services inside “Data” folder.

Employee. cs

namespace BlazorAdoNetEFCoreDapper.Data
{
    public class Employee
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public string Department { get; set; }
        public string Designation { get; set; }
        public string Company { get; set; }
        public string City { get; set; }
    }
}

Create a “SqlConnectionConfiguration” class to fetch SQL connection details from the appsettings.json file.

SqlConnectionConfiguration.cs

namespace BlazorAdoNetEFCoreDapper.Data
{
    public class SqlConnectionConfiguration
    {
        public SqlConnectionConfiguration(string value) => Value = value;
        public string Value { get; }
    }
}

Add SQL connection string in appsettings.json file.

We can create an “IEmployeeService” interface and declare the below methods.

IEmployeeService.cs

using System.Collections.Generic;
using System.Threading.Tasks;

namespace BlazorAdoNetEFCoreDapper.Data
{
    public interface IEmployeeService
    {
        Task<List<Employee>> GetEmployees();
        Task<bool> CreateEmployee(Employee employee);
        Task<bool> EditEmployee(string id, Employee employee);
        Task<Employee> SingleEmployee(string id);
        Task<bool> DeleteEmployee(string id);
    }
}

We can create an “EmployeeAdoNetService” service class for ADO.NET and implement the interface.

EmployeeAdoNetService.cs

using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BlazorAdoNetEFCoreDapper.Data
{
    public class EmployeeAdoNetService : IEmployeeService
    {
        private readonly SqlConnectionConfiguration _configuration;
        public EmployeeAdoNetService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }
        public async Task<bool> CreateEmployee(Employee employee)
        {
            using (SqlConnection con = new SqlConnection(_configuration.Value))
            {
                const string query = "insert into dbo.Employees (Id,Name,Department,Designation,Company,City) values(@Id,@Name,@Department,@Designation,@Company,@City)";
                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text,
                };

                cmd.Parameters.AddWithValue("@Id", Guid.NewGuid().ToString());
                cmd.Parameters.AddWithValue("@Name", employee.Name);
                cmd.Parameters.AddWithValue("@Department", employee.Department);
                cmd.Parameters.AddWithValue("@Designation", employee.Designation);
                cmd.Parameters.AddWithValue("@Company", employee.Company);
                cmd.Parameters.AddWithValue("@City", employee.City);

                con.Open();
                await cmd.ExecuteNonQueryAsync();

                con.Close();
                cmd.Dispose();
            }
            return true;
        }

        public async Task<bool> DeleteEmployee(string id)
        {
            using (SqlConnection con = new SqlConnection(_configuration.Value))
            {
                const string query = "delete dbo.Employees where Id=@Id";
                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text,
                };

                cmd.Parameters.AddWithValue("@Id", id);

                con.Open();
                await cmd.ExecuteNonQueryAsync();

                con.Close();
                cmd.Dispose();
            }
            return true;
        }

        public async Task<bool> EditEmployee(string id, Employee employee)
        {
            using (SqlConnection con = new SqlConnection(_configuration.Value))
            {
                const string query = "update dbo.Employees set Name = @Name, Department = @Department, Designation = @Designation, Company = @Company, City = @City where Id=@Id";
                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text,
                };

                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@Name", employee.Name);
                cmd.Parameters.AddWithValue("@Department", employee.Department);
                cmd.Parameters.AddWithValue("@Designation", employee.Designation);
                cmd.Parameters.AddWithValue("@Company", employee.Company);
                cmd.Parameters.AddWithValue("@City", employee.City);

                con.Open();
                await cmd.ExecuteNonQueryAsync();

                con.Close();
                cmd.Dispose();
            }
            return true;
        }

        public async Task<List<Employee>> GetEmployees()
        {
            List<Employee> employees = new List<Employee>();

            using (SqlConnection con = new SqlConnection(_configuration.Value))
            {
                const string query = "select * from dbo.Employees";
                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text
                };

                con.Open();
                SqlDataReader rdr = await cmd.ExecuteReaderAsync();

                while (rdr.Read())
                {
                    Employee employee = new Employee
                    {
                        Id = rdr["Id"].ToString(),
                        Name = rdr["Name"].ToString(),
                        Department = rdr["Department"].ToString(),
                        Designation = rdr["Designation"].ToString(),
                        Company = rdr["Company"].ToString(),
                        City = rdr["City"].ToString()
                    };
                    employees.Add(employee);
                }
                con.Close();
                cmd.Dispose();
            }
            return employees;
        }

        public async Task<Employee> SingleEmployee(string id)
        {
            Employee employee = new Employee();

            using (SqlConnection con = new SqlConnection(_configuration.Value))
            {
                const string query = "select * from dbo.Employees where Id = @Id";
                SqlCommand cmd = new SqlCommand(query, con)
                {
                    CommandType = CommandType.Text,
                };

                cmd.Parameters.AddWithValue("@Id", id);
                con.Open();
                SqlDataReader rdr = await cmd.ExecuteReaderAsync();

                if (rdr.Read())
                {

                    employee.Id = rdr["Id"].ToString();
                    employee.Name = rdr["Name"].ToString();
                    employee.Department = rdr["Department"].ToString();
                    employee.Designation = rdr["Designation"].ToString();
                    employee.Company = rdr["Company"].ToString();
                    employee.City = rdr["City"].ToString();
                }
                con.Close();
                cmd.Dispose();
            }
            return employee;
        }
    }
}

We have defined all the logic for ADO.NET operations in the above service class.

We need a “SqlDbContext” db context class for EF Core operations.

SqlDbContext.cs

using Microsoft.EntityFrameworkCore;

namespace BlazorAdoNetEFCoreDapper.Data
{
    public class SqlDbContext : DbContext
    {
        public SqlDbContext(DbContextOptions<SqlDbContext> options)
           : base(options)
        {
        }
        public DbSet<Employee> Employees { get; set; }
    }
}

We have inherited the DbContext class inside the above class and defined a DB set property for the Employees table from the SQL database.

We can create an “EmployeeEfService” service class for entity framework operations.

EmployeeEfService.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace BlazorAdoNetEFCoreDapper.Data
{
    public class EmployeeEfService : IEmployeeService
    {
        private readonly SqlDbContext _dbContext;

        public EmployeeEfService(SqlDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        public async Task<List<Employee>> GetEmployees()
        {
            return await _dbContext.Employees.ToListAsync();
        }

        public async Task<bool> CreateEmployee(Employee employee)
        {
            employee.Id = Guid.NewGuid().ToString();
            _dbContext.Add(employee);
            try
            {
                await _dbContext.SaveChangesAsync();
                return true;
            }
            catch (DbUpdateException)
            {
                return false;
            }

        }

        public async Task<Employee> SingleEmployee(string id)
        {
            return await _dbContext.Employees.FindAsync(id);
        }

        public async Task<bool> EditEmployee(string id, Employee employee)
        {
            if (id != employee.Id)
            {
                return false;
            }

            _dbContext.Entry(employee).State = EntityState.Modified;
            await _dbContext.SaveChangesAsync();
            return true;
        }

        public async Task<bool> DeleteEmployee(string id)
        {
            var employee = await _dbContext.Employees.FindAsync(id);
            if (employee == null)
            {
                return false;
            }

            _dbContext.Employees.Remove(employee);
            await _dbContext.SaveChangesAsync();
            return true;
        }
    }
}

We have defined all the logic for EF Core in the above service class.

We can create an “EmployeeDapperService” service class for dapper operations.

EmployeeDapperService.cs

using Dapper;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace BlazorAdoNetEFCoreDapper.Data
{
    public class EmployeeDapperService : IEmployeeService
    {
        private readonly SqlConnectionConfiguration _configuration;

        public EmployeeDapperService(SqlConnectionConfiguration configuration)
        {
            _configuration = configuration;
        }

        public async Task<bool> CreateEmployee(Employee employee)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                const string query = @"insert into dbo.Employees (Id,Name,Department,Designation,Company,City) values(@Id,@Name,@Department,@Designation,@Company,@City)";
                conn.Open();
                try
                {
                    await conn.ExecuteAsync(query, new { Id = Guid.NewGuid().ToString(), employee.Name, employee.Department, employee.Designation, employee.Company, employee.City }, commandType: CommandType.Text);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return true;
        }

        public async Task<bool> DeleteEmployee(string id)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                const string query = @"delete dbo.Employees where Id=@Id";
                conn.Open();
                try
                {
                    await conn.ExecuteAsync(query, new { id }, commandType: CommandType.Text);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return true;
        }

        public async Task<bool> EditEmployee(string id, Employee employee)
        {
            using (var conn = new SqlConnection(_configuration.Value))
            {
                const string query = @"update dbo.Employees set Name = @Name, Department = @Department, Designation = @Designation, Company = @Company, City = @City where Id=@Id";
                conn.Open();
                try
                {
                    await conn.ExecuteAsync(query, new { employee.Name, employee.Department, employee.Designation, employee.Company, employee.City, id }, commandType: CommandType.Text);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return true;
        }

        public async Task<List<Employee>> GetEmployees()
        {
            IEnumerable<Employee> employees;
            using (var conn = new SqlConnection(_configuration.Value))
            {
                const string query = @"select * from dbo.Employees";

                conn.Open();
                try
                {
                    employees = await conn.QueryAsync<Employee>(query, commandType: CommandType.Text);

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }

            }
            return employees.ToList();
        }

        public async Task<Employee> SingleEmployee(string id)
        {
            Employee employee = new Employee();
            using (var conn = new SqlConnection(_configuration.Value))
            {
                const string query = @"select * from dbo.Employees where Id=@Id";

                conn.Open();
                try
                {
                    employee = await conn.QueryFirstOrDefaultAsync<Employee>(query, new { id }, commandType: CommandType.Text);

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }

            }
            return employee;
        }
    }
}

We can register SQL connection configuration and entity framework DB context inside the Startup class

ConfigureServices method in the Startup class.

public void ConfigureServices(IServiceCollection services)
{
    services.AddRazorPages();
    services.AddServerSideBlazor();
    services.AddSingleton<WeatherForecastService>();

    services.AddDbContext<SqlDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("SqlDbContext")));

    var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDbContext"));
    services.AddSingleton(sqlConnectionConfiguration);

    services.AddServerSideBlazor(o => o.DetailedErrors = true);
}

We can create four razor components for CRUD operations inside “Pages” folder

ListEmployees.razor

@using BlazorAdoNetEFCoreDapper.Data

@page "/listemployees"
@inject IEmployeeService EmployeeService

<h2>Employee Details</h2>
<p>
    <a href="/addemployee">Create New Employee</a>
</p>
@if (employees == null)
{
    <img src="./basicloader.gif" />
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>Department</th>
                <th>Designation</th>
                <th>Company</th>
                <th>City</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var employee in employees)
            {
                <tr>
                    <td>@employee.Name</td>
                    <td>@employee.Department</td>
                    <td>@employee.Designation</td>
                    <td>@employee.Company</td>
                    <td>@employee.City</td>
                    <td>
                        <a href='/editemployee/@employee.Id'>Edit</a>
                        <a href='/deleteemployee/@employee.Id'>Delete</a>
                    </td>
                </tr>

            }
        </tbody>
    </table>
}

@code {
    List<Employee> employees;

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

AddEmployee.razor

@using BlazorAdoNetEFCoreDapper.Data

@page "/addemployee"
@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="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" @bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" @bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Company" class="control-label">Company</label>
                <input for="Company" class="form-control" @bind="@employee.Company" />
            </div>
            <div class="form-group">
                <label for="City" class="control-label">City</label>
                <input for="City" class="form-control" @bind="@employee.City" />
            </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");
    }
}

EditEmployee.razor

@using BlazorAdoNetEFCoreDapper.Data

@page "/editemployee/{id}"
@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="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" @bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Department" class="control-label">Department</label>
                <input for="Department" class="form-control" @bind="@employee.Department" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" @bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <label for="Company" class="control-label">Company</label>
                <input for="Company" class="form-control" @bind="@employee.Company" />
            </div>
            <div class="form-group">
                <label for="City" class="control-label">City</label>
                <input for="City" class="form-control" @bind="@employee.City" />
            </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 string id { get; set; }

    Employee employee = new Employee();

    protected override async Task OnInitializedAsync()
    {
        employee = await EmployeeService.SingleEmployee(id);
    }

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

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

DeleteEmployee.razor

@using BlazorAdoNetEFCoreDapper.Data

@page "/deleteemployee/{id}"
@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>Name</td>
            <td>@employee.Name</td>
        </tr>
        <tr>
            <td>Department</td>
            <td>@employee.Department</td>
        </tr>
        <tr>
            <td>Designation</td>
            <td>@employee.Designation</td>
        </tr>
        <tr>
            <td>Company</td>
            <td>@employee.Company</td>
        </tr>
        <tr>
            <td>City</td>
            <td>@employee.City</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 string id { get; set; }
    Employee employee = new Employee();

    protected override async Task OnInitializedAsync()
    {
        employee = await EmployeeService.SingleEmployee(id);
    }

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

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

We can modify the NavMenu shared component to add a navigation to the employee list page.

NavMenu.razor

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">ADO.NET, EF & Dapper</a>
    <button class="navbar-toggler" @onclick="ToggleNavMenu">
        <span class="navbar-toggler-icon"></span>
    </button>
</div>

<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
    <ul class="nav flex-column">
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="oi oi-home" aria-hidden="true"></span> Home
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="counter">
                <span class="oi oi-plus" aria-hidden="true"></span> Counter
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="listemployees">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Employee data
            </NavLink>
        </li>
    </ul>
</div>

@code {
    bool collapseNavMenu = true;

    string NavMenuCssClass => collapseNavMenu ? "collapse" : null;

    void ToggleNavMenu()
    {
        collapseNavMenu = !collapseNavMenu;
    }
}

We can register the ADO.NET service with IEmployeeService interface in Startup class.

We can run the application.

Click Employee data menu link and create a new employee data.

After saving the data, this will appear in employee list page. You can click Edit hyper link to edit the data.

We can register the IEmployeeService interface with “EmployeeEfService” instead of EmployeeAdoNetService in Startup class. With this simple change, entire application will work for entity framework. This is the main advantage of dependency injection.

We can run the application again and add/edit employee data. You can notice that, application is working as expected.

We can register the IEmployeeService interface with “EmployeeDapperService” in Startup class.

Again, we can run the application and add/edit employee data. You can perform all the CRUD operations for Dapper as well.

Conclusion

In this post, we have seen that how to connect a Blazor application with SQL server using ADO.NET, EF Core and Dapper. We have created a single interface for three different services and registered in the Startup class one by one. We have created and edited employee data using these three different methods. I believe, you all get a good understanding of ADO.NET, EF Core and Dapper from this single post.