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 + "条操作成功" ); } } |
事物的使用:
先看看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); } }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验