Dapper.Net实现增删改查

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

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

 

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

 

二、安装Dapper
1. 通过nuget安装Dapper 
 

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

 

三、实现增删改查 
1、insert单个对象
[HttpPost]
public ActionResult Create(FormCollection collection)
{
try
{
// FormCollection只能接收post请求传递的参数
using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
{
int result = connection.Execute("insert into Sys_User_bak values(@Id,@Name,@Phone,@Address,@CreateDate)", new
{
Id = collection["id"],
Name = collection["name"],
Phone = collection["phone"],
Address = collection["address"],
CreateDate = DateTime.Now
});
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
2、insert多个对象
[HttpPost]
public ActionResult CreateSome(FormCollection collection)
{
try
{
using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
{
//使用匿名对象存储数组，Range(0,10)的实际范围是[0,10)
var userList = Enumerable.Range(0, 10).Select(i => new
{
Id = i + Convert.ToInt32(collection["id"]),
Name = i + collection["name"],
Phone = i + collection["phone"],
Address = i + collection["address"],
CreateDate = DateTime.Now
});
int result = connection.Execute("insert into Sys_User_bak values(@Id,@Name,@Phone,@Address,@CreateDate)", userList);
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
3、delete操作（单个对象&多个对象）
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
using(IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
{
connection.Execute("delete from Sys_User_bak where Id=@Id", new { Id = id });
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
4、update操作 （单个对象&多个对象）
[HttpPost]
public ActionResult Edit(int id, FormCollection collection)
{
try
{
using(IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
{
int result = connection.Execute("update Sys_User_bak set Name=@Name,Phone=@Phone,Address=@Address where Id=@Id", new
{
Name=collection["name"],
Phone = collection["phone"],
Address = collection["address"],
Id = collection["id"]
});
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
 5、select单个对象 
public ActionResult DetailsOne(int id, FormCollection collection)
{
using(IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
{
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
{
Id = id,
Name = "%" + collection["name"] + "%"
});
}
return View();
}
6、select多个对象
public ActionResult DetailsSome(int id, FormCollection collection)
{
try
{
#region 查询多个对象
using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
{
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
{
Id = id,
Name = "%" + collection["name"] + "%"
}).AsList();
}
#endregion

return RedirectToAction("Index");
}
catch (Exception ex)
{
return View();
}
}
 四、执行事务
public ActionResult ExecuteTransaction(int id, string name)
{
//[1]事务1
IDbTransaction transaction = null;
try
{
#region 执行select存储过程
using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["NHibernate"].ConnectionString))
{
//[2]事务2
transaction = connection.BeginTransaction();
List<Sys_User_bak> userList = connection.Query<Sys_User_bak>("proc_GetSysUser", new
{
Id = id,
Name = name
}, commandType: CommandType.StoredProcedure).AsList();
//[3]事务3
transaction.Commit();
}
#endregion

return RedirectToAction("Index");
}
catch (Exception ex)
{
//[4]事务4
transaction.Rollback();
return View();
}
}
 
————————————————
版权声明：本文为CSDN博主「changuncle」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xiaouncle/article/details/82962459