使用 Dapper 进行简单增删改查示例
首先根据数据库表定义实体对象， 这个工作完全可以使用T4、Nvelocity或者RazorEngine 写一个代码生成器根据数据库表对象自动生成数据库表实体对象。这里我自己根据表写了一个对象
[Table("UserRole")]
public class UserRoleDbEntity:DbEntityModelBase
{
[Description("用户编号，来自用户表")]
public int UserId
{
get;
set;
}
[Description("角色编号，来自于角色表")]
public int RoleId
{
get;
set;
}
/// <summary>
/// 备注：AuthorityEnum.AuthorityValue 的取值范围是根据 AuthorityEnum位运算 或 与 的结果集；不可随意赋值
/// </summary>
[Description("权限值")]
public int AuthorityValue
{
get;
set;
}
/// <summary>
/// 根据 AuthorityEnum 枚举值生成的描述
/// </summary>
[Description("权限描述")]
public string AuthorityDescription
{
get;
set;
}
}
/// <summary>
/// 所有DbEntityModel项目中的实体必须继承DbEntityModelBase或其子类，使用supperType模式控制共有子类的行为或者状态，此项目中的类根据数据库基本表或者视图保持基本一致
/// </summary>
public abstract class DbEntityModelBase
{
[Description("Guid标识")]
public string GuidMark
{
get;
set;
}
[Description("自增Id列")]
public int Id
{
get;
set;
}
[Description("排序，倒序")]
public int Sort
{
get;
set;
}
}在DAL层就可以使用实体对象传参 或者作为返回值
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OnlineExercise.DbAccess;
using Dapper;
using System.Configuration;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using OnlineExercise.DbEntityModel;
using OnlineExercise.Log;
using OnlineExercise.Infrastructrue;
namespace OnlineExercise.DbAccess.SysAdminModule
{
public class UserRoleDB:DalBase<UserRoleDB>
{
public int AddUserRole(UserRoleDbEntity model)
{
int affecgtRow = 0;
string sql = @"INSERT INTO `userrole`
(`GuidMark`,
`UserId`,
`RoleId`,
`AuthorityValue`,
`AuthorityDescription`)
VALUES (@GuidMark,
@UserId,
@RoleId,
@AuthorityValue,
@AuthorityDescription);";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
affecgtRow = conn.Execute(sql, model);
}
return affecgtRow;
}
public int UpdateUserRoleByRoleIdAndUserId(UserRoleDbEntity model)
{
int affecgtRow = 0;
string sql = @"UPDATE `userrole`
SET `AuthorityValue` = @AuthorityValue,
`AuthorityDescription` = @AuthorityDescription
WHERE `UserId` = @UserId
AND `RoleId` = @RoleId;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
affecgtRow = conn.Execute(sql, model);
}
return affecgtRow;
}
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;
}
public int UpdateUserRoleByUserId(UserRoleDbEntity model)
{
int affecgtRow = 0;
string sql = @"UPDATE `userrole`
SET `AuthorityValue` = @AuthorityValue,
`AuthorityDescription` = @AuthorityDescription
WHERE `UserId` = @UserId;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
affecgtRow = conn.Execute(sql, model);
}
return affecgtRow;
}
public List<UserRoleDbEntity> GetUserRoleListByRoleId(UserRoleDbEntity model)
{
List<UserRoleDbEntity> modelList = null;
string sql = @"SELECT
`Id`,
`GuidMark`,
`sort`,
`UserId`,
`RoleId`,
`AuthorityValue`,
`AuthorityDescription`
FROM `userrole`
WHERE RoleId=@RoleId;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
modelList = conn.Query<UserRoleDbEntity>(sql, model).ToList<UserRoleDbEntity>();
}
return modelList;
}
public List<UserRoleDbEntity> GetUserRoleListByUserId(string userId)
{
List<UserRoleDbEntity> modelList = null;
string sql = @"SELECT
`Id`,
`GuidMark`,
`sort`,
`UserId`,
`RoleId`,
`AuthorityValue`,
`AuthorityDescription`
FROM `userrole`
WHERE UserId=@UserId;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
modelList = conn.Query<UserRoleDbEntity>(sql, new { UserId =userId}).ToList<UserRoleDbEntity>();
}
return modelList;
}
public List<UserRoleDbEntity> GetUserRoleListByRoleIdAndUserId(UserRoleDbEntity model)
{
List<UserRoleDbEntity> modelList = null;
string sql = @"SELECT
`Id`,
`GuidMark`,
`sort`,
`UserId`,
`RoleId`,
`AuthorityValue`,
`AuthorityDescription`
FROM `userrole`
WHERE RoleId=@RoleId and UserId=@UserId;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
modelList = conn.Query<UserRoleDbEntity>(sql, model).ToList<UserRoleDbEntity>();
}
return modelList;
}
public int DeleteUserRoleByUserId(string userId)
{
int affecgtRow = 0;
string sql = @"DELETE
FROM `userrole`
WHERE `UserId` = @UserId";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
affecgtRow = conn.Execute(sql, new { UserId = userId });
}
return affecgtRow;
}
public int DeleteUserRoleByRoleId(string roleId)
{
int affecgtRow = 0;
string sql = @"DELETE
FROM `userrole`
WHERE `RoleId` = @RoleId;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
affecgtRow = conn.Execute(sql, new { RoleId = roleId });
}
return affecgtRow;
}
public DataTable GetRoleInfoByUserId(string userId)
{
DataTable dt = null;
string sql = @"SELECT b.*,a.userid,c.name as userName FROM userrole AS a
INNER JOIN role AS b ON a.roleid=b.id
INNER JOIN USER AS c ON c.id=a.userid
WHERE a.userid=@userid;";
using (IDbConnection conn = new MySqlConnection(GlobalVariablesManager.G_Strconn))
{
IDataReader reader = conn.ExecuteReader(sql, new { userid=userId });
dt = CoreUtil.DataReader2Table(reader);
reader.Dispose();
}
return dt;
}
}
}
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更多特性
动态dynamic绑定
var rows = connection.Query("select 1 A, 2 B union all select 3, 4");
((int)rows[0].A)
.IsEqualTo(1);
((int)rows[0].B)
.IsEqualTo(2);
((int)rows[1].A)
.IsEqualTo(3);
((int)rows[1].B)
.IsEqualTo(4);批量插入
connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)", new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } } )
.IsEqualTo(3);
// 3 rows inserted: "1,1", "2,2" and "3,3"多表关联
var sql =
@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";
var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();
post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);多结果查询
var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";
using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
var customer = multi.Read<Customer>().Single();
var orders = multi.Read<Order>().ToList();
var returns = multi.Read<Return>().ToList();
...
}存储过程
var user = cnn.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).SingleOrDefault();
// 你还可以获取存储过程out参数的输出值或者返回值
var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);
int b = p.Get<int>("@b");
int c = p.Get<int>("@c");参数自动绑定
new {A = 1, B = "b"} // A will be mapped to the param @A, B to the param @B