C# Dapper的使用
一、什么是Dapper
Dapper是一款轻量级ORM工具（Github）。如果你在小的项目中，使用Entity Framework、NHibernate 来处理大数据访问及关系映射，未免有点杀鸡用牛刀。你又觉得ORM省时省力，这时Dapper 将是你不二的选择。
二、Dapper的优点
- 轻量。只有一个文件SqlMapper.cs，编译后就40K的一个很小的Dll.
- 速度快。Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。
- 支持多种数据库。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server
- 可以映射一对一，一对多，多对多等多种关系。
- 性能高。通过Emit反射IDataReader的序列队列，来快速的得到和产生对象，性能不错。
- 支持FrameWork2.0，3.0，3.5，4.0，4.5
- Dapper语法十分简单。并且无须迁就数据库的设计。
三、使用方法
1、添加NuGet程序包Dapper
2、配置文件添加连接数据库的字符串
<?xml version="1.0" encoding="utf-8"?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8"/> </startup> <connectionStrings> <add name="MyStrConn" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DHR_DB;Integrated Security=True" /> </connectionStrings> </configuration>
3、添加DapperTools.cs(添加、批量添加、删除、批量删除、修改、批量修改、查询、查询in的操作、多语句操作)
// <copyright file="DapperTools.cs" company="PlaceholderCompany"> // Copyright (c) PlaceholderCompany. All rights reserved. // </copyright> namespace CSharpStudy { using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; using Dapper; internal class DapperTools { public static string ConnectionString = ConfigurationManager.ConnectionStrings["MyStrConn"].ConnectionString; /// <summary> /// 添加. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <param name="t">传入实体类型.</param> /// <returns>int.</returns> public static int Add<T>(string sql, T t) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Execute(sql, t); } } /// <summary> /// 批量添加. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <param name="t">传入泛型类.</param> /// <returns>int.</returns> public static int Add<T>(string sql, List<T> t) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Execute(sql, t); } } /// <summary> /// 删除. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <param name="t">传入实体类型.</param> /// <returns>int.</returns> public static int Delete<T>(string sql, T t) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Execute(sql, t); } } /// <summary> /// 批量删除. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <param name="t">传入泛型类.</param> /// <returns>int.</returns> public static int Delete<T>(string sql, List<T> t) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Execute(sql, t); } } /// <summary> /// 修改. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <param name="t">传入实体类型.</param> /// <returns>int.</returns> public static int Update<T>(string sql, T t) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Execute(sql, t); } } /// <summary> /// 批量修改. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <param name="t">传入泛型类.</param> /// <returns>int.</returns> public static int Update<T>(string sql, List<T> t) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Execute(sql, t); } } /// <summary> /// 查询. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <returns>泛型类.</returns> public static List<T> Query<T>(string sql) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Query<T>(sql).ToList(); } } /// <summary> /// 查询指定数据. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <param name="t">传入泛型类.</param> /// <returns>类.</returns> public static T Query<T>(string sql, T t) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Query<T>(sql, t).SingleOrDefault(); } } /// <summary> /// 查询的in操作. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> /// <returns>泛型类.</returns> public static List<T> Query<T>(string sql, int[] ids) where T : class { using (IDbConnection connection = new SqlConnection(ConnectionString)) { return connection.Query<T>(sql, new { ids }).ToList(); } } /// <summary> /// 多语句操作. /// </summary> /// <typeparam name="T">实体类型.</typeparam> /// <param name="sql">传入sql执行语句.</param> public static void QueryMultiple(string sql) { using (IDbConnection connection = new SqlConnection(ConnectionString)) { var multiReader = connection.QueryMultiple(sql); var userInfo = multiReader.Read<UserInfo>(); var student = multiReader.Read<Student>(); multiReader.Dispose(); } } } }
4、添加测试类Student.cs、UserInfo。表数据自己添加
public class Student { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } public string Memo { get; set; } public DateTime CreateTime { get; set; } public DateTime UpdateTime { get; set; } }
public class UserInfo { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } public string Memo { get; set; } public DateTime CreateTime { get; set; } public DateTime UpdateTime { get; set; } }
5、调用方法
//----------------------------------------------------------------------- // <copyright file="Program.cs" company="PlaceholderCompany"> // Copyright (c) PlaceholderCompany. All rights reserved. // </copyright> // <author>John Doe</author> //----------------------------------------------------------------------- namespace CSharpStudy { using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Dapper; internal class Program { private static void Main(string[] args) { // --------------添加-------------- // UserInfo user = new UserInfo(); // user.Name = "九九"; // user.Age = 10; // user.Memo = "这是备注"; // user.CreateTime = DateTime.Now; // string sql = "Insert into UserInfo(Name,Age,Memo,CreateTime) values (@name, @Age, @Memo,@CreateTime)"; // int result = DapperTools.Add<UserInfo>(sql, user); // if (result > 0) // { // Console.WriteLine("添加成功"); // Console.ReadKey(); // } // --------------批量添加-------------- // UserInfo user = new UserInfo(); // user.Name = "李奎"; // user.Age = 50; // user.Memo = "这是备注"; // user.CreateTime = DateTime.Now; // UserInfo user2 = new UserInfo(); // user2.Name = "梁山伯"; // user2.Age = 54; // user2.Memo = "这是备注"; // user2.CreateTime = DateTime.Now; // List<UserInfo> list = new List<UserInfo>(); // list.Add(user); // list.Add(user2); // string sql = "Insert into UserInfo(Name,Age,Memo,CreateTime) values (@name, @Age, @Memo,@CreateTime)"; // int result = DapperTools.Add<UserInfo>(sql, list); // if (result > 0) // { // Console.WriteLine("添加成功"); // Console.ReadKey(); // } // --------------删除-------------- // UserInfo user = new UserInfo(); // user.Id = 18; // string sql = "delete from UserInfo where Id=@Id"; // int result = DapperTools.Delete<UserInfo>(sql, user); // if (result > 0) // { // Console.WriteLine("删除成功"); // Console.ReadKey(); // } // --------------批量删除-------------- // UserInfo user = new UserInfo(); // user.Id = 15; // UserInfo user2 = new UserInfo(); // user2.Id = 16; // List<UserInfo> list = new List<UserInfo>(); // list.Add(user); // list.Add(user2); // string sql = "delete from UserInfo where Id=@Id"; // int result = DapperTools.Delete<UserInfo>(sql, list); // if (result > 0) // { // Console.WriteLine("添加成功"); // Console.ReadKey(); // } // --------------修改-------------- // UserInfo user = new UserInfo(); // user.Id = 14; // user.Name = "九九"; // string sql = "update UserInfo set Name=@Name,UpdateTime=GETDATE() where Id=@ID"; // int result = DapperTools.Update<UserInfo>(sql, user); // if (result > 0) // { // Console.WriteLine("修改成功"); // Console.ReadKey(); // } // --------------批量修改-------------- // UserInfo user = new UserInfo(); // user.Id = 13; // user.Name = "王文"; // UserInfo user2 = new UserInfo(); // user2.Id = 14; // user2.Name = "冰冰"; // List<UserInfo> list = new List<UserInfo>(); // list.Add(user); // list.Add(user2); // string sql = "update UserInfo set Name=@Name,UpdateTime=GETDATE() where Id=@ID"; // int result = DapperTools.Update<UserInfo>(sql, list); // if (result > 0) // { // Console.WriteLine("修改成功"); // Console.ReadKey(); // } // --------------查询-------------- // string sql = "select * from UserInfo"; // List<UserInfo> list = DapperTools.Query<UserInfo>(sql); // foreach (var item in list) // { // Console.WriteLine(item.Id + "-" + item.Name + "-" + item.Age + "-" + item.Memo); // } // Console.ReadKey(); // --------------查询指定数据-------------- // UserInfo user = new UserInfo(); // user.Id = 14; // string sql = "select * from UserInfo where Id=@Id"; // UserInfo userInfo = DapperTools.Query<UserInfo>(sql, user); // Console.WriteLine(userInfo.Id + "-" + userInfo.Name + "-" + userInfo.Age + "-" + userInfo.Memo); // Console.ReadKey(); // --------------查询的in操作-------------- // string sql = "select * from UserInfo where Id in @ids"; // int[] ids = { 1, 2 }; // List<UserInfo> list = DapperTools.Query<UserInfo>(sql, ids); // foreach (var item in list) // { // Console.WriteLine(item.Id + "-" + item.Name + "-" + item.Age + "-" + item.Memo); // } // --------------多语句操作-------------- // string sql = "select * from userinfo;select * from student"; // DapperTools.QueryMultiple(sql); } } }
6、通用分页
/// <summary> /// dapper通用分页方法 /// </summary> /// <typeparam name="T">泛型集合实体类</typeparam> /// <param name="conn">数据库连接池连接对象</param> /// <param name="files">列</param> /// <param name="tableName">表</param> /// <param name="where">条件</param> /// <param name="orderby">排序</param> /// <param name="pageIndex">当前页</param> /// <param name="pageSize">当前页显示条数</param> /// <param name="total">结果集总数</param> /// <returns></returns> public static IEnumerable<T> GetPageList<T>(IDbConnection conn, string files, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total) { 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}", files, tableName, where, orderby, skip, pageIndex * pageSize); using (var reader = conn.QueryMultiple(sb.ToString())) { total = reader.ReadFirst<int>(); return reader.Read<T>(); } }
源代码：
链接：https://pan.baidu.com/s/17vGohi7Ng-xwoghKL0IMLw
提取码：wm0s
后续会陆续更新其他资料,喜欢请关注哦！
