轻量级ORM框架Dapper应用二:使用Dapper实现CURD操作
在上一篇文章中,讲解了如何安装Dapper,这篇文章中将会讲解如何使用Dapper使用CURD操作。
例子中使用到的实体类定义如下:
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 7 namespace DapperApplicationDemo.Model 8 { 9 public class User 10 { 11 public int UserId { get; set; } 12 13 public string UserName { get; set; } 14 15 public string Email { get; set; } 16 17 public string Address { get; set; } 18 } 19 }
注意:在使用下面的方法之前要首先引入Dapper的命名空间:Using Dapper;
一、插入数据
1、使用匿名类插入数据
IDbConnection connection = new SqlConnection(conn); var result = connection.Execute( "Insert into Users values (@UserName, @Email, @Address)", new { UserName = "Tom", Email = "747954712@qq.com", Address = "北京" });
查询数据库:
2、使用实体类插入数据
string sqlCommandText = "insert into Users(UserName,Email,Address) Values (@UserName,@Email,@Address)"; using (IDbConnection connection = new SqlConnection(conn)) { User user = new User() { UserName = "tim", Email = "78415155@qq.com", Address = "北京" }; int result = connection.Execute(sqlCommandText,user); if (result > 0) { Console.WriteLine("插入成功!"); } else { Console.WriteLine("插入失败!"); } }
查询数据库:
3、InsertBulk操作
既然是Bulk操作,那肯定就是批量插入了,我们要做的就是将上面使用到的“匿名对象”变成“匿名对象集合”就可以了,代码如下:
using (IDbConnection connection = new SqlConnection(conn)) { var userList = Enumerable.Range(1012, 100000).Select(i => new User() { Email = i + "qq.com", Address = "北京", UserName = "CK" + i, }); var result = connection.Execute("insert into Users values(@UserName,@Email,@Address)", userList); }
查询数据库:
二、查询数据
using (IDbConnection connection = new SqlConnection(conn)) { // 查询 var query = connection.Query<User>("SELECT * FROM Users"); query.AsList().ForEach(p => { Console.WriteLine("Id:"+p.UserId+" UserName:"+p.UserName+" Email:"+p.Email+" Address:"+p.Address); }); }
程序运行结果:
三、更新数据
1、使用匿名类更新
using (IDbConnection connection = new SqlConnection(conn)) { var result = connection.Execute("update Users set UserName='Tim',Address='上海' where UserId=@UserId", new { UserId = 2 }); }
查询数据库:
2、使用实体类更新
using (IDbConnection connection = new SqlConnection(conn)) { User user = new User(); user.UserName = "张无忌"; user.UserId = 1; var result = connection.Execute("update Users set UserName=@UserName where UserId=@UserId", user); }
查询数据库:
3、使用键值对更新
using (IDbConnection connection = new SqlConnection(conn)) { List<KeyValuePair<string, object>> keys = new List<KeyValuePair<string, object>>(); keys.Add(new KeyValuePair<string, object>("@UserName", "风清扬")); keys.Add(new KeyValuePair<string, object>("@UserId", 2)); var result = connection.Execute("update Users set UserName=@UserName where UserId=@UserId", keys); }
查询数据库:
四、删除数据
1、使用匿名类删除数据
using (IDbConnection connection = new SqlConnection(conn)) { var result = connection.Execute("delete from Users where UserId=@UserId", new { UserId = 3 }); }
2、使用实体类删除数据
using (IDbConnection connection = new SqlConnection(conn)) { User user = new User(); user.UserId = 4; var result = connection.Execute("delete from Users where UserId=@UserId", user); }
示例程序代码下载地址:https://pan.baidu.com/s/1nvaJ8LV