hjha-server/GameDAL/db/Sql/GameDB.cs

/*
 * 由SharpDevelop创建。
 * 用户： Administrator
 * 日期: 2018-05-05
 * 时间: 13:23
 * 
 * 要改变这种模板请点击 工具|选项|代码编写|编辑标准头文件
 */
using System;
using System.Data;
using System.Data.SqlClient;
using MrWu.DB;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MrWu.Debug;
using ObjectModel.Game;

namespace Server.DB.Sql {

    /// <summary>
    /// 游戏数据库访问
    /// </summary>
    public class GameDB {
        static GameDB() {
            m_Instance = new GameDB();
        }

        private GameDB() { }

        private static GameDB m_Instance;

        /// <summary>
        /// 数据库实例
        /// </summary>
        public static GameDB Instance {
            get {
                return m_Instance;
            }
        }
        
        private ISqlEx<SqlConnection, SqlCommand, SqlDbType,SqlParameter> mmsql;

        private string GetDataBaseName(dbbase db) {
            switch (db) {
                case dbbase.gamedb:
                    return "jnxdgame";
                case dbbase.game2018:
                    return "game2018";
            }
            return null;
        }

        /// <summary>
        /// 初始化
        /// </summary>
        public void Init(MrWu.DB.SqlConfig config) {
            if (mmsql == null) 
                mmsql = new MMSSQL(config);
        }
        
        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="sty">数据库类型</param>
        /// <param name="db">数据库</param>
        /// <param name="table">查那个表</param>
        /// <param name="column">需要查询的列</param>
        /// <param name="whereStr">条件</param>
        /// <returns></returns>
        public DataTable selectDb(dbbase db, string table, string[] column = null, string whereStr = null, int count = -1, params SqlParameter[] sqlparams) {
            return mmsql.Select(GetDataBaseName(db), table, column, whereStr, count, -1, sqlparams.Length > 0 ? new List<SqlParameter>(sqlparams) : null);
        }

        /// <summary>
        /// 开始设置存储过程参数
        /// </summary>
        public IProdureParameter<SqlCommand> BeginSetProcedureParameter(dbbase db, string procedureName) {
            return mmsql.BeginProcedure(GetDataBaseName(db), procedureName);
        }

        /// <summary>
        /// 添加存储过程参数
        /// </summary>
        public void AddProdureParameters(IProdureParameter<SqlCommand> spp, string parameName, SqlDbType type, object value, ParameterDirection pd = ParameterDirection.Input) {

            SqlParameter sp = spp.command.Parameters.Add(parameName, type);
            sp.Direction = pd;
            if (value == null)
                sp.Value = DBNull.Value;
            else
                sp.Value = value;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="db">数据库</param>
        /// <param name="sql">sql语句</param>
        /// <param name="ds">获得的数据</param>
        public int ExceSql(dbbase db, string sql, IEnumerable<SqlParameter> sqlparams = null, DataSet ds = null) {
            return mmsql.ExecuteSql(GetDataBaseName(db), sql, sqlparams, ds);
        }

        /// <summary>
        /// 结束执行存储过程
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, object> EndAddProdureParameters(IProdureParameter<SqlCommand> spp, DataSet ds = null) {
            return mmsql.SubProcedure(spp, ds);
        }

        /// <summary>
        /// 开启一个事务
        /// </summary>
        /// <param name="dbbase"></param>
        /// <returns></returns>
        public IProdureParameter<SqlCommand> BeginTransaction(dbbase db) {
            return mmsql.BeginTransaction(GetDataBaseName(db));
        }

        /// <summary>
        /// 事务添加sql语句
        /// </summary>
        /// <param name="spp"></param>
        /// <param name="sql"></param>
        public int TransactionAddSql(IProdureParameter<SqlCommand> spp, string sql, List<ISqlAloneParameter<SqlDbType>> pms = null) {
            return mmsql.TransactionAddSql(spp, sql, pms);
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="spp"></param>
        /// <returns>true 表示提交成功 false 表示提交失败</returns>
        public bool SubTransaction(IProdureParameter<SqlCommand> spp) {
            return mmsql.SubTransaction(spp);
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        /// <param name="spp"></param>
        public void Rollback(IProdureParameter<SqlCommand> spp) {
            mmsql.RollbackTransaction(spp);
        }

        #region 异步存储过程与事务操作

        /// <summary>
        /// 异步结束执行存储过程
        /// </summary>
        public async Task<Dictionary<string, object>> EndAddProdureParametersAsync(IProdureParameter<SqlCommand> spp, DataSet ds = null) {
            return await mmsql.SubProcedureAsync(spp, ds);
        }

        /// <summary>
        /// 异步执行sql语句
        /// </summary>
        public async Task<int> ExceSqlAsync(dbbase db, string sql, IEnumerable<SqlParameter> sqlparams = null, DataSet ds = null) {
            return await mmsql.ExecuteSqlAsync(GetDataBaseName(db), sql, sqlparams, ds);
        }

        /// <summary>
        /// 异步查询数据库
        /// </summary>
        public async Task<DataTable> selectDbAsync(dbbase db, string table, string[] column = null, string whereStr = null, int count = -1, int againCount = -1, IEnumerable<SqlParameter> sqlparams = null) {
            return await mmsql.SelectAsync(GetDataBaseName(db), table, column, whereStr, count, againCount, sqlparams);
        }

        #endregion

        #region Dapper 操作

        public SqlConnection GetSql(dbbase db)
        {
            return mmsql.GetConnection(GetDataBaseName(db));
        }

        public int DapperExecute(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return conn.Execute(sqlText, param);
            }
        }
        
        public async Task<int> DapperExecuteAsync(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return await conn.ExecuteAsync(sqlText, param);
            }
        }
    
        public async Task<bool> DapperExecuteTransactionAsync(dbbase db, Func<SqlConnection, SqlTransaction, Task<bool>> doAction)
        {
            if (doAction == null) return false;
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                await conn.OpenAsync();
                using (var transaction = conn.BeginTransaction())
                {
                    try
                    {
                        var success = await doAction.Invoke(conn, transaction);
                        if (success)
                            transaction.Commit();
                        else
                            transaction.Rollback();
                        
                        return success;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public void DapperExecuteProcedure(dbbase db, string procedureName, DynamicParameters param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                conn.Execute(procedureName, param, commandType: CommandType.StoredProcedure);
            }
        }
        
        public async Task DapperExecuteProcedureAsync(dbbase db, string procedureName, DynamicParameters param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                await conn.ExecuteAsync(procedureName, param, commandType: CommandType.StoredProcedure);
            }
        }
        
        public IEnumerable<T> DapperQuery<T>(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return conn.Query<T>(sqlText, param);
            }
        }
        
        public async Task<IEnumerable<T>> DapperQueryAsync<T>(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return await conn.QueryAsync<T>(sqlText, param);
            }
        }
    
        public T DapperQueryFirstOrDefault<T>(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return conn.QueryFirstOrDefault<T>(sqlText, param);
            }
        }
        
        public async Task<T> DapperQueryFirstOrDefaultAsync<T>(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return await conn.QueryFirstOrDefaultAsync<T>(sqlText, param);
            }
        }
        
        public T DapperExecuteScalar<T>(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return conn.ExecuteScalar<T>(sqlText, param);
            }
        }
        
        public async Task<T> DapperExecuteScalarAsync<T>(dbbase db, string sqlText, object param)
        {
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                return await conn.ExecuteScalarAsync<T>(sqlText, param);
            }
        }
        
        public async Task<T> DapperQueryMultipleAsync<T>(dbbase db, string sqlText, object param, Func<SqlMapper.GridReader, Task<T>> getResults)
        {
            if (getResults == null) return default(T);
            using (var conn = mmsql.GetConnection(GetDataBaseName(db)))
            {
                using var multi = await conn.QueryMultipleAsync(sqlText, param);
                return await getResults.Invoke(multi);
            }
        }
        
        public async Task<PageDBEntity<T>> DapperQueryPageAsync<T>(dbbase db, string sqlText, int pageIndex, int pageSize)
        {
            int skip = pageSize * (pageIndex - 1);
            return await DapperQueryMultipleAsync(dbbase.gamedb, sqlText, 
                new { StartIndex = skip, EndIndex = skip + pageSize }, 
                async reader =>
                {
                    PageDBEntity<T> page = new PageDBEntity<T>();
                    page.List = (await reader.ReadAsync<T>()).ToList();
                    page.Total = await reader.ReadSingleAsync<int>();
                    return page;
                });
        }

        public async Task<PageDBEntity<T>> DapperQueryPageAsync<T>(dbbase db, string sql, object param)
        {
            try
            {
                return await DapperQueryMultipleAsync(db, sql, 
                    param, 
                    async reader =>
                    {
                        PageDBEntity<T> page = new PageDBEntity<T>();
                        page.List = (await reader.ReadAsync<T>()).ToList();
                        page.Total = await reader.ReadSingleAsync<int>();
                        return page;
                    });
            }catch (Exception ex)
            {
                Debug.Error($"[DapperQueryPageAsync]执行失败Sql={sql}");
                throw ex;
            }
        }
        #endregion
    }
}