/// <summary>
/// 获取一个DataSet
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="cmdType">执行类型</param>
/// <param name="sqlParams">传递的参数</param>
/// <returns></returns>
public System.Data.DataSet GetDataSet(string sql, System.Data.CommandType cmdType, params SqlParameter[] sqlParams)
{
using (var db = DBContext.CreateContext())
{
var constr = db.Database.Connection.ConnectionString;
System.Data.DataSet ds = new System.Data.DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
{
adapter.SelectCommand.CommandType = cmdType;
if (sqlParams != null)
{
adapter.SelectCommand.Parameters.AddRange(sqlParams);
}
adapter.Fill(ds);
return(ds);
}
}
}
/// <summary>
/// 获取脉冲编号
/// </summary>
/// <returns></returns>
public int PulseNo(string Name)
{
int result = 0;
//第二，可以用这种方式声明，简单明了
SqlParameter[] m_parms = new SqlParameter[2]
{
new SqlParameter("@Name", Name),
new SqlParameter("@pnum", result)
};
m_parms[1].Direction = System.Data.ParameterDirection.Output;
string sql2 = $"exec Pro_GetPulse @Name,output @pnum";
using (var db = DBContext.CreateContext())
{
db.Database.ExecuteSqlCommand(sql2, m_parms);
db.Database.Log = (x) => Log.Write(LogLevel.Info, x); ///这个是获取db的sql执行代码，内部委托
return(result = Convert.ToInt32(m_parms[1].Value));
}
}
/// <summary>
/// 更新指定属性的记录
/// </summary>
/// <param name="model">模型</param>
/// <param name="propertys">属性</param>
public int UpdateEntity(T model, string[] propertys)
{
using (var db = DBContext.CreateContext())
{
if (propertys == null || propertys.Length == 0)
{
throw new Exception("当前更新的实体必须至少指定一个字段名称");
}
db.Configuration.ValidateOnSaveEnabled = false;
DbEntityEntry entry = db.Entry(model);
entry.State = System.Data.Entity.EntityState.Unchanged;
foreach (var item in propertys)
{
entry.Property(item).IsModified = true;
}
return(db.SaveChanges());
}
}
/// <summary>
/// 数据分页
/// </summary>
/// <param name="where">条件的lambda</param>
/// <param name="order">排序的lambda</param>
/// <param name="total">总数，返回值</param>
/// <param name="pageSize">每一页数据的大小</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="isAsc">排序方式，true为正序，false为倒序</param>
/// <returns></returns>
public IList <T> GetPageList <S>(Expression <Func <T, bool> > where, Func <T, S> order,
out int total, int pageSize = 10, int pageIndex = 1, bool isAsc = false)
{
using (DBContext db = DBContext.CreateContext())
{
var temp = db.Set <T>().Where <T>(where).AsQueryable();
total = temp.Count();
if (isAsc)
{
return(temp.OrderBy <T, S>(order)
.Skip <T>((pageIndex - 1) * pageSize)
.Take <T>(pageSize).ToList());
}
else
{
return(temp.OrderByDescending(order)
.Skip <T>((pageIndex - 1) * pageSize)
.Take <T>(pageSize).ToList());
}
}
}
/// <summary>
/// 批量插入
/// </summary>
/// <typeparam name="T">泛型集合的类型</typeparam>
/// <param name="tableName">将泛型集合插入到本地数据库表的表名</param>
/// <param name="list">要插入大泛型集合</param>
public void BulkInsert <S>(string tableName, List <S> list)
{
using (var db = DBContext.CreateContext())
{
//db.Database.Connection.Open(); //打开Coonnection连接
using (var bulkCopy = new SqlBulkCopy(db.Database.Connection.ConnectionString))
{
bulkCopy.BatchSize = list.Count;
bulkCopy.DestinationTableName = tableName;
var table = new DataTable();
var props = TypeDescriptor.GetProperties(typeof(T))
.Cast <PropertyDescriptor>()
.Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
.ToArray();
foreach (var propertyInfo in props)
{
bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
}
var values = new object[props.Length];
foreach (var item in list)
{
for (var i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
//需要匹配指定列的情况下
//bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
table.Rows.Add(values);
}
bulkCopy.WriteToServer(table);
}
}
}
/// <summary>
/// 返回指定类型的分页
/// </summary>
/// <param name="where">条件，不能为null</param>
/// <param name="order">排序，不能为null</param>
/// <param name="selector">返回对象，不能为null</param>
/// <param name="total">总数</param>
/// <param name="isAsc">是否升序，默认升序</param>
/// <param name="pageSize">每页数据大小，默认20</param>
/// <param name="pageIndex">当前页数，默认1</param>
/// <returns></returns>
public IList <S> GetEntityPageList <S, K>(Expression <Func <T, bool> > where, Expression <Func <T, K> > order,
Expression <Func <T, S> > selector, out int total, bool isAsc = true, int pageSize = 20, int pageIndex = 1)
where S : class, new()
{
using (var db = DBContext.CreateContext())
{
var query = db.Set <T>().AsQueryable();
query = query.Where(where);
if (isAsc)
{
query = query.OrderBy(order);
}
else
{
query = query.OrderByDescending(order);
}
total = query.Count();
return(query.Skip <T>((pageIndex - 1) * pageSize)
.Take <T>(pageSize)
.Select(selector)
.ToList());
}
}
/// <summary>
/// 获取一个实体,实体数量大于1将抛出异常
/// </summary>
/// <param name="where">条件</param>
/// <param name="order">排序</param>
/// <param name="isAsc">是否升序</param>
/// <returns></returns>
public T SingleOrDefault <S>(Expression <Func <T, bool> > where = null, Expression <Func <T, S> > order = null, bool isAsc = true)
{
using (var db = DBContext.CreateContext())
{
var m_query = db.Set <T>().AsQueryable <T>();
if (where != null)
{
m_query = m_query.Where(where);
}
if (order != null)
{
if (isAsc)
{
m_query = m_query.OrderBy(order);
}
else
{
m_query = m_query.OrderByDescending(order);
}
}
return(m_query.SingleOrDefault());
}
}
/// <summary>
/// 返回指定类型的集合
/// </summary>
/// <param name="where">条件</param>
/// <param name="order">排序</param>
/// <param name="isAsc">是否升序</param>
/// <param name="selector">返回指定对象</param>
/// <returns></returns>
public IList <S> GetEntities <S, K>(Expression <Func <T, bool> > where = null, Expression <Func <T, K> > order = null,
bool isAsc = true, Expression <Func <T, S> > selector = null)
{
using (var db = DBContext.CreateContext())
{
var query = db.Set <T>().AsQueryable();
if (where != null)
{
query = query.Where(where);
}
if (order != null)
{
if (isAsc)
{
query = query.OrderBy(order);
}
else
{
query = query.OrderByDescending(order);
}
}
return(query.Select(selector).ToList());
}
}
/// <summary>
/// 获取指定类型的第一个实体或null
/// </summary>
/// <param name="where">条件</param>
/// <param name="order">排序</param>
/// <param name="isAsc">是否升序，默认升序</param>
/// <param name="selector">要返回对象的表达式</param>
/// <returns></returns>
public S GetFirstOrDefaultEntity <S, K>(Expression <Func <T, bool> > where = null, Expression <Func <T, K> > order = null,
bool isAsc = true, Expression <Func <T, S> > selector = null) where S : class
{
using (var db = DBContext.CreateContext())
{
var query = db.Set <T>().AsQueryable();
if (where != null)
{
query = query.Where(where);
}
if (order != null)
{
if (isAsc)
{
query = query.OrderBy(order);
}
else
{
query = query.OrderByDescending(order);
}
}
return(query.Select(selector).FirstOrDefault());
}
}