MVC+Ninject+三层架构+代码生成 -- 总结（四、數據層）
1.數據層使用了SqlSugar 庫類 。
數據層使用了SqlSugar 庫類 ，有興趣的 可以學習 http://www.codeisbug.com/Doc/8/1133，個人覺得比EF 簡單，容易上手，推薦+1。
數據層使用代碼生成，所以考慮得比較多。
1.GetAllList()--獲取全部數據
2.GetAllListByCache()--通過緩存獲取全部數據
3.GetListByCondition(string queryJson)--通過條件獲取數據
4.GetListByPage(PageInfo pageInfo, string queryJson)--通過 條件 和 分頁 信息 獲取數據
5.GetEntity(string keyValue)--通過主鍵 獲取 單條實體
6.DelEntity(string keyValue)--通過主鍵 刪除 單條實體
7.DelListByCondition(string queryJson)--通過條件 刪除 單條實體
8.DelEntityByKeys(int[] keyValues)--通過主鍵 刪除 數據
9.SaveForm(string keyValue, T Entity)--保存數據（新增、修改）
2.動軟代碼
1 <#@ template language="c#" HostSpecific="True" #> 2 <#@ output extension= ".cs" #> 3 <# 4 TableHost host = (TableHost)(Host); 5 string DbParaHead=host.DbParaHead; 6 string DbParaDbType=host.DbParaDbType; 7 string preParameter=host.preParameter; 8 string ModelSpace = host.NameSpace+".Model."+ host.GetModelClass(host.TableName); 9 string TableName = host.GetModelClass(host.TableName).Split('_')[1]; 10 ColumnInfo identityKey=host.IdentityKey; 11 string returnValue = "void"; 12 if (identityKey!=null) 13 { 14 returnValue = CodeCommon.DbTypeToCS(identityKey.TypeName); 15 } 16 #> 17 using System; 18 using System.Collections.Generic; 19 using CommonLibrary.Serializer; 20 using CommonContract.Condition; 21 using CommonContract.Entity; 22 using CommonLibrary.Cache; 23 using CommonLibrary.SqlDB; 24 using CommonLibrary.ExtendsMethod; 25 26 namespace MIT.Application.Dao 27 <# if( host.Folder.Length > 0){ #> 28 .<#= host.Folder #> 29 <# } #> 30 { 31 <# if( host.TableDescription.Length > 0) {#> 32 //<#= host.TableDescription #> 33 <# } #> 34 35 public class <#= TableName #>Dao 36 { 37 38 public const string <#= TableName #>CacheKey = "<#= TableName #>CacheKey"; 39 40 /// <summary> 41 /// 獲取所有數據 42 /// </summary> 43 public List<<#= TableName #>Entity> GetList() 44 { 45 var db =SqlSugarHelper.GetInstance(); 46 List<<#= TableName #>Entity> list = db.Queryable<<#= TableName #>Entity>() 47 .OrderBy(it => it.SortCode) 48 .ToList(); 49 return list; 50 } 51 52 /// <summary> 53 /// 通過緩存獲取所有數據 54 /// </summary> 55 public List<<#= TableName #>Entity> GetListByCache() 56 { 57 List<<#= TableName #>Entity> list = PageCacheManager.Current.GetCache<<#= TableName #>Entity>(<#= TableName #>CacheKey); 58 if (list == null) 59 { 60 list = GetList(); 61 PageCacheManager.Current.AddCache(<#= TableName #>CacheKey,list); 62 } 63 return list; 64 } 65 66 /// <summary> 67 /// 通過條件獲取所有數據 68 /// </summary> 69 public List<<#= TableName #>Entity> GetListByCondition(string queryJson) 70 { 71 var db = SqlSugarHelper.GetInstance(); 72 var queryParam = JsonHelper.ToJObject(queryJson); 73 // string UserName = queryParam["UserName"].ToConvertString(); 74 // string UserAccount = queryParam["UserAccount"].ToConvertString(); 75 List<<#= TableName #>Entity> list = db.Queryable<<#= TableName #>Entity>() 76 // .WhereIF(!UserName.IsEmpty(), it => it.UserName.Contains(UserName)) 77 // .WhereIF(!UserAccount.IsEmpty(), it => it.UserAccount.Contains(UserAccount)) 78 .OrderBy(it => it.SortCode) 79 .ToList(); 80 return list; 81 } 82 83 /// <summary> 84 /// 通過分页獲取數據 85 /// </summary> 86 public List<<#= TableName #>Entity> GetListByPage(PageInfo pageInfo, string queryJson) 87 { 88 var db =SqlSugarHelper.GetInstance(); 89 int totalCount = 0; 90 var queryParam = JsonHelper.ToJObject(queryJson); 91 // string UserName = queryParam["UserName"].ToConvertString(); 92 // string UserAccount = queryParam["UserAccount"].ToConvertString(); 93 List<<#= TableName #>Entity> list = db.Queryable<<#= TableName #>Entity>() 94 // .WhereIF(!UserName.IsEmpty(), it => it.UserName.Contains(UserName)) 95 // .WhereIF(!UserAccount.IsEmpty(), it => it.UserAccount.Contains(UserAccount)) 96 .OrderBy(it => it.SortCode) 97 .ToPageList(pageInfo.page, pageInfo.rows, ref totalCount); 98 99 pageInfo.records = totalCount ; 100 return list; 101 } 102 103 /// <summary> 104 /// 通過主鍵獲取實體 105 /// </summary> 106 /// <param name="keyValue">主键值</param> 107 /// <returns></returns> 108 public <#= TableName #>Entity GetEntity(string keyValue) 109 { 110 var db = SqlSugarHelper.GetInstance(); 111 <#= TableName #>Entity entity = db.Queryable<<#= TableName #>Entity>().InSingle(keyValue); 112 return entity; 113 } 114 115 116 /// <summary> 117 /// 通過主鍵删數據 118 /// </summary> 119 /// <param name="keyValue">主键值</param> 120 /// <returns></returns> 121 public int RemoveForm(string keyValue) 122 { 123 var db = SqlSugarHelper.GetInstance(); 124 PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey); 125 return db.Deleteable<<#= TableName #>Entity>().In(keyValue).ExecuteCommand(); 126 } 127 128 /// <summary> 129 /// 通過條件删數據 130 /// </summary> 131 /// <param name="queryJson">條件</param> 132 /// <returns></returns> 133 public int RemoveFormByCondition(string queryJson) 134 { 135 var db = SqlSugarHelper.GetInstance(); 136 PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey); 137 return db.Deleteable<<#= TableName #>Entity>() 138 // .Where(it => it.Id == 1) 139 // .WhereIF(!queryParam["UserName"].IsEmpty(), it => it.UserName.Contains(queryParam["UserName"].ToString())) 140 .ExecuteCommand();; 141 } 142 143 /// <summary> 144 /// 批量删除數據 145 /// </summary> 146 /// <param name="keyValues">主键值</param> 147 /// <returns></returns> 148 public int RemoveFormByKeys(int[] keyValues) 149 { 150 var db = SqlSugarHelper.GetInstance(); 151 PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey); 152 return db.Deleteable<<#= TableName #>Entity>().In(keyValues).ExecuteCommand();; 153 } 154 155 156 /// <summary> 157 /// 保存數據（新增、修改） 158 /// </summary> 159 /// <param name="keyValue">主键值</param> 160 /// <param name="Entity">实体</param> 161 /// <returns></returns> 162 public int SaveForm(string keyValue, <#= TableName #>Entity Entity) 163 { 164 var db = SqlSugarHelper.GetInstance(); 165 PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey); 166 int keyVal = 0; 167 if (!string.IsNullOrEmpty(keyValue)) 168 { 169 //根据实体更新（主键要有值，主键是更新条件） 170 int ModifyRow = db.Updateable(Entity) 171 .IgnoreColumns(it => new { it.CreateDate, it.CreateUserId , it.CreateUserName}) 172 .Where(true) 173 .ExecuteCommand(); 174 keyVal = ModifyRow > 0 ? keyValue.ToInt() : 0; 175 } 176 else 177 { 178 //插入并返回自增列用ExecuteReutrnIdentity 179 //可以设置NULL列不插入和是否强制插入自增列 180 keyVal = db.Insertable(Entity) 181 .Where(true/* Is no insert null */, false/*off identity*/) 182 .ExecuteCommand(); 183 } 184 return keyVal; 185 186 } 187 } 188 }
3.C# 代碼
1 using CommonContract.Entity; 2 using CommonLibrary.SqlDB; 3 using System; 4 using System.Collections.Generic; 5 using System.Data; 6 using CommonLibrary.Serializer; 7 using CommonLibrary.ExtendsMethod; 8 using CommonLibrary.Cache; 9 using System.Threading; 10 using CommonContract.Condition; 11 12 namespace MIT.Application.Dao.BaseManager 13 { 14 //WB_tb_BackStageUser 后台用户 15 16 public class UserDao 17 { 18 19 public const string UserInfoCacheKey = "UserInfoCacheKey"; 20 21 /// <summary> 22 /// 獲取所有數據 23 /// </summary> 24 public List<UserEntity> GetList() 25 { 26 var db = SqlSugarHelper.GetInstance(); 27 List<UserEntity> list = db.Queryable<UserEntity>() 28 .OrderBy(it => it.CreateDate) 29 .ToList(); 30 return list; 31 } 32 33 /// <summary> 34 /// 通過緩存獲取所有數據 35 /// </summary> 36 public List<UserEntity> GetListByCache() 37 { 38 List<UserEntity> list = PageCacheManager.Current.GetCache<UserEntity>(UserInfoCacheKey); 39 if (list == null) 40 { 41 list = GetList(); 42 PageCacheManager.Current.AddCache(UserInfoCacheKey, list); 43 } 44 return list; 45 } 46 47 /// <summary> 48 /// 通過條件獲取所有數據 49 /// </summary> 50 public List<UserEntity> GetListByCondition(string queryJson) 51 { 52 var db = SqlSugarHelper.GetInstance(); 53 var queryParam = JsonHelper.ToJObject(queryJson); 54 string UserName = queryParam["UserName"].ToConvertString(); 55 string UserAccount = queryParam["UserAccount"].ToConvertString(); 56 List<UserEntity> list = db.Queryable<UserEntity>() 57 .WhereIF(!UserName.IsEmpty(), it => it.RealName.Contains(UserName)) 58 .WhereIF(!UserAccount.IsEmpty(), it => it.Account.Contains(UserAccount)) 59 .OrderBy(it => it.ModifyDate) 60 .ToList(); 61 return list; 62 } 63 64 /// <summary> 65 /// 通過分页獲取數據 66 /// </summary> 67 public List<UserEntity> GetListByPage(PageInfo pageInfo, string queryJson) 68 { 69 var db = SqlSugarHelper.GetInstance(); 70 var queryParam = JsonHelper.ToJObject(queryJson); 71 int totalCount = 0; 72 string UserName = queryParam["UserName"].ToConvertString(); 73 string UserAccount = queryParam["UserAccount"].ToConvertString(); 74 List<UserEntity> list = db.Queryable<UserEntity>() 75 .WhereIF(!UserName.IsEmpty(), it => it.RealName.Contains(UserName)) 76 .WhereIF(!UserAccount.IsEmpty(), it => it.Account.Contains(UserAccount)) 77 .OrderBy(it => it.ModifyDate) 78 .ToPageList(pageInfo.page, pageInfo.rows, ref totalCount); 79 80 pageInfo.records = totalCount; 81 return list; 82 } 83 84 /// <summary> 85 /// 通過主鍵獲取實體 86 /// </summary> 87 /// <param name="keyValue">主键值</param> 88 /// <returns></returns> 89 public UserEntity GetEntity(string keyValue) 90 { 91 var db = SqlSugarHelper.GetInstance(); 92 UserEntity entity = db.Queryable<UserEntity>().InSingle(keyValue); 93 return entity; 94 } 95 96 97 /// <summary> 98 /// 通過主鍵删數據 99 /// </summary> 100 /// <param name="keyValue">主键值</param> 101 /// <returns></returns> 102 public int RemoveForm(string keyValue) 103 { 104 var db = SqlSugarHelper.GetInstance(); 105 PageCacheManager.Current.RemoveCache(UserInfoCacheKey); 106 return db.Deleteable<UserEntity>().In(keyValue).ExecuteCommand(); 107 } 108 109 /// <summary> 110 /// 通過條件删數據 111 /// </summary> 112 /// <param name="queryJson">條件</param> 113 /// <returns></returns> 114 public int RemoveFormByCondition(string queryJson) 115 { 116 var db = SqlSugarHelper.GetInstance(); 117 PageCacheManager.Current.RemoveCache(UserInfoCacheKey); 118 return db.Deleteable<UserEntity>() 119 // .Where(it => it.Id == 1) 120 // .WhereIF(!queryParam["UserName"].IsEmpty(), it => it.UserName.Contains(queryParam["UserName"].ToString())) 121 .ExecuteCommand(); ; 122 } 123 124 /// <summary> 125 /// 批量删除數據 126 /// </summary> 127 /// <param name="keyValues">主键值</param> 128 /// <returns></returns> 129 public int RemoveFormByKeys(int[] keyValues) 130 { 131 var db = SqlSugarHelper.GetInstance(); 132 PageCacheManager.Current.RemoveCache(UserInfoCacheKey); 133 return db.Deleteable<UserEntity>().In(keyValues).ExecuteCommand(); ; 134 } 135 136 137 /// <summary> 138 /// 保存數據（新增、修改） 139 /// </summary> 140 /// <param name="keyValue">主键值</param> 141 /// <param name="Entity">实体</param> 142 /// <returns></returns> 143 public int SaveForm(string keyValue, UserEntity Entity) 144 { 145 var db = SqlSugarHelper.GetInstance(); 146 PageCacheManager.Current.RemoveCache(UserInfoCacheKey); 147 int keyVal = 0; 148 if (!string.IsNullOrEmpty(keyValue)) 149 { 150 Entity.UserId = keyValue.ToInt(); 151 // Entity.UpdateTime = DateTime.Now; 152 //根据实体更新（主键要有值，主键是更新条件） 153 int ModifyRow = db.Updateable(Entity) 154 .UpdateColumns(it => new { it.RoleId,it.RealName,it.Description,it.ModifyDate}) 155 .Where(true/* Is no insert null */, false/*off identity*/) 156 .ExecuteCommand(); 157 keyVal = ModifyRow > 0 ? keyValue.ToInt() : 0; 158 } 159 else 160 { 161 keyVal = db.Insertable(Entity) 162 .Where(true/* Is no insert null */, false/*off identity*/) 163 .ExecuteReutrnIdentity(); 164 } 165 return keyVal; 166 } 167 168 /// <summary> 169 /// 更新用戶狀態 170 /// </summary> 171 /// <param name="keyValue">主键值</param> 172 /// <param name="Entity">实体</param> 173 /// <returns></returns> 174 public int UpdateState(UserEntity Entity) 175 { 176 var db = SqlSugarHelper.GetInstance(); 177 PageCacheManager.Current.RemoveCache(UserInfoCacheKey); 178 //根据实体更新（主键要有值，主键是更新条件） 179 return db.Updateable(Entity).UpdateColumns(it => new { it.EnabledMark }).ExecuteCommand(); 180 } 181 182 } 183 }
4.緩存代碼
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Runtime.Caching; 5 using System.Text; 6 using System.Threading.Tasks; 7 using CommonLibrary.ExtendsMethod; 8 9 namespace CommonLibrary.Cache 10 { 11 /// <summary> 12 /// 页面使用缓存 13 /// </summary> 14 public class PageCacheManager 15 { 16 #region Singleton 17 private static PageCacheManager _instance; 18 private static readonly object obj_lock = new object(); 19 public static PageCacheManager Current 20 { 21 get 22 { 23 if (_instance == null) 24 { 25 lock (obj_lock) 26 { 27 if (_instance == null) 28 { 29 _instance = new PageCacheManager(); 30 } 31 } 32 } 33 return _instance; 34 } 35 } 36 #endregion 37 38 39 /// <summary> 40 /// 设置时间 41 /// </summary> 42 public int CacheTimeOut { get; set; } 43 44 private ObjectCache cache; 45 46 private PageCacheManager() 47 { 48 cache = MemoryCache.Default; 49 CacheTimeOut = AppConfig.PageCacheTimeOut; 50 } 51 52 public void AddCache(string key, object value) 53 { 54 cache.Add(key, value, new CacheItemPolicy { SlidingExpiration = TimeSpan.FromSeconds(CacheTimeOut) }); 55 } 56 57 /// <summary> 58 /// 获取缓存 --重载获取缓存 59 /// </summary> 60 /// <typeparam name="T"></typeparam> 61 /// <param name="id"></param> 62 /// <returns></returns> 63 public List<T> GetCache<T>(string key) where T : class 64 { 65 return GetCache<T>(key, null, null); 66 } 67 68 /// <summary> 69 /// 获取缓存 70 /// 排序 71 /// </summary> 72 /// <typeparam name="T"></typeparam> 73 /// <param name="id"></param> 74 /// <returns></returns> 75 public List<T> GetCache<T>(string key, string sort, string order) where T : class 76 { 77 if (!string.IsNullOrEmpty(key)) 78 { 79 if (cache.Contains(key)) 80 { 81 if (!string.IsNullOrEmpty(sort) && !string.IsNullOrEmpty(order)) 82 { 83 return (cache.Get(key) as IEnumerable<T>).AsQueryable<T>().DataSorting(sort, order).ToList(); 84 } 85 return (cache.Get(key) as IEnumerable<T>).ToList(); 86 } 87 } 88 return null; 89 } 90 91 /// <summary> 92 /// 获取单个缓存 93 /// </summary> 94 /// <param name="key"></param> 95 /// <returns></returns> 96 public string GetCache(string key) 97 { 98 if (!string.IsNullOrEmpty(key)) 99 { 100 if (cache.Contains(key)) 101 { 102 return cache.Get(key).ToString(); 103 } 104 return string.Empty; 105 } 106 return string.Empty; 107 } 108 109 /// <summary> 110 /// 是否存在key 111 /// </summary> 112 /// <param name="key"></param> 113 /// <returns></returns> 114 public bool ContainsKey(string key) 115 { 116 if (string.IsNullOrEmpty(key)) 117 return false; 118 return this.cache.Contains(key); 119 } 120 121 /// <summary> 122 /// 移除缓存 123 /// </summary> 124 /// <param name="id"></param> 125 public void RemoveCache(string key) 126 { 127 cache.Remove(key); 128 } 129 } 130 }
