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=密码";
强类型：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
#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
|
动态类型：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
#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
|
多映射：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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);
}
}
|
多返回值：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
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
}
}
}
|
增删改查：（删项目里面基本上不用）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 +
"条操作成功"
);
}
}
|
存储过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
//查询
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); } }
本文转自毒逆天博客园博客，原文链接：http://www.cnblogs.com/dunitian/p/5226265.html，如需转载请自行联系原作者
