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 }

View Code

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 }

View Code

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 }

View Code

相关阅读:
每日一题
 每日一题
 mysql 约束
 七种基础排序算法代码总汇
 netty之bytebuf粘包、分包
 Java nio 简易练手版模拟用户群聊
 Java网络编程之终端聊天菜鸟版
 双重检测优化与加锁操作redis
centos部署smb服务
 从事 iOS 研发6年的面经——送给准备跳槽的你！
原文地址：https://www.cnblogs.com/chuangjie1988/p/7397122.html

热门文章
DSP
DSP
DSP
DSP
DSP
DSP
DSP
深度学习中的正则化(一)
每日一题
 Redis面试总结

MVC+Ninject+三层架构+代码生成 总结（四、數據層）

1.數據層使用了SqlSugar 庫類 。

2.動軟代碼

3.C# 代碼

4.緩存代碼

MVC+Ninject+三层架构+代码生成总结（四、數據層）

1.數據層使用了SqlSugar 庫類。