Sqlserver数据库帮助类(EFTools)
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Linq.Expressions;
namespace QsDemo
{
public class EFTools<EFContxt> where EFContxt : DbContext, new()
{
/// <summary>
/// 获取数据库上下文
/// </summary>
/// <returns></returns>
private EFContxt GetEFContxt()
{
try
{
return new EFContxt();
}
catch (Exception)
{
return null;
}
}
public int GetFYDataAll<T>(int PageIndex, int PageCount, int zcount)
{
try
{
int Count = 0;
Count = zcount / PageCount;
int P = 0;
if (Count > 0)
{
P = (zcount % PageCount) + 1;
}
else if (Count == 0)
{
P = (zcount % PageCount);
}
return P;
}
catch (Exception)
{
return -1;
}
}
/// <summary>
/// 分页总数据量
/// </summary>
/// <returns></returns>
public int GetEntityCount<T>(Expression<Func<T, bool>> whereExp) where T : class
{
try
{
EFContxt db = GetEFContxt();
if (whereExp == null)
{
return db.Set<T>().ToList().Count;
}
else
{
return db.Set<T>().Where(whereExp).ToList().Count;
}
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// 获取所有数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public List<T> GetAllEneity<T>() where T : class
{
try
{
EFContxt db = GetEFContxt();
return db.Set<T>().ToList<T>();
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 按条件查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereExp"></param>
/// <returns></returns>
public List<T> GetEntityByWhere<T>(Expression<Func<T, bool>> whereExp) where T : class
{
try
{
EFContxt db = GetEFContxt();
List<T> list = db.Set<T>().Where(whereExp).ToList();
return list;
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 删除单个对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool Reomve<T>(T t) where T : class
{
try
{
EFContxt db = GetEFContxt();
db.Set<T>().Attach(t);
db.Set<T>().Remove(t);
return db.SaveChanges() > 0;
}
catch (Exception ex)
{
return false;
}
}
public bool Reomve<T>(List<T> t) where T : class
{
try
{
EFContxt db = GetEFContxt();
t.ForEach(n =>
{
db.Set<T>().Attach(n);
db.Set<T>().Remove(n);
});
return db.SaveChanges() >= 0;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool AddEntity<T>(List<T> t) where T : class
{
try
{
EFContxt db = new EFContxt();
t.ForEach(r =>
{
db.Set<T>().Add(r);
});
return db.SaveChanges() > 0;
}
catch (Exception)
{
return false;
}
}
public bool AddEntity<T>(T t) where T : class
{
try
{
EFContxt db = new EFContxt();
db.Set<T>().Add(t);
return db.SaveChanges() > 0;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool UpdateEntity<T>(T t) where T : class
{
try
{
EFContxt db = GetEFContxt();
db.Entry(t).State = System.Data.EntityState.Modified;
return db.SaveChanges() > 0;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T">要查询的实体</typeparam>
/// <param name="pageIndex">当前要查询的页标</param>
/// <param name="pageCount">每页显示的行数</param>
/// <returns></returns>
public List<T> GetEntityByPage<T, Tkey>(int pageIndex, int pageCount, Expression<Func<T, Tkey>> orderbyExp, Expression<Func<T, bool>> whereExp) where T : class
{
try
{
EFContxt db = GetEFContxt();
//200 1 10 (2 ,10)
//List<T> list = db.Set<T>().Take(pageIndex*pageCount).Skip((pageIndex-1)*pageCount).ToList();
//200 1 10 (2 ,10)
if (whereExp == null)
{
return db.Set<T>().OrderBy(orderbyExp).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
else
{
return db.Set<T>().OrderBy(orderbyExp).Where(whereExp).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList();
}
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 分页总数据量
/// </summary>
/// <returns></returns>
public int GetEntityByPage<T>(Expression<Func<T, bool>> whereExp) where T : class
{
try
{
EFContxt db = GetEFContxt();
if (whereExp == null)
{
return db.Set<T>().ToList().Count;
}
else
{
return db.Set<T>().Where(whereExp).ToList().Count;
}
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// 查询单个
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereExp"></param>
/// <returns></returns>
public T GetSingleEntity<T>(Expression<Func<T, bool>> whereExp) where T : class
{
try
{
EFContxt db = GetEFContxt();
// 第一种
//T t = db.Set<T>().SingleOrDefault(whereExp);
//第二种
//T t = db.Set<T>().Where(whereExp).SingleOrDefault();
//第三种
T t = db.Set<T>().Where(whereExp).FirstOrDefault();
//第四种
// db.Set<T>().FirstOrDefault(whereExp);
return t;
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// 获取单个实体(条件的最后一条)
/// </summary>
/// <typeparam name="T">泛型实体类型 在调用前必须制定 且只能为引用类型</typeparam>
/// <param name="whereProc">过滤的表达式</param>
/// <returns>实体</returns>
public T GetSingleEntity<T, Tkey>(Expression<Func<T, bool>> whereProc, Expression<Func<T, Tkey>> orderProc, string desc) where T : class
{
try
{
using (EFContxt ef = GetEFContxt())
{
if (desc == "desc")
{
return ef.Set<T>().Where(whereProc).OrderByDescending(orderProc).FirstOrDefault();
}
return ef.Set<T>().Where(whereProc).OrderBy(orderProc).FirstOrDefault();
// return ef.Set<T>().FirstOrDefault(whereProc);
}
}
catch (Exception)
{
return null;
}
}
}
}