Dapper逆天入门~强类型，动态类型，多映射，多返回值，增删改查+存储过程+事物案例演示
Dapper的牛逼就不扯蛋了，答应群友做个入门Demo的，现有园友需要，那么公开分享一下：
完整Demo：http://pan.baidu.com/s/1i3TcEzj
注 意 事 项：http://www.cnblogs.com/dunitian/p/5221058.html
平台之大势何人能挡？ 带着你的Net飞奔吧！
先安装一下Dapper（建议用nuget包来管理）
连接字符串：
string connStr = "Data Source=.;Initial Catalog=DapperDB;User ID=用户名;Password=密码";
强类型：
#region 强类型
//public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
//无参查询
//var qqModeList = conn.Query<QQModel>("select Id,Name,Count from QQModel");
//带参查询
var qqModeList = conn.Query<QQModel>("select Id,Name,Count from QQModel where Id in @id and Count>@count", new { id = new int[] { 1, 2, 3, 4, 5, 6 }, count = 1 });
foreach (var item in qqModeList)
{
Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
}
}
#endregion
动态类型：
#region 动态类型
////逆天动态类型用的比较多[可能是MVC ViewBag用多了]
//public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
var qqModeList = conn.Query("select Id,Name,Count from QQModel").ToList();
foreach (var item in qqModeList)
{
Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
}
}
#endregion
多映射：
using (SqlConnection conn = new SqlConnection(connStr))
{
string sqlStr = @"select A.Id,A.Title,S.SeoKeywords from Article A
inner join SeoTKD S on A.SeoId=S.Id
where A.Id in @ids";
conn.Open();
var articleList = conn.Query(sqlStr, new { ids = new int[] { 41, 42, 43, 44, 45, 46, 47, 48 } });
foreach (var item in articleList)
{
Console.WriteLine(item.Id + " | " + item.SeoKeywords + " | ：" + item.Title);
}
}
多返回值：
using (SqlConnection conn = new SqlConnection(connStr))
{
string sqlStr = @"select Id,Title,Author from Article where Id = @id
select * from QQModel where Name = @name
select * from SeoTKD where Status = @status";
conn.Open();
using (var multi = conn.QueryMultiple(sqlStr, new { id = 11, name = "打代码", status = 99 }))
{
//multi.IsConsumed reader的状态 ，true 是已经释放
if (!multi.IsConsumed)
{
//注意一个东西，Read获取的时候必须是按照上面返回表的顺序 （article，qqmodel，seotkd）
//强类型
var articleList = multi.Read<Temp>();//类不见得一定得和表名相同
var QQModelList = multi.Read<QQModel>();
var SeoTKDList = multi.Read<SeoTKD>();
//动态类型
//var articleList = multi.Read();
//var QQModelList = multi.Read();
//var SeoTKDList = multi.Read();
#region 输出
foreach (var item in QQModelList)
{
Console.WriteLine(item.Id + " " + item.Name + " " + item.Count);
}
foreach (var item in SeoTKDList)
{
Console.WriteLine(item.Id + " | " + item.SeoKeywords);
}
foreach (var item in articleList)
{
Console.WriteLine(item.Author);
}
#endregion
}
}
}
增删改查：（删项目里面基本上不用）
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
//增
int count = conn.Execute("insert into Article values(@title,@content,@author,961,1,2,2,N'2015-11-23 11:06:36.553',N'2015-11-23 11:06:36.553',N'5,103,113',91,N'3,5,11',0,N'/Images/article/16.jpg')", new { title = "Title1", content = "TContent1", author = "毒逆天" });
//改
//int count = conn.Execute("update Article set Title=@title where Id=@id", new { title = "么么哒", id = 274 });
if (count > 0)
{
Console.WriteLine(count + "条操作成功");
}
}
存储过程：
//查询
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
//参数名得和存储过程的变量名相同（参数可以跳跃传，键值对方式即可）
//动态类型
//var list = conn.Query("usp_test", new { aId = 11 }, commandType: CommandType.StoredProcedure);
//强类型
var list = conn.Query<TitleAndKeyWords>("usp_test", new { aId = 11 }, commandType: CommandType.StoredProcedure);
foreach (var item in list)
{
Console.WriteLine(item.Id + " | " + item.SeoKeywords + " | ：" + item.Title);
}
}
//插入
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
int count = conn.Execute("usp_insertArticle", new { title = "Title11", content = "TContent1", author = "毒逆天" }, commandType: CommandType.StoredProcedure);
if (count > 0)
{
Console.WriteLine(count + "条操作成功");
}
}
//更新
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
int count = conn.Execute("usp_updateArticle", new { id = 276, title = "Dapper使用" }, commandType: CommandType.StoredProcedure);
if (count > 0)
{
Console.WriteLine(count + "条操作成功");
}
}
事物的使用：
先看看ADO.Net怎么用的
using (SqlConnection conn = new SqlConnection(connStr)) { string sql = @"insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 999999999999999999999999) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) "; using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); cmd.Transaction = conn.BeginTransaction(); try { int i = cmd.ExecuteNonQuery();
cmd.Transaction.Commit(); Console.WriteLine(i); } catch (Exception ex) { cmd.Transaction.Rollback(); Console.WriteLine(ex); } } }
再看看Dapper的
using (SqlConnection conn = new SqlConnection(connStr)) { string sql = @"insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 9999999999999999999) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) insert into SeoTKD values(N'毒逆天，测试', N'测试测试测试', 1) "; conn.Open(); var transaction = conn.BeginTransaction(); try { int i = conn.Execute(sql, transaction: transaction); transaction.Commit(); Console.WriteLine(i); } catch (Exception ex) { transaction.Rollback(); Console.WriteLine(ex); } }
