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