使用轻量级ORM Dapper进行增删改查
项目背景
前一段时间，开始做一个项目，在考虑数据访问层是考虑技术选型，考虑过原始的ADO.NET、微软的EF、NH等。再跟经理讨论后，经理强调不要用Ef，NH做ORM，后期的sql优化不好做，公司也没有人对EF，Nh 等orm优化比较熟悉的。强调说的，我们的项目要做的得简单，可以使用ADO.NET 写原始的sql。但我自己还是喜欢ORM的，它可以提高数据访问层的开发。有一天，在订阅张善友 doNet跨平台微信公众号里，看到Dapper的推荐。了解之后，我自己喜欢喜欢Dapper，可以满足我这个项目的经理的要求，同时Dapper 对数据库的访问能做到Ado.net一样快。
下面的链接是Dapper 在github的地址 https://github.com/StackExchange/dapper-dot-net。
使用 Dapper 进行简单增删改查示例
1、首先根据数据库表定义实体对象， 这个工作完全可以使用T4、Nvelocity或者RazorEngine 写一个代码生成器根据数据库表对象自动生成数据库表实体对象。这里我自己根据表写了一个对象
1 [Table("UserRole")] 2 public class UserRoleDbEntity:DbEntityModelBase 3 { 4 [Description("用户编号，来自用户表")] 5 public int UserId 6 { 7 get; 8 set; 9 } 10 11 [Description("角色编号，来自于角色表")] 12 public int RoleId 13 { 14 get; 15 set; 16 } 17 /// <summary> 18 /// 备注：AuthorityEnum.AuthorityValue 的取值范围是根据 AuthorityEnum位运算 或 与 的结果集；不可随意赋值 19 /// </summary> 20 [Description("权限值")] 21 public int AuthorityValue { get; set; } 22 23 /// <summary> 24 /// 根据 AuthorityEnum 枚举值生成的描述 25 /// </summary> 26 [Description("权限描述")] 27 public string AuthorityDescription { get; set; } 28 } 29 30 /// <summary> 31 /// 所有DbEntityModel项目中的实体必须继承DbEntityModelBase或其子类，使用supperType模式控制共有子类的行为或者状态，此项目中的类根据数据库基本表或者视图保持基本一致 32 /// </summary> 33 public abstract class DbEntityModelBase 34 { 35 [Description("Guid标识")] 36 public string GuidMark 37 { 38 get; 39 set; 40 } 41 [Description("自增Id列")] 42 public int Id 43 { 44 get; 45 set; 46 } 47 [Description("排序，倒序")] 48 public int Sort 49 { 50 get; 51 set; 52 } 53 }
2. 在DAL层就可以使用实体对象传参 或者作为返回值
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using OnlineExercise.DbAccess; 6 using Dapper; 7 using System.Configuration; 8 using System.Data; 9 using MySql.Data; 10 using MySql.Data.MySqlClient; 11 using OnlineExercise.DbEntityModel; 12 using OnlineExercise.Log; 13 using OnlineExercise.Infrastructrue; 14 15 namespace OnlineExercise.DbAccess.SysAdminModule 16 { 17 public class UserRoleDB:DalBase<UserRoleDB> 18 { 19 public int AddUserRole(UserRoleDbEntity model) 20 { 21 int affecgtRow = 0; 22 string sql = @"INSERT INTO `userrole` 23 (`GuidMark`, 24 `UserId`, 25 `RoleId`, 26 `AuthorityValue`, 27 `AuthorityDescription`) 28 VALUES (@GuidMark, 29 @UserId, 30 @RoleId, 31 @AuthorityValue, 32 @AuthorityDescription);"; 33 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 34 { 35 affecgtRow = conn.Execute(sql, model); 36 } 37 return affecgtRow; 38 } 39 40 public int UpdateUserRoleByRoleIdAndUserId(UserRoleDbEntity model) 41 { 42 int affecgtRow = 0; 43 string sql = @"UPDATE `userrole` 44 SET `AuthorityValue` = @AuthorityValue, 45 `AuthorityDescription` = @AuthorityDescription 46 WHERE `UserId` = @UserId 47 AND `RoleId` = @RoleId;"; 48 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 49 { 50 affecgtRow = conn.Execute(sql, model); 51 } 52 return affecgtRow; 53 } 54 55 public int UpdateUserRoleByRoleId(UserRoleDbEntity model) 56 { 57 int affecgtRow = 0; 58 string sql = @"UPDATE `userrole` 59 SET `AuthorityValue` = @AuthorityValue, 60 `AuthorityDescription` = @AuthorityDescription 61 WHERE `RoleId` = @RoleId;"; 62 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 63 { 64 affecgtRow = conn.Execute(sql, model); 65 } 66 return affecgtRow; 67 } 68 69 public int UpdateUserRoleByUserId(UserRoleDbEntity model) 70 { 71 int affecgtRow = 0; 72 string sql = @"UPDATE `userrole` 73 SET `AuthorityValue` = @AuthorityValue, 74 `AuthorityDescription` = @AuthorityDescription 75 WHERE `UserId` = @UserId;"; 76 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 77 { 78 affecgtRow = conn.Execute(sql, model); 79 } 80 return affecgtRow; 81 } 82 83 public List<UserRoleDbEntity> GetUserRoleListByRoleId(UserRoleDbEntity model) 84 { 85 List<UserRoleDbEntity> modelList = null; 86 string sql = @"SELECT 87 `Id`, 88 `GuidMark`, 89 `sort`, 90 `UserId`, 91 `RoleId`, 92 `AuthorityValue`, 93 `AuthorityDescription` 94 FROM `userrole` 95 WHERE RoleId=@RoleId;"; 96 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 97 { 98 modelList = conn.Query<UserRoleDbEntity>(sql, model).ToList<UserRoleDbEntity>(); 99 } 100 return modelList; 101 } 102 103 104 public List<UserRoleDbEntity> GetUserRoleListByUserId(string userId) 105 { 106 List<UserRoleDbEntity> modelList = null; 107 string sql = @"SELECT 108 `Id`, 109 `GuidMark`, 110 `sort`, 111 `UserId`, 112 `RoleId`, 113 `AuthorityValue`, 114 `AuthorityDescription` 115 FROM `userrole` 116 WHERE UserId=@UserId;"; 117 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 118 { 119 modelList = conn.Query<UserRoleDbEntity>(sql, new { UserId =userId}).ToList<UserRoleDbEntity>(); 120 } 121 return modelList; 122 } 123 124 public List<UserRoleDbEntity> GetUserRoleListByRoleIdAndUserId(UserRoleDbEntity model) 125 { 126 List<UserRoleDbEntity> modelList = null; 127 string sql = @"SELECT 128 `Id`, 129 `GuidMark`, 130 `sort`, 131 `UserId`, 132 `RoleId`, 133 `AuthorityValue`, 134 `AuthorityDescription` 135 FROM `userrole` 136 WHERE RoleId=@RoleId and UserId=@UserId;"; 137 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 138 { 139 modelList = conn.Query<UserRoleDbEntity>(sql, model).ToList<UserRoleDbEntity>(); 140 } 141 return modelList; 142 } 143 144 public int DeleteUserRoleByUserId(string userId) 145 { 146 int affecgtRow = 0; 147 string sql = @"DELETE 148 FROM `userrole` 149 WHERE `UserId` = @UserId"; 150 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 151 { 152 affecgtRow = conn.Execute(sql, new { UserId = userId }); 153 } 154 return affecgtRow; 155 } 156 157 public int DeleteUserRoleByRoleId(string roleId) 158 { 159 int affecgtRow = 0; 160 string sql = @"DELETE 161 FROM `userrole` 162 WHERE `RoleId` = @RoleId;"; 163 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 164 { 165 affecgtRow = conn.Execute(sql, new { RoleId = roleId }); 166 167 } 168 return affecgtRow; 169 } 170 171 public DataTable GetRoleInfoByUserId(string userId) 172 { 173 DataTable dt = null; 174 175 string sql = @"SELECT b.*,a.userid,c.name as userName FROM userrole AS a 176 INNER JOIN role AS b ON a.roleid=b.id 177 INNER JOIN USER AS c ON c.id=a.userid 178 WHERE a.userid=@userid;"; 179 using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn)) 180 { 181 IDataReader reader = conn.ExecuteReader(sql, new { userid=userId }); 182 dt = CoreUtil.DataReader2Table(reader); 183 reader.Dispose(); 184 } 185 186 return dt; 187 } 188 189 } 190 }
Dapper的优势
1、Dapper是一个轻型的ORM类
2、 Dapper语法简单，如果你喜欢写原始的sql，你一定喜欢Dapper。同时团队人员也很容易上手
3、Dapper 速度快，速度接近ADO.NET访问数据库的效率。
4、多数据库切换方便
public int UpdateUserRoleByRoleId(UserRoleDbEntity model)
{
int affecgtRow = 0;
string sql = @"UPDATE `userrole`
SET `AuthorityValue` = @AuthorityValue,
`AuthorityDescription` = @AuthorityDescription
WHERE `RoleId` = @RoleId;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
affecgtRow = conn.Execute(sql, model);
}
return affecgtRow;
}
这里mysql如果要切换为Sql Server ，只要修改链接 MySqlConnection---》SqlConnection。
Dapper更多特性
1、支持动态dynamic绑定
1 var rows = connection.Query("select 1 A, 2 B union all select 3, 4"); 2 3 ((int)rows[0].A) 4 .IsEqualTo(1); 5 6 ((int)rows[0].B) 7 .IsEqualTo(2); 8 9 ((int)rows[1].A) 10 .IsEqualTo(3); 11 12 ((int)rows[1].B) 13 .IsEqualTo(4);
2、支持批量插入
1 connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)", 2 new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } } 3 ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"
3、支持多表关联
1 var sql = 2 @"select * from #Posts p 3 left join #Users u on u.Id = p.OwnerId 4 Order by p.Id"; 5 6 var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;}); 7 var post = data.First(); 8 9 post.Content.IsEqualTo("Sams Post1"); 10 post.Id.IsEqualTo(1); 11 post.Owner.Name.IsEqualTo("Sam"); 12 post.Owner.Id.IsEqualTo(99);
4、支持多结果查询
1 var sql = 2 @" 3 select * from Customers where CustomerId = @id 4 select * from Orders where CustomerId = @id 5 select * from Returns where CustomerId = @id"; 6 7 using (var multi = connection.QueryMultiple(sql, new {id=selectedId})) 8 { 9 var customer = multi.Read<Customer>().Single(); 10 var orders = multi.Read<Order>().ToList(); 11 var returns = multi.Read<Return>().ToList(); 12 ... 13 }
5 支持存储过程
1 var user = cnn.Query<User>("spGetUser", new {Id = 1}, 2 commandType: CommandType.StoredProcedure).SingleOrDefault(); 3 // 你还可以获取存储过程out参数的输出值或者返回值 4 var p = new DynamicParameters(); 5 p.Add("@a", 11); 6 p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output); 7 p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); 8 9 cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure); 10 11 int b = p.Get<int>("@b"); 12 int c = p.Get<int>("@c");
6、参数自动绑定
1 new {A = 1, B = "b"} // A will be mapped to the param @A, B to the param @B
看到Dapper那么特性，觉得使用Dapper非常方便，使用也非常方便，扩展性也非常高。 当我用Dapper写一个demo给项目经理看的时候，项目经理就同意使用
Dapper 作为ORM 进行数据访问层的开发。从此就爱上了Dapper。
希望这篇文章给你带来对Dapper清晰的了解。同时如果这文章给你到来了帮助，也别忘了帮忙推荐。
