ORM 框架 Dapper的CRUD
首先创建一个项目(我这里创建的是winform),添加引用
创建实体类,连接数据库的DBhelper以及实体类的service
目录结构:
Person.cs
public class Person
{
public int id { get; set; }
public string firstName { get; set; }
public string lastName { get; set; }
public string email { get; set; }
// public DateTime CreateOn { get; set; }
}
DBhelper
public class DBHelper
{
public static string ConnStrings
{
get
{
return ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
}
}
}
App.config中的连接字符串
<connectionStrings>
<add name="connString" connectionString="server=.;database=stuDB;uid=sa;pwd=123456"/>
</connectionStrings>
PersonService
public class PersonService
{
//根据用户姓氏 查询用户的集合
public List<Person> FindListByLastName(string lastname)
{
//查询
using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
{
string sql = $"select * from Person where lastName = @lastName";
IEnumerable<Person> lst = db.Query<Person>(sql, new { lastname = lastname });
return lst.ToList();
}
}
//根据id查询用户
public Person FindByPersonId(int perID)
{
using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
{
string sql = "select * from Person where id=@id";
IEnumerable<Person> list = db.Query<Person>(sql,new { id=perID});
return list.FirstOrDefault(); //返回序列中的第一个元素,如果没有则返回默认值
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="person"></param>
/// <returns></returns>
public bool InsertData(Person person)
{
using(IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
{
//准备插入语句
string sql = "insert into Person(firstName,lastName,email) values (@firstName,@lastName,@email)";
int res = db.Execute(sql, person);
return res > 0;
}
}
/// <summary>
/// 根据id更新数据
/// </summary>
/// <param name="person"></param>
/// <returns></returns>
public bool UpdateData(Person person)
{
using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
{
//准备更新语句
string sql = "update Person set firstName=@firstName,lastName=@lastName,email=@email where id=@id";
//执行更新语句
int res = db.Execute(sql, person);
return res > 0;
}
}
public bool DeleteData(Person person)
{
using (IDbConnection db = new SqlConnection(DBHelper.ConnStrings))
{
string sql = "delete from Person where id = @id";
int res = db.Execute(sql, person);
return res > 0;
}
}
}
- 根据用户姓氏 查询用户的集合
Form1
后台代码
/// <summary>
/// 搜索按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
//实例化业务逻辑类
PersonService ps = new PersonService();
List<Person> list = ps.FindListByLastName(this.txtName.Text.Trim());
//绑定数据源
listContent.DataSource = list;
//显示成员
listContent.DisplayMember = "email";
}
- 插入数据
后台代码
private void btnAdd_Click(object sender, EventArgs e)
{
PersonService personService = new PersonService();
Person person = new Person();
person.firstName = txtfName.Text.Trim();
person.lastName = txtlName.Text.Trim();
person.email = txtEmail.Text.Trim();
var success = personService.InsertData(person);
MessageBox.Show(success ? "插入成功" : "插入失败");
}
- 根据id,修改用户
后台代码
/// <summary>
/// 搜索按钮的事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
PersonService ps = new PersonService();
int perID = int.Parse(this.txtid.Text.Trim());
//根据id查找用户对象的方法,并返回person类型的对象
Person person = ps.FindByPersonId(perID);
//绑定数据
this.txtfName.Text = person.firstName;
this.txtlName.Text = person.lastName;
this.txtEmail.Text = person.email;
}
/// <summary>
/// 更新按钮的事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnUpdate_Click(object sender, EventArgs e)
{
PersonService ps = new PersonService();
//对象初始化器
Person person = new Person()
{
id = Convert.ToInt32(this.txtid.Text.Trim()),
firstName = this.txtfName.Text.Trim(),
lastName = this.txtlName.Text.Trim(),
email = this.txtEmail.Text.Trim()
};
var success = ps.UpdateData(person);
MessageBox.Show(success ? "更新成功!" : "更新失败");
}
- 删除用户
后台代码
/// <summary>
/// 删除按钮的事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDelete_Click(object sender, EventArgs e)
{
//获取要删除的id
int personId = Convert.ToInt32(this.txtDelId.Text);
Person person = new Person
{
id = personId
};
PersonService personService = new PersonService();
var success = personService.DeleteData(person);
MessageBox.Show(success ? "删除成功!" : "删除失败");
}