ASP.NetCore+Layui 后台管理系统-DAL

数据库连接层：使用的是EFCore，数据库用的是sqlserver，连接地址配置在appsettings.json

1.DBHelper:

  public class DBHelper : DbContext
    {
        
        public virtual DbSet<Users> Users { get; set; }

        public virtual DbSet<LoginMsg> LoginMsg { get; set; }

        /// <summary>
        /// 菜单表
        /// </summary>
        public virtual DbSet<Menus> Menus { get; set; }

        /// <summary>
        /// 角色表
        /// </summary>
        public virtual DbSet<Role> Role { get; set; }

        /// <summary>
        /// 角色菜单表
        /// </summary>
        public virtual DbSet<RoleMenus> RoleMenus { get; set; }

        /// <summary>
        /// 用户角色表
        /// </summary>
        public virtual DbSet<UserRole> UserRole { get; set; }

        /// <summary>
        /// 功能菜单
        /// </summary>
        public virtual DbSet<FunctionMenu> FunctionMenu { get; set; }

        // 在打印sql日志
        //[Obsolete]
        //public static readonly LoggerFactory LoggerFactory = new LoggerFactory(new[] { new DebugLoggerProvider((_, __) => true) });
        private DbConnection _connection;

        public DBHelper(DbConnection connection)
        {
            _connection = connection;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(_connection, providerOptions => providerOptions.CommandTimeout(60))
                          .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
            //    //optionsBuilder.UseLoggerFactory(LoggerFactory);
        }
}

2.EntityFrameworkCoreExtensions ---主要实现执行sql语句

  public static class EntityFrameworkCoreExtensions
    {
        private static void CombineParams(ref DbCommand command, params object[] parameters)
        {
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (!parameter.ParameterName.Contains("@"))
                        parameter.ParameterName = $"@{parameter.ParameterName}";
                    command.Parameters.Add(parameter);
                }
            }
        }
        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)
        {
            DbConnection conn = facade.GetDbConnection();
            dbConn = conn;
            conn.Open();
            DbCommand cmd = conn.CreateCommand();
            if (facade.IsSqlServer())
            {
                cmd.CommandText = sql;
                CombineParams(ref cmd, parameters);
            }
            return cmd;
        }

        public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }

        /// <summary>
        /// 执行查询SQL语句 返回实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="facade"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
        {
            DataTable dt = SqlQuery(facade, sql, parameters);
            return dt.ToEnumerable<T>();
        }

        public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new()
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            T[] ts = new T[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                T t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                ts[i] = t;
                i++;
            }
            return ts;
        }
    }

3.RedisHelper  Redis连接和基础操作

 

 public class RedisHelper
    {
        public static ConnectionMultiplexer redis = ConnectionMultiplexer.Connect(ReadConfig.Configuration["RedisConfig"]);
        public static IDatabase db; // 默认0

        /// <summary>
        /// 连接redis
        /// </summary>
        /// <param name="localDB">指定数据库,默认0</param>
        public RedisHelper(int localDB = 0)
        {
            db = redis.GetDatabase(localDB);
        }

        #region String
        /// <summary>
        /// 添加值
        /// 数据库存在相同key 值会覆盖
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public bool InsertStringSet(string key, string value)
        {
            Encoding coder = Encoding.GetEncoding("GBK");
            byte[] key_bytes = coder.GetBytes(key);
            byte[] data_bytes = coder.GetBytes(value);

            return db.StringSet(key_bytes, data_bytes);

        }

        /// <summary>
        /// 根据Key值查询
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public string GetString(string key)
        {

            Encoding coder = Encoding.GetEncoding("GBK");
            byte[] key_bytes = coder.GetBytes(key);
            byte[] buffer = db.StringGet(key_bytes);
            if (buffer != null)
            {
                string res = coder.GetString(buffer);
                return res;
            }
            return null;
        }

        /// <summary>
        /// 删除key
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool DeleteString(string key)
        {
            return db.KeyDelete(key);
        }
        #endregion

        #region Hash
        /// <summary>
        /// Hash 单条添加
        /// </summary>
        /// <param name="key"></param>
        /// <param name="dictKey"></param>
        /// <param name="dictValue"></param>
        /// <returns></returns>
        public bool InsertHashSet(string key, string dictKey, string dictValue)
        {

            return db.HashSet(key, dictKey, dictValue);
        }

        /// <summary>
        /// Hash 批量添加
        /// </summary>
        /// <param name="key"></param>
        /// <param name="hashEntrys"></param>
        public void InsertHashSetLst(string key, List<HashEntry> hashEntrys)
        {
            db.HashSet(key, hashEntrys.ToArray());
        }

        /// <summary>
        /// 获取指定key的所有field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public HashEntry[] HashGetAll(string key)
        {
            var value = db.HashGetAll(key);
            return value;
        }

        /// <summary>
        /// 获取指定key的指定field
        /// </summary>
        /// <param name="key"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public string GetHashGet(string key, string field)
        {
            var value = db.HashGet(key, field);
            return value.ToString();
        }
        #endregion

        #region  List  待补充
        #endregion

    }

 

 

下一编：ASP.NetCore+Layui 后台管理系统-Comm