/// <summary> /// 查询测试数据. /// </summary> /// <param name="page">当前的页.</param> /// <param name="pageSize">每页数据条数.</param> /// <param name="group">分组ID(为 null 时查所有数据).</param> /// <returns></returns> public PaginatedCollection<dynamic> Query(int page, int pageSize, int? group) { DbCommandBuilder cb = new DbCommandBuilder(); SelectBlock sb = cb.Select(Fun.Count("*")).From( fm.Table(ItemsTable, "itm"), fm.LeftJoin(GROUP_TABLE, "grp").ON(td.Field("itm", "GroupId") == td.Field("grp", "GroupId")) ); List<object> condition = new List<object>(); if (group != null) { condition.Add(td.Field("itm", "GroupId") == group.Value); sb.Where(condition.ToArray()); } object count = db.ExecuteScalar(cb); int total = (count == null || count == DBNull.Value) ? 0 : Convert.ToInt32(count); if (total < 1) return null; sb = cb.Select(td.Field("itm", "TestId"), td.Field("grp", "GroupName"), td.Field("itm", "TestName"), td.Field("itm", "TestAge"), td.Field("itm", "TestSex") ).From( fm.Table(ItemsTable, "itm"), fm.LeftJoin(GROUP_TABLE, "grp").ON(td.Field("itm", "GroupId") == td.Field("grp", "GroupId")) ); if (condition.Count > 0) sb.Where(condition.ToArray()); sb.OrderBy(td.Field("itm", "TestId"), OrderByMode.DESC).Paging(pageSize, page); List<dynamic> entities = db.ExecuteDynamicEntity(cb); return new PaginatedCollection<dynamic>(total, page, entities); }
public SpeedDataTable GetAllGroup() { DbCommandBuilder Command = new DbCommandBuilder(); Command.Select(td.Field("*")).From(TableName).OrderBy(td.Field("GroupId"), OrderByMode.ASC); return(DataEngine.CurrentEngine.ExecuteQuery(Command)); }
public Dictionary <string, object> GetDetails(int dataId) { DbCommandBuilder Command = new DbCommandBuilder(); Command.Select(td.Field("*")).From(fm.Table(TABLE_NAME)).Where(td.Field("TestId") == dataId); IDataReader Rd = DataEngine.CurrentEngine.ExecuteReader(Command); if (Rd == null) { return(null); } Dictionary <string, object> data = new Dictionary <string, object>(); while (Rd.Read()) { data.Add("data_id", Rd["TestId"]); data.Add("group_id", Rd["GroupId"]); data.Add("name", Rd["TestName"]); data.Add("age", Rd["TestAge"]); data.Add("sex", Rd["TestSex"]); //data.Add("photo", Rd["TestPhoto"]); } Rd.Close(); Rd.Dispose(); return(data); }
/// <summary> /// 查询所有分组数据. /// </summary> /// <returns></returns> public List <dynamic> Query() { DbCommandBuilder cb = new DbCommandBuilder(); cb.Select(td.Field("GroupId"), td.Field("GroupName")).From(tableName).OrderBy(td.Field("GroupId"), OrderByMode.DESC); return(db.ExecuteDynamicEntity(cb)); }
/// <summary> /// 获取指定记录的图片数据. /// </summary> /// <param name="dataId">记录ID.</param> /// <returns></returns> public byte[] GetPictureData(int dataId) { DbCommandBuilder cb = new DbCommandBuilder(); cb.Select(td.Field("TestPhoto")).From(ItemsTable).Where(td.Field("TestId") == dataId); byte[] result = db.ExecuteScalar(cb) as byte[]; return (result == null) ? new byte[0] : result; }
/// <summary> /// 获取指定记录的详细信息. /// </summary> /// <param name="dataId">记录ID.</param> /// <returns></returns> public Dictionary<string, object> Details(int dataId) { DbCommandBuilder cb = new DbCommandBuilder(); cb.Select(td.Field("TestId"), td.Field("GroupId"), td.Field("TestName"), td.Field("TestAge"), td.Field("TestSex")) .From(ItemsTable).Where(td.Field("TestId") == dataId); IDataReader Rd = db.ExecuteReader(cb); if (Rd == null) throw new Exception(db.DBA.Error != null ? db.DBA.Error.Message : "读取数据是产生了未知的错误."); Dictionary<string, object> data = null; using (Rd) { object tmp = null; if (Rd.Read()) { data = new Dictionary<string, object>(); data.Add("TestId", Convert.ToInt32(Rd["TestId"])); data.Add("GroupId", Convert.ToInt32(Rd["GroupId"])); data.Add("TestName", Rd["TestName"] as string); tmp = Rd["TestAge"]; data.Add("TestAge", (tmp == null || tmp == DBNull.Value) ? 0f : Convert.ToSingle(tmp)); data.Add("TestSex", Rd["TestSex"] as string); } Rd.Close(); } return data; }
/// <summary> /// 获取总记录数量。 /// </summary> /// <param name="groupId"></param> /// <returns></returns> public int GetTotalCount(int groupId) { DbCommandBuilder Command = new DbCommandBuilder(); Command.Select(Fun.Count("*")).From(fm.Table(TABLE_NAME)).Where(td.Field("GroupId") == groupId); object result = DataEngine.CurrentEngine.ExecuteScalar(Command); return((result == null || result == DBNull.Value) ? 0 : Convert.ToInt32(result)); }
/// <summary> /// 查询指定实体对应的表,并返回数据集合. /// </summary> /// <typeparam name="TEntity">对应数据库表的实体类型.</typeparam> /// <param name="selector">返回查询条件的方法.</param> /// <exception cref="NotSupportedException">当<c>TEntity</c>指定的实体类型未标记<see cref="EntityTableAttribute"/>属性时引发该异常</exception> /// <exception cref="Exception">当数据库查询过程产生错误时引发该异常.</exception> /// <returns></returns> protected List <TEntity> Select <TEntity>(Func <TEntityAgent, object> selector) where TEntity : DataEntity, new() { EntityTableAttribute entityTable = GetTableAttribute(); DbCommandBuilder Command = new DbCommandBuilder(); TEntityAgent agent = new TEntityAgent(); agent.TableContext = this; object Conditions = selector(agent); if (Conditions == null) { Command.Select(agent.GetFieldsArray()).From(entityTable.TableName); } else { Command.Select(agent.GetFieldsArray()).From(entityTable.TableName).Where(Conditions); } return(ExecuteQuery <TEntity>(Command)); }
/// <summary> /// 根据指定的条件查询指定实体对应的表,并返回数据集合. /// </summary> /// <typeparam name="TEntity">对应数据库表的实体类型.</typeparam> /// <param name="action">用于指定查询条件.</param> /// <returns></returns> protected List <TEntity> Select <TEntity>(Action <TEntityAgent, SelectBlock> action) where TEntity : DataEntity, new() { EntityTableAttribute entityTable = GetTableAttribute(); DbCommandBuilder Command = new DbCommandBuilder(); SelectBlock select = Command.Select(td.Field("*")).From(entityTable.TableName); TEntityAgent agent = new TEntityAgent(); agent.TableContext = this; action(agent, select); return(ExecuteQuery <TEntity>(Command)); }
/// <summary> /// 获取照片数据。 /// </summary> /// <param name="dataId"></param> /// <returns></returns> public byte[] GetPhotoData(int dataId) { DbCommandBuilder Command = new DbCommandBuilder(); Command.Select(td.Field("TestPhoto")).From(TABLE_NAME).Where(td.Field("TestId") == dataId); object Data = DataEngine.CurrentEngine.ExecuteScalar(Command); if (Data == null || Data == DBNull.Value) { return(null); } return((byte[])Data); }
/// <summary> /// 搜索测试数据. /// </summary> /// <param name="keywords">关键字.</param> /// <param name="page">当前页.</param> /// <param name="pageSize">每页数据条数.</param> /// <param name="group">分组ID(为 null 时搜索所有数据).</param> /// <returns></returns> public PaginatedCollection<dynamic> Search(string keywords, int page, int pageSize, int? group = null) { DbCommandBuilder cb = new DbCommandBuilder(); object[] condition; if (group == null) { condition = new object[] { td.Field("itm", "TestName").Like(keywords, LikeMatch.Center) | td.Field("itm", "TestSex").Like(keywords, LikeMatch.Center) }; } else { condition = new object[] { td.Field("itm", "GroupId") == group.Value & (td.Field("itm", "TestName").Like(keywords, LikeMatch.Center) | td.Field("itm", "TestSex").Like(keywords, LikeMatch.Center)).Group }; } cb.Select(Fun.Count("*")) .From( fm.Table(ItemsTable, "itm"), fm.LeftJoin(GROUP_TABLE, "grp").ON(td.Field("itm", "GroupId") == td.Field("grp", "GroupId")) ).Where(condition); object count = db.ExecuteScalar(cb); int total = (count == null || count == DBNull.Value) ? 0 : Convert.ToInt32(count); if (total < 1) return null; cb.Select(td.Field("itm", "TestId"), td.Field("grp", "GroupName"), td.Field("itm", "TestName"), td.Field("itm", "TestAge"), td.Field("itm", "TestSex") ).From( fm.Table(ItemsTable, "itm"), fm.LeftJoin(GROUP_TABLE, "grp").ON(td.Field("itm", "GroupId") == td.Field("grp", "GroupId")) ).Where(condition).OrderBy(td.Field("itm", "TestId"), OrderByMode.DESC).Paging(pageSize, page); List<dynamic> entities = db.ExecuteDynamicEntity(cb); return new PaginatedCollection<dynamic>(total, page, entities); }
/// <summary> /// 设置要查询的结果子表达式,并返回 SELECT 表达式树对象. /// </summary> /// <param name="Expressions">要查询的结果表太式(字段信息或函数表达式)</param> /// <returns></returns> public SelectBlock Select(params object[] Expressions) { selectBlock = _dbCommand.Select(); IDescription descr; foreach (object desObject in Expressions) { descr = desObject as IDescription; if (descr != null) // 当返回的字段元素非 IDescription 对象时忽略它(否则命令解无法解释). { selectBlock.AddElement(descr); } } selectBlock.From(tableName); return(selectBlock); }
/// <summary> /// 获取页数。 /// </summary> /// <param name="groupId"></param> /// <param name="pageSize"></param> /// <returns></returns> public int GetPageCount(int groupId, int pageSize) { DbCommandBuilder Command = new DbCommandBuilder(); Command.Select(Fun.Count("*")).From(fm.Table(TABLE_NAME)).Where(td.Field("GroupId") == groupId); object result = DataEngine.CurrentEngine.ExecuteScalar(Command); float count = Convert.ToSingle(GetTotalCount(groupId)); if ((count % pageSize) == 0) { return((int)(count / pageSize)); } else { return((int)(count / pageSize + 1)); } }
public SpeedDataTable GetGroupData(int groupId, int pageSize, int currentPage) { DbCommandBuilder Command = new DbCommandBuilder(); Command.Select(td.Field("t2", "TestId"), td.Field("t1", "GroupName"), td.Field("t2", "TestName"), td.Field("t2", "TestAge"), td.Field("t2", "TestSex")) .From(fm.Table(TABLE_NAME, "t2"), fm.LeftJoin(GRP_TABLE_NAME, "t1") .ON(td.Field("t2", "GroupId") == td.Field("t1", "GroupId")) ) .Where(td.Field("t2", "GroupId") == groupId) .OrderBy(td.Field("t2", "TestId"), OrderByMode.DESC) .Paging(pageSize, currentPage); return(DataEngine.CurrentEngine.ExecuteQuery(Command)); }