Dapper总结（一）---基本CRUD操作

 一、dapper是什么

　　dapper是一款轻量级的ORM(Object Relationship Mapper)，它负责数据库和编程语言之间的映射。SqlConnection,MysqlConnection,OracleConnection都是继承于DBConnection，而DBConnection又是实现了IDBConnection的接口。因为Dapper是对IDBConnection接口进行了方法扩展，比如你看到的SqlMapper.cs，所以dapper支持多数据库。

二、dapper的简单使用

0、准备的测试类

  //用户类
    public class UserInfo
    {
        public int UId { get; set; }//用户Id
        public string UserName { get; set; }//用户名
        public int Age { get; set; }//年龄
        public int RoleId { get; set; }//角色Id

    }
    //角色类
    public class RoleInfo
    {
        public int RId { get; set; }//角色Id
        public string RoleName { get; set; }//角色名
    }

1、execute方法，返回值为int类型，表示受影响行数

 　　　　//插入数据
            //单条数据插入
            int result = conn.Execute("insert into userinfo values(@username,@age,@roleid)", new
            {
                UserName = "user1",
                Age = 20,
                RoleId = 1
            });  // result=1

            //多条数据插入
            List<UserInfo> users=new List<UserInfo>(){
                 new UserInfo(){UserName="user2",Age=22,RoleId=1},
                 new UserInfo(){UserName="user3",Age=23,RoleId=1},
                 new UserInfo(){UserName="user4",Age=24,RoleId=2},
                                 };

            int result2 = conn.Execute("insert into userinfo values(@username,@age,@roleid)", users); // result2=3        

        //修改
            int rusultEdit = conn.Execute("update userinfo set age=25 where username=@username", new { UserName = "user0" });

       //删除
            int resultDel = conn.Execute("delete from userinfo where uid=@uid", new { UId = 6 });

2、Query方法，返回值为IEnumerable类型

  　 //带参数查询 例子：查询用户名为user1,年龄为20的用户
            IEnumerable<UserInfo> query1 = conn.Query<UserInfo>("select * from userinfo where age=@Age and username=@username", new{Age=20,UserName="user1"})
      
     //in查询    例子：查询年龄是20,21，或22的用户
            IEnumerable<UserInfo> query2=conn.Query<UserInfo>("select * from userinfo where age in @ages",new {ages=new int [3]{20,21,22}});
           
     //返回多个结果集   例子：查询用户列表和角色列表       
            var sql = "select * from userinfo; select * from roleinfo";
            var query3 = conn.QueryMultiple(sql);//GridReader类型
            var usesList = query3.Read<UserInfo>();//IEnumberable类型
            var roleList = query3.Read<RoleInfo>();

    //表连接查询  例子：查询用户信息和其对应的角色名
        //1、返回强类型结果 
            var sql2 = @"select u.username,u.age,u.uid ,r.rolename from userinfo as u join roleinfo as r on u.roleid=r.rid";
            var result2 = conn.Query<UserInfo, RoleInfo, UserInfo>(sql2, (user, role) =>
            {
                user.Role = role;
                return user;
            }, splitOn: "RoleName");//splitOn参数表示分割，前边的是第一个对象的属性，后边的是第二个对象的属性

        //2、返回动态类型结果
            var result3 = conn.Query(sql2);
            foreach (var item in result3)
            {
                Console.WriteLine("username:{0},rolename:{1}", item.username, item.rolename);
            }