/// <summary>Gets the SP Call using query for fetching the VenueDistanceResult TypedView.</summary>
/// <param name="maxDistance">Input parameter of stored procedure</param>
/// <param name="lat">Input parameter of stored procedure</param>
/// <param name="lng">Input parameter of stored procedure</param>
/// <returns>ready to use IRetrievalQuery instance for fetching the typedview</returns>
/// <remarks>Output parameters are not set after query is executed</remarks>
public static IRetrievalQuery GetQueryForVenueDistanceResultTypedView(System.Double maxDistance, System.Double lat, System.Double lng)
{
IRetrievalQuery query = GetVenueDistanceCallAsQuery(maxDistance, lat, lng);
query.ResultsetNumber = 1;
return(query);
}
/// <summary>Gets the SP Call using query for fetching the CreateEmployeeClassificationResult TypedView.</summary>
/// <param name="employeeClassificationName">Input parameter of stored procedure</param>
/// <param name="isExempt">Input parameter of stored procedure</param>
/// <param name="isEmployee">Input parameter of stored procedure</param>
/// <returns>ready to use IRetrievalQuery instance for fetching the typedview</returns>
/// <remarks>Output parameters are not set after query is executed</remarks>
public static IRetrievalQuery GetQueryForCreateEmployeeClassificationResultTypedView(System.String employeeClassificationName, System.Boolean isExempt, System.Boolean isEmployee)
{
IRetrievalQuery query = GetCreateEmployeeClassificationCallAsQuery(employeeClassificationName, isExempt, isEmployee);
query.ResultsetNumber = 1;
return(query);
}
/// <summary>Gets the SP Call using query for fetching the CountEmployeesByClassificationResult TypedView.</summary>
/// <returns>ready to use IRetrievalQuery instance for fetching the typedview</returns>
/// <remarks>Output parameters are not set after query is executed</remarks>
public static IRetrievalQuery GetQueryForCountEmployeesByClassificationResultTypedView()
{
IRetrievalQuery query = GetCountEmployeesByClassificationCallAsQuery();
query.ResultsetNumber = 1;
return(query);
}
/// <summary>Gets the SP Call using query for fetching the GetEmployeeClassificationsResult TypedView.</summary>
/// <param name="employeeClassificationKey">Input parameter of stored procedure</param>
/// <returns>ready to use IRetrievalQuery instance for fetching the typedview</returns>
/// <remarks>Output parameters are not set after query is executed</remarks>
public static IRetrievalQuery GetQueryForGetEmployeeClassificationsResultTypedView(Nullable <System.Int32> employeeClassificationKey)
{
IRetrievalQuery query = GetGetEmployeeClassificationsCallAsQuery(employeeClassificationKey);
query.ResultsetNumber = 1;
return(query);
}
/// <summary>
/// Executes the passed in retrievalquery and returns an open, ready to use IDataReader. The datareader's command behavior is set to the
/// readerBehavior passed in. If a transaction is in progress, the command is wired to the transaction.
/// </summary>
/// <param name="queryToExecute">The query to execute.</param>
/// <param name="readerBehavior">The reader behavior to set.</param>
/// <returns>Open, ready to use IDataReader</returns>
/// <remarks>Advanced functionality: be aware that the datareader returned is open, and the dataaccessadapter's connection is also open</remarks>
public override IDataReader FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
{
if (queryToExecute.Command.CommandType == CommandType.StoredProcedure)
{
queryToExecute.Command.CommandText = CreateCorrectStoredProcedureName(queryToExecute.Command.CommandText);
}
return(base.FetchDataReader(queryToExecute, readerBehavior));
}
/// <summary>
/// Creates a new Select Query which is ready to use, based on the specified select list and the specified set of relations.
/// If selectFilter is set to null, all rows are selected.
/// </summary>
/// <param name="selectList">list of IEntityFieldCore objects to select</param>
/// <param name="fieldsPersistenceInfo">Array of IFieldPersistenceInfo objects to use to build the select query</param>
/// <param name="connectionToUse">The connection to use for the query</param>
/// <param name="selectFilter">A complete IPredicate implementing object which contains the filter for the rows to select. When set to null, no
/// filtering is done, and all rows are returned.</param>
/// <param name="rowsToSkip">The rows to skip. Default 0</param>
/// <param name="rowsToTake">The rows to take. Default 0, which means all.</param>
/// <param name="sortClauses">The order by specifications for the sorting of the resultset. When not specified, no sorting is applied.</param>
/// <param name="relationsToWalk">list of EntityRelation objects, which will be used to formulate a FROM clause with INNER JOINs.</param>
/// <param name="allowDuplicates">Flag which forces the inclusion of DISTINCT if set to true. If the resultset contains fields of type ntext, text or image, no duplicate filtering
/// is done.</param>
/// <param name="groupByClause">The list of fields to group by on. When not specified or an empty collection is specified, no group by clause
/// is added to the query. A check is performed for each field in the selectList. If a field in the selectList is not present in the groupByClause
/// collection, an exception is thrown.</param>
/// <returns>
/// IRetrievalQuery instance which is ready to be used.
/// </returns>
/// <exception cref="System.ArgumentNullException">When selectList is null or fieldsPersistenceInfo is null or relationsToWalk is null</exception>
/// <exception cref="System.ArgumentException">When selectList contains no EntityFieldCore instances or fieldsPersistenceInfo is empty.</exception>
/// <remarks>
/// Generic version
/// </remarks>
protected override IRetrievalQuery CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo,
DbConnection connectionToUse, IPredicate selectFilter, int rowsToSkip, int rowsToTake,
ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates,
IGroupByCollection groupByClause)
{
TraceHelper.WriteLineIf(Switch.TraceInfo, "CreatePagingSelectDQ", "Method Enter");
long max = 0;
bool pagingRequired = true;
if (rowsToSkip <= 0)
{
// no paging.
max = rowsToTake;
pagingRequired = false;
}
IRetrievalQuery normalQuery = this.CreateSelectDQ(selectList, fieldsPersistenceInfo, connectionToUse, selectFilter, max, sortClauses, relationsToWalk, allowDuplicates, groupByClause);
if (!pagingRequired)
{
TraceHelper.WriteLineIf(Switch.TraceInfo, "CreatePagingSelectDQ: no paging.", "Method Exit");
return(normalQuery);
}
bool emitQueryToTrace = false;
if (normalQuery.RequiresClientSideDistinctFiltering)
{
// manual paging required
normalQuery.RequiresClientSidePaging = pagingRequired;
normalQuery.ManualRowsToSkip = rowsToSkip;
normalQuery.ManualRowsToTake = rowsToTake;
}
else
{
// normal paging. Embed paging logic. There is no TOP statement in the query as we've passed '0' for maxAmountOfItemsToReturn
string upperLimitSnippet = string.Empty;
if (rowsToTake > 0)
{
upperLimitSnippet = string.Format(" TOP {0}", rowsToTake);
}
if (normalQuery.Command.CommandText.ToLowerInvariant().StartsWith("select distinct"))
{
normalQuery.Command.CommandText = String.Format("SELECT DISTINCT{0} START AT {1} {2}",
upperLimitSnippet, rowsToSkip + 1, normalQuery.Command.CommandText.Substring(16));
}
else
{
normalQuery.Command.CommandText = String.Format("SELECT{0} START AT {1} {2}",
upperLimitSnippet, rowsToSkip + 1, normalQuery.Command.CommandText.Substring(7));
}
emitQueryToTrace = true;
}
TraceHelper.WriteIf(Switch.TraceVerbose && emitQueryToTrace, normalQuery, "Generated Sql query");
TraceHelper.WriteLineIf(Switch.TraceInfo, "CreatePagingSelectDQ", "Method Exit");
return(normalQuery);
}
/// <summary>Fetches instances of the the typed view row 'CreateEmployeeClassificationResultRow' class using a stored procedure call. </summary>
/// <typeparam name="T">The type of the typed view row</typeparam>
/// <param name="adapter">The adapter.</param>
/// <param name="projectionOfTypedView">The projection of the typed view, created using the method QueryFactory.GetCreateEmployeeClassificationResultTypedViewProjection().</param>
/// <param name="employeeClassificationName">Input parameter of stored procedure</param>
/// <param name="isExempt">Input parameter of stored procedure</param>
/// <param name="isEmployee">Input parameter of stored procedure</param>
/// <returns>The results in a List</returns>
public static List <T> FetchCreateEmployeeClassificationResultTypedView <T>(IDataAccessAdapter adapter, SD.LLBLGen.Pro.QuerySpec.DynamicQuery <T> projectionOfTypedView, System.String employeeClassificationName, System.Boolean isExempt, System.Boolean isEmployee)
{
StoredProcedureCall call = CreateCreateEmployeeClassificationCall(adapter, employeeClassificationName, isExempt, isEmployee);
IRetrievalQuery query = call.ToRetrievalQuery();
query.ResultsetNumber = 1;
var toReturn = adapter.FetchQueryFromSource(projectionOfTypedView, query);
return(toReturn);
}
/// <summary>Fetches instances of the the typed view row 'CountEmployeesByClassificationResultRow' class using a stored procedure call. </summary>
/// <typeparam name="T">The type of the typed view row</typeparam>
/// <param name="adapter">The adapter.</param>
/// <param name="projectionOfTypedView">The projection of the typed view, created using the method QueryFactory.GetCountEmployeesByClassificationResultTypedViewProjection().</param>
/// <returns>The results in a List</returns>
public static List <T> FetchCountEmployeesByClassificationResultTypedView <T>(IDataAccessAdapter adapter, SD.LLBLGen.Pro.QuerySpec.DynamicQuery <T> projectionOfTypedView)
{
StoredProcedureCall call = CreateCountEmployeesByClassificationCall(adapter);
IRetrievalQuery query = call.ToRetrievalQuery();
query.ResultsetNumber = 1;
var toReturn = adapter.FetchQueryFromSource(projectionOfTypedView, query);
return(toReturn);
}
/// <summary>Fetches instances of the the typed view row 'GetEmployeeClassificationsResultRow' class using a stored procedure call. </summary>
/// <typeparam name="T">The type of the typed view row</typeparam>
/// <param name="adapter">The adapter.</param>
/// <param name="projectionOfTypedView">The projection of the typed view, created using the method QueryFactory.GetGetEmployeeClassificationsResultTypedViewProjection().</param>
/// <param name="employeeClassificationKey">Input parameter of stored procedure</param>
/// <returns>The results in a List</returns>
public static List <T> FetchGetEmployeeClassificationsResultTypedView <T>(IDataAccessAdapter adapter, SD.LLBLGen.Pro.QuerySpec.DynamicQuery <T> projectionOfTypedView, Nullable <System.Int32> employeeClassificationKey)
{
StoredProcedureCall call = CreateGetEmployeeClassificationsCall(adapter, employeeClassificationKey);
IRetrievalQuery query = call.ToRetrievalQuery();
query.ResultsetNumber = 1;
var toReturn = adapter.FetchQueryFromSource(projectionOfTypedView, query);
return(toReturn);
}
/// <summary>Fetches instances of the the typed view row 'VenueDistanceResultRow' class using a stored procedure call. </summary>
/// <typeparam name="T">The type of the typed view row</typeparam>
/// <param name="adapter">The adapter.</param>
/// <param name="projectionOfTypedView">The projection of the typed view, created using the method QueryFactory.GetVenueDistanceResultTypedViewProjection().</param>
/// <param name="maxDistance">Input parameter of stored procedure</param>
/// <param name="lat">Input parameter of stored procedure</param>
/// <param name="lng">Input parameter of stored procedure</param>
/// <returns>The results in a List</returns>
public static List <T> FetchVenueDistanceResultTypedView <T>(IDataAccessAdapter adapter, SD.LLBLGen.Pro.QuerySpec.DynamicQuery <T> projectionOfTypedView, System.Double maxDistance, System.Double lat, System.Double lng)
{
StoredProcedureCall call = CreateVenueDistanceCall(adapter, maxDistance, lat, lng);
IRetrievalQuery query = call.ToRetrievalQuery();
query.ResultsetNumber = 1;
var toReturn = adapter.FetchQueryFromSource(projectionOfTypedView, query);
return(toReturn);
}
public static List <T> FetchDerivedModel <T>(IRetrievalQuery qry)
{
List <T> pmv = new List <T>();
using (DataAccessManager adapter = GetAdapter())
{
pmv = adapter.FetchProjection <T>(qry);
adapter.CloseConnection();
}
return(pmv);
}
/// <summary>
/// Executes the passed in retrievalquery and returns an open, ready to use IDataReader. The datareader's command behavior is set to the
/// readerBehavior passed in. If a transaction is in progress, the command is wired to the transaction.
/// </summary>
/// <param name="queryToExecute">The query to execute.</param>
/// <param name="readerBehavior">The reader behavior to set.</param>
/// <returns>Open, ready to use IDataReader</returns>
/// <remarks>Advanced functionality: be aware that the datareader returned is open, and the dataaccessadapter's connection is also open</remarks>
public override IDataReader FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
{
if(queryToExecute.Command.CommandType == CommandType.StoredProcedure)
{
queryToExecute.Command.CommandText = CreateCorrectStoredProcedureName(queryToExecute.Command.CommandText);
}
return base.FetchDataReader(queryToExecute, readerBehavior);
}
/// <summary>
/// Creates a new Select Query which is ready to use, based on the specified select list and the specified set of relations.
/// If selectFilter is set to null, all rows are selected.
/// </summary>
/// <param name="selectList">list of IEntityFieldCore objects to select</param>
/// <param name="fieldsPersistenceInfo">Array of IFieldPersistenceInfo objects to use to build the select query</param>
/// <param name="query">The query to fill.</param>
/// <param name="selectFilter">A complete IPredicate implementing object which contains the filter for the rows to select. When set to null, no
/// filtering is done, and all rows are returned.</param>
/// <param name="maxNumberOfItemsToReturn">The maximum number of items to return with this retrieval query.
/// If the used Dynamic Query Engine supports it, 'TOP' is used to limit the amount of rows to return.
/// When set to 0, no limitations are specified.</param>
/// <param name="sortClauses">The order by specifications for the sorting of the resultset. When not specified, no sorting is applied.</param>
/// <param name="relationsToWalk">list of EntityRelation objects, which will be used to formulate a FROM clause with INNER JOINs.</param>
/// <param name="allowDuplicates">Flag which forces the inclusion of DISTINCT if set to true. If the resultset contains fields of type ntext, text or image, no duplicate filtering
/// is done.</param>
/// <param name="groupByClause">The list of fields to group by on. When not specified or an empty collection is specified, no group by clause
/// is added to the query. A check is performed for each field in the selectList. If a field in the selectList is not present in the groupByClause
/// collection, an exception is thrown.</param>
/// <param name="relationsSpecified">flag to signal if relations are specified, this is a result of a check. This routine should
/// simply assume the value of this flag is correct.</param>
/// <param name="sortClausesSpecified">flag to signal if sortClauses are specified, this is a result of a check. This routine should
/// simply assume the value of this flag is correct.</param>
/// <exception cref="System.ArgumentNullException">When selectList is null or fieldsPersistenceInfo is null</exception>
/// <exception cref="System.ArgumentException">When selectList contains no EntityFieldCore instances or fieldsPersistenceInfo is empty.</exception>
protected override void CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo,
IRetrievalQuery query, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses,
IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause,
bool relationsSpecified, bool sortClausesSpecified)
{
TraceHelper.WriteLineIf(Switch.TraceInfo, "CreateSelectDQ", "Method Enter");
QueryFragments fragments = new QueryFragments();
fragments.AddFragment("SELECT");
StringPlaceHolder distinctPlaceholder = fragments.AddPlaceHolder();
StringPlaceHolder topPlaceholder = fragments.AddPlaceHolder();
DelimitedStringList projection = fragments.AddCommaFragmentList(false);
UniqueList <string> fieldNamesInSelectList;
bool distinctViolatingTypesFound;
bool pkFieldSeen;
AppendResultsetFields(selectList, fieldsPersistenceInfo, relationsToWalk, projection, sortClausesSpecified, allowDuplicates, true, new UniqueList <string>(),
query, out fieldNamesInSelectList, out distinctViolatingTypesFound, out pkFieldSeen);
bool resultsCouldContainDuplicates = this.DetermineIfDuplicatesWillOccur(relationsToWalk);
bool distinctEmitted = this.HandleDistinctEmit(sortClauses, allowDuplicates, sortClausesSpecified, query, distinctPlaceholder, distinctViolatingTypesFound,
(pkFieldSeen && !resultsCouldContainDuplicates), fieldNamesInSelectList);
bool groupByClauseSpecified = ((groupByClause != null) && (groupByClause.Count > 0));
if (maxNumberOfItemsToReturn > 0)
{
// row limits are emitted always, unless duplicates are required but DISTINCT wasn't emitable. If not emitable, switch to client-side row limitation
if (distinctEmitted || !resultsCouldContainDuplicates || groupByClauseSpecified || allowDuplicates)
{
topPlaceholder.SetFormatted("TOP {0}", maxNumberOfItemsToReturn);
}
else
{
query.RequiresClientSideLimitation = true;
query.ManualRowsToTake = (int)maxNumberOfItemsToReturn;
}
}
if (relationsSpecified)
{
fragments.AddFormatted("FROM {0}", relationsToWalk.ToQueryText());
query.AddParameters(((RelationCollection)relationsToWalk).CustomFilterParameters);
}
else
{
var persistenceInfoToUse = fieldsPersistenceInfo.FirstOrDefault(p => p != null);
if ((persistenceInfoToUse != null) && (persistenceInfoToUse.SourceObjectName.Length > 0))
{
fragments.AddFormatted(" FROM {0}", this.Creator.CreateObjectName(persistenceInfoToUse));
string targetAlias = this.DetermineTargetAlias(selectList[0], relationsToWalk);
if (targetAlias.Length > 0)
{
fragments.AddFormatted(" {0}", this.Creator.CreateValidAlias(targetAlias));
}
}
}
AppendWhereClause(selectFilter, fragments, query);
AppendGroupByClause(groupByClause, fragments, query);
AppendOrderByClause(sortClauses, fragments, query);
query.SetCommandText(MakeParametersAnonymous(fragments.ToString(), query.Parameters));
TraceHelper.WriteIf(Switch.TraceVerbose, query, "Generated Sql query");
TraceHelper.WriteLineIf(Switch.TraceInfo, "CreateSelectDQ", "Method Exit");
}
