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;

        }

    }

}

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; }

    }

}

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;

 //查询单个实体

        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 =  });

                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();

            idList.Add();

            idList.Add();

            idList.Add();

            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 = ;

                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 =  });

            }

        }

        //事务控制

        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 =  }, ts);

                    string sql2 = " INSERT INTO dbo.Course(id, courseName ) VALUES  (N'4', N'sdfsfd') ";

                    conn.Execute(sql2, new { id =  }, ts);

                    ts.Commit();

                }

                catch (Exception ex)

                {

                    ts.Rollback();

                }

                finally

                {

                    conn.Close();

                }

            }

        }