Dapper学习笔记
2018-05-29 10:30 STARSIX03 阅读(141) 评论(0) 编辑 收藏 举报连接数据库
var connection = new SqlConnection("Data Source=1.1.1.1;Initial Catalog=Test;User ID=sa;Password=123;MultipleActiveResultSets=True");
多表关联
1 var sql = @"select top 10 mj.MajorID,mj.MajorName,ms.MinScore,sc.SchoolID,sc.SchoolName,sc.Address from Major mj 2 join MajorScore ms on mj.MajorID = ms.MajorID 3 join School sc on ms.SchoolID = sc.SchoolID 4 where sc.SchoolID = @SchoolID and ms.Year = @Year"; 5 6 var sss = connection.Query<Major, MajorScore, School, ResultInfo>(sql, (mj, ms, sc) => 7 { 8 var result = new ResultInfo 9 { 10 MajorID = mj.MajorID, 11 MajorName = mj.MajorName, 12 Score = ms.MinScore, 13 SchoolID = sc.SchoolID, 14 SchoolName = sc.SchoolName, 15 Address = sc.Address 16 }; 17 return result; 18 }, splitOn: "MinScore,SchoolID", param: new { SchoolID = "10226", Year = "2016" });
splitOn:用于标识关联表的首个字段名,多个关联表时用“,”分割。
查询多个表
1 sql = @"select * from CityInfo;select * from Major;"; 2 3 var multiReader = connection.QueryMultiple(sql); 4 5 var cityList = multiReader.Read<CityInfo>(); 6 7 var majorList = multiReader.Read<Major>();
In操作
1 sql = @"select * from CityInfo where ProvinceID in @ProvinceID ;"; 2 3 var info = connection.Query<CityInfo>(sql, new { ProvinceID = new List<string> { "110000", "120000" } });
Insert操作
1 var sql = @"insert into Test values (@Name,@Pwd);"; 2 var insert = connection.Execute(sql, new { Name = "test", Pwd = "123" });
批量插入
1 sql = @"insert into Test values (@Name,@Pwd);"; 2 3 var list = new List<Test> 4 { 5 new Test{Name="1",Pwd="1"}, 6 new Test{Name="2",Pwd="2"}, 7 new Test{Name="3",Pwd="3"}, 8 }; 9 10 var insert = connection.Execute(sql, list);
Update操作
1 sql = @"update Test set Name = @Name where ID = @ID ;"; 2 3 var update = connection.Execute(sql, new { Name = "hello", ID = 2 });