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('_')[];
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 > ){ #>
.<#= host.Folder #>
<# } #>
{
<# if( host.TableDescription.Length > ) {#>
//<#= 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 = ;
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 = ;
if (!string.IsNullOrEmpty(keyValue))
{
//根据实体更新（主键要有值，主键是更新条件）
int ModifyRow = db.Updateable(Entity)
.IgnoreColumns(it => new { it.CreateDate, it.CreateUserId , it.CreateUserName})
.Where(true)
.ExecuteCommand();
keyVal = ModifyRow > ? keyValue.ToInt() : ;
}
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 = ;
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 = ;
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 > ? keyValue.ToInt() : ;
}
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);
}
}
}