介绍
我们将了解如何将 ADO.NET、EF Core 和 Dapper 与 Blazor 应用程序结合使用。我在同一个 Blazor 应用程序中结合了这三种不同的方法。这样，人们就可以从一个地方了解这些不同的方法。
ADO.NET 提供对数据源（例如 SQL Server 和 XML）以及通过 OLE DB 和 ODBC 公开的数据源的一致访问。数据共享消费者应用程序可以使用 ADO.NET 连接到这些数据源并检索、处理和更新它们包含的数据。
Entity Framework 是 Microsoft 支持的 .NET 应用程序开源 ORM 框架。它使开发人员能够使用特定于域的类的对象来处理数据，而无需关注存储这些数据的底层数据库表和列。借助 Entity Framework，开发人员可以在处理数据时以更高的抽象级别工作，并且与传统应用程序相比，可以使用更少的代码来创建和维护面向数据的应用程序。
Dapper 是一个微型 ORM（对象关系映射器），可帮助将本机查询输出映射到域类。它是由 StackOverflow 团队构建并作为开源发布的高性能数据访问系统。如果您的项目更喜欢编写存储过程或编写原始 SQL 查询，而不是使用 EntityFramework 等成熟的 ORM 工具，那么 Dapper 是您的更好选择。使用 Dapper，可以非常轻松地对数据库执行 SQL 查询并将结果映射到 C# 域类。
我们将在 Visual Studio 中创建一个 Blazor 应用程序，并为 ADO.NET、EF Core 和 Dapper 创建三种不同的服务。我们将对所有这些服务使用单一接口。我们将在 Startup 类中逐一注册这些服务，并将它们注入到 Razor 组件中。我们将为 CRUD 操作创建四个 Razor 组件。通过在 Startup 类中注册，我们可以查看 ADO.NET、EF Core 和 Dapper 的所有 CRUD 操作。我们将创建一个简单的员工应用程序来查看这些操作。我将逐步解释所有操作。
在 SQL Server 中创建数据库和表
使用下面的 SQL 脚本创建一个新的数据库和表。
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
在 Visual Studi 中创建 Blazor 应用程序
从 Visual Studio 中选择 Blazor 模板并创建 Blazor 应用程序。
我们必须在我们的项目中安装以下库。
- “Microsoft.Data.SqlClient”,
- “Microsoft.EntityFrameworkCore.SqlServer” and
- “Dapper”
我们可以创建一个具有以下属性的“Employee”模型类。我们将在“Data”文件夹中创建所有 C# 类和服务。
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; }
}
}
我们可以为 ADO.NET 创建一个“EmployeeAdoNetService”服务类并实现该接口。
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;
}
}
}
我们需要一个“SqlDbContext”数据库上下文类来进行 EF Core 操作。
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; }
}
}
我们继承了上述类中的 DbContext 类，并为 SQL 数据库中的 Employees 表定义了一个 DB 设置属性。
我们可以创建一个“EmployeeEfService”服务类用于实体框架操作。
EmployeeEfService.
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;
}
}
}
我们在上面的服务类中定义了 EF Core 的所有逻辑。
我们可以创建一个“EmployeeDapperService”服务类，用于dapper操作。
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;
}
}
}
我们可以在 Startup 类中注册 SQL 连接配置和实体框架 DB 上下文
Startup 类中的 ConfigureServices 方法。
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);
}
我们可以在“Pages”文件夹中创建四个用于 CRUD 操作的 Razor 组件
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");
}
}
我们可以修改 NavMenu 共享组件来添加员工列表页面的导航。
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;
}
}
我们可以在 Startup 类中使用 IEmployeeService 接口注册 ADO.NET 服务。
我们可以运行该应用程序。
单击员工数据菜单链接并创建新的员工数据。
保存数据后，数据将出现在员工列表页面。您可以点击“编辑”超链接来编辑数据。
我们可以在 Startup 类中使用“EmployeeEfService”而不是 EmployeeAdoNetService 注册 IEmployeeService 接口。通过这个简单的更改，整个应用程序将适用于实体框架。这是依赖注入的主要优势。
我们可以再次运行该应用程序并添加/编辑员工数据。您可以注意到，应用程序正在按预期运行。
我们可以在Startup类中使用“EmployeeDapperService”注册IEmployeeService接口。
再次，我们可以运行应用程序并添加/编辑员工数据。您也可以对 Dapper 执行所有 CRUD 操作。
结论
在这篇文章中，我们了解了如何使用 ADO.NET、EF Core 和 Dapper 将 Blazor 应用程序与 SQL Server 连接起来。我们为三种不同的服务创建了一个接口，并逐一在 Startup 类中注册。我们使用这三种不同的方法创建和编辑了员工数据。我相信，通过这篇文章，您都能很好地理解 ADO.NET、EF Core 和 Dapper。
扫一扫
6608
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
