在本节我想与大家与分享一下，我所将要做的权限系统的架构和数据库的表的设计。请各位大神们对我项目中设计的不足之处进行指导，让我得以更好的写完它，留给需要它的人。
我的项目架构如下图所示：
如上图所示，在数据访问层，我采用了抽象工厂的方式，来对数据访问层和业务逻辑层解耦，当然如果你想更高大上一些，可以用第三方的框架，比如Spring.Net ，Autofac来实现。解耦的好处在于可以方便的切换数据库，当数据库变更时，只需换一下对应的数据访问DAL就行，本系列中，我所采用的是SQLServer。写到这我想在如下这个大数据时代，MongoDB其实也是不错的。后面有机会，可以开一个MongoDB的专题和大家一起来使用一下MongoDB学习一下它。对于抽象工厂来实现业务逻辑层与数据访问层的解耦实现代码如下，主要用到了反射，面向接口编程。
配置：
<appSettings> <add key="DAL" value="MCFramework.SQLDAL"/> <!--页容量--> <add key="PageSize" value="20"/></appSettings>
View Code
抽象工厂：
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"); } } }}View Code
所有的访问数据库的操作都用接口来约束：
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); }}View Code
namespace MCFramework.IDAL{ public interface ISystem_ButtonRepository:IBaseRepository<System_ButtonModel> { bool IsExit(string ButtonCode); }}View Code
接口的实现：
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 }}View Code
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; } }}View Code
数据访问层操作数据库我没有用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 }}View Code
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); } }}View Code
对于，业务层到UI层之间的解耦，我没有按架构图中的去实现，我觉在一般中的项目中没有这个必要，为了简化 ，我所以省去了，如果你想加上，完全可以按抽象工厂的方式加上。以上就是我对整个项目的架构上做的一个说明。接下来我们再看看我数据库的设计。
权限的细化颗粒度到各页面的按钮。对于以上的架构和表设计，如果有什么好的建议可以给我留言。下一节，我们将完成主页面的主休框架和登录验证。
原标题：以一个权限系统来告别WebForm —（一）项目整休架构设计与数据库设计
关键词：数据库
我知道了 