当前位置: 代码迷 >> 综合 >> Dapper.Net实现增删改查
  详细解决方案

Dapper.Net实现增删改查

热度:113   发布时间:2023-09-11 16:51:17.0

Dapper是一款轻量级ORM工具(Github)。如果你在小的项目中,使用Entity Framework、NHibernate 来处理大数据访问及关系映射,未免有点杀鸡用牛刀。你又觉得ORM省时省力,这时Dapper将是你的不二选择。

一、为什么选择Dapper

1、性能优越

在各大网站上,我们大概都会看到这样的一个对比效果图,在超过500次poco serialization的过程中所表现的性能,我们发现dapper是第二名,当然第一名谁也无法超越,越底层的就越快,同时也就越麻烦。就好像谁能超过“01代码”呢???

Dapper.Net实现增删改查

2、支持多数据库

支持多数据库的本质是因为Dapper对IDBConnection接口进行了方法扩展,我们可以在SqlMapper.cs中看出来,SqlConnection、MySqlConnection、OracleConnection都继承自DBConnection,而DBConnection实现了IDBConnection接口,因此Dapper对IDBConnection接口的扩展项对SqlServer、MySql、Oracle均有效。

Dapper.Net实现增删改查

二、安装Dapper

1. 通过nuget安装Dapper 

 Dapper.Net实现增删改查

2、 在github上获取源码

你如果想在开发过程中对Dapper进行调试的话,那就要用到源码了,我们只需把项目完整的下载下来,然后把Dapper文件夹拷贝到我们的项目中即可。

Dapper.Net实现增删改查

三、实现增删改查 

1、insert单个对象


     
  1. [ HttpPost]
  2. public ActionResult Create(FormCollection collection)
  3. {
  4. try
  5. {
  6. // FormCollection只能接收post请求传递的参数
  7. using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "NHibernate"].ConnectionString))
  8. {
  9. int result = connection.Execute( "insert into Sys_User_bak values(@Id,@Name,@Phone,@Address,@CreateDate)", new
  10. {
  11. Id = collection[ "id"],
  12. Name = collection[ "name"],
  13. Phone = collection[ "phone"],
  14. Address = collection[ "address"],
  15. CreateDate = DateTime.Now
  16. });
  17. }
  18. return RedirectToAction( "Index");
  19. }
  20. catch
  21. {
  22. return View();
  23. }
  24. }

2、insert多个对象


     
  1. [ HttpPost]
  2. public ActionResult CreateSome(FormCollection collection)
  3. {
  4. try
  5. {
  6. using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "NHibernate"].ConnectionString))
  7. {
  8. //使用匿名对象存储数组,Range(0,10)的实际范围是[0,10)
  9. var userList = Enumerable.Range( 0, 10).Select(i => new
  10. {
  11. Id = i + Convert.ToInt32(collection[ "id"]),
  12. Name = i + collection[ "name"],
  13. Phone = i + collection[ "phone"],
  14. Address = i + collection[ "address"],
  15. CreateDate = DateTime.Now
  16. });
  17. int result = connection.Execute( "insert into Sys_User_bak values(@Id,@Name,@Phone,@Address,@CreateDate)", userList);
  18. }
  19. return RedirectToAction( "Index");
  20. }
  21. catch
  22. {
  23. return View();
  24. }
  25. }

3、delete操作(单个对象&多个对象)


     
  1. [ HttpPost]
  2. public ActionResult Delete(int id, FormCollection collection)
  3. {
  4. try
  5. {
  6. using(IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "NHibernate"].ConnectionString))
  7. {
  8. connection.Execute( "delete from Sys_User_bak where Id=@Id", new { Id = id });
  9. }
  10. return RedirectToAction( "Index");
  11. }
  12. catch
  13. {
  14. return View();
  15. }
  16. }

4、update操作 (单个对象&多个对象)


     
  1. [ HttpPost]
  2. public ActionResult Edit(int id, FormCollection collection)
  3. {
  4. try
  5. {
  6. using(IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "NHibernate"].ConnectionString))
  7. {
  8. int result = connection.Execute( "update Sys_User_bak set Name=@Name,Phone=@Phone,Address=@Address where Id=@Id", new
  9. {
  10. Name=collection[ "name"],
  11. Phone = collection[ "phone"],
  12. Address = collection[ "address"],
  13. Id = collection[ "id"]
  14. });
  15. }
  16. return RedirectToAction( "Index");
  17. }
  18. catch
  19. {
  20. return View();
  21. }
  22. }

 5、select单个对象 


     
  1. public ActionResult DetailsOne(int id, FormCollection collection)
  2. {
  3. using(IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "NHibernate"].ConnectionString))
  4. {
  5. Sys_User_bak user = connection.QueryFirst<Sys_User_bak>( "select Id,Name,Phone,Address from Sys_User_bak where Id>@Id and Name like @Name", new
  6. {
  7. Id = id,
  8. Name = "%" + collection[ "name"] + "%"
  9. });
  10. }
  11. return View();
  12. }

6、select多个对象


     
  1. public ActionResult DetailsSome(int id, FormCollection collection)
  2. {
  3. try
  4. {
  5. #region 查询多个对象
  6. using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "NHibernate"].ConnectionString))
  7. {
  8. List<Sys_User_bak> userList = connection.Query<Sys_User_bak>( "select Id,Name,Phone,Address from Sys_User_bak where Id>@Id and Name like @Name", new
  9. {
  10. Id = id,
  11. Name = "%" + collection[ "name"] + "%"
  12. }).AsList();
  13. }
  14. #endregion
  15. return RedirectToAction( "Index");
  16. }
  17. catch (Exception ex)
  18. {
  19. return View();
  20. }
  21. }

 四、执行事务


     
  1. public ActionResult ExecuteTransaction(int id, string name)
  2. {
  3. //[1]事务1
  4. IDbTransaction transaction = null;
  5. try
  6. {
  7. #region 执行select存储过程
  8. using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "NHibernate"].ConnectionString))
  9. {
  10. //[2]事务2
  11. transaction = connection.BeginTransaction();
  12. List<Sys_User_bak> userList = connection.Query<Sys_User_bak>( "proc_GetSysUser", new
  13. {
  14. Id = id,
  15. Name = name
  16. }, commandType: CommandType.StoredProcedure).AsList();
  17. //[3]事务3
  18. transaction.Commit();
  19. }
  20. #endregion
  21. return RedirectToAction( "Index");
  22. }
  23. catch (Exception ex)
  24. {
  25. //[4]事务4
  26. transaction.Rollback();
  27. return View();
  28. }
  29. }