/// <summary> /// 根据条件分页查询 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="page">页码</param> /// <param name="size">页面大小</param> /// <param name="where">linq表达式</param> /// <returns></returns> public static DataGrid <T> SelectPage <T, TKey>(int page, int size, Expression <Func <T, TKey> > orderby, Expression <Func <T, bool> > where = null) where T : class { try { where = where ?? (p => true); var total = Count(where); if (total <= 0) { return new DataGrid <T>() { total = 0, rows = new List <T>(), emptyMsg = "没有查询到相关数据" } } ; DataGrid <T> grid = new DataGrid <T>(); grid.total = total; grid.emptyMsg = "查询成功"; using (var db = new DemoEntities()) { grid.rows = db.Set <T>().Where(where).OrderBy(orderby).Skip((page - 1) * size).Take(size).ToList(); } return(grid); } catch (Exception ex) { WriteException(ex); return(new DataGrid <T>() { total = 0, rows = new List <T>(), emptyMsg = "查询故障,请联系开发人员" }); } }
/// <summary> /// 更新指定字段 /// </summary> /// <param name="model">实体</param> /// <param name="fileds">更新字段数组</param> public static int Update <T>(T model, params string[] fileds) where T : class { try { if (model == null || fileds == null) { return(0); } using (var db = new DemoEntities()) { db.Set <T>().Attach(model); var SetEntry = ((IObjectContextAdapter)db).ObjectContext.ObjectStateManager.GetObjectStateEntry(model); foreach (var filed in fileds) { SetEntry.SetModifiedProperty(filed); } return(db.SaveChanges()); } } catch (Exception ex) { WriteException(ex); return(0); } }
/// <summary> /// 按条件删除数据 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="where">linq</param> /// <returns>删除行数</returns> public static int Delete <T>(Expression <Func <T, bool> > where) where T : class { try { if (where == null) { throw new ArgumentNullException(); } int rows = 0; using (var db = new DemoEntities()) { var list = db.Set <T>().Where(where).ToList(); foreach (var item in list) { db.Set <T>().Remove(item); rows += db.SaveChanges(); } } return(rows); } catch (Exception ex) { WriteException(ex); return(0); } }
public static DemoEntities Entity() { try { using (var db = new DemoEntities()) { return(db); } } catch (Exception ex) { WriteException(ex); return(null); } }
/// <summary> /// 执行SQL,带事务 /// </summary> /// <param name="transactionalBehavior">对于此命令控制事务的创建</param> /// <param name="sql">命令字符串</param> /// <param name="parameters">要应用于命令字符串的参数</param> public static int ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, string sql, params object[] parameters) { try { using (var db = new DemoEntities()) { return(db.Database.ExecuteSqlCommand(transactionalBehavior, sql, parameters)); } } catch (Exception ex) { WriteException(ex); return(0); } }
/// <summary> /// 创建一个原始 SQL 查询,该查询将返回给定泛型类型的元素。类型可以是包含与从查询返回的列名匹配的属性的任何类型,也可以是简单的基元类型。该类型不必是实体类型。即使返回对象的类型是实体类型,上下文也决不会跟踪此查询的结果。使用 /// System.Data.Entity.DbSet`1.SqlQuery(System.String,System.Object[]) 方法可返回上下文跟踪的实体。与接受 /// SQL 的任何 API 一样,对任何用户输入进行参数化以便避免 SQL 注入攻击是十分重要的。您可以在 SQL 查询字符串中包含参数占位符,然后将参数值作为附加参数提供。您提供的任何参数值都将自动转换为 /// DbParameter。context.Database.SqlQuery<Post>("SELECT * FROM dbo.Posts WHERE /// Author = @p0", userSuppliedAuthor); 或者,您还可以构造一个 DbParameter 并将它提供给 SqlQuery。这允许您在 /// SQL 查询字符串中使用命名参数。context.Database.SqlQuery<Post>("SELECT * FROM dbo.Posts /// WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor)); /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="sql">查询字符串</param> /// <param name="parameters">要应用于命令字符串的参数</param> public static IEnumerable <T> View <T>(string sql, params object[] parameters) { try { using (var db = new DemoEntities()) { return(db.Database.SqlQuery <T>(sql, parameters).ToList()); } } catch (Exception ex) { WriteException(ex); return(new List <T>()); } }
/// <summary> /// 根据条件查询数据量,返回-1表示错误 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="where">linq表达式</param> public static int Count <T>(Expression <Func <T, bool> > where) where T : class { try { where = where ?? (p => true); using (var db = new DemoEntities()) { return(db.Set <T>().Count(where)); } } catch (Exception ex) { WriteException(ex); return(-1); } }
/// <summary> /// 根据条件查询数据 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="where">linq表达式</param> public static IList <T> Select <T>(Expression <Func <T, bool> > where = null) where T : class { try { where = where ?? (p => true); using (var db = new DemoEntities()) { return(db.Set <T>().Where(where).ToList()); } } catch (Exception ex) { WriteException(ex); return(new List <T>()); } }
/// <summary> /// 新增多条数据 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="models">多条模型数据</param> public static int InsertList <T>(IEnumerable <T> models) where T : class { try { int rows = 0; using (var db = new DemoEntities()) { db.Set <T>().AddRange(models); rows += db.SaveChanges(); } return(rows); } catch (Exception ex) { WriteException(ex); return(0); } }
/// <summary> /// 删除单条数据 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="model">模型数据</param> /// <returns>删除行数</returns> public static int Delete <T>(T model) where T : class { try { int rows = 0; using (var db = new DemoEntities()) { db.Entry(model).State = EntityState.Deleted; rows += db.SaveChanges(); } return(rows); } catch (Exception ex) { WriteException(ex); return(0); } }
public static string Test() { try { var connStr = new DemoEntities().Database.Connection.ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); if (conn.State == System.Data.ConnectionState.Open) { return("服务器连接正常"); } return("服务器连接异常"); } } catch (Exception ex) { return(string.Format("数据库连接失败,{0}", ex.Message)); } }
/// <summary> /// 更新单条数据 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="model">模型数据</param> public static int Update <T>(T model) where T : class { try { int rows = 0; using (var db = new DemoEntities()) { if (db.Entry(model).State == EntityState.Detached) { db.Set <T>().Attach(model); db.Entry(model).State = EntityState.Modified; } rows += db.SaveChanges(); } return(rows); } catch (Exception ex) { WriteException(ex); return(0); } }
/// <summary> /// 使用查询条件更新数据 /// </summary> /// <typeparam name="T">表的实体类</typeparam> /// <param name="action">更新数据动作</param> /// <param name="where">表达式</param> public static int Update <T>(Action <T> action, Expression <Func <T, bool> > where) where T : class { try { int rows = 0; using (var db = new DemoEntities()) { var list = db.Set <T>().Where(where); foreach (var model in list) { action.Invoke(model); db.Entry(model).State = EntityState.Modified; } rows += db.SaveChanges(); } return(0); } catch (Exception ex) { WriteException(ex); return(0); } }