一、Dapper是什么

Dapper是一款.Net平台简单(Simple)的对象映射库,并且Dapper拥有着“微型ORM之王”的称号。 就速度而言与手写ADO.NET SqlDateReader相同。 ORM是Object Relational Mapper的缩写,代表着数据库与编程语言之间的映射。 简单来说就是使用Entity Framework、NHibernate 来处理大数据访问及关系映射,未免有点杀鸡用牛刀,那么Dapper会是不错的选择。

二、Dapper基本用法

通过NuGet安装:Dapper包,如果要进行mysql连接还需要安装Mysql.Data包

1. 数据库连接配置

  1. <connectionStrings>
  2.   <add name="MysqlServer" connectionString="Database=用数据库名称;Data Source=IP;Port=端口; User Id=用户名;Password=密码;Charset=utf8mb4;TreatTinyAsBoolean=false;" /> //Mysql
  3.   <add name="LinqConnection" connectionString="Data Source=IP;Initial Catalog=数据库名称; User ID=用户名;Password=密码" providerName="System.Data.SqlClient"/> //sql
  4. </connectionStrings>

2. dapper 数据库连接方法

Mysql连接方法
  1. public class DapperService
  2. {
  3.   public static MySqlConnection MySqlConnection()
  4.   {
  5.      string mysqlConnectionStr = ConfigurationManager.ConnectionStrings["MysqlServer"].ToString();
  6.      var connection = new MySqlConnection(mysqlConnectionStr); connection.Open();
  7.     return connection;
  8.   }
  9. }
mssql连接方法
  1. public class DapperService {
  2.   public static SqlConnection MySqlConnection()
  3.   {
  4.     string mysqlConnectionStr =ConfigurationManager.ConnectionStrings["LinqConnection"].ToString();
  5.     var connection = new SqlConnection(mysqlConnectionStr);
  6.     connection.Open();
  7.     return connection;
  8.   }
  9. }

3. 新增方法(单体、批量)

单体
  1. public ActionResult GetDapper(CarModel carModel)
  2. {
  3.   try
  4.   {
  5.     using (IDbConnection conn = DapperService.MySqlConnection())
  6.    {
  7.       int result = conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values (@UserTel,@UserName,@UserPwd)", carModel);
  8.     }
  9.     return Json("success");
  10.   }
  11.   catch(Exception e)
  12.   {
  13.    return Json("failed");
  14.   }
  15. }
批量
  1. public ActionResult GetDapper(List<CarModel> carModel)
  2. {
  3.  try
  4.   {
  5.     using (IDbConnection conn = DapperService.MySqlConnection())
  6.     {
  7.       int result = conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values(@UserTel,@UserName,@UserPwd)", carModel);
  8.     }
  9.     return Json("success");
  10.   }
  11.   catch(Exception e)
  12.   {
  13.     return Json("failed");
  14.   }
  15. }

4. 删除方法(单体、批量)

单体
  1. public static int Delete(CarModel carModel)
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.    return conn.Execute("delete from UserInfo where id=@ID", carModel);
  6.   }
  7. }
批量
  1. public static int Delete(List<CarModel> carModel)
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.     return conn.Execute("delete from UserInfo where id=@ID", carModel);
  6.   }
  7. }

5. 更新方法(单体、批量)

单体
  1. public static int Update(CarModel carModel)
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.     return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
  6.   }
  7. }
批量
  1. public static int Update(List<CarModel> carModel)
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.     return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
  6.   }
  7. }
启用事务
  1. public static int Update(List<CarModel> carModel)
  2. {
  3.   using (SqlConnection connection = new SqlConnection(ConnString))
  4. {
  5. connection.Open();
  6. //启动事务
  7. SqlTransaction transaction = connection.BeginTransaction();
  8. try
  9. {
  10. connection.Execute("update *** set ***='Y' where ID=@ID AND *** = '"+iCaseId+"'", selectItems, transaction);
  11. //完成提交
  12. transaction.Commit();
  13. }
  14. catch (Exception ex)
  15. {
  16. //数据回滚
  17. transaction.Rollback();
  18. // sm.sendAdmMail("InsertSql报错","Func: InsertSql SQL语句:<br/>" + ASql + "<br/>报错!" + ex.Message + "<br/>");
  19. }
  20. finally
  21. {
  22. connection.Close();
  23. }
  24. }
  25. }

6. 查询

无参查询
  1. public static List<CarModel> Query()
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.     return conn.Query<CarModel>("select * from UserInfo ").ToList();
  6.   }
  7. }
有参查询
  1. public static Person Query(CarModel carModel)
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.     return conn.Query<CarModel>("select * from UserInfo where id=@ID",carModel).SingleOrDefault();
  6.   }
  7. }

7. Dapper的复杂操作

In操作
  1. public static List<CarModel> QueryIn()
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.     var sql = "select * from UserInfo where id in @ids";
  6.     return conn.Query<CarModel>(sql, new { ids = new int[2] { 1, 2 }, }).ToList();
  7.   }
  8. }
  1. public static List<CarModel> QueryIn(int[] ids)
  2. {
  3.   using (IDbConnection conn = DapperService.MySqlConnection())
  4.   {
  5.     var sql = "select * from UserInfo where id in @ids";
  6.     return conn.Query<CarModel>(sql, new { ids }).ToList();
  7.   }
  8. }

8. 多语句操作

  1. public ActionResult QueryMultiple()
  2. {
  3.   try
  4.   {
  5.     using (IDbConnection conn = DapperService.MySqlConnection())
  6.     {
  7.       var sql= "select * from Person; select * from UserInfo";
  8.       var multiReader = conn.QueryMultiple(sql);
  9.       var personList = multiReader.Read<Person>();
  10.       var bookList = multiReader.Read<CarModel>(); multiReader.Dispose();
  11.     }
  12.     return Json("success");
  13.   }
  14.   catch(Exception e)
  15.   {
  16.     return Json("failed");
  17.   }
  18. }