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

In this post, we will see how to use ADO.NET, EF Core and Dapper in same Blazor app to connect with SQL server database.

Introduction


Blazor is a new framework built by Microsoft for creating interactive client-side web UI with .NET codebase. We can write both client-side and server-side code in C#.NET itself. I have already written four articles about Blazor server on C# Corner. Please refer to below articles for more basics about Blazor framework.
We will see how to use ADO.NET, EF Core and Dapper with Blazor application. I have combined these three different approaches in same Blazor application. So that, a person can get idea about these different methods from one place. Please note, this is an experimental effort from my side to combine different methodologies in 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) which helps to map the native query output to a domain class. It is a high-performance data access system built by StackOverflow team and released as open source. If your project prefers writing stored procedures or writing raw SQL queries instead of using a 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 database and get the result mapped to 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 Startup class one by one and inject 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 to create new database and table.
  1. USE master  
  2. CREATE DATABASE SarathlalDb;  
  3.   
  4. GO  
  5.   
  6. USE SarathlalDb  
  7.   
  8. CREATE TABLE [dbo].[Employees] (  
  9.     [Id]          NVARCHAR (250) NOT NULL,  
  10.     [Name]        NVARCHAR (250) NULL,  
  11.     [Department]  NVARCHAR (250) NULL,  
  12.     [Designation] NVARCHAR (250) NULL,  
  13.     [Company]     NVARCHAR (250) NULL,  
  14.     [City]        NVARCHAR (250) NULL,  
  15.     CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([Id] ASC)  
  16. );  
  17.   
  18. GO  

Create Blazor application in Visual Studio 2019


Choose Blazor template from Visual Studio 2019 and create Blazor application.
 
We must install below libraries in our project.
  • “Microsoft.Data.SqlClient”,
  • “Microsoft.EntityFrameworkCore.SqlServer” and
  • “Dapper”
We can create “Employee” model class with below properties. We will create all C# classes and services inside “Data” folder.
 
Employee.cs
  1. namespace BlazorAdoNetEFCoreDapper.Data  
  2. {  
  3.     public class Employee  
  4.     {  
  5.         public string Id { getset; }  
  6.         public string Name { getset; }  
  7.         public string Department { getset; }  
  8.         public string Designation { getset; }  
  9.         public string Company { getset; }  
  10.         public string City { getset; }  
  11.     }  
  12. }  
Create a “SqlConnectionConfiguration” class to fetch SQL connection details from appsettings.json file.
 
SqlConnectionConfiguration.cs
  1. namespace BlazorAdoNetEFCoreDapper.Data  
  2. {  
  3.     public class SqlConnectionConfiguration  
  4.     {  
  5.         public SqlConnectionConfiguration(string value) => Value = value;  
  6.         public string Value { get; }  
  7.     }  
  8. }  
Add SQL connection string in appsettings.json file.
 
 
We can create an “IEmployeeService” interface and declare below methods.
 
IEmployeeService.cs
  1. using System.Collections.Generic;  
  2. using System.Threading.Tasks;  
  3.   
  4. namespace BlazorAdoNetEFCoreDapper.Data  
  5. {  
  6.     public interface IEmployeeService  
  7.     {  
  8.         Task<List<Employee>> GetEmployees();  
  9.         Task<bool> CreateEmployee(Employee employee);  
  10.         Task<bool> EditEmployee(string id, Employee employee);  
  11.         Task<Employee> SingleEmployee(string id);  
  12.         Task<bool> DeleteEmployee(string id);  
  13.     }  
  14. }  
We can create “EmployeeAdoNetService” service class for ADO.NET and implement the interface.
 
EmployeeAdoNetService.cs
  1. using Microsoft.Data.SqlClient;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Data;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace BlazorAdoNetEFCoreDapper.Data  
  8. {  
  9.     public class EmployeeAdoNetService : IEmployeeService  
  10.     {  
  11.         private readonly SqlConnectionConfiguration _configuration;  
  12.         public EmployeeAdoNetService(SqlConnectionConfiguration configuration)  
  13.         {  
  14.             _configuration = configuration;  
  15.         }  
  16.         public async Task<bool> CreateEmployee(Employee employee)  
  17.         {  
  18.             using (SqlConnection con = new SqlConnection(_configuration.Value))  
  19.             {  
  20.                 const string query = "insert into dbo.Employees (Id,Name,Department,Designation,Company,City) values(@Id,@Name,@Department,@Designation,@Company,@City)";  
  21.                 SqlCommand cmd = new SqlCommand(query, con)  
  22.                 {  
  23.                     CommandType = CommandType.Text,  
  24.                 };  
  25.   
  26.                 cmd.Parameters.AddWithValue("@Id", Guid.NewGuid().ToString());  
  27.                 cmd.Parameters.AddWithValue("@Name", employee.Name);  
  28.                 cmd.Parameters.AddWithValue("@Department", employee.Department);  
  29.                 cmd.Parameters.AddWithValue("@Designation", employee.Designation);  
  30.                 cmd.Parameters.AddWithValue("@Company", employee.Company);  
  31.                 cmd.Parameters.AddWithValue("@City", employee.City);  
  32.   
  33.                 con.Open();  
  34.                 await cmd.ExecuteNonQueryAsync();  
  35.   
  36.                 con.Close();  
  37.                 cmd.Dispose();  
  38.             }  
  39.             return true;  
  40.         }  
  41.   
  42.         public async Task<bool> DeleteEmployee(string id)  
  43.         {  
  44.             using (SqlConnection con = new SqlConnection(_configuration.Value))  
  45.             {  
  46.                 const string query = "delete dbo.Employees where Id=@Id";  
  47.                 SqlCommand cmd = new SqlCommand(query, con)  
  48.                 {  
  49.                     CommandType = CommandType.Text,  
  50.                 };  
  51.   
  52.                 cmd.Parameters.AddWithValue("@Id", id);  
  53.   
  54.                 con.Open();  
  55.                 await cmd.ExecuteNonQueryAsync();  
  56.   
  57.                 con.Close();  
  58.                 cmd.Dispose();  
  59.             }  
  60.             return true;  
  61.         }  
  62.   
  63.         public async Task<bool> EditEmployee(string id, Employee employee)  
  64.         {  
  65.             using (SqlConnection con = new SqlConnection(_configuration.Value))  
  66.             {  
  67.                 const string query = "update dbo.Employees set Name = @Name, Department = @Department, Designation = @Designation, Company = @Company, City = @City where Id=@Id";  
  68.                 SqlCommand cmd = new SqlCommand(query, con)  
  69.                 {  
  70.                     CommandType = CommandType.Text,  
  71.                 };  
  72.   
  73.                 cmd.Parameters.AddWithValue("@Id", id);  
  74.                 cmd.Parameters.AddWithValue("@Name", employee.Name);  
  75.                 cmd.Parameters.AddWithValue("@Department", employee.Department);  
  76.                 cmd.Parameters.AddWithValue("@Designation", employee.Designation);  
  77.                 cmd.Parameters.AddWithValue("@Company", employee.Company);  
  78.                 cmd.Parameters.AddWithValue("@City", employee.City);  
  79.   
  80.                 con.Open();  
  81.                 await cmd.ExecuteNonQueryAsync();  
  82.   
  83.                 con.Close();  
  84.                 cmd.Dispose();  
  85.             }  
  86.             return true;  
  87.         }  
  88.   
  89.         public async Task<List<Employee>> GetEmployees()  
  90.         {  
  91.             List<Employee> employees = new List<Employee>();  
  92.   
  93.             using (SqlConnection con = new SqlConnection(_configuration.Value))  
  94.             {  
  95.                 const string query = "select * from dbo.Employees";  
  96.                 SqlCommand cmd = new SqlCommand(query, con)  
  97.                 {  
  98.                     CommandType = CommandType.Text  
  99.                 };  
  100.   
  101.                 con.Open();  
  102.                 SqlDataReader rdr = await cmd.ExecuteReaderAsync();  
  103.   
  104.                 while (rdr.Read())  
  105.                 {  
  106.                     Employee employee = new Employee  
  107.                     {  
  108.                         Id = rdr["Id"].ToString(),  
  109.                         Name = rdr["Name"].ToString(),  
  110.                         Department = rdr["Department"].ToString(),  
  111.                         Designation = rdr["Designation"].ToString(),  
  112.                         Company = rdr["Company"].ToString(),  
  113.                         City = rdr["City"].ToString()  
  114.                     };  
  115.                     employees.Add(employee);  
  116.                 }  
  117.                 con.Close();  
  118.                 cmd.Dispose();  
  119.             }  
  120.             return employees;  
  121.         }  
  122.   
  123.         public async Task<Employee> SingleEmployee(string id)  
  124.         {  
  125.             Employee employee = new Employee();  
  126.   
  127.             using (SqlConnection con = new SqlConnection(_configuration.Value))  
  128.             {  
  129.                 const string query = "select * from dbo.Employees where Id = @Id";  
  130.                 SqlCommand cmd = new SqlCommand(query, con)  
  131.                 {  
  132.                     CommandType = CommandType.Text,  
  133.                 };  
  134.   
  135.                 cmd.Parameters.AddWithValue("@Id", id);  
  136.                 con.Open();  
  137.                 SqlDataReader rdr = await cmd.ExecuteReaderAsync();  
  138.   
  139.                 if (rdr.Read())  
  140.                 {  
  141.   
  142.                     employee.Id = rdr["Id"].ToString();  
  143.                     employee.Name = rdr["Name"].ToString();  
  144.                     employee.Department = rdr["Department"].ToString();  
  145.                     employee.Designation = rdr["Designation"].ToString();  
  146.                     employee.Company = rdr["Company"].ToString();  
  147.                     employee.City = rdr["City"].ToString();  
  148.                 }  
  149.                 con.Close();  
  150.                 cmd.Dispose();  
  151.             }  
  152.             return employee;  
  153.         }  
  154.     }  
  155. }  
We have defined all the logic for ADO.NET operations in above service class.
 
We need an “SqlDbContext” db context class for EF Core operations.
 
SqlDbContext.cs
  1. using Microsoft.EntityFrameworkCore;  
  2.   
  3. namespace BlazorAdoNetEFCoreDapper.Data  
  4. {  
  5.     public class SqlDbContext : DbContext  
  6.     {  
  7.         public SqlDbContext(DbContextOptions<SqlDbContext> options)  
  8.            : base(options)  
  9.         {  
  10.         }  
  11.         public DbSet<Employee> Employees { getset; }  
  12.     }  
  13. }  
We have inherited DbContext class inside above class and define a DB set property for Employees table from SQL database.
 
We can create “EmployeeEfService” service class for entity framework operations.
 
EmployeeEfService.cs
  1. using Microsoft.EntityFrameworkCore;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Threading.Tasks;  
  5.   
  6. namespace BlazorAdoNetEFCoreDapper.Data  
  7. {  
  8.     public class EmployeeEfService : IEmployeeService  
  9.     {  
  10.         private readonly SqlDbContext _dbContext;  
  11.   
  12.         public EmployeeEfService(SqlDbContext dbContext)  
  13.         {  
  14.             _dbContext = dbContext;  
  15.         }  
  16.         public async Task<List<Employee>> GetEmployees()  
  17.         {  
  18.             return await _dbContext.Employees.ToListAsync();  
  19.         }  
  20.         public async Task<bool> CreateEmployee(Employee employee)  
  21.         {  
  22.             employee.Id = Guid.NewGuid().ToString();  
  23.             _dbContext.Add(employee);  
  24.             try  
  25.             {  
  26.                 await _dbContext.SaveChangesAsync();  
  27.                 return true;  
  28.             }  
  29.             catch (DbUpdateException)  
  30.             {  
  31.                 return false;  
  32.             }  
  33.   
  34.         }  
  35.         public async Task<Employee> SingleEmployee(string id)  
  36.         {  
  37.             return await _dbContext.Employees.FindAsync(id);  
  38.         }  
  39.         public async Task<bool> EditEmployee(string id, Employee employee)  
  40.         {  
  41.             if (id != employee.Id)  
  42.             {  
  43.                 return false;  
  44.             }  
  45.   
  46.             _dbContext.Entry(employee).State = EntityState.Modified;  
  47.             await _dbContext.SaveChangesAsync();  
  48.             return true;  
  49.         }  
  50.         public async Task<bool> DeleteEmployee(string id)  
  51.         {  
  52.             var patient = await _dbContext.Employees.FindAsync(id);  
  53.             if (patient == null)  
  54.             {  
  55.                 return false;  
  56.             }  
  57.   
  58.             _dbContext.Employees.Remove(patient);  
  59.             await _dbContext.SaveChangesAsync();  
  60.             return true;  
  61.         }  
  62.     }  
  63. }  
We have defined all the logic for EF Core in above service class.
 
We can create “EmployeeDapperService” service class for dapper operations.
 
EmployeeDapperService.cs
  1. using Dapper;  
  2. using Microsoft.Data.SqlClient;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Data;  
  6. using System.Linq;  
  7. using System.Threading.Tasks;  
  8.   
  9. namespace BlazorAdoNetEFCoreDapper.Data  
  10. {  
  11.     public class EmployeeDapperService : IEmployeeService  
  12.     {  
  13.         private readonly SqlConnectionConfiguration _configuration;  
  14.         public EmployeeDapperService(SqlConnectionConfiguration configuration)  
  15.         {  
  16.             _configuration = configuration;  
  17.         }  
  18.         public async Task<bool> CreateEmployee(Employee employee)  
  19.         {  
  20.             using (var conn = new SqlConnection(_configuration.Value))  
  21.             {  
  22.                 const string query = @"insert into dbo.Employees (Id,Name,Department,Designation,Company,City) values(@Id,@Name,@Department,@Designation,@Company,@City)";  
  23.                 conn.Open();  
  24.                 try  
  25.                 {  
  26.                     await conn.ExecuteAsync(query, new { Id = Guid.NewGuid().ToString(), employee.Name, employee.Department, employee.Designation, employee.Company, employee.City }, commandType: CommandType.Text);  
  27.                 }  
  28.                 catch (Exception ex)  
  29.                 {  
  30.                     throw ex;  
  31.                 }  
  32.                 finally  
  33.                 {  
  34.                     conn.Close();  
  35.                 }  
  36.             }  
  37.             return true;  
  38.         }  
  39.   
  40.         public async Task<bool> DeleteEmployee(string id)  
  41.         {  
  42.             using (var conn = new SqlConnection(_configuration.Value))  
  43.             {  
  44.                 const string query = @"delete dbo.Employees where Id=@Id";  
  45.                 conn.Open();  
  46.                 try  
  47.                 {  
  48.                     await conn.ExecuteAsync(query, new { id }, commandType: CommandType.Text);  
  49.                 }  
  50.                 catch (Exception ex)  
  51.                 {  
  52.                     throw ex;  
  53.                 }  
  54.                 finally  
  55.                 {  
  56.                     conn.Close();  
  57.                 }  
  58.             }  
  59.             return true;  
  60.         }  
  61.   
  62.         public async Task<bool> EditEmployee(string id, Employee employee)  
  63.         {  
  64.             using (var conn = new SqlConnection(_configuration.Value))  
  65.             {  
  66.                 const string query = @"update dbo.Employees set Name = @Name, Department = @Department, Designation = @Designation, Company = @Company, City = @City where Id=@Id";  
  67.                 conn.Open();  
  68.                 try  
  69.                 {  
  70.                     await conn.ExecuteAsync(query, new { employee.Name, employee.Department, employee.Designation, employee.Company, employee.City, id }, commandType: CommandType.Text);  
  71.                 }  
  72.                 catch (Exception ex)  
  73.                 {  
  74.                     throw ex;  
  75.                 }  
  76.                 finally  
  77.                 {  
  78.                     conn.Close();  
  79.                 }  
  80.             }  
  81.             return true;  
  82.         }  
  83.   
  84.         public async Task<List<Employee>> GetEmployees()  
  85.         {  
  86.             IEnumerable<Employee> employees;  
  87.             using (var conn = new SqlConnection(_configuration.Value))  
  88.             {  
  89.                 const string query = @"select * from dbo.Employees";  
  90.   
  91.                 conn.Open();  
  92.                 try  
  93.                 {  
  94.                     employees = await conn.QueryAsync<Employee>(query, commandType: CommandType.Text);  
  95.   
  96.                 }  
  97.                 catch (Exception ex)  
  98.                 {  
  99.                     throw ex;  
  100.                 }  
  101.                 finally  
  102.                 {  
  103.                     conn.Close();  
  104.                 }  
  105.   
  106.             }  
  107.             return employees.ToList();  
  108.         }  
  109.   
  110.         public async Task<Employee> SingleEmployee(string id)  
  111.         {  
  112.             Employee employee = new Employee();  
  113.             using (var conn = new SqlConnection(_configuration.Value))  
  114.             {  
  115.                 const string query = @"select * from dbo.Employees where Id=@Id";  
  116.   
  117.                 conn.Open();  
  118.                 try  
  119.                 {  
  120.                     employee = await conn.QueryFirstOrDefaultAsync<Employee>(query, new { id }, commandType: CommandType.Text);  
  121.   
  122.                 }  
  123.                 catch (Exception ex)  
  124.                 {  
  125.                     throw ex;  
  126.                 }  
  127.                 finally  
  128.                 {  
  129.                     conn.Close();  
  130.                 }  
  131.   
  132.             }  
  133.             return employee;  
  134.         }  
  135.     }  
  136. }  
We can register SQL connection configuration and entity framework DB context inside the Startup class
 
 
 
 
ConfigureServices method in Startup class
  1. public void ConfigureServices(IServiceCollection services)  
  2.         {  
  3.             services.AddRazorPages();  
  4.             services.AddServerSideBlazor();  
  5.             services.AddSingleton<WeatherForecastService>();  
  6.   
  7.             services.AddDbContext<SqlDbContext>(options =>  
  8.                    options.UseSqlServer(Configuration.GetConnectionString("SqlDbContext")));  
  9.   
  10.             var sqlConnectionConfiguration = new SqlConnectionConfiguration(Configuration.GetConnectionString("SqlDbContext"));  
  11.             services.AddSingleton(sqlConnectionConfiguration);  
  12.   
  13.             services.AddServerSideBlazor(o => o.DetailedErrors = true);  
  14.   
  15.         }  
We can create four razor components for CRUD operations inside “Pages” folder
 
ListEmployees.razor
  1. @using BlazorAdoNetEFCoreDapper.Data  
  2.   
  3. @page "/listemployees"  
  4. @inject IEmployeeService EmployeeService  
  5.   
  6. <h2>Employee Details</h2>  
  7. <p>  
  8.     <a href="/addemployee">Create New Employee</a>  
  9. </p>  
  10. @if (employees == null)  
  11. {  
  12.   
  13.     <img src="./basicloader.gif" />  
  14. }  
  15. else  
  16. {  
  17.     <table class='table'>  
  18.         <thead>  
  19.             <tr>  
  20.                 <th>Name</th>  
  21.                 <th>Department</th>  
  22.                 <th>Designation</th>  
  23.                 <th>Company</th>  
  24.                 <th>City</th>  
  25.             </tr>  
  26.         </thead>  
  27.         <tbody>  
  28.             @foreach (var employee in employees)  
  29.             {  
  30.                 <tr>  
  31.                     <td>@employee.Name</td>  
  32.                     <td>@employee.Department</td>  
  33.                     <td>@employee.Designation</td>  
  34.                     <td>@employee.Company</td>  
  35.                     <td>@employee.City</td>  
  36.                     <td>  
  37.                         <a href='/editemployee/@employee.Id'>Edit</a>  
  38.                         <a href='/deleteemployee/@employee.Id'>Delete</a>  
  39.                     </td>  
  40.                 </tr>  
  41.   
  42.             }  
  43.         </tbody>  
  44.     </table>  
  45. }  
  46.   
  47. @code {  
  48.     List<Employee> employees;  
  49.   
  50.     protected override async Task OnInitializedAsync()  
  51.     {  
  52.         employees = await EmployeeService.GetEmployees();  
  53.     }  
  54. }     
AddEmployee.razor
  1. @using BlazorAdoNetEFCoreDapper.Data  
  2.   
  3. @page "/addemployee"  
  4. @inject NavigationManager NavigationManager  
  5. @inject IEmployeeService EmployeeService  
  6.   
  7. <h2>Create Employee</h2>  
  8. <hr />  
  9. <form>  
  10.     <div class="row">  
  11.         <div class="col-md-8">  
  12.             <div class="form-group">  
  13.                 <label for="Name" class="control-label">Name</label>  
  14.                 <input for="Name" class="form-control" @bind="@employee.Name" />  
  15.             </div>  
  16.             <div class="form-group">  
  17.                 <label for="Department" class="control-label">Department</label>  
  18.                 <input for="Department" class="form-control" @bind="@employee.Department" />  
  19.             </div>  
  20.             <div class="form-group">  
  21.                 <label for="Designation" class="control-label">Designation</label>  
  22.                 <input for="Designation" class="form-control" @bind="@employee.Designation" />  
  23.             </div>  
  24.             <div class="form-group">  
  25.                 <label for="Company" class="control-label">Company</label>  
  26.                 <input for="Company" class="form-control" @bind="@employee.Company" />  
  27.             </div>  
  28.             <div class="form-group">  
  29.                 <label for="City" class="control-label">City</label>  
  30.                 <input for="City" class="form-control" @bind="@employee.City" />  
  31.             </div>  
  32.         </div>  
  33.     </div>  
  34.     <div class="row">  
  35.         <div class="col-md-4">  
  36.             <div class="form-group">  
  37.                 <input type="button" class="btn btn-primary" @onclick="@CreateEmployee" value="Save" />  
  38.                 <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
  39.             </div>  
  40.         </div>  
  41.     </div>  
  42. </form>  
  43.   
  44. @code {  
  45.   
  46.     Employee employee = new Employee();  
  47.   
  48.     protected async Task CreateEmployee()  
  49.     {  
  50.         await EmployeeService.CreateEmployee(employee);  
  51.         NavigationManager.NavigateTo("listemployees");  
  52.     }  
  53.   
  54.     void Cancel()  
  55.     {  
  56.         NavigationManager.NavigateTo("listemployees");  
  57.     }  
  58. }     
EditEmployee.razor
  1. @using BlazorAdoNetEFCoreDapper.Data  
  2.   
  3. @page "/editemployee/{id}"  
  4. @inject NavigationManager NavigationManager  
  5. @inject IEmployeeService EmployeeService  
  6.   
  7. <h2>Edit Employee</h2>  
  8. <hr />  
  9. <form>  
  10.     <div class="row">  
  11.         <div class="col-md-8">  
  12.             <div class="form-group">  
  13.                 <label for="Name" class="control-label">Name</label>  
  14.                 <input for="Name" class="form-control" @bind="@employee.Name" />  
  15.             </div>  
  16.             <div class="form-group">  
  17.                 <label for="Department" class="control-label">Department</label>  
  18.                 <input for="Department" class="form-control" @bind="@employee.Department" />  
  19.             </div>  
  20.             <div class="form-group">  
  21.                 <label for="Designation" class="control-label">Designation</label>  
  22.                 <input for="Designation" class="form-control" @bind="@employee.Designation" />  
  23.             </div>  
  24.             <div class="form-group">  
  25.                 <label for="Company" class="control-label">Company</label>  
  26.                 <input for="Company" class="form-control" @bind="@employee.Company" />  
  27.             </div>  
  28.             <div class="form-group">  
  29.                 <label for="City" class="control-label">City</label>  
  30.                 <input for="Company" class="form-control" @bind="@employee.City" />  
  31.             </div>  
  32.         </div>  
  33.     </div>  
  34.     <div class="row">  
  35.         <div class="form-group">  
  36.             <input type="button" class="btn btn-primary" @onclick="@UpdateEmployee" value="Update" />  
  37.             <input type="button" class="btn" @onclick="@Cancel" value="Cancel" />  
  38.         </div>  
  39.     </div>  
  40. </form>  
  41.   
  42. @code {  
  43.   
  44.     [Parameter]  
  45.     public string id { getset; }  
  46.   
  47.     Employee employee = new Employee();  
  48.   
  49.     protected override async Task OnInitializedAsync()  
  50.     {  
  51.         employee = await EmployeeService.SingleEmployee(id);  
  52.     }  
  53.   
  54.     protected async Task UpdateEmployee()  
  55.     {  
  56.         await EmployeeService.EditEmployee(id, employee);  
  57.         NavigationManager.NavigateTo("listemployees");  
  58.     }  
  59.   
  60.     void Cancel()  
  61.     {  
  62.         NavigationManager.NavigateTo("listemployees");  
  63.     }  
  64. }     
DeleteEmployee.razor
  1. @using BlazorAdoNetEFCoreDapper.Data  
  2.   
  3. @page "/deleteemployee/{id}"  
  4. @inject NavigationManager NavigationManager  
  5. @inject IEmployeeService EmployeeService  
  6.   
  7. <h2>Confirm Delete</h2>  
  8. <p>Are you sure you want to delete this Employee with Id :<b> @id</b></p>  
  9. <br />  
  10. <div class="col-md-4">  
  11.     <table class="table">  
  12.         <tr>  
  13.             <td>Name</td>  
  14.             <td>@employee.Name</td>  
  15.         </tr>  
  16.         <tr>  
  17.             <td>Department</td>  
  18.             <td>@employee.Department</td>  
  19.         </tr>  
  20.         <tr>  
  21.             <td>Designation</td>  
  22.             <td>@employee.Designation</td>  
  23.         </tr>  
  24.         <tr>  
  25.             <td>Company</td>  
  26.             <td>@employee.Company</td>  
  27.         </tr>  
  28.         <tr>  
  29.             <td>City</td>  
  30.             <td>@employee.City</td>  
  31.         </tr>  
  32.     </table>  
  33.     <div class="form-group">  
  34.         <input type="button" value="Delete" @onclick="@Delete" class="btn btn-primary" />  
  35.         <input type="button" value="Cancel" @onclick="@Cancel" class="btn" />  
  36.     </div>  
  37. </div>  
  38.   
  39. @code {  
  40.   
  41.     [Parameter]  
  42.     public string id { getset; }  
  43.     Employee employee = new Employee();  
  44.   
  45.     protected override async Task OnInitializedAsync()  
  46.     {  
  47.         employee = await EmployeeService.SingleEmployee(id);  
  48.     }  
  49.   
  50.     protected async Task Delete()  
  51.     {  
  52.         await EmployeeService.DeleteEmployee(id);  
  53.         NavigationManager.NavigateTo("listemployees");  
  54.     }  
  55.   
  56.     void Cancel()  
  57.     {  
  58.         NavigationManager.NavigateTo("listemployees");  
  59.     }  
  60. }     
We can modify the NavMenu shared component to add a navigation to the employee list page.
 
NavMenu.razor
  1. <div class="top-row pl-4 navbar navbar-dark">  
  2.     <a class="navbar-brand" href="">ADO.NET, EF & Dapper</a>  
  3.     <button class="navbar-toggler" @onclick="ToggleNavMenu">  
  4.         <span class="navbar-toggler-icon"></span>  
  5.     </button>  
  6. </div>  
  7.   
  8. <div class="@NavMenuCssClass" @onclick="ToggleNavMenu">  
  9.     <ul class="nav flex-column">  
  10.         <li class="nav-item px-3">  
  11.             <NavLink class="nav-link" href="" Match="NavLinkMatch.All">  
  12.                 <span class="oi oi-home" aria-hidden="true"></span> Home  
  13.             </NavLink>  
  14.         </li>  
  15.         <li class="nav-item px-3">  
  16.             <NavLink class="nav-link" href="counter">  
  17.                 <span class="oi oi-plus" aria-hidden="true"></span> Counter  
  18.             </NavLink>  
  19.         </li>  
  20.         <li class="nav-item px-3">  
  21.             <NavLink class="nav-link" href="listemployees">  
  22.                 <span class="oi oi-list-rich" aria-hidden="true"></span> Employee data  
  23.             </NavLink>  
  24.         </li>  
  25.     </ul>  
  26. </div>  
  27.   
  28. @code {  
  29.     bool collapseNavMenu = true;  
  30.   
  31.     string NavMenuCssClass => collapseNavMenu ? "collapse" : null;  
  32.   
  33.     void ToggleNavMenu()  
  34.     {  
  35.         collapseNavMenu = !collapseNavMenu;  
  36.     }  
  37. }  
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.