Dapper学习笔记

连接数据库

var connection = new SqlConnection("Data Source=1.1.1.1;Initial Catalog=Test;User ID=sa;Password=123;MultipleActiveResultSets=True");

多表关联

　　　　　　 var sql = @"select top 10 mj.MajorID,mj.MajorName,ms.MinScore,sc.SchoolID,sc.SchoolName,sc.Address from Major mj 
                        join MajorScore ms on mj.MajorID = ms.MajorID 
                        join School sc on ms.SchoolID = sc.SchoolID
                        where sc.SchoolID = @SchoolID and  ms.Year = @Year";

            var sss = connection.Query<Major, MajorScore, School, ResultInfo>(sql, (mj, ms, sc) =>
             {
                 var result = new ResultInfo
                 {
                     MajorID = mj.MajorID,
                     MajorName = mj.MajorName,
                     Score = ms.MinScore,
                     SchoolID = sc.SchoolID,
                     SchoolName = sc.SchoolName,
                     Address = sc.Address
                 };
                 return result;
             }, splitOn: "MinScore,SchoolID", param: new { SchoolID = "10226", Year = "2016" });

splitOn：用于标识关联表的首个字段名，多个关联表时用“，”分割。

查询多个表

　　　　　　　sql = @"select * from CityInfo;select * from Major;";

            var multiReader = connection.QueryMultiple(sql);

            var cityList = multiReader.Read<CityInfo>();

            var majorList = multiReader.Read<Major>();

 

In操作

            sql = @"select * from CityInfo where ProvinceID in @ProvinceID ;";

            var info = connection.Query<CityInfo>(sql, new { ProvinceID = new List<string> { "110000", "120000" } });

Insert操作

            var sql = @"insert into Test values (@Name,@Pwd);";
            var insert = connection.Execute(sql, new { Name = "test", Pwd = "123" });

批量插入

            sql = @"insert into Test values (@Name,@Pwd);";

            var list = new List<Test>
            {
                new Test{Name="1",Pwd="1"},
                new Test{Name="2",Pwd="2"},
                new Test{Name="3",Pwd="3"},
            };

            var insert = connection.Execute(sql, list);

Update操作

            sql = @"update Test set Name = @Name where ID = @ID ;";

            var update = connection.Execute(sql, new { Name = "hello", ID = 2 });