2、Dapper的使用

1、表结构介绍：

1）课程表

2）成绩表

3）学生表 

2、获取数据库连接的工厂类

需要添加System.Configuration和MySql.Data.MySqlClient引用

namespace db
{
    /// <summary>
    /// 数据库连接工厂
    /// </summary>
    public class dbFactory
    {
        public static string connStr = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
        private static string prividerName = ConfigurationManager.ConnectionStrings["dbConn"].ProviderName;

        public static IDbConnection createConn()
        {
            IDbConnection conn = null;
            switch (prividerName)
            {
                case "System.Data.SqlClient":
                    conn = new SqlConnection(connStr);
                    break;
                case "MySql.Data.MySqlClient":
                    conn = new MySqlConnection(connStr);
                    break;
            }
            return conn;
        }
    }
}

3、模型类定义

namespace db.model
{
    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 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; }
    }
}

4、需要引入的包

using Dapper;
using DapperExtensions;
using db.model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;

5、相关使用例子

 //查询单个实体
        public static Couser query()
        {
            using (IDbConnection conn = db.dbFactory.createConn())
            {
                Console.WriteLine(conn.State);
                //原生写法
                string sql = " SELECT * FROM dbo.Course where id=@id ";
                Couser model1 = conn.QueryFirstOrDefault<Couser>(sql, new { id = 1 });
                Console.WriteLine(conn.State);
                return model1;
            }
        }

        //过滤查询like方式1
        public static List<Couser> queryWhere1(string courseName)
        {
            using (IDbConnection conn = db.dbFactory.createConn())
            {
                //原生写法 模糊查询1
                string sql = " SELECT * FROM dbo.Course where charindex(@courseName,courseName)>0 ";
                List<Couser> list = conn.Query<Couser>(sql, new { courseName = courseName }).ToList();
                return list;
            }
        }

        //过滤查询like方式2
        public static List<Couser> queryWhere2(string courseName)
        {
            using (IDbConnection conn = db.dbFactory.createConn())
            {
                //原生写法 模糊查询2
                string sql = " SELECT * FROM dbo.Course where courseName like @courseName ";
                List<int> idList = new List<int>();
                List<Couser> list = conn.Query<Couser>(sql, new { courseName = $"%{courseName}%" }).ToList();
                return list;
            }
        }

        //in 查询
        public static List<Couser> queryWhere3()
        {
            List<int> idList = new List<int>();
            idList.Add(1);
            idList.Add(2);
            idList.Add(3);
            idList.Add(4);

            using (IDbConnection conn = db.dbFactory.createConn())
            {
                //原生写法 模糊查询2
                string sql = " SELECT * FROM dbo.Course where id in @id ";
                List<Couser> list = conn.Query<Couser>(sql, new { id = idList }).ToList();
                return list;
            }
        }

        //查询所有
        public static List<Couser> queryAll()
        {
            using (IDbConnection conn = db.dbFactory.createConn())
            {
                //原生写法
                string sql = " SELECT * FROM dbo.Course ";
                List<Couser> list = conn.Query<Couser>(sql).ToList();
                return list;
            }
        }

        //返回动态类型
        public static List<dynamic> getStudentScore()
        {
            using (IDbConnection conn = db.dbFactory.createConn())
            {
                //原生写法
                string sql = @" SELECT dbo.Student.name,courseName,score FROM dbo.Course
                    LEFT JOIN dbo.Score ON dbo.Course.id = coursedId
                    LEFT JOIN dbo.Student ON studentId = dbo.Student.id; ";
                List<dynamic> list = conn.Query<dynamic>(sql).ToList();
                return list;
            }
        }

        //新增
        public static int insert()
        {
            using (IDbConnection conn = db.dbFactory.createConn())
            {
                db.model.Couser model = new Couser();
                model.courseName = "数据库原理";
                //原生写法
                string sql = " INSERT INTO dbo.Course(courseName ) VALUES (@courseName) ";
                return conn.Execute(sql, model);
            }
        }

        //批量新增
        public static void insertBatch()
        {
            using (IDbConnection conn = db.dbFactory.createConn())
            {
                List<Couser> list = new List<Couser>();
                list.Add(new Couser { courseName = "Batch1" });
                list.Add(new Couser { courseName = "Batch2" });

                Console.WriteLine(conn.State);
                //原生写法
                string sql = " INSERT INTO dbo.Course(courseName ) VALUES (@courseName) ";
                conn.Execute(sql, list);
                Console.WriteLine(conn.State);

            }
        }

        //修改
        public static void update()
        {
            using (IDbConnection conn = dbFactory.createConn())
            {
                db.model.Couser model = new Couser();
                model.id = 5;
                model.courseName = "数据库原理1";
                //原生写法
                string sql = " UPDATE dbo.Course SET courseName=@courseName WHERE id=@id ";
                conn.Execute(sql, model);
            }
        }

        //删除
        public static void delete()
        {
            using (IDbConnection conn = dbFactory.createConn())
            {
                string sql = " DELETE FROM dbo.Course WHERE id=@id ";
                conn.Execute(sql, new { id = 6 });
            }
        }

        //事务控制
        public static void testTran()
        {
            using (IDbConnection conn = dbFactory.createConn())
            {
                conn.Open();
                IDbTransaction ts = conn.BeginTransaction();
                try
                {
                    string sql1 = " DELETE FROM dbo.Course WHERE id=@id ";
                    conn.Execute(sql1, new { id = 5 }, ts);

                    string sql2 = " INSERT INTO dbo.Course(id, courseName ) VALUES  (N'4', N'sdfsfd') ";
                    conn.Execute(sql2, new { id = 6 }, ts);

                    ts.Commit();
                }
                catch (Exception ex)
                {
                    ts.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
        }