/// <summary> 执行Sql查询语句
/// 例如：repository.ExecuteSqlQuery<InpInfo>("select * from InpInfo where InpID={0}", "Inp001")
/// </summary>
/// <typeparam name="TElement">类型参数</typeparam>
/// <param name="sql">Sql查询语句</param>
/// <param name="parameters">要传递给命令的参数数组</param>
/// <returns>类型为TElement的对象List</returns>
public virtual List <TElement> ExecuteSqlQuery <TElement>(string sql, params object[] parameters)
{
using (var rowsContext = new SGDCEntities())
{
return(rowsContext.Database.SqlQuery <TElement>(sql, parameters).ToList());
}
}
/// <summary> 执行指定Sql语句
/// 例如：repository.ExecuteSqlCommand("delete from InpInfo where InpID={0}", "Inp001")
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="parameters">要传递给命令的参数数组</param>
/// <returns>受影响的行数</returns>
public virtual int ExecuteSqlCommand(string sql, params object[] parameters)
{
using (var rowsContext = new SGDCEntities())
{
return(rowsContext.Database.ExecuteSqlCommand(sql, parameters));
}
}
/// <summary> 获取所有数据 </summary>
/// <returns>对象List</returns>
public virtual List <TEntity> GetAll()
{
using (var rowsContext = new SGDCEntities())
{
return(rowsContext.Set <TEntity>().ToList());
}
}
/// <summary> 获取分页数据 </summary>
/// <param name="pageIndex">页码（从0开始）</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="filters">过滤条件List，多个条件之间为And关系</param>
/// <param name="orderByFields">排序字段(可多个)</param>
/// <returns>ReturnData对象，其中rows为数据List，total为总记录数</returns>
public virtual ReturnData <TEntity> GetPaged(int pageIndex, int pageSize, List <Filter> filters, List <SortField> orderByFields)
{
using (var rowsContext = new SGDCEntities())
{
var query = rowsContext.Set <TEntity>().AsQueryable();
var whereClauseExp = ConvertToWhereClauseExpression(filters);
if (whereClauseExp != null)
{
query = query.Where(whereClauseExp);
}
if (orderByFields == null || orderByFields.Count == 0)
{
var orderByField = GetPrimaryKey(rowsContext);
orderByFields = new List <SortField> {
new SortField(orderByField, true)
};
}
query = AppendOrderByFields(query, orderByFields);
var ReturnData = new ReturnData <TEntity>();
ReturnData.total = query.Count();
ReturnData.rows = query.Skip(pageSize * pageIndex).Take(pageSize).ToList();
return(ReturnData);
}
}
/// <summary> 获取对象 </summary>
/// <param name="keyValues">主键值</param>
/// <returns></returns>
public virtual TEntity Get(params object[] keyValues)
{
using (var rowsContext = new SGDCEntities())
{
return(rowsContext.Set <TEntity>().Find(keyValues));
}
}
/// <summary> 更新对象 </summary>
/// <param name="item">要更新的对象</param>
public virtual void Modify(TEntity item)
{
if (item != (TEntity)null)
{
using (var rowsContext = new SGDCEntities())
{
rowsContext.Entry <TEntity>(item).State = System.Data.EntityState.Modified;
// rowsContext.SaveChanges();
try
{
rowsContext.SaveChanges();
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
String Response = "Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage;
string s = "";
}
}
}
}
}
}
public virtual string GetPrimaryKey(SGDCEntities rowsContext)
{
var objectContext = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)rowsContext).ObjectContext;
var set = objectContext.CreateObjectSet <TEntity>();
string keyName = set.EntitySet.ElementType.KeyMembers.Select(k => k.Name).FirstOrDefault();
return(keyName);
}
/// <summary> 删除对象 </summary>
/// <param name="item">要删除的对象</param>
public virtual void Remove(TEntity item)
{
if (item != null)
{
using (var rowsContext = new SGDCEntities())
{
rowsContext.Entry <TEntity>(item).State = System.Data.EntityState.Deleted;
rowsContext.SaveChanges();
}
}
}
/// <summary> 批量新增，完成后返回保存的记录条数 </summary>
/// <param name="items">要新增的对象List</param>
/// <returns>保存的记录条数</returns>
public virtual int BatchAdd(List <TEntity> items)
{
using (var rowsContext = new SGDCEntities())
{
foreach (var item in items)
{
rowsContext.Set <TEntity>().Add(item);
}
return(rowsContext.SaveChanges());
}
}
/// <summary> 删除对象 </summary>
/// <param name="keyValues">对象的主键值</param>
public virtual void Remove(params object[] keyValues)
{
using (var rowsContext = new SGDCEntities())
{
var entity = rowsContext.Set <TEntity>().Find(keyValues);
if (entity != null)
{
rowsContext.Set <TEntity>().Remove(entity);
rowsContext.SaveChanges();
}
}
}
/// <summary> 新增对象，完成后返回保存的记录条数 </summary>
/// <param name="item">要新增的对象</param>
/// <returns>保存的记录条数</returns>
public virtual int Add2(TEntity item)
{
if (item != (TEntity)null)
{
using (var rowsContext = new SGDCEntities())
{
rowsContext.Set <TEntity>().Add(item);
return(rowsContext.SaveChanges());
}
}
return(0);
}
/// <summary> 获取符合过滤条件的记录 </summary>
/// <param name="filters">过滤条件List</param>
/// <returns>对象List</returns>
//public virtual List<TEntity> GetByFilter(List<string> filters)
//{
// using (var rowsContext = new SGDCEntities())
// {
// var type = typeof(TEntity);
// string strSql = string.Format(" select * from {0} where 1=1", type.Name);
// strSql = filters.Aggregate(strSql, (current, t) => current + (" " + t));
// return rowsContext.rowsbase.SqlQuery<TEntity>(strSql, "").ToList<TEntity>();
// }
//}
/// <summary> 获取符合过滤条件的记录 </summary>
/// <param name="filters">过滤条件List，多个条件之间为And关系</param>
/// <returns>对象List</returns>
public virtual List <TEntity> GetByFilter(List <Filter> filters)
{
using (var rowsContext = new SGDCEntities())
{
var query = rowsContext.Set <TEntity>().AsQueryable();
var whereClauseExp = ConvertToWhereClauseExpression(filters);
if (whereClauseExp != null)
{
query = query.Where(whereClauseExp);
}
return(query.ToList());
}
}
/// <summary> 获取符合过滤条件的记录 </summary>
/// <param name="filters">过滤条件List，多个条件之间为And关系</param>
/// <param name="orderByField">排序字段</param>
/// <param name="ascending">是否升序，true为升序，false为降序</param>
/// <returns>对象List</returns>
public virtual List <TEntity> GetByFilter(List <Filter> filters, string orderByField, bool ascending)
{
using (var rowsContext = new SGDCEntities())
{
var query = rowsContext.Set <TEntity>().AsQueryable();
if (string.IsNullOrWhiteSpace(orderByField))
{
orderByField = GetPrimaryKey(rowsContext);
}
query = AppendWhereClauseAndOrderByField(query, filters, orderByField, ascending);
return(query.ToList());
}
}
public virtual List <TEntity> GetPaged(int pageIndex, int pageSize, Expression <Func <TEntity, bool> > condition, string orderByField, bool ascending)
{
using (var rowsContext = new SGDCEntities())
{
var type = typeof(TEntity);
var property = type.GetProperty(orderByField);
var parameter = Expression.Parameter(type, "p");
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
var query = rowsContext.Set <TEntity>().Where(condition);
string methodName = ascending ? "OrderBy" : "OrderByDescending";
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName, new Type[] { type, property.PropertyType }, query.Expression, Expression.Quote(orderByExp));
query = query.Provider.CreateQuery <TEntity>(resultExp);
return(query.Skip(pageSize * pageIndex).Take(pageSize).ToList());
}
}
/// <summary> 获取分页数据 </summary>
/// <param name="pageIndex">页码（从0开始）</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="filters">过滤条件List，多个条件之间为And关系</param>
/// <param name="orderByField">排序字段</param>
/// <param name="ascending">是否升序，true为升序，false为降序</param>
/// <returns>ReturnData对象，其中rows为数据List，total为总记录数</returns>
public virtual ReturnData <TEntity> GetPaged(int pageIndex, int pageSize, List <Filter> filters, string orderByField, bool ascending)
{
using (var rowsContext = new SGDCEntities())
{
var query = rowsContext.Set <TEntity>().AsQueryable();
if (string.IsNullOrWhiteSpace(orderByField))
{
orderByField = GetPrimaryKey(rowsContext);
}
query = AppendWhereClauseAndOrderByField(query, filters, orderByField, ascending);
var ReturnData = new ReturnData <TEntity>();
ReturnData.total = query.Count();
ReturnData.rows = query.Skip(pageSize * pageIndex).Take(pageSize).ToList();
return(ReturnData);
}
}
public virtual List <TEntity> GetPaged <KProperty>(int pageIndex, int pageCount, System.Linq.Expressions.Expression <Func <TEntity, KProperty> > orderByExpression, bool ascending)
{
using (var rowsContext = new SGDCEntities())
{
var set = rowsContext.Set <TEntity>();
if (ascending)
{
return(set.OrderBy(orderByExpression)
.Skip(pageCount * pageIndex)
.Take(pageCount).ToList());
}
else
{
return(set.OrderByDescending(orderByExpression)
.Skip(pageCount * pageIndex)
.Take(pageCount).ToList());
}
}
}