Dapper 是一个开源的、轻量级的 ORM 框架,性能出色,关于它的特点不必多说,网上到处都是。个人比较喜欢它基于 SQL 语句的方式。本博文讲解 Dapper 的用法。
开发环境:
- Microsoft Visual Studio Community 2019
- Database: SQL Server 2019 Express
Dapper 的安装
新建一个 Class Library 项目,通过菜单: Tools -> NuGet Package Manager -> Manage NuGet packages for Solution,打开 第三方 package 的浏览界面,在搜索框中输入 Dapper,选中右边的项目,然后点击 Install 按钮即可以安装。
准备数据
本文的示例数据是通过 https://mockaroo.com/ 网站提供的模拟数据生成。我利用该网站生成了 employees 表的模拟数据,并且将创建表的 SQL 语句和创建模拟数据的 SQL 语句下载为 sql 脚本。示例数据我将和本文的源码一起上传到 Github,供小伙伴们参考。emp_master 表结构如下:
CREATE TABLE emp_master(
[EMP_ID] [int] NOT NULL,
[GENDER] [varchar](10) NULL,
[AGE] [int] NULL,
[EMAIL] [varchar](50) NULL,
[PHONE_NR] [varchar](20) NULL,
[EDUCATION] [varchar](20) NULL,
[MARITAL_STAT] [varchar](20) NULL,
[NR_OF_CHILDREN] [int] NULL,
CONSTRAINT pk_employee primary key(EMP_ID));
连接字符串
在 .Net 平台编写数据库程序,都需要针对不同数据库的连接字符串,我使用的是 sql server express,连接字符串如下,sql server 其它版本类似。
private String connStr = @"Server=localhost\sqlexpress;Database=stonetest;Integrated Security=True;";
// 使用用户名和密码连接
private String connStr = @"Server=localhost\sqlexpress;Database=stonetest;uid=sa;pwd=123456;";
Domain 类
在项目中新建一个 Employee 类,将表的字段映射为属性:
namespace EmployeeMgr.Domain
{
public class Employee
{
public int EMP_ID { get; set; }
public String GENDER { get; set; }
public int AGE { get; set; }
public String EMAIL { get; set; }
public String PHONE_NR { get; set; }
public String EDUCATION { get; set; }
public String MARITAL_STAT { get; set; }
public int NR_OF_CHILDREN { get; set; }
public Employee ShallowCopy()
{
return (Employee)this.MemberwiseClone();
}
}
}
使用 Dapper 实现 CRUD
新建 EmployeeDao 类,在类中实现 CRUD 操作。ListAll() 方法:
public List<Employee> ListAll()
{
String sql = "select * from emp_master order by emp_id";
using (IDbConnection db = new SqlConnection(connStr)) {
var emps = db.Query<Employee>(sql);
return emps.ToList();
}
}
- 方法返回
List
的泛型接口,这是一个常规的处理方式,展示层可以直接将控件绑定到 List<T> IDbConnection
是 Ado.Net 的接口类型,Dapper 的方法都是基于 IDbConnection 接口类型实现了扩展方法- 本例后台数据库为 sql server/sql server express,所以实际的连接对象为
System.Data.SqlClient.SqlConnection
对象 IDbConnection
的Query<T>()
方法,返回值类型为IEnumerable<T>
, 通过ToList()
方法转换为List<T>
。
其它方法:
public int Insert(Employee emp)
{
String sql = $@"insert into emp_master
(EMP_ID, GENDER, AGE, EMAIL, EDUCATION, PHONE_NR, MARITAL_STAT, NR_OF_CHILDREN)
values(@EMP_ID, @GENDER, @AGE, @EMAIL, @EDUCATION,
@PHONE_NR, @MARITAL_STAT, @NR_OF_CHILDREN)";
using (IDbConnection db = new SqlConnection(connStr)) {
return db.Execute(sql, emp);
}
}
public int Update(Employee emp)
{
String sql = $@"update emp_master
set gender=@gender,
age=@age,
email=@email,
education=@education,
phone_nr=@phone_nr,
marital_stat=@marital_stat
where emp_id=@emp_id";
using (IDbConnection db = new SqlConnection(connStr)) {
return db.Execute(sql, emp);
}
}
public int Delete(int empId)
{
var sql = "delete from emp_master where emp_id=@emp_id";
using (IDbConnection db = new SqlConnection(connStr)) {
var parameters = new { emp_id = empId };
return db.Execute(sql, parameters);
}
}
我们注意到,在这些方法中,插入和修改都是传入一个 Employee 对象,Dapper 自动在对象中找到匹配的属性,将其作为 sql 语句的参数值,极大减少了开发人员的手工代码。规则是要求 entity 类的属性名称与参数名称相同。
单元测试
创建一个 Unit Test Project,编写如下测试代码,测试 CRUD 功能:
namespace UnitTest1
{
[TestClass]
public class DapperCrudTest
{
private EmployeeDao empDao;
public DapperCrudTest()
{
empDao = new EmployeeDao();
}
[TestMethod]
public void TestListAll()
{
var employees = empDao.ListAll();
foreach (var emp in employees) {
Console.WriteLine(String.Format("{0}, {1}", emp.EMP_ID, emp.EMAIL));
}
}
[TestMethod]
public void TestFindByName()
{
var employees = empDao.FindByName("Sh");
foreach (var emp in employees) {
Console.WriteLine($"{ emp.EMP_ID } { emp.EMAIL }");
}
}
[TestMethod]
public void TestFindById()
{
Employee emp = empDao.FindById(1001);
Console.WriteLine($"{ emp.EMP_ID } { emp.EMAIL }");
}
[TestMethod]
public void TestInsert()
{
var emp = new Employee
{
EMP_ID = 9002,
GENDER = null,
AGE = 19,
EMAIL = "test@qq.com",
PHONE_NR = "13800-138000",
EDUCATION = "Bachelor",
MARITAL_STAT = "Single",
NR_OF_CHILDREN = 0
};
int rv = empDao.Insert(emp);
Console.WriteLine(rv);
}
[TestMethod]
public void TestUpdate()
{
var emp = new Employee
{
EMP_ID = 9002,
GENDER = "Male",
AGE = 21,
EMAIL = "test@qq.com",
PHONE_NR = "13800-138000",
EDUCATION = "Bachelor",
MARITAL_STAT = "Single",
NR_OF_CHILDREN = 0
};
int rv = empDao.Update(emp);
Assert.AreEqual(rv, 1);
}
[TestMethod]
public void TestDelete()
{
int rv = empDao.Delete(9002);
Assert.AreEqual(rv, 1);
}
}
}
WinForm 程序示例
接下来使用 WinForm 作界面,用两个 Form 来完成对 employee 表的 CRUD:EmpListForm 列示所有的 employees,使用 BindingNavigator 控件用于导航,基于 BindingSource 的方式实现数据绑定。
主要控件的属性设置说明:
bindingNavigator:
- AddNewItem: 设置为 none,目的是取消控件的默认行为,用自己的代码来代替。
- DeleteItem: 设置为 none,目的是取消控件的默认行为,用自己的代码来代替
- Dock: Bottom,显示在表单的下方
bindingSource1:
- Modifier: 默认为 private,更改为 public。目的是在不用的 Form 中,通过 bindingSource 实现表单记录同步,这是一种常用的方法,请参考本文的参考部分。
DataGridView:
- Dock: Fill,让 DataGridView 填满表单。
以下是 EmpListForm 的代码:
namespace SqlSugarTest {
public partial class EmpListForm : Form {
private EmployeeRepository empRepo;
public EmpListForm() {
InitializeComponent();
empRepo = new EmployeeRepository();
}
private void EmpListForm_Load(object sender, EventArgs e) {
this.Text = "Employee Master";
// Get employee list
List<Employee> emps = empRepo.ListAll();
// Data binding
bindingSource1.DataSource = emps;
dataGridView1.DataSource = bindingSource1;
bindingNavigator1.BindingSource = bindingSource1;
}
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e) {
// Execute deletion
if (bindingSource1.Current == null) return;
if (MessageBox.Show("确定删除此笔记录吗?", "删除确认",
MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
int empid = (bindingSource1.Current as Employee).EMP_ID;
int count = empRepo.Delete(empid);
if (count > 0) {
bindingSource1.RemoveCurrent(); // 从数据库删除后保持界面同步
}
}
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e) {
// Open sinle form
var singleForm = new EmpSingleForm(this.bindingSource1, 2);
singleForm.Show();
}
private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e) {
bindingSource1.AddNew();
var singleForm = new EmpSingleForm(this.bindingSource1, 1);
singleForm.Show();
}
}
}
新建 EmpSingleForm,设计时界面如下:
该表单的完整代码:
namespace SqlSugarTest {
public partial class EmpSingleForm : Form {
private EmployeeRepository empRepo;
private int actionType; // 1: insert, 2: update
private Employee empForRestore; // 用于用户点击取消的时候恢复到之前状态
public EmpSingleForm() {
InitializeComponent();
empRepo = new EmployeeRepository();
}
public EmpSingleForm(BindingSource bs, int actionType) : this() {
this.bindingSource1 = bs;
this.actionType = actionType;
}
private void SetBinding() {
// data binding
txtEmpID.DataBindings.Add("Text", bindingSource1, "EMP_ID", true);
cboSex.DataBindings.Add("Text", bindingSource1, "GENDER", true);
txtAge.DataBindings.Add("Text", bindingSource1, "AGE", true);
txtEMail.DataBindings.Add("Text", bindingSource1, "EMAIL", true);
txtPhone.DataBindings.Add("Text", bindingSource1, "PHONE_NR", true);
txtEducation.DataBindings.Add("Text", bindingSource1, "EDUCATION", true);
txtMaritalStat.DataBindings.Add("Text", bindingSource1, "MARITAL_STAT", true);
txtNumOfChildren.DataBindings.Add("Text", bindingSource1, "NR_OF_CHILDREN", true);
}
private void Backup() {
var old = empForRestore = this.bindingSource1.Current as Employee;
empForRestore = old.ShallowCopy();
}
private void CancelUpdate()
{
if (empForRestore == null) return;
var current = bindingSource1.Current as Employee;
current.EMP_ID = empForRestore.EMP_ID;
current.AGE = empForRestore.AGE;
current.EDUCATION = empForRestore.EDUCATION;
current.EMAIL = empForRestore.EMAIL;
current.GENDER = empForRestore.GENDER;
current.MARITAL_STAT = empForRestore.MARITAL_STAT;
current.NR_OF_CHILDREN = empForRestore.NR_OF_CHILDREN;
current.PHONE_NR = empForRestore.PHONE_NR;
bindingSource1.ResetBindings(false);
}
private void SaveUpdate()
{
var employee = bindingSource1.Current as Employee;
empRepo.Update(employee);
}
private void SaveInsert()
{
var employee = bindingSource1.Current as Employee;
empRepo.Insert(employee);
}
private void btnSave_Click(object sender, EventArgs e) {
try {
if (actionType == 1) {
SaveInsert();
}else if(actionType == 2) {
SaveUpdate();
}
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
this.Close();
}
private void btnCancel_Click(object sender, EventArgs e) {
CancelUpdate();
this.Close();
}
private void EmpSingleForm_Load(object sender, EventArgs e)
{
this.SetBinding();
this.Backup();
}
}
}
源码
github - dotnet_practice_projects/Data Access Model/DotNet-Data-Access-Model