///<summary>
/// Creates a SelectQueryDB, wrapping an ISelectQuery (Decorator pattern)
///</summary>
///<param name="selectQuery"></param>
///<param name="databaseConnection"></param>
public SelectQueryDB(ISelectQuery selectQuery, IDatabaseConnection databaseConnection)
{
Aliases = new Dictionary <string, string>();
_selectQuery = selectQuery;
_databaseConnection = databaseConnection;
SetupAliases();
}
public DataTable Select(ISelectQuery aQuery, OnRowSelect onExecute)
{
if (aQuery == null)
{
aQuery = QueryFactory.CreateSelectQuery();
}
DataRow dr = null;
RowBinder binder = new RowBinder();
int i = 0;
binder.OnBind = delegate(DataRow aRow)
{
dr = aRow;
if (onExecute != null)
{
onExecute(dr, i);
i++;
}
};
Connetion.execute(aQuery, binder);
if (dr == null)
{
return(null);
}
return(dr.Table);
}
private static void ApplyLimitsToList(ISelectQuery selectQuery, IList loadedBos)
{
int firstRecordToLoad = selectQuery.FirstRecordToLoad;
if (firstRecordToLoad < 0)
{
throw new IndexOutOfRangeException("FirstRecordToLoad should not be negative.");
}
if (firstRecordToLoad > loadedBos.Count)
{
loadedBos.Clear();
return;
}
if (firstRecordToLoad > 0)
{
for (int i = 0; i < firstRecordToLoad; i++)
{
loadedBos.RemoveAt(0);
}
}
if (selectQuery.Limit < 0)
{
return;
}
while (loadedBos.Count > selectQuery.Limit)
{
loadedBos.RemoveAt(selectQuery.Limit);
}
}
///<summary>
/// Creates a SelectQueryDB, wrapping an ISelectQuery (Decorator pattern)
///</summary>
///<param name="selectQuery"></param>
///<param name="databaseConnection"></param>
public SelectQueryDB(ISelectQuery selectQuery, IDatabaseConnection databaseConnection)
{
Aliases = new Dictionary<string, string>();
_selectQuery = selectQuery;
_databaseConnection = databaseConnection;
SetupAliases();
}
public Command Build(ISelectQuery query)
{
if (query.IsPaging())
{
AppendPagingStart();
}
AppendSelect();
if (query.IsTop())
{
AppendTop(query);
}
AppendColumnsAndFrom(query);
AppendJoins(query);
if (query.WhereExpressions.Any())
{
AppendWhere(query);
}
if (query.IsPaging())
{
AppendPagingEnd(query);
}
else if (query.OrderByColumns.Any())
{
AppendOrderBy(query);
}
return Command.TextCommand(_cmd.ToString(), _param);
}
public Join(ISelectQuery <TEntity> query, JoinType joinType, Expression <Func <object> > joinAlias)
{
_query = query;
_joinType = joinType;
JoinAlias = joinAlias.GetPropertyName();
JoinTable = joinAlias.GetPropertyType();
}
/// <summary>
/// Populates a <see cref="QueryResult"/> using the given <see cref="ISelectQuery"/>.
/// With this method you can execute a custom select query and get a result set back. If you are loading against a
/// database, the <see cref="IClassDef"/> associated to the <see cref="ISelectQuery"/> will be used to map property names
/// to database fields and will also be used to convert values that are returned from the database to the expected
/// type. This can be used to get result sets that span tables.
/// </summary>
/// <param name="selectQuery">The select query to execute</param>
/// <returns>A <see cref="QueryResult"/> that contains the results of the query</returns>
public IQueryResult GetResultSet(ISelectQuery selectQuery)
{
var classDef = selectQuery.ClassDef;
var criteria = selectQuery.Criteria;
QueryBuilder.PrepareCriteria(classDef, criteria);
//Ensure that all the criteria field sources are merged correctly
selectQuery.Criteria = criteria;
selectQuery.Fields.ForEach(pair =>
{
var field = pair.Value;
var fieldSource = field.Source;
QueryBuilder.PrepareField(fieldSource, classDef, field);
selectQuery.Source.MergeWith(field.Source);
field.Source = field.Source.ChildSourceLeaf;
});
var queryDb = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = queryDb.CreateSqlStatement();
var resultSet = new QueryResult();
var propNames = selectQuery.Fields.Keys;
propNames.ForEach(resultSet.AddField);
using (IDataReader dr = _databaseConnection.LoadDataReader(statement))
{
while (dr.Read())
{
var rawValues = new object[dr.FieldCount];
dr.GetValues(rawValues);
resultSet.AddResult(rawValues);
}
}
return(resultSet);
}
public static ISelectQuery CreateSelectQuery(DbType db, DbProviderType provider)
{
ISelectQuery query = CreateSelectQuery(db);
query.DbProviderType = provider;
return(query);
}
/// <summary>
/// Returns the business object of the type provided that meets the search criteria.
/// An exception is thrown if more than one business object is found that matches the criteria.
/// If that situation could arise, rather use GetBusinessObjectCol.
/// </summary>
/// <param name="criteria">The search criteria</param>
/// <returns>Returns the business object found</returns>
/// <exception cref="UserException">Thrown if more than one object matches the criteria</exception>
public T GetBusinessObject <T>(Criteria criteria) where T : class, IBusinessObject, new()
{
IClassDef classDef = ClassDef.ClassDefs[typeof(T)];
ISelectQuery selectQuery = GetSelectQuery(classDef, criteria);
return(GetBusinessObject <T>(selectQuery));
}
/// <summary>
/// Populates a <see cref="QueryResult"/> using the given <see cref="ISelectQuery"/>.
/// With this method you can execute a custom select query and get a result set back. If you are loading against a
/// database, the <see cref="IClassDef"/> associated to the <see cref="ISelectQuery"/> will be used to map property names
/// to database fields and will also be used to convert values that are returned from the database to the expected
/// type. This can be used to get result sets that span tables.
/// </summary>
/// <param name="selectQuery">The select query to execute</param>
/// <returns>A <see cref="QueryResult"/> that contains the results of the query</returns>
public IQueryResult GetResultSet(ISelectQuery selectQuery)
{
var classDef = selectQuery.ClassDef;
var criteria = selectQuery.Criteria;
QueryBuilder.PrepareCriteria(classDef, criteria);
//Ensure that all the criteria field sources are merged correctly
selectQuery.Criteria = criteria;
selectQuery.Fields.ForEach(pair =>
{
var field = pair.Value;
var fieldSource = field.Source;
QueryBuilder.PrepareField(fieldSource, classDef, field);
selectQuery.Source.MergeWith(field.Source);
field.Source = field.Source.ChildSourceLeaf;
});
var queryDb = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = queryDb.CreateSqlStatement();
var resultSet = new QueryResult();
var propNames = selectQuery.Fields.Keys;
propNames.ForEach(resultSet.AddField);
using (IDataReader dr = _databaseConnection.LoadDataReader(statement))
{
while (dr.Read())
{
var rawValues = new object[dr.FieldCount];
dr.GetValues(rawValues);
resultSet.AddResult(rawValues);
}
}
return resultSet;
}
private void AppendColumnsAndFrom(ISelectQuery query)
{
_cmd.AppendLine()
.AppendLine(String.Join(",\n", query.SelectColumns.Values.Select(
c => $" {c.Table.Alias}.{c.Meta.ColumnName} {c.Alias}")))
.AppendFormat("FROM [{0}] {1}", query.From.Meta.TableName, query.From.Alias)
.AppendLine();
}
///<summary>
///Create a select Query based on the class definition and the search criteria.
///</summary>
///<param name="classDef">The class definition.</param>
///<param name="criteria">The load criteria.</param>
///<returns></returns>
protected ISelectQuery GetSelectQuery(IClassDef classDef, Criteria criteria)
{
ISelectQuery selectQuery = QueryBuilder.CreateSelectQuery(classDef);
QueryBuilder.PrepareCriteria(classDef, criteria);
selectQuery.Criteria = criteria;
return(selectQuery);
}
private static void RemoveOrderBy(ISelectQuery selectQuery)
{
if (selectQuery.OrderBy.Items.Count > 0 && selectQuery.Select.SkipValue == null &&
selectQuery.Select.TakeValue == null)
{
selectQuery.OrderBy.Items.Clear();
}
}
public List <T> Select <T>(ISelectQuery aQuery, OnEntitySelect <T> onExecute) where T : class, new()
{
if (aQuery == null)
{
aQuery = QueryFactory.CreateSelectQuery <T>();
}
return(Select <T>(aQuery, new TemplateBinder <T>(), onExecute));
}
/// <summary>
/// Loads a BusinessObjectCollection using the SelectQuery given. It's important to make sure that T (meaning the ClassDef set up for T)
/// has the properties defined in the fields of the select query.
/// This method allows you to define a custom query to load a businessobjectcollection so that you can perhaps load from multiple
/// tables using a join (if loading from a database source).
/// </summary>
/// <typeparam name="T">The type of collection to load. This must be a class that implements IBusinessObject and has a parameterless constructor</typeparam>
/// <param name="selectQuery">The select query to use to load from the data source</param>
/// <returns>The loaded collection</returns>
public BusinessObjectCollection <T> GetBusinessObjectCollection <T>(ISelectQuery selectQuery) where T : class, IBusinessObject, new()
{
if (_businessObjectLoaders.ContainsKey(typeof(T)))
{
return(_businessObjectLoaders[typeof(T)].GetBusinessObjectCollection <T>(selectQuery));
}
return(_defaultBusinessObjectLoader.GetBusinessObjectCollection <T>(selectQuery));
}
/// <summary>
/// Loads a business object of the type identified by a <see cref="ClassDef"/>,
/// using the SelectQuery given. It's important to make sure that the ClassDef parameter given
/// has the properties defined in the fields of the select query.
/// This method allows you to define a custom query to load a business object
/// </summary>
/// <param name="classDef">The ClassDef of the object to load.</param>
/// <param name="selectQuery">The select query to use to load from the data source</param>
/// <returns>The business object that was found. If none was found, null is returned. If more than one is found an <see cref="HabaneroDeveloperException"/> error is throw</returns>
public IBusinessObject GetBusinessObject(IClassDef classDef, ISelectQuery selectQuery)
{
if (_businessObjectLoaders.ContainsKey(classDef.ClassType))
{
return(_businessObjectLoaders[classDef.ClassType].GetBusinessObject(classDef, selectQuery));
}
return(_defaultBusinessObjectLoader.GetBusinessObject(classDef, selectQuery));
}
public QueryRepository(IDelete delete, IInsert insert, ISelectQuery selectQuery, ISelectQueryParam selectQueryParam, IUpdate update)
{
_delete = delete;
_insert = insert;
_selectQuery = selectQuery;
_selectQueryParam = selectQueryParam;
_update = update;
}
public SelectQuery(ISelectQuery <TEntity> query)
{
Columns = query.Columns;
Alias = query.Alias;
AliasExpression = query.AliasExpression;
Restriction = query.Restriction;
Order = query.Order;
Joins = query.Joins;
}
internal WhereClause(
ISelectQuery selectQuery,
IWhereClause clone,
Dictionary <ICloneableElement, ICloneableElement> objectTree,
Predicate <ICloneableElement> doClone)
: base(selectQuery)
{
Search = (ISearchCondition)clone.Search.Clone(objectTree, doClone);
}
private ISqlStatement GetSelectSQLStatement()
{
BusinessObjectLoaderDB boLoaderDB = (BusinessObjectLoaderDB)BORegistry.DataAccessor.BusinessObjectLoader;
ISelectQuery selectQuery = boLoaderDB.GetSelectQuery(_busObj.ClassDef, _busObj.ID);
SelectQueryDB selectQueryDB = new SelectQueryDB(selectQuery, boLoaderDB.DatabaseConnection);
return(selectQueryDB.CreateSqlStatement());
}
internal SelectClause(ISelectQuery selectQuery, ISelectClause clone,
Dictionary <ICloneableElement, ICloneableElement> objectTree, Predicate <ICloneableElement> doClone)
: base(selectQuery)
{
Columns.AddRange(clone.Columns.Select(c => (IColumn)c.Clone(objectTree, doClone)));
IsDistinct = clone.IsDistinct;
TakeValue = (IQueryExpression)clone.TakeValue?.Clone(objectTree, doClone);
SkipValue = (IQueryExpression)clone.SkipValue?.Clone(objectTree, doClone);
}
internal OrderByClause(
ISelectQuery selectQuery,
IOrderByClause clone,
Dictionary <ICloneableElement, ICloneableElement> objectTree,
Predicate <ICloneableElement> doClone)
: base(selectQuery)
{
Items.AddRange(clone.Items.Select(item => (IOrderByItem)item.Clone(objectTree, doClone)));
}
private int GetTotalNoOfRecordsIfNeeded(IClassDef classDef, ISelectQuery selectQuery)
{
int totalNoOfRecords = -1;
if ((selectQuery.FirstRecordToLoad > 0) || (selectQuery.Limit >= 0))
{
totalNoOfRecords = GetCount(classDef, selectQuery.Criteria);
}
return(totalNoOfRecords);
}
/// <summary>
/// Creates a command to select all records
/// </summary>
/// <returns></returns>
protected IDbCommand CreateSelectAllCommand()
{
IDbCommand command = DbProviderFactory.CreateCommand(Provider);
ISelectQuery s = QueryFactory.CreateSelectQuery(Db);
s.AddAll();
s.SetTable(Table);
command.CommandText = s.GetText();
return(command);
}
public virtual DataTable ExecuteSelectQuery(ISelectQuery aQuery, OnRowSelect onSelect)
{
DataTable dt = null;
DBHelper.Execute(
delegate(DbSession aSession)
{
dt = aSession.Select(aQuery, onSelect);
});
return(dt);
}
public virtual List <T> ExecuteSelectQuery(ISelectQuery aQuery, OnEntitySelect <T> onSelect)
{
List <T> list = null;
DBHelper.Execute(
delegate(DbSession aSession)
{
list = aSession.Select <T>(aQuery, onSelect);
});
return(list);
}
internal GroupByClause(ISelectQuery selectQuery, IGroupByClause clone,
Dictionary <ICloneableElement, ICloneableElement> objectTree, Predicate <ICloneableElement> doClone)
: base(selectQuery)
{
clone.Items.ForEach(
node =>
{
var value = (IQueryExpression)node.Value.Clone(objectTree, doClone);
Items.AddLast(value);
});
}
internal FromClause(ISelectQuery selectQuery, IFromClause clone,
Dictionary <ICloneableElement, ICloneableElement> objectTree, Predicate <ICloneableElement> doClone)
: base(selectQuery)
{
clone.Tables.ForEach(
node =>
{
var value = (ITableSource)node.Value.Clone(objectTree, doClone);
Tables.AddLast(value);
});
}
public override void VisitSelectClause(SelectClause selectClause, QueryModel queryModel)
{
//var source = _selectQuery.Source;
var itemClassDef = ClassDef.ClassDefs[queryModel.MainFromClause.ItemType];
_selectQuery = QueryBuilder.CreateSelectQuery(itemClassDef);
//QueryBuilder.PrepareSource(itemClassDef, ref source);
//_selectQuery.Source = source;
base.VisitSelectClause(selectClause, queryModel);
}
public void TestBuiltSource()
{
//---------------Set up test pack-------------------
MyBO.LoadDefaultClassDef();
IClassDef classdef = ClassDef.ClassDefs[typeof(MyBO)];
//---------------Execute Test ----------------------
ISelectQuery query = QueryBuilder.CreateSelectQuery(classdef);
//---------------Test Result -----------------------
Assert.AreEqual("MyBO", query.Source.EntityName);
//---------------Tear Down -------------------------
}
public void TestHasClassDef()
{
//---------------Set up test pack-------------------
MyBO.LoadDefaultClassDef();
IClassDef classdef = ClassDef.ClassDefs[typeof(MyBO)];
//---------------Execute Test ----------------------
ISelectQuery query = QueryBuilder.CreateSelectQuery(classdef);
//---------------Test Result -----------------------
Assert.AreSame(classdef, query.ClassDef);
//---------------Tear Down -------------------------
}
/// <summary>
/// Returns the business object of the type provided that meets the search criteria.
/// An exception is thrown if more than one business object is found that matches the criteria.
/// If that situation could arise, rather use GetBusinessObjectCol.
/// </summary>
/// <param name="selectQuery">The select query</param>
/// <returns>Returns the business object found</returns>
/// <exception cref="UserException">Thrown if more than one object matches the criteria</exception>
public T GetBusinessObject <T>(ISelectQuery selectQuery) where T : class, IBusinessObject, new()
{
var classDef = ClassDef.Get <T>();
var source = selectQuery.Source;
QueryBuilder.PrepareSource(classDef, ref source);
selectQuery.Source = source;
QueryBuilder.PrepareCriteria(classDef, selectQuery.Criteria);
var selectQueryDB = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = selectQueryDB.CreateSqlStatement();
IClassDef correctSubClassDef = null;
T loadedBo = null;
var objectUpdatedInLoading = false;
using (var dr = _databaseConnection.LoadDataReader(statement))
{
if (dr.Read())
{
loadedBo = LoadBOFromReader <T>(dr, selectQueryDB, out objectUpdatedInLoading);
//Checks to see if the loaded object is the base of a single table inheritance structure
// and has a sub type if so then returns the correct sub type.
correctSubClassDef = GetCorrectSubClassDef(loadedBo, dr);
//Checks to see if there is a duplicate object meeting this criteria
if (dr.Read())
{
ThrowRetrieveDuplicateObjectException(statement, loadedBo);
}
}
}
if (correctSubClassDef != null)
{
loadedBo = GetLoadedBoOfSpecifiedType(loadedBo, correctSubClassDef);
}
if (loadedBo == null)
{
return(null);
}
var isFreshlyLoaded = loadedBo.Status.IsNew;
SetStatusAfterLoad(loadedBo);
if (objectUpdatedInLoading)
{
CallAfterLoad(loadedBo);
if (!isFreshlyLoaded)
{
FireUpdatedEvent(loadedBo);
}
}
return(loadedBo);
}
/// <summary>
/// Loads a business object of the type identified by a <see cref="ClassDef"/>,
/// using the SelectQuery given. It's important to make sure that the ClassDef parameter given
/// has the properties defined in the fields of the select query.
/// This method allows you to define a custom query to load a business object
/// </summary>
/// <param name="classDef">The ClassDef of the object to load.</param>
/// <param name="selectQuery">The select query to use to load from the data source</param>
/// <returns>The business object that was found. If none was found, null is returned. If more than one is found an <see cref="HabaneroDeveloperException"/> error is throw</returns>
public IBusinessObject GetBusinessObject(IClassDef classDef, ISelectQuery selectQuery)
{
var source = selectQuery.Source;
QueryBuilder.PrepareSource(classDef, ref source);
selectQuery.Source = source;
QueryBuilder.PrepareCriteria(classDef, selectQuery.Criteria);
var selectQueryDB = new SelectQueryDB(selectQuery, _databaseConnection);
var statement = selectQueryDB.CreateSqlStatement();
IClassDef correctSubClassDef = null;
IBusinessObject loadedBo = null;
var objectUpdatedInLoading = false;
using (var dr = _databaseConnection.LoadDataReader(statement))
{
if (dr.Read())
{
loadedBo = LoadBOFromReader(classDef, dr, selectQueryDB, out objectUpdatedInLoading);
correctSubClassDef = GetCorrectSubClassDef(loadedBo, dr);
if (dr.Read())
{
ThrowRetrieveDuplicateObjectException(statement, loadedBo);
}
}
}
if (correctSubClassDef != null)
{
BORegistry.BusinessObjectManager.Remove(loadedBo);
var subClassBusinessObject = GetBusinessObject(correctSubClassDef, loadedBo.ID);
loadedBo = subClassBusinessObject;
}
if (loadedBo == null)
{
return(null);
}
var isFreshlyLoaded = loadedBo.Status.IsNew;
SetStatusAfterLoad(loadedBo);
if (objectUpdatedInLoading)
{
CallAfterLoad(loadedBo);
if (!isFreshlyLoaded)
{
FireUpdatedEvent(loadedBo);
}
}
return(loadedBo);
}
private void AppendJoins(ISelectQuery query)
{
foreach (var join in query.Joins.Select(j => j.Value))
{
_cmd.AppendFormat(" {0} JOIN [{1}] {2} ON {3}.{4} = {2}.{5}",
join.Type.ToString().ToUpperInvariant(),
join.RightColumn.Table.Meta.TableName,
join.RightColumn.Table.Alias,
join.LeftColumn.Table.Alias,
join.LeftColumn.Meta.ColumnName,
join.RightColumn.Meta.ColumnName)
.AppendLine();
}
}
///<summary>
/// Creates a select query to return the count of objects in a table for that classdef with the criteria
///</summary>
///<param name="classDef">The class def for the class that the count is being returned</param>
///<param name="criteria">The Criteria for the class that the count is being returned</param>
///<returns></returns>
///<exception cref="ArgumentNullException"></exception>
public static ISelectQuery CreateSelectCountQuery(IClassDef classDef, Criteria criteria)
{
if (classDef == null)
{
throw new ArgumentNullException("classDef");
}
ISelectQuery selectQuery = CreateSelectQuery(classDef, criteria);
selectQuery.Fields.Clear();
//selectQuery.Fields.Add("count", QueryField.FromString("Count(*)"));
selectQuery.Fields.Add("count", new CountFunctionQueryField());
return(selectQuery);
}
/// <summary>
/// Populates a <see cref="QueryResult"/> using the given <see cref="ISelectQuery"/>.
/// With this method you can execute a custom select query and get a result set back. If you are loading against a
/// database, the <see cref="IClassDef"/> associated to the <see cref="ISelectQuery"/> will be used to map property names
/// to database fields and will also be used to convert values that are returned from the database to the expected
/// type. This can be used to get result sets that span tables.
/// </summary>
/// <param name="selectQuery">The select query to execute</param>
/// <returns>A <see cref="QueryResult"/> that contains the results of the query</returns>
public IQueryResult GetResultSet(ISelectQuery selectQuery)
{
QueryBuilder.PrepareCriteria(selectQuery.ClassDef, selectQuery.Criteria);
var collection = _dataStore.FindAll(selectQuery.ClassDef, selectQuery.Criteria);
var resultSet = new QueryResult();
var propNames = selectQuery.Fields.Keys;
propNames.ForEach(resultSet.AddField);
foreach (IBusinessObject bo in collection)
{
var bo1 = bo;
resultSet.AddResult(
propNames.Select(s => new BOMapper(bo1).GetPropertyValueToDisplay(s))
.ToArray()
);
}
var sorter = new QueryResultSorter();
sorter.Sort(resultSet, selectQuery.OrderCriteria);
return resultSet;
}
private void AppendOrderBy(ISelectQuery query)
{
_cmd.Append("ORDER BY ");
var comma = "";
foreach (var col in query.OrderByColumns)
{
_cmd.AppendFormat("{0}{1}.{2} {3}", comma, col.Column.Table.Alias, col.Column.Meta.ColumnName,
col.Desc ? "DESC" : "ASC");
comma = ",";
}
}
private static void AddDiscriminatorFields(ISelectQuery selectQuery, IClassDef classDef, ref Criteria criteria)
{
ClassDefCol classDefsToSearch = ((ClassDef)classDef).AllChildren;
classDefsToSearch.Add((ClassDef) classDef);
List<Criteria> discriminatorCriteriaList = new List<Criteria>();
string discriminator = null;
foreach (ClassDef thisClassDef in classDefsToSearch)
{
if (!thisClassDef.IsUsingSingleTableInheritance()) continue;
ISuperClassDef superClassDef = thisClassDef.SuperClassDef;
discriminator = superClassDef.Discriminator;
if (String.IsNullOrEmpty(discriminator)) continue;
if (!selectQuery.Fields.ContainsKey(discriminator))
{
selectQuery.Fields.Add(discriminator,
new QueryField(discriminator, discriminator, new Source(((ClassDef)classDef).GetBaseClassOfSingleTableHierarchy().ClassNameExcludingTypeParameter, classDef.GetTableName())));
}
discriminatorCriteriaList.Add(new Criteria(discriminator, Criteria.ComparisonOp.Equals, thisClassDef.ClassName));
}
if (discriminatorCriteriaList.Count > 0)
{
if (!((ClassDef)classDef).IsUsingSingleTableInheritance())
criteria = new Criteria(discriminator, Criteria.ComparisonOp.Is, "null");
foreach (Criteria discCriteria in discriminatorCriteriaList)
{
if (criteria == null) { criteria = discCriteria; continue; }
criteria = new Criteria(criteria, Criteria.LogicalOp.Or, discCriteria);
}
}
}
private void AppendWhere(ISelectQuery query)
{
var where = new LogicalExpression
{
Operand1 = query.WhereExpressions[0],
Operator = Operator.And
};
foreach (var whereExpression in query.WhereExpressions.Skip(1))
{
where.Operand2 = whereExpression;
where = new LogicalExpression
{
Operand1 = where,
Operator = Operator.And
};
}
var whereExp = where.Operand1;
var builder = _meta.DbProvider.CreateWhereCommandBuilder(_meta);
whereExp.Accept(builder);
var whereCmd = builder.Build();
_cmd.AppendLine("WHERE")
.AppendLine(whereCmd.CommandText);
foreach (var sqlParameter in whereCmd.Parameters)
{
_param.Add(sqlParameter.Key, sqlParameter.Value);
}
}
private void AppendTop(ISelectQuery query)
{
_cmd.AppendFormat(" TOP {0}", query.PageSize);
}
private void AppendPagingEnd(ISelectQuery query)
{
/*
with
__DATA as (SELECT...),
__COUNT as (select count(0) as _ROWCOUNT from __DATA)
select * from __COUNT, __DATA
order by s.SalesOrderID
offset 0 rows fetch next 10 rows only*/
_cmd.AppendLine(
"),\n__COUNT AS (SELECT COUNT(0) AS __ROWCOUNT FROM __DATA)\nSELECT * FROM __COUNT, __DATA")
.Append("ORDER BY ");
if (query.OrderByColumns.Any())
{
var comma = "";
foreach (var col in query.OrderByColumns)
{
_cmd.AppendFormat("{0}__DATA.{1} {2}", comma, col.Column.Alias, col.Desc ? "DESC" : "ASC");
comma = ",";
}
}
else
{
var fromCol = (query.SelectColumns.Values.FirstOrDefault(c => c.Meta.Identity && c.Table == query.From) ??
query.SelectColumns.Values.FirstOrDefault(c => c.Meta.Identity)) ??
query.SelectColumns.Values.First();
_cmd.AppendFormat("__DATA.{0}", fromCol.Alias);
}
var offsetParam = new CommandParameter
{
Name = "pOffset",
Value = (query.Page - 1) * query.PageSize,
ParameterMeta = new ParameterMeta { DbType = DbType.Int32 }
};
var limitParam = new CommandParameter
{
Name = "pLimit",
Value = query.PageSize,
ParameterMeta = new ParameterMeta { DbType = DbType.Int32 }
};
_param.Add(offsetParam.Name, offsetParam);
_param.Add(limitParam.Name, limitParam);
_cmd.AppendLine()
.Append("OFFSET @pOffset ROWS FETCH NEXT @pLimit ROWS ONLY");
}
public void SetUp()
{
this.select = SqlBuilder.Select<OrmType>();
}
/// <summary>
/// Selects records using the specified query
/// </summary>
/// <param name="query">The query to use</param>
/// <returns></returns>
public IList Select(ISelectQuery query)
{
return this.Select(this.CreateCommand(query.GetText()));
}
/// <summary>
/// Selects records using the specified query
/// </summary>
/// <param name="query">The query to use</param>
/// <param name="connection">The open IDbConnection</param>
/// <returns></returns>
public IList Select(ISelectQuery query, IDbConnection connection)
{
return this.Select(this.CreateCommand(query.GetText()), connection);
}
