一、缘起
闲来无事发现这个东西的，和我自己写的轻量级ORM有异曲同工之妙，所以研究了一下，同时写下使用说明。
Dapper是一款轻量级.net ORM工具，网评都不错。
Dapper是开源的，Github地址是：https://github.com/StackExchange/Dapper/tree/master/Dapper
Dapper的使用方法参考：https://www.w3cschool.cn/dapperorm/
注意：如果你的VS或者.Net版本较低，可以选择较早的版本。我这里用的就是VS2017+.Net 4.5
二、续缘
新创建一个空的MVC5项目，并把Dapper项目加入到项目中。
1、扩展Dapper
原生的查询很多都是直接写SQL的，有时候表名称可能会带有前缀如"cms_User”，而我们习惯使用的实体类可能不会关注数据库的表名，所以我们可以对Dapper适当扩展一下。
在Dapper.Contrib里面有个文件SqlMapperExtensions.cs就是对Dapper的扩展，但是暴露了conn，我们再创建一个DapperClient.cs进行封装。
1) 实体类
实体类是对数据源表的映射转换，同时也要反映数据源的属性。比如表名、字段、关键字等。我们用特性表示
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;
namespace Useage.Code.Model
{
[Table("cms_User")]
public class UserInfo
{
[Key]
public int AutoID { get; set; }
public string UserName { get; set; }
public string Gander { get; set; }
public int Age { get; set; }
}
}特性TableAttribute和KeyAttribute类的定义可以在SqlMapperExtensions.cs中找到
2) 封装CRUD基本操作
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Dapper
{
/// <summary>
/// Dapper客户端 Created by JsonLee 2019-12-20
/// </summary>
public class DapperClient
{
private string _connstr = ConnSource.ConnStr;
public DapperClient(string connStr)
{
_connstr = connStr;
}
public DapperClient() { }
#region 一般查询
/// <summary>
/// 查询并返回Id为主键的对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Id"></param>
/// <returns></returns>
public T GetModel<T>(int id) where T : class
{
using (var conn = new SqlConnection(_connstr))
{
return conn.Get<T>(id);
}
}
public T GetModel<T>(string sql) where T : class
{
using (var conn = new SqlConnection(_connstr))
{
return conn.QueryFirstOrDefault<T>(sql);
}
}
public IList<T> GetList<T>(string where="",string orderBy="") where T:class
{
using (var conn = new SqlConnection(_connstr))
{
string tableName = SqlMapperExtensions.GetTableName(typeof(T));
string sql = $" select * from {tableName} ";
if (!string.IsNullOrEmpty(where))
sql += $" where {where} ";
if (!string.IsNullOrEmpty(orderBy))
sql += $" order by {orderBy} ";
return conn.Query<T>(sql).ToList();
}
}
#endregion
#region 分页
/// <summary>
/// 查询分页记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filters"></param>
/// <param name="where"></param>
/// <param name="orderby"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <param name="totalPage"></param>
/// <returns></returns>
public IList<T> GetPageList<T>(string filters, string where, string orderby,
int pageIndex, int pageSize, ref int totalCount, ref int totalPage) where T : class
{
string tableName = SqlMapperExtensions.GetTableName(typeof(T));
if (string.IsNullOrEmpty(where))
where = " 1=1 ";
using (var conn = new SqlConnection(_connstr))
{
int skip = 1;
if (pageIndex > 0)
{
skip = (pageIndex - 1) * pageSize + 1;
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where); //总记录数
sb.AppendFormat(@"SELECT {0}
FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
FROM {1}
WHERE {2}
) AS result
WHERE RowNum >= {4} AND RowNum <= {5}
ORDER BY {3}", filters, tableName, where, orderby, skip, pageIndex * pageSize);
using (var reader = conn.QueryMultiple(sb.ToString()))
{
totalCount = reader.ReadFirst<int>();
totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize) + 1;
return reader.Read<T>().ToList();
}
}
}
public int GetCount<T>(string condition) where T : class
{
using (var conn = new SqlConnection(_connstr))
{
return conn.GetCount<T>(condition);
}
}
#endregion
#region 增加
public int Add<T>(T model) where T : class
{
using (var conn = new SqlConnection(_connstr))
{
return conn.Insert<T>(model);
}
}
#endregion
#region 修改
public bool Update<T>(T model) where T : class
{
using (var conn = new SqlConnection(_connstr))
{
return conn.Update<T>(model);
}
}
#endregion
#region 删除
public bool Delete<T>(string condition) where T : class
{
using (var conn = new SqlConnection(_connstr))
{
return conn.Delete<T>(condition);
}
}
public bool Delete<T>(T model) where T : class
{
using (var conn = new SqlConnection(_connstr))
{
return conn.Delete<T>(model);
}
}
#endregion
}
}
2、使用例子
在MVC的Action中使用DapperClient，创建多个业务方法。前端使用vue异步加载。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Useage.Code.Model;
using Dapper;
using Useage.Code.Utils;
namespace Useage.Controllers
{
public class DefaultController : Controller
{
DapperClient client = new DapperClient();
// GET: Default
public ActionResult Index()
{
ViewBag.List = client.GetList<UserInfo>();
return View();
}
[HttpGet]
public string Get(int id) =>
client.GetModel<UserInfo>(id).ToJson();
[HttpGet]
public string GetPager(int pageIndex,int pageSize)
{
int totalCount = 0;
int totalPage = 0;
var lst = client.GetPageList<UserInfo>("*", " 1=1 ", "AutoID desc", pageIndex, pageSize, ref totalCount, ref totalPage);
return $"{{\"result\":{lst.ToJson()},\"totalCount\":{totalCount},\"totalPage\":{totalPage}}}";
}
[HttpPost]
public string Add()
{
int ret = client.Add<UserInfo>(new UserInfo()
{
UserName = Request.Form["name"].ToString(),
Gander = Request.Form["gander"].ToString(),
Age = int.Parse(Request.Form["age"])
});
return $"{{\"result\":{(ret > 0 ? "true" : "false")}}}";
}
[HttpPost]
public string Update()
{
bool ret = false;
var user = client.GetModel<UserInfo>(int.Parse(Request.Form["id"]));
if (user != null)
{
user.UserName = Request.Form["name"].ToString();
user.Gander = Request.Form["gander"].ToString();
user.Age = int.Parse(Request.Form["age"]);
ret = client.Update<UserInfo>(user);
}
return $"{{\"result\":{(ret ? "true" : "false")}}}";
}
[HttpPost]
public string Del()
{
bool ret = client.Delete<UserInfo>(" and AutoID=" + int.Parse(Request.Form["id"]));
return $"{{\"result\":{(ret ? "true" : "false")}}}";
}
}
}三、缘灭
Dapper挺简单的，也不知道说些什么，主要是用到了反射，反射实体类获得字段名称并赋值，而表名和关键字等附加信息可以用特性来表示。
以上代码写成了Demo，点击这里下载