Dapper基础增删查改、事务和存储过程

1.前言

　　Dapper是一个轻量级的orm框架，上手也非常的简单，它可以实体映射，所以先准备实体如下：

    public class Couser
    {
        public int id { get; set; }
        public string courseName { get; set; }
    }
    public partial class Score
    {
        public int id { get; set; }
        public int score { get; set; }
        public int courseId { get; set; }
        public int studentId { get; set; }
        public Student student { get; set; }
        public Couser couser { get; set; }
    }
    public partial class Student
    {
        public int id { get; set; }
        public string name { get; set; }
        public int sex { get; set; }
        public string tel { get; set; }
        public string other { get; set; }
        public Score scoreModel { get; set; }
    }

2.查询

1.QueryFist查询单个实体
　　Dapper会自动匹配class的属性和sql查询出来的字段进行数据的组装，这里的属性可以不是表中存在的而是自己定义添加的。但是不会对class中的类属性里的属性进行赋值，比如Student里面有个public Score scoreModel { get; set; }就不会对Score这个类里面的属性进行赋值。反而会对scoreModel进行赋值，加入sql语句返回了一个int类型的scoreModel字段的数据，但是和scoreModel的类型不匹配无法转换就会报错。

        public Student QueryFirst_1() 
        {
            using (IDbConnection conn = new SqlConnection(sqlconnection))
            {
                return Dapper.SqlMapper.QueryFirstOrDefault<Student>(conn, "select a.*,b.score as other,b.* from Student a left join Score b on a.id = b.studentId where a.id=@id ", new { id = 2 });
                //return Dapper.SqlMapper.QueryFirst<Student>(conn, "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId ");
                //return Dapper.SqlMapper.QueryFirst<Student>(conn, "select * from Student where id=123");
            }
        }

　　其实CommandDefinition传入的效果和直接传递参数效果相同，只是把原先要传递的参数先传到CommandDefinition中

        public Student QueryFist_2()
        {
            using (IDbConnection conn = new SqlConnection(sqlconnection))
            {
                string strsql = "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId where a.id=@id";
                CommandDefinition command = new CommandDefinition(strsql, new { id = 2 });
                return Dapper.SqlMapper.QueryFirstOrDefault<Student>(conn, command);
            }
        }

2.QuerySingle查询单个实体
　　和QueryFist一样是查询单条数据，差别是QueryFist在返回多条数据的情况下会默认获取第一条，QuerySingle返回多条数据的话就会报错

        public Student QuerySingle_1()
        {
            using (IDbConnection conn = new SqlConnection(sqlconnection))
            {
                return Dapper.SqlMapper.QuerySingleOrDefault<Student>(conn, "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId where a.id=@id ", new { id = 2 });
            }
        }

3.Query查询多条数据，单表查询

        public List<Student> Query_1()
        {
            using (IDbConnection conn = new SqlConnection(sqlconnection))
            {
                return Dapper.SqlMapper.Query<Student>(conn, "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId where a.id=@id ", new { id = 2 }).ToList<Student>();
            }
        }

4.Query查询多条数据，多表的映射
　　splitOn默认值是id，它是用来切割将不同的类的属性匹配到相应的类中

        public List<Student> Query_2()
        {
            using (IDbConnection conn = new SqlConnection(sqlconnection))
            {
                return Dapper.SqlMapper.Query<Student,Score,Student>(conn, "select a.*,b.* from Student a left join Score b on a.id = b.studentId where a.id=@id ",
                    (Student, Score) => { Student.scoreModel = Score; return Student; },
                     new { id = 2 }, //参数
                     null, //事务
                     true, //是否缓存
                    "id",
                     null,//超时时间
                     null//执行类型，如存储过程等
                    ).ToList<Student>();
            }
        }

 3.增删改和事务

　　增删改的操作其实是差不多的，所以用一下代码为实例：

        public void Add()
        {
            using (IDbConnection conn = new SqlConnection(sqlconnection))
            {
                string inesrtSql = "insert into Student(name,sex,tel) values (@name,@sex,@tel)";
                Student student_1 = new Student();
                student_1.name = "名字哦";
                student_1.sex = 1;
                student_1.tel = "13323456765";
                Student student_2 = new Student();
                student_2.name = "名字哦1";
                student_2.sex = 1;
                student_2.tel = "13323456765";
                List<Student> list = new List<Student>();
                list.Add(student_1);
                list.Add(student_2);
                conn.Open();//使用事务前必须打开链接
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    if (Dapper.SqlMapper.Execute(conn, inesrtSql, list, tran) > 0)//多条插入，不使用事务的话，执行错误会导致脏数据
                    {
                        tran.Commit();
                        //成功
                    }
                    else
                    {
                        //失败
                    }
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                }
                //if (Dapper.SqlMapper.Execute(conn, inesrtSql, student_1) > 0)//单条插入
                //{
                //    //成功
                //}
                //else 
                //{
                //    //失败
                //}
            }
        }

4.存储过程和函数

　　简单实例如下：

        public void StoredProcedureAndFun()
        {
            using(IDbConnection conn = new SqlConnection(sqlconnection))
            {
                var para = new  DynamicParameters();
                para.Add("@id", 20);
                para.Add("@res", 0, DbType.Int32, ParameterDirection.ReturnValue);//定义返回值
                var res1 = conn.Query("storedProcedure", para, null, true, null, CommandType.StoredProcedure).FirstOrDefault(); //只要using Dapper就可以直接用conn.Query()的形式
                para.Get<int>("@res");//获取返回值
            }
        }