C# WhereClause类代码示例

        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 },
                                 };
 }

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

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

        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 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 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;
        }

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

 public static int GetRecordCount(BaseFilter join, WhereClause where)
 {
     return (int)UsersTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null);
 }

        public static string Export(WhereClause where)
        {
            BaseFilter whereFilter = null;
            if (where != null)
            {
            whereFilter = where.GetFilter();
            }

            return UsersTable.Instance.ExportRecordData(whereFilter);
        }

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

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