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.動軟代碼

<#@ template language="c#" HostSpecific="True" #>
<#@ output extension= ".cs" #>
<#
    TableHost host = (TableHost)(Host);    
    string DbParaHead=host.DbParaHead;
    string DbParaDbType=host.DbParaDbType;
    string preParameter=host.preParameter;
    string ModelSpace = host.NameSpace+".Model."+ host.GetModelClass(host.TableName);    
    string TableName =  host.GetModelClass(host.TableName).Split('_')[1];   
    ColumnInfo identityKey=host.IdentityKey;
    string returnValue = "void";
    if (identityKey!=null)
    {         
         returnValue = CodeCommon.DbTypeToCS(identityKey.TypeName);              
    }
#>
using System;
using System.Collections.Generic;
using CommonLibrary.Serializer;
using CommonContract.Condition;
using CommonContract.Entity;
using CommonLibrary.Cache;
using CommonLibrary.SqlDB;
using CommonLibrary.ExtendsMethod;

namespace MIT.Application.Dao
<# if( host.Folder.Length > 0){ #>
    .<#= host.Folder #>
<# } #>
{
    <# if( host.TableDescription.Length > 0) {#>
     //<#= host.TableDescription #>
<# } #>
    
public  class <#= TableName #>Dao
{
    
       public const string <#= TableName #>CacheKey = "<#= TableName #>CacheKey";
    
        /// <summary>
        ///  獲取所有數據
        /// </summary>
        public List<<#= TableName #>Entity> GetList()
        {        
            var db =SqlSugarHelper.GetInstance();                            
            List<<#= TableName #>Entity> list = db.Queryable<<#= TableName #>Entity>()               
                .OrderBy(it => it.SortCode)
                .ToList();   
            return list; 
        }
        
        /// <summary>
        ///  通過緩存獲取所有數據
        /// </summary>
        public List<<#= TableName #>Entity> GetListByCache()
        {        
            List<<#= TableName #>Entity> list = PageCacheManager.Current.GetCache<<#= TableName #>Entity>(<#= TableName #>CacheKey);
            if (list == null)
            {
                 list = GetList();
                 PageCacheManager.Current.AddCache(<#= TableName #>CacheKey,list);
            }
            return list; 
        }
        
        /// <summary>
        ///  通過條件獲取所有數據
        /// </summary>
        public List<<#= TableName #>Entity> GetListByCondition(string queryJson)
        {                     
             var db = SqlSugarHelper.GetInstance();
             var queryParam = JsonHelper.ToJObject(queryJson);
           //  string UserName = queryParam["UserName"].ToConvertString();
           //  string UserAccount = queryParam["UserAccount"].ToConvertString();
             List<<#= TableName #>Entity> list = db.Queryable<<#= TableName #>Entity>()
              //  .WhereIF(!UserName.IsEmpty(), it => it.UserName.Contains(UserName))
              //  .WhereIF(!UserAccount.IsEmpty(), it => it.UserAccount.Contains(UserAccount))
                .OrderBy(it => it.SortCode)
                .ToList();  
            return list; 
        }
        
        /// <summary>
        /// 通過分页獲取數據
        /// </summary>
        public List<<#= TableName #>Entity> GetListByPage(PageInfo pageInfo, string queryJson)
        {        
            var db =SqlSugarHelper.GetInstance();
            int totalCount = 0;
            var queryParam = JsonHelper.ToJObject(queryJson);  
         //   string UserName = queryParam["UserName"].ToConvertString();
          //  string UserAccount = queryParam["UserAccount"].ToConvertString();
            List<<#= TableName #>Entity> list = db.Queryable<<#= TableName #>Entity>()
              //  .WhereIF(!UserName.IsEmpty(), it => it.UserName.Contains(UserName))
              //  .WhereIF(!UserAccount.IsEmpty(), it => it.UserAccount.Contains(UserAccount))
                .OrderBy(it => it.SortCode)                
                .ToPageList(pageInfo.page, pageInfo.rows, ref totalCount);

            pageInfo.records = totalCount ;          
            return list;
        }
        
        /// <summary>
        ///  通過主鍵獲取實體
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <returns></returns>
        public <#= TableName #>Entity GetEntity(string keyValue)
        {
            var db = SqlSugarHelper.GetInstance();
            <#= TableName #>Entity entity = db.Queryable<<#= TableName #>Entity>().InSingle(keyValue);
            return entity; 
        }
        
        
        /// <summary>
        ///  通過主鍵删數據
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <returns></returns>
        public int RemoveForm(string keyValue)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey);
            return db.Deleteable<<#= TableName #>Entity>().In(keyValue).ExecuteCommand();            
        }
        
        /// <summary>
        ///  通過條件删數據
        /// </summary>
        /// <param name="queryJson">條件</param>
        /// <returns></returns>
        public int RemoveFormByCondition(string queryJson)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey);
            return db.Deleteable<<#= TableName #>Entity>()
                    // .Where(it => it.Id == 1)
                    // .WhereIF(!queryParam["UserName"].IsEmpty(), it => it.UserName.Contains(queryParam["UserName"].ToString()))
                     .ExecuteCommand();;
        }
        
        /// <summary>
        ///  批量删除數據
        /// </summary>
        /// <param name="keyValues">主键值</param>
        /// <returns></returns>
        public int RemoveFormByKeys(int[] keyValues)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey);
            return db.Deleteable<<#= TableName #>Entity>().In(keyValues).ExecuteCommand();;            
        }       
        
        
        /// <summary>
        ///  保存數據（新增、修改）
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <param name="Entity">实体</param>
        /// <returns></returns>
        public int SaveForm(string keyValue, <#= TableName #>Entity Entity)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(<#= TableName #>CacheKey);
            int keyVal = 0;
            if (!string.IsNullOrEmpty(keyValue))
            {             
                //根据实体更新（主键要有值，主键是更新条件）
                int ModifyRow =  db.Updateable(Entity)
                .IgnoreColumns(it => new {  it.CreateDate, it.CreateUserId , it.CreateUserName})
                .Where(true)
                .ExecuteCommand();
                keyVal = ModifyRow > 0 ? keyValue.ToInt() : 0;
            }
            else
            {
                //插入并返回自增列用ExecuteReutrnIdentity
                //可以设置NULL列不插入和是否强制插入自增列
                keyVal = db.Insertable(Entity)
               .Where(true/* Is no insert null */, false/*off identity*/)
               .ExecuteCommand();
            }    
            return keyVal;
                   
        } 
    }
}

3.C# 代碼

using CommonContract.Entity;
using CommonLibrary.SqlDB;
using System;
using System.Collections.Generic;
using System.Data;
using CommonLibrary.Serializer;
using CommonLibrary.ExtendsMethod;
using CommonLibrary.Cache;
using System.Threading;
using CommonContract.Condition;

namespace MIT.Application.Dao.BaseManager
{
    //WB_tb_BackStageUser 后台用户

    public class UserDao
    {

        public const string UserInfoCacheKey = "UserInfoCacheKey";

        /// <summary>
        ///  獲取所有數據
        /// </summary>
        public List<UserEntity> GetList()
        {
            var db = SqlSugarHelper.GetInstance();
            List<UserEntity> list = db.Queryable<UserEntity>()
                .OrderBy(it => it.CreateDate)
                .ToList();
            return list;
        }

        /// <summary>
        ///  通過緩存獲取所有數據
        /// </summary>
        public List<UserEntity> GetListByCache()
        {
            List<UserEntity> list = PageCacheManager.Current.GetCache<UserEntity>(UserInfoCacheKey);
            if (list == null)
            {
                list = GetList();
                PageCacheManager.Current.AddCache(UserInfoCacheKey, list);
            }
            return list;
        }

        /// <summary>
        ///  通過條件獲取所有數據
        /// </summary>
        public List<UserEntity> GetListByCondition(string queryJson)
        {
            var db = SqlSugarHelper.GetInstance();
            var queryParam = JsonHelper.ToJObject(queryJson);
            string UserName = queryParam["UserName"].ToConvertString();
            string UserAccount = queryParam["UserAccount"].ToConvertString();
            List<UserEntity> list = db.Queryable<UserEntity>()
                 .WhereIF(!UserName.IsEmpty(), it => it.RealName.Contains(UserName))
                .WhereIF(!UserAccount.IsEmpty(), it => it.Account.Contains(UserAccount))
               .OrderBy(it => it.ModifyDate)
               .ToList();
            return list;
        }

        /// <summary>
        /// 通過分页獲取數據
        /// </summary>
        public List<UserEntity> GetListByPage(PageInfo pageInfo, string queryJson)
        {
            var db = SqlSugarHelper.GetInstance();
            var queryParam = JsonHelper.ToJObject(queryJson);           
            int totalCount = 0;
            string UserName = queryParam["UserName"].ToConvertString();
            string UserAccount = queryParam["UserAccount"].ToConvertString();  
            List<UserEntity> list = db.Queryable<UserEntity>()
                .WhereIF(!UserName.IsEmpty(), it => it.RealName.Contains(UserName))
                .WhereIF(!UserAccount.IsEmpty(), it => it.Account.Contains(UserAccount))
                .OrderBy(it => it.ModifyDate)              
                .ToPageList(pageInfo.page, pageInfo.rows, ref totalCount);

            pageInfo.records = totalCount;
            return list;
        }

        /// <summary>
        ///  通過主鍵獲取實體
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <returns></returns>
        public UserEntity GetEntity(string keyValue)
        {
            var db = SqlSugarHelper.GetInstance();
            UserEntity entity = db.Queryable<UserEntity>().InSingle(keyValue);
            return entity;
        }


        /// <summary>
        ///  通過主鍵删數據
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <returns></returns>
        public int RemoveForm(string keyValue)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(UserInfoCacheKey);
            return db.Deleteable<UserEntity>().In(keyValue).ExecuteCommand();
        }

        /// <summary>
        ///  通過條件删數據
        /// </summary>
        /// <param name="queryJson">條件</param>
        /// <returns></returns>
        public int RemoveFormByCondition(string queryJson)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(UserInfoCacheKey);
            return db.Deleteable<UserEntity>()
                // .Where(it => it.Id == 1)
                // .WhereIF(!queryParam["UserName"].IsEmpty(), it => it.UserName.Contains(queryParam["UserName"].ToString()))
                     .ExecuteCommand(); ;
        }

        /// <summary>
        ///  批量删除數據
        /// </summary>
        /// <param name="keyValues">主键值</param>
        /// <returns></returns>
        public int RemoveFormByKeys(int[] keyValues)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(UserInfoCacheKey);
            return db.Deleteable<UserEntity>().In(keyValues).ExecuteCommand(); ;
        }


        /// <summary>
        ///  保存數據（新增、修改）
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <param name="Entity">实体</param>
        /// <returns></returns>
        public int SaveForm(string keyValue, UserEntity Entity)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(UserInfoCacheKey);
            int keyVal = 0;
            if (!string.IsNullOrEmpty(keyValue))
            {
                Entity.UserId = keyValue.ToInt();
              //  Entity.UpdateTime = DateTime.Now;
                //根据实体更新（主键要有值，主键是更新条件）
                int ModifyRow = db.Updateable(Entity)                
                    .UpdateColumns(it => new { it.RoleId,it.RealName,it.Description,it.ModifyDate})
                    .Where(true/* Is no insert null */, false/*off identity*/)
                    .ExecuteCommand();
                keyVal = ModifyRow > 0 ? keyValue.ToInt() : 0;
            }
            else
            {                             
                 keyVal = db.Insertable(Entity)
                          .Where(true/* Is no insert null */, false/*off identity*/)
                          .ExecuteReutrnIdentity();
            }
            return keyVal;
        }

        /// <summary>
        ///  更新用戶狀態
        /// </summary>
        /// <param name="keyValue">主键值</param>
        /// <param name="Entity">实体</param>
        /// <returns></returns>
        public int UpdateState(UserEntity Entity)
        {
            var db = SqlSugarHelper.GetInstance();
            PageCacheManager.Current.RemoveCache(UserInfoCacheKey);            
            //根据实体更新（主键要有值，主键是更新条件）
            return db.Updateable(Entity).UpdateColumns(it => new { it.EnabledMark }).ExecuteCommand();
        }
        
    }
}

4.緩存代碼

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Caching;
using System.Text;
using System.Threading.Tasks;
using CommonLibrary.ExtendsMethod;

namespace CommonLibrary.Cache
{
    /// <summary>
    /// 页面使用缓存
    /// </summary>
    public class PageCacheManager
    {
        #region Singleton
        private static PageCacheManager _instance;
        private static readonly object obj_lock = new object();      
        public static PageCacheManager Current
        {
            get
            {
                if (_instance == null)
                {
                    lock (obj_lock)
                    {
                        if (_instance == null)
                        {
                            _instance = new PageCacheManager();
                        }
                    }
                }
                return _instance;
            }
        } 
        #endregion

         
        /// <summary>
        /// 设置时间
        /// </summary>
        public int CacheTimeOut { get; set; }

        private ObjectCache cache;

        private PageCacheManager()
        {
            cache = MemoryCache.Default;
            CacheTimeOut = AppConfig.PageCacheTimeOut;
        }

        public void AddCache(string key, object value)
        {
            cache.Add(key, value, new CacheItemPolicy { SlidingExpiration = TimeSpan.FromSeconds(CacheTimeOut) });
        }

        /// <summary>
        /// 获取缓存 --重载获取缓存
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<T> GetCache<T>(string key) where T : class
        {
            return GetCache<T>(key, null, null);
        }

        /// <summary>
        /// 获取缓存 
        /// 排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<T> GetCache<T>(string key, string sort, string order) where T : class
        {
            if (!string.IsNullOrEmpty(key))
            {
                if (cache.Contains(key))
                {
                    if (!string.IsNullOrEmpty(sort) && !string.IsNullOrEmpty(order))
                    {
                        return (cache.Get(key) as IEnumerable<T>).AsQueryable<T>().DataSorting(sort, order).ToList();
                    }
                    return (cache.Get(key) as IEnumerable<T>).ToList();
                }
            }
            return null;
        }

        /// <summary>
        /// 获取单个缓存
        /// </summary> 
        /// <param name="key"></param>
        /// <returns></returns>
        public string GetCache(string key)
        {
            if (!string.IsNullOrEmpty(key))
            {
                if (cache.Contains(key))
                {
                    return cache.Get(key).ToString();
                }
                return string.Empty;
            }
            return string.Empty;
        }

        /// <summary>
        /// 是否存在key
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool ContainsKey(string key)
        {
            if (string.IsNullOrEmpty(key))
                return false;
            return this.cache.Contains(key);
        }

        /// <summary>
        /// 移除缓存
        /// </summary>
        /// <param name="id"></param>
        public void RemoveCache(string key)
        {
            cache.Remove(key);
        }
    }
}