以一个权限系统来告别WebForm —（一）项目整休架构设计与数据库设计

 在本节我想与大家与分享一下，我所将要做的权限系统的架构和数据库的表的设计。请各位大神们对我项目中设计的不足之处进行指导，让我得以更好的写完它，留给需要它的人。

我的项目架构如下图所示：

如上图所示，在数据访问层，我采用了抽象工厂的方式，来对数据访问层和业务逻辑层解耦，当然如果你想更高大上一些，可以用第三方的框架，比如Spring.Net ，Autofac来实现。解耦的好处在于可以方便的切换数据库，当数据库变更时，只需换一下对应的数据访问DAL就行，本系列中，我所采用的是SQLServer。写到这我想在如下这个大数据时代，MongoDB其实也是不错的。后面有机会，可以开一个MongoDB的专题和大家一起来使用一下MongoDB学习一下它。对于抽象工厂来实现业务逻辑层与数据访问层的解耦实现代码如下，主要用到了反射，面向接口编程。

配置：

<appSettings>

  <add key="DAL" value="MCFramework.SQLDAL"/>
  
  <!--页容量-->
  <add key="PageSize" value="20"/>

</appSettings>

抽象工厂：

namespace MCFramework.RepositoryFactory
{
    public class RepositoryFactory
    {
        public RepositoryFactory()
        { }

        private static readonly string AssemblyPath =ConfigurationSettings.AppSettings["DAL"];

        #region CreateObject

        //不使用缓存
        private static object CreateObjectNoCache(string AssemblyPath, string classNamespace)
        {
            try
            {
                object objType = Assembly.Load(AssemblyPath).CreateInstance(classNamespace);
                return objType;
            }
            catch(System.Exception ex)
            {
                string str=ex.StackTrace;// 记录错误日志
                return null;
            }

        }
        //使用缓存
        //private static object CreateObject(string AssemblyPath, string classNamespace)
        //{
        //    object objType = DataCache.GetCache(classNamespace);
        //    if (objType == null)
        //    {
        //        try
        //        {
        //            objType = Assembly.Load(AssemblyPath).CreateInstance(classNamespace);
        //            DataCache.SetCache(classNamespace, objType);// 写入缓存
        //        }
        //        catch//(System.Exception ex)
        //        {
        //            //string str=ex.Message;// 记录错误日志
        //        }
        //    }
        //    return objType;
        //}
        #endregion


        /// <summary>
        /// 用户仓储
        /// </summary>
        public static ISystem_EmployeeRepository System_EmployeeRepository { get { return (ISystem_EmployeeRepository)CreateObjectNoCache(AssemblyPath, AssemblyPath + ".System_EmployeeRepository"); } }

        /// <summary>
        ///菜单仓储
        /// </summary>
        public static ISystem_MenuRepository  System_MenuRepository { get { return (ISystem_MenuRepository)CreateObjectNoCache(AssemblyPath, AssemblyPath + ".System_MenuRepository"); } }

        /// <summary>
        ///角色仓储
        /// </summary>
        public static ISystem_RoleRepository System_RoleRepository { get { return (ISystem_RoleRepository)CreateObjectNoCache(AssemblyPath, AssemblyPath + ".System_RoleRepository"); } }

        /// <summary>
        ///按钮仓储
        /// </summary>
        public static ISystem_ButtonRepository System_ButtonRepository { get { return (ISystem_ButtonRepository)CreateObjectNoCache(AssemblyPath, AssemblyPath + ".System_ButtonRepository"); } }

    }
}

所有的访问数据库的操作都用接口来约束：

namespace MCFramework.IDAL
{
    public interface IBaseRepository<T> where T:class
    {
        int Add(T model);

        int UpdateDel(string ids, bool isDel);

        int Del(string ids);

        int Update(T model);

        DataSet GetListByProc(string procName, System.Data.SqlClient.SqlParameter[] paras);

        DataSet GetModel(string Id);

        DataSet GetList(string strWhere);

    }
}

namespace MCFramework.IDAL
{
   public interface ISystem_ButtonRepository:IBaseRepository<System_ButtonModel>
    {
        bool IsExit(string ButtonCode);
    }
}

接口的实现：

namespace MCFramework.SQLDAL
{
    /// <summary>
    /// Author: MaChun
    /// Description: DALTier -- the DAL class of System_Button.
    /// Datetime:2015/6/8 13:00:35
    /// </summary>
    public class  BaseSystem_ButtonRepository: IBaseRepository<System_ButtonModel>
    {
    
        //创建企业库连接
     public  SqlDataAccess db = SqlDataAccess.CreateDataAccess();
    
        #region 新增一条记录 Add(System_ButtonModel model)
        /// <summary>
        /// 新增一条记录
        /// </summary>
        public int Add(System_ButtonModel  model)
        {
            int result = 0;
            try
            {

                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into System_Button(");
                strSql.Append("SBT_Guid,SBT_ButtonCode,SBT_ButtonName,SBT_IconUrl,SBT_IconCss,SBT_CreateBy,SBT_CreatedDate)");
                strSql.Append(" values (");
                strSql.Append("@SBT_Guid,@SBT_ButtonCode,@SBT_ButtonName,@SBT_IconUrl,@SBT_IconCss,@SBT_CreateBy,@SBT_CreatedDate)");
                strSql.Append(";select @@IDENTITY");
                SqlParameter[] parameters = {
                    new SqlParameter("@SBT_Guid", SqlDbType.VarChar,36),
                    new SqlParameter("@SBT_ButtonCode", SqlDbType.VarChar,200),
                    new SqlParameter("@SBT_ButtonName", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_IconUrl", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_IconCss", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_CreateBy", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_CreatedDate", SqlDbType.DateTime,8)};
                                parameters[0].Value = model.SBTGuid;
                parameters[1].Value = model.SBTButtonCode;
                parameters[2].Value = model.SBTButtonName;
                parameters[3].Value = model.SBTIconUrl;
                parameters[4].Value = model.SBTIconCss;
                parameters[5].Value = model.SBTCreateBy;
                parameters[6].Value = model.SBTCreatedDate;
                
                result =   db.ExecuteNonQuery(strSql.ToString(), parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion 

        #region 逻辑删除 UpdateDel(string ids, bool isDel)
        /// <summary>
        /// 逻辑删除
        /// </summary>
        public int UpdateDel(string ids, bool isDel)
        {
          
           
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_Button set noDelKey='" + isDel.ToString() + "' where SBT_Guid in (" + ids + ")");
         
            return   db.ExecuteNonQuery(strSql.ToString());
        }
        #endregion
        
        #region 物理删除 Del(string id)
        /// <summary>
        /// 物理删除
        /// </summary>
        public int Del(string id)
        {
   
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete System_Button where SBT_Guid ='"+id+"' ");
            return  db.ExecuteNonQuery(strSql.ToString());
        }
        #endregion 

        #region 修改记录 Update(System_ButtonModel model)
        /// <summary>
        /// 修改记录
        /// </summary>
        public int Update(System_ButtonModel model)
        {
            int res = -2;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_Button set ");
                        strSql.Append("SBT_ButtonCode=@SBT_ButtonCode,");
            strSql.Append("SBT_ButtonName=@SBT_ButtonName,");
            strSql.Append("SBT_IconUrl=@SBT_IconUrl,");
            strSql.Append("SBT_IconCss=@SBT_IconCss,");
            strSql.Append("SBT_CreateBy=@SBT_CreateBy,");
            strSql.Append("SBT_CreatedDate=@SBT_CreatedDate");
            strSql.Append(" where SBT_Guid=@SBT_Guid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@SBT_Guid", SqlDbType.VarChar,36),
                    new SqlParameter("@SBT_ButtonCode", SqlDbType.VarChar,200),
                    new SqlParameter("@SBT_ButtonName", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_IconUrl", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_IconCss", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_CreateBy", SqlDbType.VarChar,100),
                    new SqlParameter("@SBT_CreatedDate", SqlDbType.DateTime,8)};
                            parameters[0].Value = model.SBTGuid;
                parameters[1].Value = model.SBTButtonCode;
                parameters[2].Value = model.SBTButtonName;
                parameters[3].Value = model.SBTIconUrl;
                parameters[4].Value = model.SBTIconCss;
                parameters[5].Value = model.SBTCreateBy;
                parameters[6].Value = model.SBTCreatedDate;

            try
            {
                res = db.ExecuteNonQuery(strSql.ToString(), parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally {  }
            return res;
        }
        #endregion 
        
        #region  根据主键查询出DataSet GetModel(string Id)
        /// <summary>
        /// 根据主键查询出DataSet
        /// </summary>
        public DataSet GetModel(string Id)
        {
           
           
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select SBT_Guid,SBT_ButtonCode,SBT_ButtonName,SBT_IconUrl,SBT_IconCss,SBT_CreateBy,SBT_CreatedDate from System_Button ");
            strSql.Append(" where SBT_Guid=@SBT_Guid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@SBT_Guid", SqlDbType.Char,36)};
            parameters[0].Value = Id;
         
            DataSet ds =  db.ExecuteDataSet(strSql.ToString(), parameters);
         
            return ds;
        }

        #endregion
        
        #region 根据where 条件查询出DataSet  GetList(string strWhere)
        /// <summary>
        /// 根据where 条件查询出DataSet
        /// </summary>
        public DataSet GetList(string strWhere)
        {
           
           
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select SBT_Guid,SBT_ButtonCode,SBT_ButtonName,SBT_IconUrl,SBT_IconCss,SBT_CreateBy,SBT_CreatedDate ");
            strSql.Append(" FROM System_Button ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            
            DataSet ds = db.ExecuteDataSet(strSql.ToString());
            
            return ds;
        }
        #endregion

        #region 根据存储过程查询出DataSet GetListByProc(string procName,SqlParameter[] paras)
        /// <summary>
        ///根据存储过程查询出DataSet
        /// </summary>
        /// <param name="procName">procName</param>
        /// <param name="paras">paras</param>
        public DataSet GetListByProc(string procName,SqlParameter[] paras)
        {

            DataSet ds =  db.ExecuteDataSet(procName, paras);
           
            return ds;
        }
        #endregion


        
    }
}

namespace MCFramework.SQLDAL
{
    /// <summary>
    /// Author: MaChun
    /// Description: DALTier -- the DAL class of System_Button.
    /// Datetime:2015/4/20 16:01:42
    /// </summary>
    public class System_ButtonRepository:BaseSystem_ButtonRepository,ISystem_ButtonRepository
    {


        public bool IsExit(string ButtonCode)
        {
            bool flag = false;
            string sql = string.Format(@"SELECT * FROM dbo.System_Button WHERE SBT_ButtonCode='{0}'", ButtonCode);
            DataSet ds = db.ExecuteDataSet(sql);
            if (ds.Tables.Count > 0)
            {
                flag = ds.Tables[0].Rows.Count > 0;
            }

            return flag;
        }
    }
}

数据访问层操作数据库我没有用ORM框架，用的是传统的ADO.Net,我个人觉得这样可控性会更好，也利于以后的优化。

业务逻辑层的调用：

namespace MCFramework.BLL
{
    /// <summary>
    /// Author: MaChun
    /// Description: BLLTier -- the BLL class of System_Button.
    /// Datetime:2015/5/6 17:00:35
    /// </summary>
    public  class  BaseSystem_ButtonService
    {
        protected  static readonly ISystem_ButtonRepository  dal = RepositoryFactory.RepositoryFactory.System_ButtonRepository;
      
        
            #region 新增一条记录 Add(System_ButtonModel model)
        /// <summary>
        /// 新增一条记录
        /// </summary>
        public int Add(System_ButtonModel model)
        {
            return dal.Add(model);
        }
        #endregion
        
            #region 根据ID 逻辑删除一条记录 UpdateDel(string ids, bool isDel)
        /// <summary>
        /// 根据ID 逻辑删除一条记录
        /// </summary>
        public int UpdateDel(string ids, bool isDel)
        {
            return dal.UpdateDel(ids, isDel);
        }
        #endregion
        
        　　#region 根据ID物理删除一条记录　Del(string id)
        /// <summary>
        /// 根据ID物理删除一条记录
        /// </summary>
        public int Del(string id)
        {
            return dal.Del(id);
        }
        #endregion

            #region 更新一条记录 Update(System_ButtonModel model)
            /// <summary>
            /// 更新一条记录
            /// </summary>
            public int Update(System_ButtonModel model)
            {
                return dal.Update(model);
            }
            #endregion
            
            #region 根据存储过程来查询返回一个list 集合 GetListByProc
            /// <summary>
            ///根据存储过程来查询返回一个list 集合
            /// </summary>
            /// <param name="procName">procName</param>
            /// <param name="paras">paras</param>
            public List<System_ButtonModel> GetListByProc(string procName,System.Data.SqlClient.SqlParameter[] paras)
            {
                     DataTable dt=  dal.GetListByProc(procName,paras).Tables[0];
                     
                     List<System_ButtonModel> list = null;
                     list = new List<System_ButtonModel>();
                     LoadListData(ref list, dt);
                    if (list.Count > 0)
                        {
                          return list;
                        }
                        else
                        { 
                          return null;
                        }
            }
            #endregion
            
            #region  根据主键来查询得到一个实体 GetModel(string Id)
            /// <summary>
            /// 根据主键来查询得到一个实体
            /// </summary>
            public System_ButtonModel GetModel(string Id)
            {
                 
                    DataTable dt= dal.GetModel(Id).Tables[0];
                    
                   System_ButtonModel model = new System_ButtonModel();
                  if (dt.Rows.Count > 0)
                    {
                        LoadEntityData(ref model, dt.Rows[0]);
                        return model;
                    }
                    else
                    {
                        return null;
                    }
            }
            #endregion
            
            #region  根据 where 条件来查询得到一个list集合 GetList(string strWhere)
            /// <summary>
            /// 根据 where 条件来查询得到一个list集合
            /// </summary>
            public List<System_ButtonModel> GetList(string strWhere)
            {
                    DataTable dt= dal.GetList(strWhere).Tables[0];
                    
                   List<System_ButtonModel> list = null;
                   list = new List<System_ButtonModel>();
                   LoadListData(ref list, dt);   
                    if (list.Count > 0)
                        {
                          return list;
                        }
                        else
                        {
                          return null;
                        }
                
            }
            #endregion
        
       
       
       
            #region 将DataTable转换为 List 对象集合  LoadListData
        /// <summary>
        ///  将DataTable转换为 List 对象集合
        /// </summary>
        /// <param name="list">GenericList</param>
        /// <param name="dt">DataTable</param>
        public void LoadListData(ref List<System_ButtonModel> list, DataTable dt)
        {
            if (dt.Rows.Count > 0)
            {
                System_ButtonModel model;
                foreach (DataRow dr in dt.Rows)
                {
                    model = new System_ButtonModel();
                    LoadEntityData(ref model, dr);
                    list.Add(model);
                }
            }
        }
        #endregion
        
            #region 将Datatable中的Dr 转换成 Model 实体  LoadEntityData
        /// <summary>
        /// 将Datatable中的Dr 转换成 Model 实体
        /// </summary>
        /// <param name="model">Entity</param>
        /// <param name="dr">DataRow</param>
        public void LoadEntityData(ref System_ButtonModel model, DataRow dr)
        {
                        model.SBTGuid = dr["SBT_Guid"].ToString();
            model.SBTButtonCode = dr["SBT_ButtonCode"].ToString();
            model.SBTButtonName = dr["SBT_ButtonName"].ToString();
            model.SBTIconUrl = dr["SBT_IconUrl"].ToString();
            model.SBTIconCss = dr["SBT_IconCss"].ToString();
            model.SBTCreateBy = dr["SBT_CreateBy"].ToString();
            if (dr["SBT_CreatedDate"].ToString() != "")
            {
                model.SBTCreatedDate = DateTime.Parse(dr["SBT_CreatedDate"].ToString());
            }

        }
        #endregion
        
       
    }
}

 public  class System_ButtonService:BaseSystem_ButtonService
    {
      
        #region 单例模式

        private System_ButtonService()
        {
        }

        private static readonly System_ButtonService instance = new System_ButtonService();

        public static System_ButtonService Instance
        {
            get { return instance; }
        }

        #endregion





        public bool IsExit(string ButtonCode)
        {

            return dal.IsExit(ButtonCode);
        }
    }
}

对于，业务层到UI层之间的解耦，我没有按架构图中的去实现，我觉在一般中的项目中没有这个必要，为了简化 ，我所以省去了，如果你想加上，完全可以按抽象工厂的方式加上。以上就是我对整个项目的架构上做的一个说明。接下来我们再看看我数据库的设计。

 

   权限的细化颗粒度到各页面的按钮。对于以上的架构和表设计，如果有什么好的建议可以给我留言。下一节，我们将完成主页面的主休框架和登录验证。