UserCenter/Code/UC/Data.Dapper/MySqlDatabase.cs

using Common;
using Common.LambdaToSQL;
using Common.Models;
using Dapper;
using Data.Interfaces;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;

namespace Data.Dapper
{
    public class MySqlDatabase : IDatabase

    {
        private readonly string _connString;
        protected IDbTransaction _trans = null;
        public MySqlDatabase(string connString = "")
        {
            _connString = connString.IsEmpty() ? ConfigHelper.GetConnectionString("mysql") : connString;
        }

        #region 属性
        /// <summary>
        /// 获取 数据库连接串
        /// </summary>
        private IDbConnection Connection
        {
            get
            {
                var dbconnection = new MySqlConnection(_connString);
                if (dbconnection.State == ConnectionState.Closed)
                {
                    dbconnection.Open();
                }
                return dbconnection;
            }

        }
        /// <summary>
        /// 确认开启事务
        /// </summary>
        private void EnsureTransaction()
        {
            if (_trans == null)
            {
                var dbconnection = new MySqlConnection(_connString);
                if (dbconnection.State == ConnectionState.Closed)
                {
                    dbconnection.Open();
                }
                _trans = dbconnection.BeginTransaction();
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (_trans != null)
            {
                _trans.Rollback();
                if (_trans.Connection != null)
                {
                    _trans.Connection.Dispose();
                }
                _trans.Dispose();
                _trans = null;
            }
        } /// <summary>
          /// 提交事务
          /// </summary>
        public void Commit()
        {
            if (_trans != null)
            {
                _trans.Commit();
                if (_trans.Connection != null)
                {
                    _trans.Connection.Dispose();
                }
                _trans.Dispose();
                _trans = null;
            }
        }
        #endregion 属性

        #region 查询
        /// <summary>
        /// 集合查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public IEnumerable<T> FindList<T>(string sql, object para = null) where T : class, new()
        {
            using (var db = Connection)
            {
                try
                {
                    return db.Query<T>(sql, para).ToList();
                }
                catch (Exception ex)
                {

                    throw;
                }
            }
        }

        /// <summary>
        /// 分页集合查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public PaginationDTO<IEnumerable<T>> FindPageList<T>(string sql, PaginationQuery pagination, object para = null) where T : class, new()
        {
            using (var db = Connection)
            {
                var orderBy = "";
                if (!string.IsNullOrEmpty(pagination.Sidx))
                {
                    if (pagination.Sord.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + pagination.Sord.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
                    {
                        orderBy = "Order By " + pagination.Sord;
                    }
                    else
                    {
                        orderBy = "Order By " + pagination.Sidx + " " + pagination.Sord.ToUpper();
                    }
                }
                else
                {
                    orderBy = "Order By (Select 0)";
                }
                var sqls = $@"{sql} {orderBy} limit { (pagination.Page - 1) * pagination.Rows},{ pagination.Rows}";
                string selectCountSql = "Select Count(*) From (" + sql + ") AS t";
                try
                {
                    return new PaginationDTO<IEnumerable<T>>
                    {
                        Data = db.Query<T>(sqls, para).ToList(),
                        Total = Convert.ToInt32(db.ExecuteScalar(selectCountSql, para)),
                        Rows = pagination.Rows
                    };

                }
                catch (Exception e)
                {
                    return new PaginationDTO<IEnumerable<T>>
                    {
                        Data = null,
                        Total = 0,
                        Rows = pagination.Rows
                    };
                }

            }
        }

        /// <summary>
        /// 分页查询-Lambda
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public PaginationDTO<IEnumerable<T>> FindPageList<T>(Expression<Func<T, bool>> condition, PaginationQuery pagination) where T : class, new()
        {
            var lambda = new LambdaExpConditions<T>();
            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon<T>.SelectSql(where).ToString();
            return FindPageList<T>(sql, pagination);
        }

        /// <summary>
        /// 集合查询（Lambda）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IEnumerable<T> FindList<T>(Expression<Func<T, bool>> condition) where T : class, new()
        {

            var lambda = new LambdaExpConditions<T>();
            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon<T>.SelectSql(where).ToString();
            return this.FindList<T>(sql);

        }

        /// <summary>
        /// 查询实体对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public T FindEntity<T>(string sql, object para = null)
        {
            using (var db = Connection)
            {
                return db.QueryFirstOrDefault<T>(sql, para);
            }
        }
        /// <summary>
        /// 集合查询（Lambda）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public T FindEntity<T>(Expression<Func<T, bool>> condition) where T : class, new()
        {
            var lambda = new LambdaExpConditions<T>();
            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon<T>.SelectSql(where).ToString();
            return FindEntity<T>(sql);
        }

        /// <summary>
        /// 数据条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int Count<T>(Expression<Func<T, bool>> condition) where T : class, new()
        {
            var lambda = new LambdaExpConditions<T>();
            lambda.AddAndWhere(condition);
            string where = lambda.Where();
            string sql = DatabaseCommon<T>.SelectCountSql(where).ToString();
            using (var db = Connection)
            {
                return Convert.ToInt32(db.ExecuteScalar(sql));
            }
        }

        /// <summary>
        /// 数据条数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public int Count(string sql, object para)
        {
            using (var db = Connection)
            {
                return Convert.ToInt32(db.ExecuteScalar(sql, para));
            }
        }
        #endregion

        #region 编辑
        /// <summary>
        /// 新增sql
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="para">参数化</param>
        public int Insert(string sql, object para = null)
        {

            using (var db = Connection)
            {
                return db.Execute(sql, para);
            }
        }

        /// <summary>
        /// 实体新增
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="para">参数化</param>
        public int Insert<T>(T model) where T : class, new()
        {

            using (var db = Connection)
            {
                string sql = DatabaseCommon<T>.InsertSql().ToString();
                return db.ExecuteScalar<int>(sql, model);
            }
        }
        /// <summary>
        /// 事务实体新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public int TranInsert<T>(T model) where T : class, new()
        {
            using (var db = Connection)
            {
                EnsureTransaction();
                string sql = DatabaseCommon<T>.InsertSql().ToString();
                return db.Execute(sql, model, _trans);

            }
        }

        /// <summary>
        /// 批量实体新增
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="para">参数化</param>
        public int Insert<T>(List<T> list) where T : class, new()
        {

            try
            {
                using (var db = Connection)
                {
                    string sql = DatabaseCommon<T>.InsertSql().ToString();
                    return db.Execute(sql, list);
                }
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        /// 修改sql
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="para">参数化</param>
        public int Update(string sql, object para = null)
        {

            using (var db = Connection)
            {
                return db.Execute(sql, para);
            }
        }

        /// <summary>
        /// 修改lammbda
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Update<T>(T t) where T : class, new()
        {
            using (var db = Connection)
            {
                string sql = DatabaseCommon<T>.UpdateSql(t).ToString();
                if (sql.IsEmpty())
                    return 0;
                return db.Execute(sql, t);
            }
        }
        /// <summary>
        /// 事务修改lammbda
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int TranUpdate<T>(T t) where T : class, new()
        {
            using (var db = Connection)
            {
                EnsureTransaction();
                string sql = DatabaseCommon<T>.UpdateSql(t).ToString();
                if (sql.IsEmpty())
                    return 0;
                return db.Execute(sql, t, _trans);
            }
        }
        #endregion

        #region 删除
        public int Delete(string sql, object para = null)
        {
            using (var db = Connection)
            {
                return db.Execute(sql, para);
            }
        }

        /// <summary>
        /// 泛型删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Delete<T>(T t) where T : class, new()
        {
            using (var db = Connection)
            {
                string sql = DatabaseCommon<T>.DeleteSql(t).ToString();
                return db.Execute(sql, t);
            }
        }
        /// <summary>
        /// 泛型删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Delete<T>(int id) where T : class, new()
        {
            using (var db = Connection)
            {
                string sql = DatabaseCommon<T>.DeleteSqlById(id).ToString();
                return db.Execute(sql);
            }
        }
        #endregion
    }
}