dotnetcore 3.0+Dapper+MySQL

1、vs2019 创建core 3.0 控制台应用；

2、Nuget程序包管理控制台依次安装mysql.data和dapper

Install-Package mysql.data

Install-Package Dapper

3、建立DBHelper.cs,方便不同数据库连接

  public class DBHelper

    {

        public static string providerName;

        public static string connectionString;

        public static IDbConnection OpenConnection()

        {

            return new MySqlConnection(connectionString);

        }  

    }

4、创建封装的dapper泛型数据库访问层

 public class Data<T> where T : class

    {

        #region 构造函数

        private static Data<T> _instance;

        public static Data<T> GetInstance()

        {

            if (_instance == null)

            {

                _instance = new Data<T>(); 

            }

            return _instance;

        }

        #endregion

        private IDbConnection context;  

        /// <summary>

        /// 添加

        /// </summary>

        /// <param name="query"></param>

        /// <param name="model"></param>

        /// <param name="context"></param>

        /// <param name="transaction"></param>

        /// <returns></returns>

        public int Add(string query, T model , IDbTransaction transaction = null)

        {

            if (context == null) context = DBHelper.OpenConnection();

            //  query += " ; select last_insert_rowid() newid";

            //  string id = context.ExecuteScalar(query, model, transaction).ToString();

            //  return ZConvert.StrToInt(id, 0);

            int id = context.Execute(query, model, transaction);

            return id;

        }

        /// <summary>

        /// 修改

        /// </summary>

        /// <param name="query"></param>

        /// <param name="model"></param>

        /// <param name="context"></param>

        /// <param name="transaction"></param>

        /// <returns></returns>

        public int Update(string query, T model , IDbTransaction transaction = null)

        {

            if (context == null) context = DBHelper.OpenConnection();

            int id = context.Execute(query, model, transaction);

            return id;

        }

        /// <summary>

        /// 删除

        /// </summary>

        /// <param name="tablename"></param>

        /// <param name="wheresql"></param>

        /// <param name="model"></param>

        /// <param name="context"></param>

        /// <param name="transaction"></param>

        /// <returns></returns>

        public int Delete(string query, T model , IDbTransaction transaction = null)

        {

            if (context == null) context = DBHelper.OpenConnection(); 

            int id = context.Execute(query, model, transaction);

            return id;

        }

        /// <summary>

        /// 获取单个实体

        /// </summary>

        /// <param name="tablename"></param>

        /// <param name="wheresql"></param>

        /// <param name="model"></param>

        /// <param name="context"></param>

        /// <param name="transaction"></param>

        /// <returns></returns>

        public T GetModel(string query, T model,  IDbTransaction transaction = null)

        {

            if (context == null) context = DBHelper.OpenConnection(); 

            T obj = context.Query<T>(query, model, transaction).SingleOrDefault();

            return obj;

        }

        /// <summary>

        /// 实体列表

        /// </summary>

        /// <param name="query"></param>

        /// <param name="model"></param>

        /// <param name="context"></param>

        /// <param name="transaction"></param>

        /// <returns></returns>

        public List<T> GetModelList(string query, T model , IDbTransaction transaction = null)

        {

            if (context == null) context = DBHelper.OpenConnection();

            List<T> list = context.Query<T>(query, model, transaction).ToList();

            return list;

        }

    }

5、调用示例

 static void Main(string[] args)

        {

            Console.WriteLine("Hello World!");

            DBHelper.connectionString = "server=localhost;port=3306;user=root;password=123456; database=taskms;";

            Data<customer> db = Data<customer>.GetInstance();

            List<customer> customers = db.GetModelList("select * from customer", new customer());

            Console.ReadKey(true);

        }