Dapper基本操作 _操作

采得百花成蜜后，为谁辛苦为谁甜。这篇文章主要讲述Dapper基本操作相关的知识，希望能为你提供帮助。
参考资料：Cooper Liu 毒逆天

一、Dapper安装
添加引用--> NuGet管理--> 搜索Dapper--> 安装

文章图片

二、新建表

文章图片

--创建一个员工表 create table Employee ( Em_Id int identity(1,1) primary key, Em_Name varchar(50) not null, Em_Age int default(18) not null )--部门表 Create Table Department ( Depart_Id int identity(1,1) primary key, Depart_Name varchar(20) not null, )--员工所属部门关系表 Create table EmployeePartment ( EP_Id int identity(1,1) primary key, Em_Id int not null, Depart_Id int not null )

View Code
三、新建实体类
实体类属性要与数据库中的字段名称对应

文章图片

/// < summary> /// 生成Employee实体，注意类属性与表字段要一一对应 /// < /summary> public class Employee { public int Em_Id { get; set; } public string Em_Name { get; set; }public int Em_Age { get; set; } } /// < summary> /// 生成部门Department实体 /// < /summary> public class Department { public int Depart_Id { get; set; } public string Depart_Name { get; set; } }/// < summary> /// 生成部门员工所属部门对应关系 /// < /summary> public class EmployeePartment { public int EP_Id { get; set; } public int Em_Id { get; set; } public int Depart_Id { get; set; } }

View Code四、插入操作
Dapper支持单个插入，也支持批量插入(Bulk)，支持存储过程进行插入操作。

文章图片

/// < summary> /// 声明object类型可用单个对象时插入单对象，也可批量BulkInsert插入(只要实现IEnumable接口) /// < /summary> /// < param name="obj"> < /param> private static void InsertEmployee(Object obj) { using (var conn = GetConnection()) { string sql = "insert into employee values (@Em_Name,@Em_Age)"; int result = conn.Execute(sql, obj); } }/// < summary> /// 插入部门操作 /// < /summary> /// < param name="depart"> < /param> private static void InsertDepartment(object depart) { CommandDefinition command = new CommandDefinition("insert into department values (@Depart_Name)", depart); using (var conn=GetConnection()) { conn.Execute(command); }}/// < summary> /// 生成sqlConnection对象。返回IDbConnection. /// < /summary> /// < returns> < /returns> private static IDbConnection GetConnection() { var conn = new SqlConnection(connstr); return conn; }

View Code示例中使用了conn.Execute()方法，该方法两个种形式的重载。

1、public static int Execute(this IDbConnection cnn, CommandDefinition command);
2、public static int Execute(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
虽说是两种形式，其实质是CommandDefinition对重载的第二个方法条件进行了封闭。由CommandDefinition构造函数定义可以看到对应关系。
public CommandDefinition(string commandText, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, CommandFlags flags = CommandFlags.Buffered);
Execute是IDbConnection的扩展方法，所以conn可以直接调用。
五、删除操作
Dapper支持sql语句删除操作，也支持存储过程删除操作

文章图片

//此处为方法调用 //Ado.net方法删除 DeleteEmployeeById(1); UseCommandDeleteEmployeeUseById(2); //存储过程删除 ProcDeleteEmployeeById(3); //此处为方法实现 /// < summary> /// 根据ID删除对应Employee /// < /summary> /// < param name="id"> 待删除的EmployeeId< /param> private static void DeleteEmployeeById(int id) { using (var conn=GetConnection()) { int result= conn.Execute("delete from Employee where EM_Id=@id", new { @id = id }); } }/// < summary> /// 使用Command形式删除操作 /// < /summary> /// < param name="id"> 待删除的Employee的ID< /param> private static void UseCommandDeleteEmployeeUseById(int id) { var command = new CommandDefinition("delete from Employee where Em_Id=@Eid", new { @Eid = id }, null, null, CommandType.Text,CommandFlags.None); using (var conn=GetConnection()) { int result = conn.Execute(command); } } /// < summary> /// 使用存储过程形式删除Employee /// < /summary> /// < param name="id"> 待删除的Employee< /param> private static void ProcDeleteEmployeeById(int id) { using (var conn = GetConnection()) { int result= conn.Execute("pr_delete_employee", new { @id = id },null,null,CommandType.StoredProcedure); } }

View Code删除示例中也是使用conn.Execute()方法进行操作。
六、更新操作
操作同新增、删除同样使用conn.Execute()方法进行。

文章图片

//方法调用 UpdateEmployeeName(4, "新名称"); UseCommandUpdateEmployee(4, 18); ProcUpdateEmployeeName(5, "旧名称"); //方法实现/// < summary> /// 更新指定ID名称为新值 /// < /summary> /// < param name="eid"> Employee的Id< /param> /// < param name="name"> 新的employee名称< /param> private static void UpdateEmployeeName(int eid, string name) { using (var conn=GetConnection()) { int result= conn.Execute("update Employee set Em_Name=@name where Em_Id=@id", new { @name = name, @id = eid }); } }/// < summary> /// 使用Command形式更新Employee信息 /// < /summary> /// < param name="eid"> 待更新的EmployeeId< /param> /// < param name="Age"> Age新值< /param> private static void UseCommandUpdateEmployee(int eid, int Age) { var command=new CommandDefinition("update Employee set Em_Age=@age where em_Id=@eid",new{@age=Age,@eid=eid},null,null,CommandType.Text,CommandFlags.None); using (var conn=GetConnection()) { int result = conn.Execute(command); } }/// < summary> /// 更新指定ID名称为新值 /// < /summary> /// < param name="eid"> Employee的Id< /param> /// < param name="name"> 新的employee名称< /param> private static void ProcUpdateEmployeeName(int eid, string name) { using (var conn = GetConnection()) { var p = new DynamicParameters(); p.Add("@id", eid); p.Add("@name", name); int result = conn.Execute("pr_update_employee", new {id = eid,name = name },null,null,CommandType.StoredProcedure)); } }

View Code七、查找操作

文章图片

//简单查询 Employee employee = GetEmployeeById(5); if (employee != null) { Console.WriteLine("ID为5的员工已找到名称:" + employee.Em_Name); }//子查询 List< Employee> list_employees = GetEmployeesByPartId(1); Console.WriteLine("共找到{0}记录:", list_employees.Count); for (int i = 0; i < list_employees.Count; i++) { Console.Write(list_employees[i].Em_Name + ","); }//多返回值 GetMutile(); /// < summary> /// 根据ID查找EmpolyeeID 支持ADO语句和存储过程 /// < /summary> /// < param name="id"> < /param> /// < returns> < /returns> private static Employee GetEmployeeById(int id) { Employee employee = new Employee(); using (var conn = GetConnection()) { employee = conn.Query< Employee> ("select * from employee where Em_Id=@id", new { id = id }).FirstOrDefault(); }//CommandDefinition command = new CommandDefinition("select * from employee wehre em_id=@id"); //using (var conn = GetConnection()) //{ //employee = conn.Query< Employee> (command).FirstOrDefault(); //}return employee; }/// < summary> /// 子查询 /// < /summary> /// < param name="partid"> < /param> /// < returns> < /returns> private static List< Employee> GetEmployeesByPartId(int partid) { List< Employee> employees = new List< Employee> (); CommandDefinition command = new CommandDefinition("select * from employee where em_id in (select em_id fromEmployeePARTMENT where Depart_Id=@id) ", new { id = partid }); using (var conn = GetConnection()) { employees = conn.Query< Employee> (command).ToList(); } return employees; }/// < summary> /// 多返回值查询 /// < /summary> private static void GetMutile() { string sql = @"select * from [Employee]; select*from [Department]"; CommandDefinition command = new CommandDefinition(sql); using (var conn = GetConnection()) { var muitle = conn.QueryMultiple(sql); //var muitle=conn.QueryMultiple(command); if (!muitle.IsConsumed) { //强类型读取 //var employees = muitle.Read< Employee> (); //var deparements = muitle.Read< Department> (); //动态类型读取 var employees = muitle.Read(); var deparements = muitle.Read(); foreach (var item in employees) { Console.WriteLine(item.Em_Name + ":" + item.Em_Age.ToString()); }Console.WriteLine(); foreach (var item in deparements) { Console.WriteLine(item.Depart_Name + ":" + item.Depart_Id); }} } }

View Code八、总结
【Dapper基本操作】使用Dapper进行增删改查、存储过程调用、多表值返回操作。