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         ///  41         ///  獲取所有數據 42         /// 
 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         ///  53         ///  通過緩存獲取所有數據 54         /// 
 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         ///  67         ///  通過條件獲取所有數據 68         /// 
 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         ///  84         /// 通過分页獲取數據 85         /// 
 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         /// 104         ///  通過主鍵獲取實體105         /// 
106         /// 主键值107         /// 
      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         /// 117         ///  通過主鍵删數據118         /// 
119         /// 主键值120         /// 
      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         /// 129         ///  通過條件删數據130         /// 
131         /// 條件132         /// 
      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         /// 144         ///  批量删除數據145         /// 
146         /// 主键值147         /// 
      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         /// 157         ///  保存數據（新增、修改）158         /// 
159         /// 主键值160         /// 实体161         /// 
      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             else177             {178                 //插入并返回自增列用ExecuteReutrnIdentity179                 //可以设置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         ///  22         ///  獲取所有數據 23         /// 
 24         public List
      
        GetList() 25         { 26             var db = SqlSugarHelper.GetInstance(); 27             List
       
         list = db.Queryable
        
         () 28                 .OrderBy(it => it.CreateDate) 29                 .ToList(); 30             return list; 31         } 32  33         /// 
          34         ///  通過緩存獲取所有數據 35         /// 
 36         public List
         
           GetListByCache() 37         { 38             List
          
            list = PageCacheManager.Current.GetCache
           
            (UserInfoCacheKey); 39 if (list == null) 40 { 41 list = GetList(); 42 PageCacheManager.Current.AddCache(UserInfoCacheKey, list); 43 } 44 return list; 45 } 46 47 /// 
             48 /// 通過條件獲取所有數據 49 /// 
 50 public List
            
              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
             
               list = db.Queryable
              
               () 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 /// 
                65 /// 通過分页獲取數據 66 /// 
 67 public List
               
                 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
                
                  list = db.Queryable
                 
                  () 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 /// 
                   85 /// 通過主鍵獲取實體 86 /// 
 87 /// 
                  主键值 88 /// 
                   89 public UserEntity GetEntity(string keyValue) 90 { 91 var db = SqlSugarHelper.GetInstance(); 92 UserEntity entity = db.Queryable
                  
                   ().InSingle(keyValue); 93 return entity; 94 } 95 96 97 /// 
                    98 /// 通過主鍵删數據 99 /// 
100 /// 
                   主键值101 /// 
                   102 public int RemoveForm(string keyValue)103 {104 var db = SqlSugarHelper.GetInstance();105 PageCacheManager.Current.RemoveCache(UserInfoCacheKey);106 return db.Deleteable
                   
                    ().In(keyValue).ExecuteCommand();107 }108 109 /// 
                    110 /// 通過條件删數據111 /// 
112 /// 
                    條件113 /// 
                    114 public int RemoveFormByCondition(string queryJson)115 {116 var db = SqlSugarHelper.GetInstance();117 PageCacheManager.Current.RemoveCache(UserInfoCacheKey);118 return db.Deleteable
                    
                     ()119 // .Where(it => it.Id == 1)120 // .WhereIF(!queryParam["UserName"].IsEmpty(), it => it.UserName.Contains(queryParam["UserName"].ToString()))121 .ExecuteCommand(); ;122 }123 124 /// 
                     125 /// 批量删除數據126 /// 
127 /// 
                     主键值128 /// 
                     129 public int RemoveFormByKeys(int[] keyValues)130 {131 var db = SqlSugarHelper.GetInstance();132 PageCacheManager.Current.RemoveCache(UserInfoCacheKey);133 return db.Deleteable
                     
                      ().In(keyValues).ExecuteCommand(); ;134 }135 136 137 /// 
                      138 /// 保存數據（新增、修改）139 /// 
140 /// 
                      主键值141 /// 
                      实体142 /// 
                      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 else160 { 161 keyVal = db.Insertable(Entity)162 .Where(true/* Is no insert null */, false/*off identity*/)163 .ExecuteReutrnIdentity();164 }165 return keyVal;166 }167 168 /// 
                      169 /// 更新用戶狀態170 /// 
171 /// 
                      主键值172 /// 
                      实体173 /// 
                      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     ///  12     /// 页面使用缓存 13     /// 
 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         ///  40         /// 设置时间 41         /// 
 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         ///  58         /// 获取缓存 --重载获取缓存 59         /// 
 60         /// 
       61         ///  62         /// 
       63         public List
      
        GetCache
       
        (string key) where T : class 64         { 65             return GetCache
        
         (key, null, null); 66         } 67  68         /// 
          69         /// 获取缓存  70         /// 排序 71         /// 
 72         /// 
          73         /// 
          74         /// 
          75         public List
         
           GetCache
          
           (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
           
            ).AsQueryable
            
             ().DataSorting(sort, order).ToList(); 84 } 85 return (cache.Get(key) as IEnumerable
             
              ).ToList(); 86 } 87 } 88 return null; 89 } 90 91 /// 
               92 /// 获取单个缓存 93 /// 
 94 /// 
               95 /// 
               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 /// 
              110 /// 是否存在key111 /// 
112 /// 
              113 /// 
              114 public bool ContainsKey(string key)115 {116 if (string.IsNullOrEmpty(key))117 return false;118 return this.cache.Contains(key);119 }120 121 /// 
              122 /// 移除缓存123 /// 
124 /// 
              125 public void RemoveCache(string key)126 {127 cache.Remove(key);128 }129 }130 }