public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { var where = new WhereClauseExpressionTreeVisitor(queryModel.MainFromClause.ItemType, _args.ColumnMappings); where.Visit(whereClause.Predicate); SqlStatement.Where = where.WhereClause; SqlStatement.Parameters = where.Params; SqlStatement.ColumnNamesUsed.AddRange(where.ColumnNamesUsed); base.VisitWhereClause(whereClause, queryModel, index); }
public WhereClauseHandler(WhereClause whereClause) { _whereClause = whereClause; _expressionFormatters = new Dictionary<SimpleExpressionType, Func<SimpleExpression, Func<IDictionary<string,object>, bool>>> { {SimpleExpressionType.And, LogicalExpressionToWhereClause}, {SimpleExpressionType.Or, LogicalExpressionToWhereClause}, {SimpleExpressionType.Equal, EqualExpressionToWhereClause}, {SimpleExpressionType.NotEqual, NotEqualExpressionToWhereClause}, {SimpleExpressionType.Function, FunctionExpressionToWhereClause}, {SimpleExpressionType.GreaterThan, GreaterThanToWhereClause}, {SimpleExpressionType.LessThan, LessThanToWhereClause}, {SimpleExpressionType.GreaterThanOrEqual, GreaterThanOrEqualToWhereClause}, {SimpleExpressionType.LessThanOrEqual, LessThanOrEqualToWhereClause}, {SimpleExpressionType.Empty, expr => _ => true }, }; }
public static int GetRecordCount(WhereClause where) { return (int)EstimateTable.Instance.GetRecordListCount(null, where.GetFilter(), null, null); }
/// <summary> /// Recursivly renders a WhereClause /// </summary> /// <param name="builder"></param> /// <param name="group"></param> protected virtual void WhereClause(StringBuilder builder, WhereClause group) { if (group.IsEmpty) { return; } builder.AppendFormat("("); for (int i = 0; i < group.Terms.Count; i++) { if (i > 0) { RelationshipOperator(builder, group.Relationship); } WhereTerm term = group.Terms[i]; WhereClause(builder, term); } bool operatorRequired = group.Terms.Count > 0; foreach (WhereClause childGroup in group.SubClauses) { if (childGroup.IsEmpty) { continue; } if (operatorRequired) { RelationshipOperator(builder, group.Relationship); } WhereClause(builder, childGroup); operatorRequired = true; } builder.AppendFormat(")"); }
/// <summary> /// Overrides the <see cref="QueryModelVisitorBase.VisitWhereClause(WhereClause, QueryModel, int)"/>. /// </summary> /// <param name="whereClause">The <see cref="WhereClause"/>.</param> /// <param name="queryModel">The <see cref="QueryModel"/>.</param> /// <param name="index">The index.</param> public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { _aggregator.Where.Add(GetODataExpression(whereClause.Predicate)); base.VisitWhereClause(whereClause, queryModel, index); }
public virtual WhereClause CreateWhereClause(String searchText, String fromSearchControl, String AutoTypeAheadSearch, String AutoTypeAheadWordSeparators) { // This CreateWhereClause is used for loading list of suggestions for Auto Type-Ahead feature. ScopeTable.Instance.InnerFilter = null; WhereClause wc= new WhereClause(); // Compose the WHERE clause consiting of: // 1. Static clause defined at design time. // 2. User selected search criteria. // 3. User selected filter criteria. String appRelativeVirtualPath = (String)HttpContext.Current.Session["AppRelativeVirtualPath"]; // Adds clauses if values are selected in Filter controls which are configured in the page. return wc; }
public virtual WhereClause CreateWhereClause() { // This CreateWhereClause is used for loading the data. UOMTable.Instance.InnerFilter = null; WhereClause wc = new WhereClause(); // CreateWhereClause() Compose the WHERE clause consiting of: // 1. Static clause defined at design time. // 2. User selected search criteria. // 3. User selected filter criteria. if (MiscUtils.IsValueSelected(this.UOMDescriptionFilter)) { wc.iAND(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.EqualsTo, MiscUtils.GetSelectedValue(this.UOMDescriptionFilter, this.GetFromSession(this.UOMDescriptionFilter)), false, false); } if (MiscUtils.IsValueSelected(this.UOMNameFilter)) { wc.iAND(UOMTable.UOMName, BaseFilter.ComparisonOperator.EqualsTo, MiscUtils.GetSelectedValue(this.UOMNameFilter, this.GetFromSession(this.UOMNameFilter)), false, false); } if (MiscUtils.IsValueSelected(this.UOMSearch)) { // Strip "..." from begin and ending of the search text, otherwise the search will return 0 values as in database "..." is not stored. if (this.UOMSearch.Text.StartsWith("...")) { this.UOMSearch.Text = this.UOMSearch.Text.Substring(3,this.UOMSearch.Text.Length-3); } if (this.UOMSearch.Text.EndsWith("...")) { this.UOMSearch.Text = this.UOMSearch.Text.Substring(0,this.UOMSearch.Text.Length-3); // Strip the last word as well as it is likely only a partial word int endindex = this.UOMSearch.Text.Length - 1; while (!Char.IsWhiteSpace(UOMSearch.Text[endindex]) && endindex > 0) { endindex--; } if (endindex > 0) { this.UOMSearch.Text = this.UOMSearch.Text.Substring(0, endindex); } } string formatedSearchText = MiscUtils.GetSelectedValue(this.UOMSearch, this.GetFromSession(this.UOMSearch)); // After stripping "..." see if the search text is null or empty. if (MiscUtils.IsValueSelected(this.UOMSearch)) { // These clauses are added depending on operator and fields selected in Control's property page, bindings tab. WhereClause search = new WhereClause(); search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false); search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false); wc.iAND(search); } } return wc; }
public static UsersRecord[] GetRecords( WhereClause where, OrderBy orderBy, int pageIndex, int pageSize) { BaseClasses.Data.BaseFilter join = null; ArrayList recList = UsersTable.Instance.GetRecordList(join, where.GetFilter(), null, orderBy, pageIndex, pageSize); return (UsersRecord[])recList.ToArray(Type.GetType("FPCEstimate.Business.UsersRecord")); }
/// <summary> /// Visits the where clause. /// </summary> /// <param name="whereClause">The where clause.</param> /// <param name="queryModel">The query model.</param> /// <param name="index">The index.</param> public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { var query = QueryDocumentBuilder.BuildFrom(this.mongoSession, whereClause.Predicate); query.CopyTo(this.querySpec.Conditions); }
public static List <T> Select <T>(WhereClause where) { return(Select(typeof(T), where) as List <T>); }
public static IEnumerable Select(this Type type, WhereClause where) { var method = type.BaseType.BaseType.GetMethod("Select", new Type[] { typeof(WhereClause) }); return(method.Invoke(null, new object[] { where }) as IEnumerable); }
public static int GetRecordCount(BaseFilter join, WhereClause where) { return((int)FieldTripChoicesTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null)); }
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { base.VisitWhereClause(whereClause, queryModel, index); filterPredicateAnalyer.Apply(whereClause.Predicate); }
/// <summary> /// Adds the is after date or equal. /// </summary> /// <param name="whereClause">The where clause.</param> /// <param name="logicOperator">The logic operator.</param> /// <param name="column">The column.</param> /// <param name="date">The date.</param> /// <returns>The clause.</returns> public static WhereClause AddIsAfterDateOrEqual(this WhereClause whereClause, LogicOperator logicOperator, string column, DateTime date) { whereClause.AddClause(logicOperator, column, Comparison.GreaterOrEquals, date.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture)); return(whereClause); }
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { _whereJoinDetector.Transform(whereClause); }
/// <summary> /// Creates a WHERE clause for a multivalued attribute /// </summary> /// <param name="builder">The builder for this query</param> /// <param name="parameterNames">The parameters to evaluate against</param> /// <returns>The WhereClause object for this query</returns> private WhereClause CreateWhereClauseforAVPTarget(DBQueryBuilder builder, IList <string> parameterNames) { FromTerm fromTable; fromTable = builder.GetNextAVPTableReference(this.SearchAttribute.TableName); WhereClause clause = new WhereClause(WhereClauseRelationship.And); if (this.Operator != ValueOperator.NotPresent) { string paramName = builder.AddParameter(this.SearchAttribute.Name); WhereTerm attributeNameTerm = WhereTerm.CreateCompare(SqlExpression.Field("attributeName", fromTable), SqlExpression.Parameter(paramName), CompareOperator.Equal); clause.Terms.Add(attributeNameTerm); } if (this.Operator == ValueOperator.IsPresent) { clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable))); } else if (this.Operator == ValueOperator.NotPresent) { string sql = this.CreateSubSelectStatementForMVNotExists(builder); clause.Terms.Add(WhereTerm.CreateNotExists(sql)); } else { WhereClause subClause = new WhereClause(WhereClauseRelationship.Or); switch (this.Operator) { case ValueOperator.And: case ValueOperator.Contains: case ValueOperator.GreaterThan: case ValueOperator.GreaterThanOrEq: case ValueOperator.LessThan: case ValueOperator.StartsWith: case ValueOperator.EndsWith: case ValueOperator.LessThanOrEq: foreach (string parameterName in parameterNames) { subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator)); } break; case ValueOperator.Equals: subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString())); break; case ValueOperator.NotEquals: string sql = this.CreateSubSelectStatementForMVNotEquals(builder, parameterNames); clause.Terms.Add(WhereTerm.CreateNotExists(sql)); break; default: throw new InvalidOperationException(); } clause.SubClauses.Add(subClause); } return(clause); }
/// <summary> /// Return an array of values from the database. The values returned are DISTINCT values. /// For example, GetColumnValues("Employees", "City") will return a list of all Cities /// from the Employees table. There will be no duplicates in the list. /// This function adds a Where Clause. So you can say something like "Country = 'USA'" and in this /// case only cities in the US will be returned. /// You can use the IN operator to compare the values. You can also use the resulting array to display /// such as String.Join(", ", GetColumnValues("Employees", "City", "Country = 'USA'") /// to display: New York, Chicago, Los Angeles, San Francisco /// </summary> /// <returns>An array of values for the given field as an Object.</returns> public static string[] GetColumnValues(string tableName, string fieldName, string whereStr) { // Find the PrimaryKeyTable bt = null; bt = (PrimaryKeyTable)DatabaseObjects.GetTableObject(tableName); if (bt == null) { throw new Exception("GETCOLUMNVALUES(" + tableName + ", " + fieldName + ", " + whereStr + "): " + Resource("Err:NoRecRetrieved")); } BaseColumn col = bt.TableDefinition.ColumnList.GetByCodeName(fieldName); if (col == null) { throw new Exception("GETCOLUMNVALUES(" + tableName + ", " + fieldName + ", " + whereStr + "): " + Resource("Err:NoRecRetrieved")); } string[] values = null; try { // Always start a transaction since we do not know if the calling function did. SqlBuilderColumnSelection sqlCol = new SqlBuilderColumnSelection(false, true); sqlCol.AddColumn(col); WhereClause wc = new WhereClause(); if (!(whereStr == null) && whereStr.Trim().Length > 0) { wc.iAND(whereStr); } BaseClasses.Data.BaseFilter join = null; values = bt.GetColumnValues(sqlCol, join, wc.GetFilter(), null, null, BaseTable.MIN_PAGE_NUMBER, BaseTable.MAX_BATCH_SIZE); } catch { } // The value can be null. In this case, return an empty array since // that is an acceptable value. if (values == null) { values = new string[0]; } return values; }
public static T SelectSingle <T>(WhereClause where) { return(SelectSingle(typeof(T), where).ConvertTo <T>()); }
public static string Export(WhereClause where) { BaseFilter whereFilter = null; if (where != null) { whereFilter = where.GetFilter(); } return UsersTable.Instance.ExportRecordData(whereFilter); }
/// <summary> /// Generates a command that can be executed to update a row -- and return the contents of /// the updated row. /// </summary> /// <param name="connection">The connection the command should be associated with</param> /// <returns>Command to update the row</returns> public override DbCommand GetCommand(DbConnection connection) { Validate.IsNotNull(nameof(connection), connection); // Process the cells and columns List <string> declareColumns = new List <string>(); List <SqlParameter> inParameters = new List <SqlParameter>(); List <string> setComponents = new List <string>(); List <string> outClauseColumns = new List <string>(); List <string> selectColumns = new List <string>(); for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++) { EditColumnMetadata metadata = AssociatedObjectMetadata.Columns[i]; // Add the output columns regardless of whether the column is read only declareColumns.Add($"{metadata.EscapedName} {ToSqlScript.FormatColumnType(metadata.DbColumn, useSemanticEquivalent: true)}"); if (metadata.IsHierarchyId) { outClauseColumns.Add($"inserted.{metadata.EscapedName}.ToString() {metadata.EscapedName}"); } else { outClauseColumns.Add($"inserted.{metadata.EscapedName}"); } selectColumns.Add(metadata.EscapedName); // If we have a new value for the column, proccess it now CellUpdate cellUpdate; if (cellUpdates.TryGetValue(i, out cellUpdate)) { string paramName = $"@Value{RowId}_{i}"; if (metadata.IsHierarchyId) { setComponents.Add($"{metadata.EscapedName} = CONVERT(hierarchyid,{paramName})"); } else { setComponents.Add($"{metadata.EscapedName} = {paramName}"); } inParameters.Add(new SqlParameter(paramName, AssociatedResultSet.Columns[i].SqlDbType) { Value = cellUpdate.Value }); } } // Put everything together into a single query // Step 1) Build a temp table for inserting output values into string tempTableName = $"@Update{RowId}Output"; string declareStatement = string.Format(DeclareStatement, tempTableName, string.Join(", ", declareColumns)); // Step 2) Build the update statement WhereClause whereClause = GetWhereClause(true); string updateStatementFormat = AssociatedObjectMetadata.IsMemoryOptimized ? UpdateOutputMemOptimized : UpdateOutput; string updateStatement = string.Format(updateStatementFormat, AssociatedObjectMetadata.EscapedMultipartName, string.Join(", ", setComponents), string.Join(", ", outClauseColumns), tempTableName, whereClause.CommandText); string validateScript = string.Format(CultureInfo.InvariantCulture, validateUpdateOnlyOneRow, AssociatedObjectMetadata.EscapedMultipartName, whereClause.CommandText); // Step 3) Build the select statement string selectStatement = string.Format(SelectStatement, string.Join(", ", selectColumns), tempTableName); // Step 4) Put it all together into a results object StringBuilder query = new StringBuilder(); query.AppendLine(declareStatement); query.AppendLine(validateScript); query.AppendLine(updateStatement); query.AppendLine(selectStatement); query.Append("END"); // Build the command DbCommand command = connection.CreateCommand(); command.CommandText = query.ToString(); command.CommandType = CommandType.Text; command.Parameters.AddRange(inParameters.ToArray()); command.Parameters.AddRange(whereClause.Parameters.ToArray()); return(command); }
public static string GetSum( BaseColumn col, BaseFilter join, WhereClause where, OrderBy orderBy, int pageIndex, int pageSize) { SqlBuilderColumnSelection colSel = new SqlBuilderColumnSelection(false, false); colSel.AddColumn(col, SqlBuilderColumnOperation.OperationType.Sum); return UsersTable.Instance.GetColumnStatistics(colSel, join, where.GetFilter(), null, orderBy, pageIndex, pageSize); }
static void Main(string[] args) { var exampleToRun = ExamplesEnumeration.JoinWithExtensionMethodsSyntax; switch (exampleToRun) { case ExamplesEnumeration.BasicSyntax: BasicSyntax.ShowBasicSyntaxLinq(); break; case ExamplesEnumeration.GenderCondition: BasicSyntax.ShowConditionWithGender(); break; case ExamplesEnumeration.ExtensionMethodSyntax: ExtensionMethodSyntax.ShowExtensionMethodSyntax(); break; case ExamplesEnumeration.DefferedExecution: ExtensionMethodSyntax.DefferedExecution(); break; case ExamplesEnumeration.WhereBasicSyntax: WhereClause.ShowWhereWithBasicSyntax(); break; case ExamplesEnumeration.WhereExtensionSyntax: WhereClause.ShowWhereWithExtensionSyntax(); break; case ExamplesEnumeration.OrderByBasicSyntax: OrderByClause.ShowOrderBy(); break; case ExamplesEnumeration.OrderByExtensionSyntax: OrderByClause.OrderByExtensionSyntax(); break; case ExamplesEnumeration.GroupByBasicSyntax: GroupByClause.ShowGroupBy(); break; case ExamplesEnumeration.GroupByExtensionSyntax: GroupByClause.GroupByExtensionSyntax(); break; case ExamplesEnumeration.GroupByInto: GroupByClause.GroupByInto(); break; case ExamplesEnumeration.AnonymousTypes: AnonymousTypes.ShowAnonymousTypes(); break; case ExamplesEnumeration.AnonymousTypesTryChangeProperty: AnonymousTypes.TryChangeProperty(); break; case ExamplesEnumeration.AnonymousTypesPropertyNamesInheritance: AnonymousTypes.PropertiesNamesInheritance(); break; case ExamplesEnumeration.ExplicitSelectClause: ExplicitSelectClause.ShowExplicitSelect(); break; case ExamplesEnumeration.ExplicitSelectAnonymousType: ExplicitSelectClause.SelectAnonymousType(); break; case ExamplesEnumeration.DynamicTypes: DynamicTypes.ShowDynamicTypes(); break; case ExamplesEnumeration.JoinWithQuerySyntax: JoinClause.ShowJoinWithQuerySyntax(); break; case ExamplesEnumeration.JoinWithExtensionMethodsSyntax: JoinClause.ShowJoinWithExtensionMethodsSyntax(); break; } Console.Read(); }
public virtual WhereClause CreateWhereClause(String searchText, String fromSearchControl, String AutoTypeAheadSearch, String AutoTypeAheadWordSeparators) { // This CreateWhereClause is used for loading list of suggestions for Auto Type-Ahead feature. EstimateLineTable.Instance.InnerFilter = null; WhereClause wc = new WhereClause(); // Compose the WHERE clause consiting of: // 1. Static clause defined at design time. // 2. User selected search criteria. // 3. User selected filter criteria. String appRelativeVirtualPath = (String)HttpContext.Current.Session["AppRelativeVirtualPath"]; string selectedRecordInScopeRecordControl = HttpContext.Current.Session["EstimateLineTableControlWhereClause"] as string; if (selectedRecordInScopeRecordControl != null && KeyValue.IsXmlKey(selectedRecordInScopeRecordControl)) { KeyValue selectedRecordKeyValue = KeyValue.XmlToKey(selectedRecordInScopeRecordControl); if (selectedRecordKeyValue != null && selectedRecordKeyValue.ContainsColumn(EstimateLineTable.ScopeID)) { wc.iAND(EstimateLineTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, selectedRecordKeyValue.GetColumnValue(EstimateLineTable.ScopeID).ToString()); } } // Adds clauses if values are selected in Filter controls which are configured in the page. return wc; }
public virtual void LoadData() { // Load the data from the database into the DataSource DatabaseOLR_db%dbo.RegistrationTypes record. // It is better to make changes to functions called by LoadData such as // CreateWhereClause, rather than making changes here. // The RecordUniqueId is set the first time a record is loaded, and is // used during a PostBack to load the record. if (this.RecordUniqueId != null && this.RecordUniqueId.Length > 0) { this.DataSource = RegistrationTypesTable.GetRecord(this.RecordUniqueId, true); return; } // This is the first time a record is being retrieved from the database. // So create a Where Clause based on the staic Where clause specified // on the Query wizard and the dynamic part specified by the end user // on the search and filter controls (if any). WhereClause wc = this.CreateWhereClause(); System.Web.UI.WebControls.Panel Panel = (System.Web.UI.WebControls.Panel)MiscUtils.FindControlRecursively(this, "RegistrationTypesRecordControlPanel"); if (Panel != null) { Panel.Visible = true; } // If there is no Where clause, then simply create a new, blank record. if (wc == null || !(wc.RunQuery)) { this.DataSource = new RegistrationTypesRecord(); if (Panel != null) { Panel.Visible = false; } return; } // Retrieve the record from the database. It is possible RegistrationTypesRecord[] recList = RegistrationTypesTable.GetRecords(wc, null, 0, 2); if (recList.Length == 0) { // There is no data for this Where clause. wc.RunQuery = false; if (Panel != null) { Panel.Visible = false; } return; } // Set DataSource based on record retrieved from the database. this.DataSource = RegistrationTypesTable.GetRecord(recList[0].GetID().ToXmlString(), true); }
// Fill the UOMID list. protected virtual void PopulateUOMIDDropDownList(string selectedValue, int maxItems) { this.UOMID.Items.Clear(); // 1. Setup the static list items // Add the Please Select item. this.UOMID.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:PleaseSelect", "FPCEstimate"), "--PLEASE_SELECT--")); // 2. Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_UOMIDDropDownList function. // It is better to customize the where clause there. WhereClause wc = CreateWhereClause_UOMIDDropDownList(); // Create the ORDER BY clause to sort based on the displayed value. OrderBy orderBy = new OrderBy(false, false); orderBy.Add(UOMTable.UOMName, OrderByItem.OrderDir.Asc); System.Collections.Generic.IDictionary<string, object> variables = new System.Collections.Generic.Dictionary<string, object> (); // 3. Read a total of maxItems from the database and insert them into the UOMIDDropDownList. UOMRecord[] itemValues = null; if (wc.RunQuery) { int counter = 0; int pageNum = 0; FormulaEvaluator evaluator = new FormulaEvaluator(); do { itemValues = UOMTable.GetRecords(wc, orderBy, pageNum, maxItems); foreach (UOMRecord itemValue in itemValues) { // Create the item and add to the list. string cvalue = null; string fvalue = null; if (itemValue.UOMIDSpecified) { cvalue = itemValue.UOMID.ToString().ToString(); if (counter < maxItems && this.UOMID.Items.FindByValue(cvalue) == null) { Boolean _isExpandableNonCompositeForeignKey = ScopeTable.Instance.TableDefinition.IsExpandableNonCompositeForeignKey(ScopeTable.UOMID); if(_isExpandableNonCompositeForeignKey && ScopeTable.UOMID.IsApplyDisplayAs) fvalue = ScopeTable.GetDFKA(itemValue, ScopeTable.UOMID); if ((!_isExpandableNonCompositeForeignKey) || (String.IsNullOrEmpty(fvalue))) fvalue = itemValue.Format(UOMTable.UOMName); if (fvalue == null || fvalue.Trim() == "") fvalue = cvalue; ListItem newItem = new ListItem(fvalue, cvalue); this.UOMID.Items.Add(newItem); counter += 1; } } } pageNum++; } while (itemValues.Length == maxItems && counter < maxItems); } // 4. Set the selected value (insert if not already present). if (selectedValue != null && selectedValue.Trim() != "" && !MiscUtils.SetSelectedValue(this.UOMID, selectedValue) && !MiscUtils.SetSelectedDisplayText(this.UOMID, selectedValue)) { // construct a whereclause to query a record with UOM.UOMID = selectedValue CompoundFilter filter2 = new CompoundFilter(CompoundFilter.CompoundingOperators.And_Operator, null); WhereClause whereClause2 = new WhereClause(); filter2.AddFilter(new BaseClasses.Data.ColumnValueFilter(UOMTable.UOMID, selectedValue, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, false)); whereClause2.AddFilter(filter2, CompoundFilter.CompoundingOperators.And_Operator); // Execute the query try { UOMRecord[] rc = UOMTable.GetRecords(whereClause2, new OrderBy(false, false), 0, 1); System.Collections.Generic.IDictionary<string, object> vars = new System.Collections.Generic.Dictionary<string, object> (); // if find a record, add it to the dropdown and set it as selected item if (rc != null && rc.Length == 1) { string fvalue = ScopeTable.UOMID.Format(selectedValue); ListItem item = new ListItem(fvalue, selectedValue); item.Selected = true; this.UOMID.Items.Add(item); } } catch { } } }
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { whereClause.TransformExpressions(e => MergeAggregatingResultsInExpressionRewriter.Rewrite(e, new NameGenerator(queryModel))); }
public virtual WhereClause CreateWhereClause_UOMNameFilter() { // Create a where clause for the filter UOMNameFilter. // This function is called by the Populate method to load the items // in the UOMNameFilterDropDownList WhereClause wc = new WhereClause(); return wc; }
internal void Parse(WhereClause whereClause) { Visit(whereClause.Predicate); }
/// <summary> /// Creates a WHERE clause for a single-valued attribute /// </summary> /// <param name="parameterNames">The parameters to evaluate against</param> /// <returns>The WhereClause object for this query</returns> private WhereClause CreateWhereClauseforSVTarget(IList <string> parameterNames) { FromTerm fromTable; fromTable = this.SearchAttribute.DBTable; WhereClause clause = new WhereClause(WhereClauseRelationship.And); if (this.Operator == ValueOperator.IsPresent) { clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable))); } else if (this.Operator == ValueOperator.NotPresent) { clause.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable))); } else if (parameterNames.Count == 0) { throw new ArgumentNullException("parameterNames"); } else if (parameterNames.Count > 1) { WhereClause subClause = new WhereClause(WhereClauseRelationship.Or); switch (this.Operator) { case ValueOperator.And: case ValueOperator.Contains: case ValueOperator.StartsWith: case ValueOperator.EndsWith: case ValueOperator.GreaterThan: case ValueOperator.GreaterThanOrEq: case ValueOperator.LessThan: case ValueOperator.LessThanOrEq: foreach (string parameterName in parameterNames) { subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator)); } break; case ValueOperator.Equals: subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString())); break; case ValueOperator.NotEquals: subClause.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString())); break; default: throw new InvalidOperationException(); } clause.SubClauses.Add(subClause); } else { clause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterNames[0]), this.CompareOperator)); } return(clause); }
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { AppendLine(); TransformingVisitor.StringBuilder.Append("where "); base.VisitWhereClause(whereClause, queryModel, index); }
public static string GetCount( BaseColumn col, WhereClause where, OrderBy orderBy, int pageIndex, int pageSize) { SqlBuilderColumnSelection colSel = new SqlBuilderColumnSelection(false, false); colSel.AddColumn(col, SqlBuilderColumnOperation.OperationType.Count); return EstimateTable.Instance.GetColumnStatistics(colSel, null, where.GetFilter(), null, orderBy, pageIndex, pageSize); }
public static int GetRecordCount(WhereClause where) { return((int)Roles1Table.Instance.GetRecordListCount(null, where.GetFilter(), null, null)); }
public static EstimateRecord[] GetRecords( BaseFilter join, WhereClause where, OrderBy orderBy, int pageIndex, int pageSize) { ArrayList recList = EstimateTable.Instance.GetRecordList(join, where.GetFilter(), null, orderBy, pageIndex, pageSize); return (EstimateRecord[])recList.ToArray(Type.GetType("FPCEstimate.Business.EstimateRecord")); }
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index) { _expander.Transform(whereClause); }
// To customize, override this method in ReportRecordControl. public virtual WhereClause CreateWhereClause() { WhereClause wc; ReportTable.Instance.InnerFilter = null; wc = new WhereClause(); // Compose the WHERE clause consiting of: // 1. Static clause defined at design time. // 2. User selected search criteria. // 3. User selected filter criteria. // Retrieve the record id from the URL parameter. string recId = this.Page.Request.QueryString["Report"]; if (recId == null || recId.Length == 0) { return null; } recId = ((BaseApplicationPage)(this.Page)).Decrypt(recId); if (recId == null || recId.Length == 0) { return null; } HttpContext.Current.Session["QueryString in AddReport"] = recId; if (KeyValue.IsXmlKey(recId)) { // Keys are typically passed as XML structures to handle composite keys. // If XML, then add a Where clause based on the Primary Key in the XML. KeyValue pkValue = KeyValue.XmlToKey(recId); wc.iAND(ReportTable.ReportID, BaseFilter.ComparisonOperator.EqualsTo, pkValue.GetColumnValueString(ReportTable.ReportID)); } else { // The URL parameter contains the actual value, not an XML structure. wc.iAND(ReportTable.ReportID, BaseFilter.ComparisonOperator.EqualsTo, recId); } return wc; }
public static void Update(EmployeeOtherControlsSubscriptionsViewData data) { // Create and execute the command EmployeeOtherControlsSubscriptionsViewData oldData = Load(); string sql = "Update " + TABLE + " set "; if (!oldData.Src.Equals(data.Src)) { sql = sql + "Src=@Src,"; } if (!oldData.Description.Equals(data.Description)) { sql = sql + "Description=@Description,"; } if (!oldData.ClassSrc.Equals(data.ClassSrc)) { sql = sql + "ClassSrc=@ClassSrc,"; } if (!oldData.ClassDescription.Equals(data.ClassDescription)) { sql = sql + "ClassDescription=@ClassDescription,"; } if (!oldData.AllowUnsubscribe.Equals(data.AllowUnsubscribe)) { sql = sql + "AllowUnsubscribe=@AllowUnsubscribe,"; } if (!oldData.SortOrder.Equals(data.SortOrder)) { sql = sql + "SortOrder=@SortOrder,"; } if (!oldData.OrgEmployeesID.Equals(data.OrgEmployeesID)) { sql = sql + "OrgEmployeesID=@OrgEmployeesID,"; } if (!oldData.ControlsID.Equals(data.ControlsID)) { sql = sql + "ControlsID=@ControlsID,"; } if (!oldData.ControlSubscriptionsID.Equals(data.ControlSubscriptionsID)) { sql = sql + "ControlSubscriptionsID=@ControlSubscriptionsID,"; } WhereClause w = new WhereClause(); sql = sql.Substring(0, sql.Length - 1) + w.FormatSql(); SqlCommand cmd = GetSqlCommand(DatabaseEnum.INTRANET, sql, CommandType.Text, COMMAND_TIMEOUT); //Create the parameters and append them to the command object if (!oldData.Src.Equals(data.Src)) { cmd.Parameters.Add(new SqlParameter("@Src", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Src", DataRowVersion.Proposed, data.Src.DBValue)); } if (!oldData.Description.Equals(data.Description)) { cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Description", DataRowVersion.Proposed, data.Description.DBValue)); } if (!oldData.ClassSrc.Equals(data.ClassSrc)) { cmd.Parameters.Add(new SqlParameter("@ClassSrc", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "ClassSrc", DataRowVersion.Proposed, data.ClassSrc.DBValue)); } if (!oldData.ClassDescription.Equals(data.ClassDescription)) { cmd.Parameters.Add(new SqlParameter("@ClassDescription", SqlDbType.VarChar, 30, ParameterDirection.Input, false, 0, 0, "ClassDescription", DataRowVersion.Proposed, data.ClassDescription.DBValue)); } if (!oldData.AllowUnsubscribe.Equals(data.AllowUnsubscribe)) { cmd.Parameters.Add(new SqlParameter("@AllowUnsubscribe", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "AllowUnsubscribe", DataRowVersion.Proposed, !data.AllowUnsubscribe.IsValid ? data.AllowUnsubscribe.DBValue : data.AllowUnsubscribe.DBValue.Equals("Y") ? 1 : 0)); } if (!oldData.SortOrder.Equals(data.SortOrder)) { cmd.Parameters.Add(new SqlParameter("@SortOrder", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "SortOrder", DataRowVersion.Proposed, data.SortOrder.DBValue)); } if (!oldData.OrgEmployeesID.Equals(data.OrgEmployeesID)) { cmd.Parameters.Add(new SqlParameter("@OrgEmployeesID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "OrgEmployeesID", DataRowVersion.Proposed, data.OrgEmployeesID.DBValue)); } if (!oldData.ControlsID.Equals(data.ControlsID)) { cmd.Parameters.Add(new SqlParameter("@ControlsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "ControlsID", DataRowVersion.Proposed, data.ControlsID.DBValue)); } if (!oldData.ControlSubscriptionsID.Equals(data.ControlSubscriptionsID)) { cmd.Parameters.Add(new SqlParameter("@ControlSubscriptionsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "ControlSubscriptionsID", DataRowVersion.Proposed, data.ControlSubscriptionsID.DBValue)); } // Execute the query if (cmd.Parameters.Count > 0) { cmd.ExecuteNonQuery(); } }
public static int GetRecordCount(BaseFilter join, WhereClause where) { return (int)UsersTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null); }
/// <summary> /// 获取查询条件 /// </summary> public virtual string GetCondition() => WhereClause.GetCondition();
public static UsersRecord[] GetRecords( BaseFilter join, WhereClause where, OrderBy orderBy, int pageIndex, int pageSize, ref int totalRecords) { ArrayList recList = UsersTable.Instance.GetRecordList(join, where.GetFilter(), null, orderBy, pageIndex, pageSize, ref totalRecords); return (UsersRecord[])recList.ToArray(Type.GetType("FPCEstimate.Business.UsersRecord")); }
public TObject SelectObject <TObject>(WhereClause whereClause) where TObject : DataObject => (TObject)SelectObjectReturns.FirstOrDefault();
public static String[] GetValues( BaseColumn col, BaseFilter join, WhereClause where, OrderBy orderBy, int maxItems) { // Create the filter list. SqlBuilderColumnSelection retCol = new SqlBuilderColumnSelection(false, true); retCol.AddColumn(col); return UsersTable.Instance.GetColumnValues(retCol, join, where.GetFilter(), null, orderBy, BaseTable.MIN_PAGE_NUMBER, maxItems); }
public IList <TObject> SelectObjects <TObject>(WhereClause whereClause) where TObject : DataObject => new List <TObject>();
public virtual WhereClause CreateWhereClause() { // This CreateWhereClause is used for loading the data. EstimateLineTable.Instance.InnerFilter = null; WhereClause wc = new WhereClause(); // CreateWhereClause() Compose the WHERE clause consiting of: // 1. Static clause defined at design time. // 2. User selected search criteria. // 3. User selected filter criteria. KeyValue selectedRecordKeyValue = new KeyValue(); FPCEstimate.UI.Controls.EditScope.ScopeRecordControl scopeRecordControlObj = (MiscUtils.FindControlRecursively(this.Page , "ScopeRecordControl") as FPCEstimate.UI.Controls.EditScope.ScopeRecordControl); if (scopeRecordControlObj != null && scopeRecordControlObj.GetRecord() != null && scopeRecordControlObj.GetRecord().IsCreated && scopeRecordControlObj.GetRecord().ScopeID != null) { wc.iAND(EstimateLineTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, scopeRecordControlObj.GetRecord().ScopeID.ToString()); selectedRecordKeyValue.AddElement(EstimateLineTable.ScopeID.InternalName, scopeRecordControlObj.GetRecord().ScopeID.ToString()); } else { wc.RunQuery = false; return wc; } HttpContext.Current.Session["EstimateLineTableControlWhereClause"] = selectedRecordKeyValue.ToXmlString(); return wc; }
public static void Update(KnowledgebasesSectionsArticlesData data) { // Create and execute the command KnowledgebasesSectionsArticlesData oldData = Load(data.KnowledgebasesSectionsArticlesID); string sql = "Update " + TABLE + " set "; if (!oldData.Title.Equals(data.Title)) { sql = sql + "Title=@Title,"; } if (!oldData.DateStart.Equals(data.DateStart)) { sql = sql + "DateStart=@DateStart,"; } if (!oldData.DateEnd.Equals(data.DateEnd)) { sql = sql + "DateEnd=@DateEnd,"; } if (!oldData.ParentID.Equals(data.ParentID)) { sql = sql + "ParentID=@ParentID,"; } if (!oldData.KnowledgebasesID.Equals(data.KnowledgebasesID)) { sql = sql + "KnowledgebasesID=@KnowledgebasesID,"; } if (!oldData.HasChild.Equals(data.HasChild)) { sql = sql + "HasChild=@HasChild,"; } if (!oldData.PrevKnowledgebasesSectionsID.Equals(data.PrevKnowledgebasesSectionsID)) { sql = sql + "PrevKnowledgebasesSectionsID=@PrevKnowledgebasesSectionsID,"; } if (!oldData.NextKnowledgebasesSectionsID.Equals(data.NextKnowledgebasesSectionsID)) { sql = sql + "NextKnowledgebasesSectionsID=@NextKnowledgebasesSectionsID,"; } if (!oldData.ListInParentArticle.Equals(data.ListInParentArticle)) { sql = sql + "ListInParentArticle=@ListInParentArticle,"; } if (!oldData.IncludeSummaryinParent.Equals(data.IncludeSummaryinParent)) { sql = sql + "IncludeSummaryinParent=@IncludeSummaryinParent,"; } if (!oldData.Sort.Equals(data.Sort)) { sql = sql + "Sort=@Sort,"; } if (!oldData.IsSection.Equals(data.IsSection)) { sql = sql + "IsSection=@IsSection,"; } if (!oldData.IsTemp.Equals(data.IsTemp)) { sql = sql + "IsTemp=@IsTemp,"; } WhereClause w = new WhereClause(); w.And("KnowledgebasesSectionsArticlesID", data.KnowledgebasesSectionsArticlesID.DBValue); sql = sql.Substring(0, sql.Length - 1) + w.FormatSql(); SqlCommand cmd = GetSqlCommand(DatabaseEnum.INTRANET, sql, CommandType.Text, COMMAND_TIMEOUT); //Create the parameters and append them to the command object if (!oldData.KnowledgebasesSectionsArticlesID.Equals(data.KnowledgebasesSectionsArticlesID)) { cmd.Parameters.Add(new SqlParameter("@KnowledgebasesSectionsArticlesID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "KnowledgebasesSectionsArticlesID", DataRowVersion.Proposed, data.KnowledgebasesSectionsArticlesID.DBValue)); } if (!oldData.Title.Equals(data.Title)) { cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "Title", DataRowVersion.Proposed, data.Title.DBValue)); } if (!oldData.DateStart.Equals(data.DateStart)) { cmd.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.SmallDateTime, 0, ParameterDirection.Input, false, 0, 0, "DateStart", DataRowVersion.Proposed, data.DateStart.DBValue)); } if (!oldData.DateEnd.Equals(data.DateEnd)) { cmd.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.SmallDateTime, 0, ParameterDirection.Input, false, 0, 0, "DateEnd", DataRowVersion.Proposed, data.DateEnd.DBValue)); } if (!oldData.ParentID.Equals(data.ParentID)) { cmd.Parameters.Add(new SqlParameter("@ParentID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "ParentID", DataRowVersion.Proposed, data.ParentID.DBValue)); } if (!oldData.KnowledgebasesID.Equals(data.KnowledgebasesID)) { cmd.Parameters.Add(new SqlParameter("@KnowledgebasesID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "KnowledgebasesID", DataRowVersion.Proposed, data.KnowledgebasesID.DBValue)); } if (!oldData.HasChild.Equals(data.HasChild)) { cmd.Parameters.Add(new SqlParameter("@HasChild", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "HasChild", DataRowVersion.Proposed, !data.HasChild.IsValid ? data.HasChild.DBValue : data.HasChild.DBValue.Equals("Y") ? 1 : 0)); } if (!oldData.PrevKnowledgebasesSectionsID.Equals(data.PrevKnowledgebasesSectionsID)) { cmd.Parameters.Add(new SqlParameter("@PrevKnowledgebasesSectionsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "PrevKnowledgebasesSectionsID", DataRowVersion.Proposed, data.PrevKnowledgebasesSectionsID.DBValue)); } if (!oldData.NextKnowledgebasesSectionsID.Equals(data.NextKnowledgebasesSectionsID)) { cmd.Parameters.Add(new SqlParameter("@NextKnowledgebasesSectionsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "NextKnowledgebasesSectionsID", DataRowVersion.Proposed, data.NextKnowledgebasesSectionsID.DBValue)); } if (!oldData.ListInParentArticle.Equals(data.ListInParentArticle)) { cmd.Parameters.Add(new SqlParameter("@ListInParentArticle", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "ListInParentArticle", DataRowVersion.Proposed, !data.ListInParentArticle.IsValid ? data.ListInParentArticle.DBValue : data.ListInParentArticle.DBValue.Equals("Y") ? 1 : 0)); } if (!oldData.IncludeSummaryinParent.Equals(data.IncludeSummaryinParent)) { cmd.Parameters.Add(new SqlParameter("@IncludeSummaryinParent", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IncludeSummaryinParent", DataRowVersion.Proposed, !data.IncludeSummaryinParent.IsValid ? data.IncludeSummaryinParent.DBValue : data.IncludeSummaryinParent.DBValue.Equals("Y") ? 1 : 0)); } if (!oldData.Sort.Equals(data.Sort)) { cmd.Parameters.Add(new SqlParameter("@Sort", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "Sort", DataRowVersion.Proposed, data.Sort.DBValue)); } if (!oldData.IsSection.Equals(data.IsSection)) { cmd.Parameters.Add(new SqlParameter("@IsSection", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IsSection", DataRowVersion.Proposed, !data.IsSection.IsValid ? data.IsSection.DBValue : data.IsSection.DBValue.Equals("Y") ? 1 : 0)); } if (!oldData.IsTemp.Equals(data.IsTemp)) { cmd.Parameters.Add(new SqlParameter("@IsTemp", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IsTemp", DataRowVersion.Proposed, !data.IsTemp.IsValid ? data.IsTemp.DBValue : data.IsTemp.DBValue.Equals("Y") ? 1 : 0)); } // Execute the query if (cmd.Parameters.Count > 0) { cmd.ExecuteNonQuery(); } }
// To customize, override this method in ScopeRecordControl. public virtual WhereClause CreateWhereClause() { WhereClause wc; ScopeTable.Instance.InnerFilter = null; wc = new WhereClause(); // Compose the WHERE clause consiting of: // 1. Static clause defined at design time. // 2. User selected search criteria. // 3. User selected filter criteria. // Retrieve the record id from the URL parameter. string recId = ((BaseApplicationPage)(this.Page)).Decrypt(this.Page.Request.QueryString["Scope"]); if (recId == null || recId.Length == 0) { // Get the error message from the application resource file. throw new Exception(Page.GetResourceValue("Err:UrlParamMissing", "FPCEstimate").Replace("{URL}", "Scope")); } HttpContext.Current.Session["QueryString in EditScope"] = recId; if (KeyValue.IsXmlKey(recId)) { // Keys are typically passed as XML structures to handle composite keys. // If XML, then add a Where clause based on the Primary Key in the XML. KeyValue pkValue = KeyValue.XmlToKey(recId); wc.iAND(ScopeTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, pkValue.GetColumnValueString(ScopeTable.ScopeID)); } else { // The URL parameter contains the actual value, not an XML structure. wc.iAND(ScopeTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, recId); } return wc; }
public static int GetRecordCount(BaseFilter join, WhereClause where) { return((int)UserRolesLinkTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null)); }
// Generate the event handling functions for pagination events. // Generate the event handling functions for filter and search events. public virtual WhereClause CreateWhereClause_UOMIDDropDownList() { // By default, we simply return a new WhereClause. // Add additional where clauses to restrict the items shown in the dropdown list. // This WhereClause is for the UOM table. // Examples: // wc.iAND(UOMTable.UOMName, BaseFilter.ComparisonOperator.EqualsTo, "XYZ"); // wc.iAND(UOMTable.Active, BaseFilter.ComparisonOperator.EqualsTo, "1"); WhereClause wc = new WhereClause(); return wc; }
public static int GetRecordCount(BaseFilter join, WhereClause where) { return (int)PhotoClubsTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null); }
protected virtual void UOMID_SelectedIndexChanged(object sender, EventArgs args) { // If a large list selector or a Quick Add link is used, the dropdown list // will contain an item that was not in the original (smaller) list. During postbacks, // this new item will not be in the list - since the list is based on the original values // read from the database. This function adds the value back if necessary. // In addition, This dropdown can be used on make/model/year style dropdowns. Make filters the result of Model. // Mode filters the result of Year. When users change the value of Make, Model and Year are repopulated. // When this function is fire for Make or Model, we don't want the following code executed. // Therefore, we check this situation using Items.Count > 1 // Since both of these feature use javascript to fire this event, this can be skip if the event is fired by server side, // so we check if it is not postback if (!this.Page.IsPostBack && this.UOMID.Items.Count > 1) { string selectedValue = MiscUtils.GetValueSelectedPageRequest(this.UOMID); if (selectedValue != null && selectedValue.Trim() != "" && !MiscUtils.SetSelectedValue(this.UOMID, selectedValue) && !MiscUtils.SetSelectedDisplayText(this.UOMID, selectedValue)) { // construct a whereclause to query a record with UOM.UOMID = selectedValue CompoundFilter filter2 = new CompoundFilter(CompoundFilter.CompoundingOperators.And_Operator, null); WhereClause whereClause2 = new WhereClause(); filter2.AddFilter(new BaseClasses.Data.ColumnValueFilter(UOMTable.UOMID, selectedValue, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, false)); whereClause2.AddFilter(filter2, CompoundFilter.CompoundingOperators.And_Operator); // Execute the query try { UOMRecord[] rc = UOMTable.GetRecords(whereClause2, new OrderBy(false, false), 0, 1); System.Collections.Generic.IDictionary<string, object> vars = new System.Collections.Generic.Dictionary<string, object> (); // if find a record, add it to the dropdown and set it as selected item if (rc != null && rc.Length == 1) { string fvalue = ScopeTable.UOMID.Format(selectedValue); ListItem item = new ListItem(fvalue, selectedValue); item.Selected = true; this.UOMID.Items.Add(item); } } catch { } } } }
/// <summary> /// 返回数量 /// </summary> /// <returns></returns> public int GetCount() { List <JoinRelation> tables = new List <JoinRelation>(); SqlBuilder.AppendFormat(" SELECT COUNT(1) AS CT FROM {0}", JoinRelations[0].LeftTable); if (WithAlias) { SqlBuilder.AppendFormat(" AS {0}", JoinRelations[0].LeftTableAlias); } foreach (JoinRelation j in JoinRelations) { if (j.JoinType == JoinType.Outer) { SqlBuilder.Append(" LEFT OUTER JOIN "); } else { SqlBuilder.Append(" INNER JOIN "); } if (WithAlias) { if (tables.Count(m => m.LeftTableAlias == j.RightTableAlias || m.RightTableAlias == j.RightTableAlias) == 0) { SqlBuilder.AppendFormat("{0} AS {1}", j.RightTable, j.RightTableAlias); tables.Add(j); } else { SqlBuilder.AppendFormat("{0} AS {1}", j.LeftTable, j.LeftTableAlias); tables.Add(j); } } else { if (tables.Count(m => m.LeftTable == j.RightTable || m.RightTable == j.RightTable) == 0) { SqlBuilder.Append(j.RightTable); tables.Add(j); } else { SqlBuilder.Append(j.LeftTable); tables.Add(j); } } SqlBuilder.AppendFormat(" ON {0}", j.OnSql.TrimEnd("AND".ToCharArray())); } if (WhereClause.Length > 0) { SqlBuilder.AppendFormat(" WHERE {0}", WhereClause.ToString().Trim().TrimEnd("AND".ToCharArray())); } if (GroupByFields.Length > 0) { SqlBuilder.AppendFormat(" GROUP BY {0}", GroupByFields.ToString().Trim().TrimEnd(',')); } if (OrderByFields.Length > 0) { SqlBuilder.AppendFormat(" ORDER BY {0}", OrderByFields.ToString().Trim().TrimEnd(',')); } //开始组装sql int result = DbContext.GetSingle <int>(SqlBuilder.ToString(), Parameters.ToArray()); if (this.IsAutoDisposeDbContext) { DbContext.Dispose(); } return(result); }
public virtual WhereClause CreateWhereClause(String searchText, String fromSearchControl, String AutoTypeAheadSearch, String AutoTypeAheadWordSeparators) { // This CreateWhereClause is used for loading list of suggestions for Auto Type-Ahead feature. UOMTable.Instance.InnerFilter = null; WhereClause wc = new WhereClause(); // Compose the WHERE clause consiting of: // 1. Static clause defined at design time. // 2. User selected search criteria. // 3. User selected filter criteria. String appRelativeVirtualPath = (String)HttpContext.Current.Session["AppRelativeVirtualPath"]; // Adds clauses if values are selected in Filter controls which are configured in the page. String UOMDescriptionFilterSelectedValue = (String)HttpContext.Current.Session[HttpContext.Current.Session.SessionID + appRelativeVirtualPath + "UOMDescriptionFilter_Ajax"]; if (MiscUtils.IsValueSelected(UOMDescriptionFilterSelectedValue)) { wc.iAND(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.EqualsTo, UOMDescriptionFilterSelectedValue, false, false); } String UOMNameFilterSelectedValue = (String)HttpContext.Current.Session[HttpContext.Current.Session.SessionID + appRelativeVirtualPath + "UOMNameFilter_Ajax"]; if (MiscUtils.IsValueSelected(UOMNameFilterSelectedValue)) { wc.iAND(UOMTable.UOMName, BaseFilter.ComparisonOperator.EqualsTo, UOMNameFilterSelectedValue, false, false); } if (MiscUtils.IsValueSelected(searchText) && fromSearchControl == "UOMSearch") { String formatedSearchText = searchText; // strip "..." from begin and ending of the search text, otherwise the search will return 0 values as in database "..." is not stored. if (searchText.StartsWith("...")) { formatedSearchText = searchText.Substring(3,searchText.Length-3); } if (searchText.EndsWith("...")) { formatedSearchText = searchText.Substring(0,searchText.Length-3); } // After stripping "...", trim any leading and trailing whitespaces formatedSearchText = formatedSearchText.Trim(); // After stripping "..." see if the search text is null or empty. if (MiscUtils.IsValueSelected(searchText)) { // These clauses are added depending on operator and fields selected in Control's property page, bindings tab. WhereClause search = new WhereClause(); if (StringUtils.InvariantLCase(AutoTypeAheadSearch).Equals("wordsstartingwithsearchstring")) { search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Starts_With, formatedSearchText, true, false); search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Contains, AutoTypeAheadWordSeparators + formatedSearchText, true, false); search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Starts_With, formatedSearchText, true, false); search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Contains, AutoTypeAheadWordSeparators + formatedSearchText, true, false); } else { search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false); search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false); } wc.iAND(search); } } return wc; }
private bool TryApplyWhereClause(WhereClause clause) { if (SkipCount.HasValue || TakeCount.HasValue) return false; if (_processedClauses.Any() && !(_processedClauses.Peek() is WhereClause)) return false; Criteria = Criteria == null ? clause.Criteria : new SimpleExpression(Criteria, clause.Criteria, SimpleExpressionType.And); return true; }
/// <summary> /// Renders the begining of a HAVING statement /// </summary> /// <param name="builder"></param> /// <param name="group"></param> protected virtual void Having(StringBuilder builder, WhereClause group) { if (group.IsEmpty) { return; } builder.AppendFormat(" having "); }
public TearOffWhereParameter(WhereClause clause) { this._clause = clause; }
/// <summary> /// 多表连查分页 /// </summary> /// <typeparam name="TModel"></typeparam> /// <param name="page"></param> /// <returns></returns> public PageDataSource <TModel> GetPage <TModel>(PageFilter page) { List <JoinRelation> tables = new List <JoinRelation>(); SqlBuilder.AppendFormat(" SELECT {0} FROM {1}", SelectFields.ToString().TrimEnd(','), JoinRelations[0].LeftTable); if (WithAlias) { SqlBuilder.AppendFormat(" AS {0}", JoinRelations[0].LeftTableAlias); } foreach (JoinRelation j in JoinRelations) { if (j.JoinType == JoinType.Outer) { SqlBuilder.Append(" LEFT OUTER JOIN "); } else { SqlBuilder.Append(" INNER JOIN "); } if (WithAlias) { if (tables.Count(m => m.LeftTableAlias == j.RightTableAlias || m.RightTableAlias == j.RightTableAlias) == 0) { SqlBuilder.AppendFormat("{0} AS {1}", j.RightTable, j.RightTableAlias); tables.Add(j); } else { SqlBuilder.AppendFormat("{0} AS {1}", j.LeftTable, j.LeftTableAlias); tables.Add(j); } } else { if (tables.Count(m => m.LeftTable == j.RightTable || m.RightTable == j.RightTable) == 0) { SqlBuilder.Append(j.RightTable); tables.Add(j); } else { SqlBuilder.Append(j.LeftTable); tables.Add(j); } } SqlBuilder.AppendFormat(" ON {0}", j.OnSql.TrimEnd("AND".ToCharArray())); } if (WhereClause.Length > 0) { SqlBuilder.AppendFormat(" WHERE {0}", WhereClause.ToString().Trim().TrimEnd("AND".ToCharArray())); } if (GroupByFields.Length > 0) { SqlBuilder.AppendFormat(" GROUP BY {0}", GroupByFields.ToString().Trim().TrimEnd(',')); } if (OrderByFields.Length > 0) { page.OrderText = OrderByFields.ToString().Trim().TrimEnd(','); } //开始组装sql //开始组装sql PageDataSource <TModel> result = DbContext.GetPage <TModel>(SqlBuilder.ToString(), page, Parameters.ToArray()); if (this.IsAutoDisposeDbContext) { DbContext.Dispose(); } return(result); }
protected PostgresUpdateBase(IServiceProvider serviceProvider, UpdateClause updateClause, FromClause fromClause, JoinClause joinClause, WhereClause whereClause) : base(serviceProvider) { this.updateClause = updateClause; this.fromClause = fromClause; this.joinClause = joinClause; this.whereClause = whereClause; }
public override void FlushData() { this._whereClause = null; this._aggregateClause = null; base.FlushData(); }
public TearOffWhereParameter(WhereClause clause) { this._clause = clause; }
private static WhereClause GetWhereClause(IDictionary<string, object> whereParameters) { Debug.Assert((whereParameters != null) && (whereParameters.Count > 0)); WhereClause whereClause = new WhereClause(); whereClause.Parameters = new Dictionary<string, object>(whereParameters.Count); StringBuilder where = new StringBuilder(); int index = 0; foreach (KeyValuePair<string, object> parameter in whereParameters) { string key = parameter.Key; string value = (parameter.Value == null) ? null : parameter.Value.ToString(); // exclude null and empty values. if (!(String.IsNullOrEmpty(key) || String.IsNullOrEmpty(value))) { string newKey = "@p" + index++; if (where.Length > 0) { where.Append(" AND "); } where.Append(key); where.Append(" == "); where.Append(newKey); whereClause.Parameters.Add(newKey, parameter.Value); } } whereClause.Expression = where.ToString(); return whereClause; }
public static int ExecuteUpdateParams(this IDbConnection connection, string tableName, WhereClause whereClause, params ColumnValue[] values) { return(SqlDialect.ExecuteUpdateParams(connection, tableName, new[] { whereClause }, values)); }