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=密码";
强类型：
|
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 + "条操作成功"); }} |
作者：毒逆天