private object ExecuteScalar <TResult>(QuerySection <TResult> query)
where TResult : Entity
{
try
{
string queryString = query.QueryString;
string cacheKey = GetCacheKey(queryString, query.Parameters);
object obj = GetCache <TResult>("GetObject", cacheKey);
if (obj != null)
{
return(obj);
}
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(queryString, query.Parameters);
object newobj = dbProvider.ExecuteScalar(queryCommand, dbTran);
SetCache <TResult>("GetObject", cacheKey, newobj);
return(newobj);
}
catch
{
throw;
}
}
private SourceTable ExecuteDataTable <TResult>(QuerySection <TResult> query)
where TResult : Entity
{
try
{
string queryString = query.QueryString;
string cacheKey = GetCacheKey(queryString, query.Parameters);
object obj = GetCache <TResult>("DataTable", cacheKey);
if (obj != null)
{
return((SourceTable)obj);
}
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(queryString, query.Parameters);
using (DataTable dataTable = dbProvider.ExecuteDataTable(queryCommand, dbTran))
{
dataTable.TableName = typeof(TResult).Name;
SourceTable table = new SourceTable(dataTable);
SetCache <TResult>("DataTable", cacheKey, table);
return(table);
}
}
catch
{
throw;
}
}
/// <summary>
/// 返回一个Query节
/// </summary>
/// <returns></returns>
public QuerySection From(QueryCreator creator)
{
if (creator.Table == null)
{
throw new DataException("用创建器操作时，表不能为null！");
}
FromSection <ViewEntity> f = this.From <ViewEntity>(creator.Table);
if (creator.IsRelation)
{
foreach (TableJoin join in creator.Relations.Values)
{
if (join.Type == JoinType.LeftJoin)
{
f.LeftJoin <ViewEntity>(join.Table, join.Where);
}
else if (join.Type == JoinType.RightJoin)
{
f.RightJoin <ViewEntity>(join.Table, join.Where);
}
else
{
f.InnerJoin <ViewEntity>(join.Table, join.Where);
}
}
}
QuerySection <ViewEntity> query = f.Select(creator.Fields).Where(creator.Where)
.OrderBy(creator.OrderBy);
return(new QuerySection(query));
}
internal FromSection(Table table)
: this()
{
this.tableName = table == null ? fromTable.Name : table.Name;
fromTable.As(this.tableName);
this.query = new QuerySection <T>(this);
}
internal FromSection(string tableName, string relation, IList <Entity> list)
{
this.tableName = tableName;
this.relation = relation;
this.entityList.AddRange(list);
this.query = new QuerySection <T>(this);
}
private DataSet ExecuteDataSet <TResult>(QuerySection <TResult> query)
where TResult : Entity
{
try
{
string queryString = query.QueryString;
string cacheKey = GetCacheKey(queryString, query.Parameters);
object obj = GetCache <TResult>("DataTable", cacheKey);
if (obj != null)
{
return((DataSet)obj);
}
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(queryString, query.Parameters);
using (DataSet dataSet = dbProvider.ExecuteDataSet(queryCommand, dbTran))
{
SetCache <TResult>("DataSet", cacheKey, dataSet);
return(dataSet);
}
}
catch
{
throw;
}
}
/// <summary>
/// 联合查询
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public override QuerySection <T> UnionAll(QuerySection <T> query)
{
this.queries.Add(new UnionItem <T> {
Query = query, IsUnionAll = true
});
return(this);
}
/// <summary>
/// 创建分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="itemCount"></param>
/// <param name="skipCount"></param>
/// <returns></returns>
protected internal virtual QuerySection <T> CreatePageQuery <T>(QuerySection <T> query, int itemCount, int skipCount)
where T : Entity
{
if (skipCount == 0)
{
((IPaging)query).Prefix("top " + itemCount);
return(query);
}
else
{
((IPaging)query).Prefix("top " + itemCount);
Field pagingField = query.PagingField;
if ((IField)pagingField == null)
{
throw new MySoftException("SqlServer2000或Access请使用SetPagingField设定分页主键！");
}
QuerySection <T> jquery = query.CreateQuery <T>();
((IPaging)jquery).Prefix("top " + skipCount);
jquery.Select(pagingField);
//如果是联合查询，则需要符值整个QueryString
if (query.UnionQuery)
{
jquery.QueryString = query.QueryString;
}
query.PageWhere = !pagingField.In(jquery);
return(query);
}
}
private SourceReader ExcuteDataReader <TResult>(QuerySection <TResult> query, bool all)
where TResult : Entity
{
try
{
if (all)
{
prefixString = null;
endString = null;
}
string queryString = query.QueryString;
if (this.unionQuery && all)
{
queryString += OrderString;
}
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(queryString, query.Parameters);
return(dbProvider.ExecuteReader(queryCommand, dbTran));
}
catch
{
throw;
}
}
private int GetCount(QuerySection <T> query)
{
string countString = query.CountString;
if (query.unionQuery)
{
countString = "select sum(row_count) as total_row_count from (" + countString + ") tmp_table";
}
string cacheKey = GetCacheKey(countString, this.Parameters);
object obj = GetCache <T>("Count", cacheKey);
if (obj != null)
{
return(DataUtils.ConvertValue <int>(obj));
}
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(countString, query.Parameters);
object value = dbProvider.ExecuteScalar(queryCommand, dbTran);
int ret = DataUtils.ConvertValue <int>(value);
SetCache <T>("Count", cacheKey, ret);
return(ret);
}
/// <summary>
/// UnionQuery构造函数
/// </summary>
/// <param name="query1"></param>
/// <param name="query2"></param>
/// <param name="dbProvider"></param>
/// <param name="dbTran"></param>
/// <param name="isUnionAll"></param>
public UnionQuery(QuerySection <T> query1, QuerySection <T> query2, DbProvider dbProvider, DbTrans dbTran, bool isUnionAll)
: base(query1.FromSection, dbProvider, dbTran)
{
this.queries.Add(new UnionItem <T> {
Query = query1, IsUnionAll = isUnionAll
});
this.queries.Add(new UnionItem <T> {
Query = query2, IsUnionAll = isUnionAll
});
}
private ArrayList <TResult> ExcuteDataListResult <TResult>(QuerySection <T> query, bool all)
{
try
{
string queryString = query.QueryString;
if (this.unionQuery && all)
{
queryString += OrderString;
}
string cacheKey = GetCacheKey(queryString, this.Parameters);
object obj = GetCache <T>("ListObject", cacheKey);
if (obj != null)
{
return((SourceList <TResult>)obj);
}
using (SourceReader reader = ExcuteDataReader(query, all))
{
ArrayList <TResult> list = new ArrayList <TResult>();
if (typeof(TResult) == typeof(object[]))
{
while (reader.Read())
{
List <object> objs = new List <object>();
for (int row = 0; row < reader.FieldCount; row++)
{
objs.Add(reader.GetValue(row));
}
TResult result = (TResult)(objs.ToArray() as object);
list.Add(result);
}
}
else
{
while (reader.Read())
{
list.Add(reader.GetValue <TResult>(0));
}
}
SetCache <T>("ListObject", cacheKey, list);
reader.Close();
return(list);
}
}
catch
{
throw;
}
}
/// <summary>
/// 返回一个分页处理的Page节
/// </summary>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageSection <T> GetPage(int pageSize)
{
QuerySection <T> query = this;
if (this.unionQuery)
{
query = SubQuery();
query.OrderBy(this.orderBy);
}
return(new PageSection <T>(query, pageSize));
}
internal FromSection(DbProvider dbProvider, DbTrans dbTran, Table table)
: this()
{
this.entityList.Add(this.fromEntity);
//从传入的表信息中获取表名
this.tableName = table == null ? fromTable.Name : table.Name;
fromTable.As(this.tableName);
Field pagingField = fromEntity.PagingField;
this.query = new QuerySection <T>(this, dbProvider, dbTran, pagingField);
}
internal FromSection(string tableName, string aliasName)
: this()
{
fromTable.As(aliasName);
this.entityList.Add(this.fromEntity);
this.tableName = tableName;
if (aliasName != null)
{
this.tableName += " {0}" + aliasName + "{1}";
}
this.query = new QuerySection <T>(this);
}
/// <summary>
/// 返回一个分页处理的Page节
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageSection <TEntity> GetPage <TEntity>(int pageSize)
where TEntity : Entity
{
QuerySection <TEntity> query = CreateQuery <TEntity>();
if (this.unionQuery)
{
query = SubQuery <TEntity>();
query.OrderBy(this.orderBy);
}
return(new PageSection <TEntity>(query, pageSize));
}
/// <summary>
/// 返回一个列表
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <returns></returns>
public virtual SourceList <TEntity> ToList <TEntity>()
where TEntity : Entity
{
QuerySection <TEntity> query = CreateQuery <TEntity>();
if (this.unionQuery)
{
query = SubQuery <TEntity>();
query.OrderBy(this.orderBy);
}
return(ExcuteDataList <TEntity>(query, true));
}
/// <summary>
/// 创建一个跟当前相同的子查询
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <returns></returns>
internal QuerySection <TResult> CreateQuery <TResult>()
where TResult : Entity
{
QuerySection <TResult> query = new QuerySection <TResult>(new FromSection <TResult>(fromSection.TableName, fromSection.Relation, fromSection.EntityList), dbProvider, dbTran, pagingField);
query.Where(queryWhere).OrderBy(orderBy).GroupBy(groupBy).Having(havingWhere);
query.Parameters = this.Parameters;
if (fieldSelect)
{
query.Select(fieldList.ToArray());
}
return(query);
}
/// <summary>
/// 生成一个子查询
/// </summary>
/// <returns></returns>
public QuerySection <TSub> SubQuery <TSub>(string aliasName)
where TSub : Entity
{
TSub entity = DataUtils.CreateInstance <TSub>();
string tableName = entity.GetTable().Name;
QuerySection <TSub> query = new QuerySection <TSub>(new FromSection <TSub>(tableName, aliasName), dbProvider, dbTran, pagingField);
query.SqlString = "(" + QueryString + ") " + (aliasName != null ? "{0}" + aliasName + "{1}" : tableName);
query.Parameters = this.Parameters;
if ((IField)this.pagingField == null)
{
query.pagingField = entity.PagingField;
}
List <Field> flist = new List <Field>();
if (aliasName != null)
{
if ((IField)query.pagingField != null)
{
query.pagingField = query.pagingField.At(aliasName);
}
fieldList.ForEach(field =>
{
flist.Add(field.At(aliasName));
});
}
else
{
if ((IField)query.pagingField != null)
{
query.pagingField = query.pagingField.At(tableName);
}
fieldList.ForEach(field =>
{
flist.Add(field.At(tableName));
});
}
//如果当前子查询与上一级子查询类型相同，则直接使用上一级的字段列表
if (typeof(TSub) == typeof(T))
{
query.Select(flist.ToArray());
}
return(query);
}
/// <summary>
/// 返回一个Query节
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public QuerySection <T> From <T>(TableRelation <T> relation)
where T : Entity
{
//重新实例化一个Query
var query = new QuerySection <T>(relation.GetFromSection(), dbProvider, this);
//处理前n条
if (relation.GetTopSize() > 0)
{
query = query.GetTop(relation.GetTopSize());
}
return(query);
}
/// <summary>
/// 返回一个列表
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public SourceList <TEntity> ToList <TEntity>(int startIndex, int endIndex)
where TEntity : Entity
{
if (startIndex <= 0)
{
startIndex = 1;
}
int topItem = endIndex - startIndex + 1;
QuerySection <TEntity> query = CreateQuery <TEntity>();
if (this.unionQuery)
{
query = SubQuery <TEntity>();
query.OrderBy(this.orderBy);
}
return(GetList <TEntity>(query, topItem, endIndex - topItem));
}
/// <summary>
/// 进行Union操作
/// </summary>
/// <param name="query"></param>
/// <param name="isUnionAll"></param>
/// <returns></returns>
private QuerySection <T> Union(QuerySection <T> query, bool isUnionAll)
{
QuerySection <T> q = CreateQuery <T>();
q.QueryString = this.QueryString;
q.CountString = this.CountString;
q.QueryString += " union " + (isUnionAll ? "all " : "") + query.QueryString;
q.CountString += " union " + (isUnionAll ? "all " : "") + query.CountString;
q.unionQuery = true;
//将排序进行合并
OrderByClip order = this.orderBy && query.orderBy;
q.Parameters = query.Parameters;
return(q.OrderBy(order));
}
private SourceReader ExecuteDataReader <TResult>(QuerySection <TResult> query)
where TResult : Entity
{
try
{
string queryString = query.QueryString;
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(queryString, query.Parameters);
return(dbProvider.ExecuteReader(queryCommand, dbTran));
}
catch
{
throw;
}
}
private SourceList <TResult> ExcuteDataList <TResult>(QuerySection <TResult> query, bool all)
where TResult : Entity
{
try
{
string queryString = query.QueryString;
if (this.unionQuery && all)
{
queryString += OrderString;
}
string cacheKey = GetCacheKey(queryString, this.Parameters);
object obj = GetCache <TResult>("ListEntity", cacheKey);
if (obj != null)
{
return((SourceList <TResult>)obj);
}
using (SourceReader reader = ExcuteDataReader(query, all))
{
SourceList <TResult> list = new SourceList <TResult>();
FastCreateInstanceHandler creator = DataUtils.GetFastInstanceCreator(typeof(TResult));
while (reader.Read())
{
TResult entity = (TResult)creator();
entity.SetAllValues(reader);
entity.Attach();
list.Add(entity);
}
SetCache <TResult>("ListEntity", cacheKey, list);
reader.Close();
return(list);
}
}
catch
{
throw;
}
}
/// <summary>
/// 生成一个子查询
/// </summary>
/// <returns></returns>
public virtual QuerySection <TSub> SubQuery <TSub>(string aliasName)
where TSub : Entity
{
TSub entity = CoreHelper.CreateInstance <TSub>();
var tableName = entity.GetTable().Name;
if (aliasName != null)
{
tableName = string.Format("__[{0}]__", aliasName);
}
QuerySection <TSub> query = new QuerySection <TSub>(new FromSection <TSub>(entity.GetTable(), aliasName), dbProvider, dbTran);
query.SqlString = string.Format("({0}) {1}", QueryString, tableName);
query.Parameters = this.Parameters;
query.Select(Field.All.At(entity.GetTable().As(aliasName)));
return(query);
}
internal FromSection(DbProvider dbProvider, DbTrans dbTran, string aliasName)
: this()
{
fromTable.As(aliasName);
this.entityList.Add(this.fromEntity);
this.tableName = fromTable.Name;
Field pagingField = fromEntity.PagingField;
if (aliasName != null)
{
if ((IField)pagingField != null)
{
pagingField = pagingField.At(aliasName);
}
this.tableName += " {0}" + aliasName + "{1}";
}
this.query = new QuerySection <T>(this, dbProvider, dbTran, pagingField);
}
private int GetCount(QuerySection <T> query)
{
string countString = query.CountString;
string cacheKey = GetCacheKey(countString, this.Parameters);
object obj = GetCache <T>("Count", cacheKey);
if (obj != null)
{
return(CoreHelper.ConvertValue <int>(obj));
}
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(countString, query.Parameters);
object value = dbProvider.ExecuteScalar(queryCommand, dbTran);
int ret = CoreHelper.ConvertValue <int>(value);
SetCache <T>("Count", cacheKey, ret);
return(ret);
}
private SourceList <TResult> ExecuteDataList <TResult>(QuerySection <TResult> query)
where TResult : Entity
{
try
{
string queryString = query.QueryString;
string cacheKey = GetCacheKey(queryString, query.Parameters);
object obj = GetCache <TResult>("ListEntity", cacheKey);
if (obj != null)
{
return((SourceList <TResult>)obj);
}
using (SourceReader reader = ExecuteDataReader(queryString, query.Parameters))
{
SourceList <TResult> list = new SourceList <TResult>();
FastCreateInstanceHandler creator = CoreHelper.GetFastInstanceCreator(typeof(TResult));
while (reader.Read())
{
TResult entity = (TResult)creator();
entity.SetDbValues(reader);
entity.Attach();
list.Add(entity);
}
SetCache <TResult>("ListEntity", cacheKey, list);
reader.Close();
return(list);
}
}
catch
{
throw;
}
}
/// <summary>
/// 返回一个实体
/// </summary>
/// <typeparam name="TResult"></typeparam>
/// <returns></returns>
public TEntity ToSingle <TEntity>()
where TEntity : Entity
{
QuerySection <TEntity> query = CreateQuery <TEntity>();
if (this.unionQuery)
{
query = SubQuery <TEntity>();
query.OrderBy(this.orderBy);
}
ISourceList <TEntity> list = GetList <TEntity>(query, 1, 0);
if (list.Count == 0)
{
return(default(TEntity));
}
else
{
return(list[0]);
}
}
private object ExcuteScalar <TResult>(QuerySection <TResult> query, bool all)
where TResult : Entity
{
try
{
if (all)
{
prefixString = null;
endString = null;
}
string queryString = query.QueryString;
if (this.unionQuery && all)
{
queryString += OrderString;
}
string cacheKey = GetCacheKey(queryString, this.Parameters);
object obj = GetCache <TResult>("GetObject", cacheKey);
if (obj != null)
{
return(obj);
}
//添加参数到Command中
queryCommand = dbProvider.CreateSqlCommand(queryString, query.Parameters);
object newobj = dbProvider.ExecuteScalar(queryCommand, dbTran);
SetCache <TResult>("GetObject", cacheKey, newobj);
return(newobj);
}
catch
{
throw;
}
}
