/// <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));
}
