C# WhereCondition.WhereNull方法代码示例

    /// <summary>
    /// Returns site condition for given site id.
    /// </summary>
    /// <param name="siteID">Site id</param>
    private static WhereCondition GetSiteCondition(int siteID)
    {
        var condition = new WhereCondition();

        switch (siteID)
        {
            case UniSelector.US_GLOBAL_RECORD:
                condition.WhereNull("StateSiteID");
                break;

            case UniSelector.US_ALL_RECORDS:
            case UniSelector.US_NONE_RECORD:
                break;

            default:
                condition.WhereEquals("StateSiteID", siteID);
                break;
        }

        return condition;
    }

    /// <summary>
    /// Returns where condition based on webpart fields.
    /// </summary>
    private WhereCondition GetWhereCondition()
    {
        var where = new WhereCondition().WhereEquals("SKUSiteID", SiteContext.CurrentSiteID);

        // Show products only from current site or global too, based on setting
        if (ECommerceSettings.AllowGlobalProducts(SiteContext.CurrentSiteName))
        {
            where.Where(w => w.WhereEquals("SKUSiteID", SiteContext.CurrentSiteID).Or().WhereNull("SKUSiteID"));
        }

        // Show/hide product variants - it is based on type of inventory tracking for parent product
        string trackByVariants = TrackInventoryTypeEnum.ByVariants.ToStringRepresentation();
        where.Where(v => v.Where(w => w.WhereNull("SKUParentSKUID").And().WhereNotEquals("SKUTrackInventory", trackByVariants))
                          .Or()
                          .Where(GetParentProductWhereCondition(new WhereCondition().WhereEquals("SKUTrackInventory", trackByVariants))));

        // Product type filter
        if (!string.IsNullOrEmpty(ProductType) && (ProductType != FILTER_ALL))
        {
            if (ProductType == PRODUCT_TYPE_PRODUCTS)
            {
                where.WhereNull("SKUOptionCategoryID");
            }
            else if (ProductType == PRODUCT_TYPE_PRODUCT_OPTIONS)
            {
                where.WhereNotNull("SKUOptionCategoryID");
            }
        }

        // Representing filter
        if (!string.IsNullOrEmpty(Representing) && (Representing != FILTER_ALL))
        {
            SKUProductTypeEnum productTypeEnum = Representing.ToEnum<SKUProductTypeEnum>();
            string productTypeString = productTypeEnum.ToStringRepresentation();

            where.WhereEquals("SKUProductType", productTypeString);
        }

        // Product number filter
        if (!string.IsNullOrEmpty(ProductNumber))
        {
            where.WhereContains("SKUNumber", ProductNumber);
        }

        // Department filter
        DepartmentInfo di = DepartmentInfoProvider.GetDepartmentInfo(Department, CurrentSiteName);
        di = di ?? DepartmentInfoProvider.GetDepartmentInfo(Department, null);

        if (di != null)
        {
            where.Where(GetColumnWhereCondition("SKUDepartmentID", new WhereCondition().WhereEquals("SKUDepartmentID", di.DepartmentID)));
        }

        // Manufacturer filter
        ManufacturerInfo mi = ManufacturerInfoProvider.GetManufacturerInfo(Manufacturer, CurrentSiteName);
        mi = mi ?? ManufacturerInfoProvider.GetManufacturerInfo(Manufacturer, null);
        if (mi != null)
        {
            where.Where(GetColumnWhereCondition("SKUManufacturerID", new WhereCondition().WhereEquals("SKUManufacturerID", mi.ManufacturerID)));
        }

        // Supplier filter
        SupplierInfo si = SupplierInfoProvider.GetSupplierInfo(Supplier, CurrentSiteName);
        si = si ?? SupplierInfoProvider.GetSupplierInfo(Supplier, null);
        if (si != null)
        {
            where.Where(GetColumnWhereCondition("SKUSupplierID", new WhereCondition().WhereEquals("SKUSupplierID", si.SupplierID)));
        }

        // Needs shipping filter
        if (!string.IsNullOrEmpty(NeedsShipping) && (NeedsShipping != FILTER_ALL))
        {
            if (NeedsShipping == NEEDS_SHIPPING_YES)
            {
                where.Where(GetColumnWhereCondition("SKUNeedsShipping", new WhereCondition().WhereTrue("SKUNeedsShipping")));
            }
            else if (NeedsShipping == NEEDS_SHIPPING_NO)
            {
                where.Where(GetColumnWhereCondition("SKUNeedsShipping", new WhereCondition().WhereFalse("SKUNeedsShipping").Or().WhereNull("SKUNeedsShipping")));
            }
        }

        // Price from filter
        if (PriceFrom > 0)
        {
            where.WhereGreaterOrEquals("SKUPrice", PriceFrom);
        }

        // Price to filter
        if (PriceTo > 0)
        {
            where.WhereLessOrEquals("SKUPrice", PriceTo);
        }

        // Public status filter
        PublicStatusInfo psi = PublicStatusInfoProvider.GetPublicStatusInfo(PublicStatus, CurrentSiteName);
        if (psi != null)
//.........这里部分代码省略.........

    /// <summary>
    /// Returns SQL WHERE condition depending on selected checkboxes.
    /// </summary>
    /// <returns>Returns SQL WHERE condition</returns>
    public string GetWhereCondition()
    {
        var where = new WhereCondition();

        // Contacts checked
        if (chkContacts.Checked)
        {
            where.Where(GetContactWhereCondition());
        }

        // Address checked
        if (chkAddress.Checked)
        {
            where.Where(GetAddressWhereCondition());
        }

        // Email address checked
        if (chkEmail.Checked)
        {
            string domain = ContactHelper.GetEmailDomain(CurrentAccount.AccountEmail);
            if (!String.IsNullOrEmpty(domain))
            {
                var emailWhere = new WhereCondition().WhereEndsWith("AccountEmail", "@" + domain);
                where.Where(emailWhere);
            }
        }

        // URL checked
        if (chkURL.Checked && !String.IsNullOrEmpty(CurrentAccount.AccountWebSite))
        {
            var urlWhere = new WhereCondition().WhereContains("AccountWebSite", URLHelper.CorrectDomainName(CurrentAccount.AccountWebSite));
            where.Where(urlWhere);
        }

        // Phone & fax checked
        if (chkPhone.Checked && (!String.IsNullOrEmpty(CurrentAccount.AccountPhone) || !String.IsNullOrEmpty(CurrentAccount.AccountFax)))
        {
            where.Where(GetPhoneWhereCondition());
        }

        if ((!chkContacts.Checked && !chkAddress.Checked && !chkEmail.Checked && !chkURL.Checked && !chkPhone.Checked) || (String.IsNullOrEmpty(where.WhereCondition)))
        {
            return "(1 = 0)";
        }

        // Filter out current account
        where.WhereNotEquals("AccountID", CurrentAccount.AccountID);

        // Filter out merged records
        where.Where(w => w.Where(x => x.WhereNull("AccountMergedWithAccountID")
                                       .WhereNull("AccountGlobalAccountID")
                                       .WhereGreaterThan("AccountSiteID", 0))
                          .Or(y => y.WhereNull("AccountGlobalAccountID")
                                    .WhereNull("AccountSiteID")));

        // For global object use siteselector's value
        if (plcSite.Visible)
        {
            mSelectedSiteID = UniSelector.US_ALL_RECORDS;
            if (siteSelector.Visible)
            {
                mSelectedSiteID = siteSelector.SiteID;
            }
            else if (siteOrGlobalSelector.Visible)
            {
                mSelectedSiteID = siteOrGlobalSelector.SiteID;
            }

            // Only global objects
            if (mSelectedSiteID == UniSelector.US_GLOBAL_RECORD)
            {
                where.WhereNull("AccountSiteID");
            }
            // Global and site objects
            else if (mSelectedSiteID == UniSelector.US_GLOBAL_AND_SITE_RECORD)
            {
                where.Where(w => w.WhereNull("AccountSiteID").Or().WhereEquals("AccountSiteID", SiteContext.CurrentSiteID));
            }
            // Site objects
            else if (mSelectedSiteID != UniSelector.US_ALL_RECORDS)
            {
                where.WhereEquals("AccountSiteID", mSelectedSiteID);
            }
        }
        // Filter out accounts from different sites
        else
        {
            // Site accounts only
            if (CurrentAccount.AccountSiteID > 0)
            {
                where.WhereEquals("AccountSiteID", CurrentAccount.AccountSiteID);
            }
            // Global accounts only
            else
            {
                where.WhereNull("AccountSiteID");
//.........这里部分代码省略.........

    private DataSet GetRecycleBinSeletedItems(BinSettingsContainer settings, string columns)
    {
        var where = new WhereCondition();

        switch (settings.CurrentWhat)
        {
            case What.AllObjects:
                if (IsSingleSite)
                {
                    where.WhereNull("VersionObjectSiteID");
                }
                if (settings.Site != null)
                {
                    where.Or().WhereEquals("VersionObjectSiteID", settings.Site.SiteID);
                }

                // Wrap filter condition with brackets
                where.Where(new WhereCondition(filter.WhereCondition) { WhereIsComplex = true });
                where = GetWhereCondition(where);
                break;

            case What.SelectedObjects:
                // Restore selected objects
                var toRestore = settings.SelectedItems;
                where.WhereIn("VersionID", toRestore);
                break;
        }

        return ObjectVersionHistoryInfoProvider.GetRecycleBin(where.ToString(true), OrderBy, -1, columns);
    }

    /// <summary>
    /// Returns complete WHERE condition.
    /// </summary>
    protected WhereCondition GetCompleteWhereCondition()
    {
        string allRecords = UniSelector.US_ALL_RECORDS.ToString();

        string customWhere = ValidationHelper.GetString(GetValue("WhereCondition"), "");
        var where = new WhereCondition();

        // Do not select product options and select only enabled products
        where.WhereNull("SKUOptionCategoryID").And().WhereTrue("SKUEnabled");

        // Get products only with specified public status
        if (ProductPublicStatusName != allRecords)
        {
            int pStatusSiteID = ECommerceSettings.UseGlobalPublicStatus(SiteName) ? 0 : SiteInfoProvider.GetSiteID(SiteName);
            where.WhereEquals("SKUPublicStatusID", new IDQuery<PublicStatusInfo>("PublicStatusID").Where("ISNULL(PublicStatusSiteID, 0) = " + pStatusSiteID).And().WhereEquals("PublicStatusName", ProductPublicStatusName).TopN(1));
        }

        // Get products only with specified internal status
        if (ProductInternalStatusName != allRecords)
        {
            int iStatusSiteID = ECommerceSettings.UseGlobalInternalStatus(SiteName) ? 0 : SiteInfoProvider.GetSiteID(SiteName);
            where.WhereEquals("SKUInternalStatusID", new IDQuery<InternalStatusInfo>("InternalStatusID").Where("ISNULL(InternalStatusSiteID, 0) = " + iStatusSiteID).And().WhereEquals("InternalStatusName", ProductInternalStatusName).TopN(1));
        }

        // Get products only from specified department
        if (!string.IsNullOrEmpty(ProductDepartmentName) && (ProductDepartmentName != allRecords))
        {
            DepartmentInfo dept = DepartmentInfoProvider.GetDepartmentInfo(ProductDepartmentName, SiteName);

            int departmentID = (dept != null) ? dept.DepartmentID : 0;
            where.WhereEquals("SKUDepartmentID", departmentID);
        }

        // Include user custom WHERE condition
        if (customWhere.Trim() != "")
        {
            where.Where(customWhere);
        }

        return where;
    }

    /// <summary>
    /// Reloads control.
    /// </summary>
    public void ReloadData()
    {
        var where = new WhereCondition(WhereCondition);

        var siteName = SiteID > 0 ? SiteInfoProvider.GetSiteName(SiteID) : SiteContext.CurrentSiteName;
        var allowGlobal = SettingsKeyInfoProvider.GetBoolValue(siteName + ".cmscmglobalconfiguration");

        uniselector.AllowAll = AllowAllItem;

        if (DisplayAll || DisplaySiteOrGlobal)
        {
            // Display all site and global statuses
            if (DisplayAll && allowGlobal)
            {
                // No WHERE condition required
            }
            // Display current site and global statuses
            else if (DisplaySiteOrGlobal && allowGlobal && (SiteID > 0))
            {
                where.WhereEqualsOrNull("AccountStatusSiteID", SiteID);
            }
            // Current site
            else if (SiteID > 0)
            {
                where.WhereEquals("AccountStatusSiteID", SiteID);
            }
            // Display global statuses
            else if (allowGlobal)
            {
                where.WhereNull("AccountStatusSiteID");
            }

            // Don't display anything
            if (String.IsNullOrEmpty(where.WhereCondition) && !DisplayAll)
            {
                where.NoResults();
            }
        }
        // Display either global or current site statuses
        else
        {
            // Current site
            if (SiteID > 0)
            {
                where.WhereEquals("AccountStatusSiteID", SiteID);
            }
            // Display global statuses
            else if (((SiteID == UniSelector.US_GLOBAL_RECORD) || (SiteID == UniSelector.US_NONE_RECORD)) && allowGlobal)
            {
                where.WhereNull("AccountStatusSiteID");
            }
            // Don't display anything
            if (String.IsNullOrEmpty(where.WhereCondition))
            {
                where.NoResults();
            }
        }

        // Do not add condition to empty condition which allows everything
        if (!String.IsNullOrEmpty(where.WhereCondition))
        {
            string status = ValidationHelper.GetString(Value, "");
            if (!String.IsNullOrEmpty(status))
            {
                where.Or().WhereEquals(uniselector.ReturnColumnName, status);
            }
        }

        uniselector.WhereCondition = where.ToString(expand: true);
        uniselector.Reload(true);
    }

    /// <summary>
    /// Reloads the data.
    /// </summary>
    /// <param name="forceReload">If true, the data is reloaded even when already loaded</param>
    public void ReloadData(bool forceReload)
    {
        if (!dataLoaded || forceReload)
        {
            drpWebpart.Items.Clear();

            if (DisplayNone)
            {
                drpWebpart.Items.Add(new ListItem(ResHelper.GetString("General.SelectNone"), ""));
            }

            // Do not retrieve webparts
            WhereCondition condition = new WhereCondition(WhereCondition);
            if (!ShowWebparts)
            {
                condition.WhereEquals("ObjectType", "webpartcategory");
            }

            if (!ShowInheritedWebparts)
            {
                condition.WhereNull("WebPartParentID");
            }

            if (!String.IsNullOrEmpty(RootPath))
            {
                string rootPath = RootPath.TrimEnd('/');
                condition.Where(new WhereCondition().WhereEquals("ObjectPath", rootPath).Or().WhereStartsWith("ObjectPath", rootPath + "/"));
            }

            ds = WebPartCategoryInfoProvider.GetCategoriesAndWebparts(condition.ToString(true), "DisplayName", 0, "ObjectID, DisplayName, ParentID, ObjectType");

            if (!DataHelper.DataSourceIsEmpty(ds))
            {
                int counter = 0;

                // Make special collection for "tree mapping"
                Dictionary<int, SortedList<string, object[]>> categories = new Dictionary<int, SortedList<string, object[]>>();

                // Fill collection from dataset
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int parentId = ValidationHelper.GetInteger(dr["ParentID"], 0);
                    int id = ValidationHelper.GetInteger(dr["ObjectID"], 0);
                    string name = ResHelper.LocalizeString(ValidationHelper.GetString(dr["DisplayName"], String.Empty));
                    string type = ValidationHelper.GetString(dr["ObjectType"], String.Empty);

                    // Skip webpart, take only WebpartCategory
                    if (type == "webpart")
                    {
                        continue;
                    }

                    SortedList<string, object[]> list;
                    categories.TryGetValue(parentId, out list);

                    // Sub categories list not created yet
                    if (list == null)
                    {
                        list = new SortedList<string, object[]>();
                        categories.Add(parentId, list);
                    }

                    list.Add(name + "_" + counter, new object[] { id, name });

                    counter++;
                }

                // Start filling the dropdown from the root(parentId = 0)
                int level = 0;

                // Root is not shown, start indentation later
                if (!ShowRoot)
                {
                    level = -1;
                }

                AddSubCategories(categories, 0, level);
            }

            dataLoaded = true;
        }
    }

    /// <summary>
    /// Generates complete filter where condition.
    /// </summary>    
    private string GenerateWhereCondition()
    {
        var whereCondition = new WhereCondition();

        // Create WHERE condition for basic filter
        int contactStatus = ValidationHelper.GetInteger(fltContactStatus.Value, -1);
        if (fltContactStatus.Value == null)
        {
            whereCondition = whereCondition.WhereNull("ContactStatusID");
        }
        else if (contactStatus > 0)
        {
            whereCondition = whereCondition.WhereEquals("ContactStatusID", contactStatus);
        }

        whereCondition = whereCondition
            .Where(fltFirstName.GetCondition())
            .Where(fltLastName.GetCondition())
            .Where(fltEmail.GetCondition());

        // Only monitored contacts
        if (radMonitored.SelectedIndex == 1)
        {
            whereCondition = whereCondition.WhereTrue("ContactMonitored");
        }
        // Only not monitored contacts
        else if (radMonitored.SelectedIndex == 2)
        {
            whereCondition = whereCondition.WhereEqualsOrNull("ContactMonitored", 0);
        }

        // Only contacts that were replicated to SalesForce leads
        if (radSalesForceLeadReplicationStatus.SelectedIndex == 1)
        {
            whereCondition = whereCondition.WhereNotNull("ContactSalesForceLeadID");
        }
        // Only contacts that were not replicated to SalesForce leads
        else if (radSalesForceLeadReplicationStatus.SelectedIndex == 2)
        {
            whereCondition = whereCondition.WhereNull("ContactSalesForceLeadID");
        }

        // Create WHERE condition for advanced filter (id needed)
        if (IsAdvancedMode)
        {
            whereCondition = whereCondition
               .Where(fltMiddleName.GetCondition())
               .Where(fltCity.GetCondition())
               .Where(fltPhone.GetCondition())
               .Where(fltCreated.GetCondition())
               .Where(GetOwnerCondition(fltOwner))
               .Where(GetCountryCondition(fltCountry))
               .Where(GetStateCondition(fltState));

            if (!String.IsNullOrEmpty(txtIP.Text))
            {
                var nestedIpQuery = IPInfoProvider.GetIps().WhereLike("IPAddress", SqlHelper.EscapeLikeText(SqlHelper.EscapeQuotes(txtIP.Text)));

                whereCondition = whereCondition.WhereIn("ContactID", nestedIpQuery.Column("IPOriginalContactID")).Or().WhereIn("ContactID", nestedIpQuery.Column("IPActiveContactID"));
            }
        }

        // When "merged/not merged" filter is hidden or in advanced mode display contacts according to filter or in basic mode don't display merged contacts
        if ((HideMergedFilter && NotMerged) ||
            (IsAdvancedMode && !HideMergedFilter && !chkMerged.Checked) ||
            (!HideMergedFilter && !NotMerged && !IsAdvancedMode))
        {
            whereCondition = whereCondition
                .Where(
                    new WhereCondition(
                        new WhereCondition()
                            .WhereNull("ContactMergedWithContactID")
                            .WhereGreaterOrEquals("ContactSiteID", 0)
                        )
                        .Or(
                            new WhereCondition()
                                .WhereNull("ContactGlobalContactID")
                                .WhereNull("ContactSiteID")
                        ));
        }

        // Hide contacts merged into global contact when displaying list of available contacts for global contact
        if (HideMergedIntoGlobal)
        {
            whereCondition = whereCondition.WhereNull("ContactGlobalContactID");
        }

        if (!DisableGeneratingSiteClause)
        {
            // Filter by site
            if (!plcSite.Visible)
            {
                // Filter site objects
                if (SiteID > 0)
                {
                    whereCondition = whereCondition.WhereEquals("ContactSiteID", SiteID);
                }
//.........这里部分代码省略.........

    /// <summary>
    /// Creates where condition according to values selected in filter.
    /// </summary>
    private WhereCondition GenerateWhereCondition()
    {
        var where = new WhereCondition();

        string productNameColumnName = (ParentNode != null) ? "DocumentName" : "SKUName";

        // Append name/number condition
        var nameOrNumber = txtNameOrNumber.Text.Trim().Truncate(txtNameOrNumber.MaxLength);
        if (!string.IsNullOrEmpty(nameOrNumber))
        {
            // condition to get also products with variants that contains
            where.Where(k => k.Where(w => w.WhereContains(productNameColumnName, nameOrNumber)
                                           .Or()
                                           .WhereContains("SKUNumber", nameOrNumber))
                              .Or()
                              .Where(v => v.WhereIn("SKUID", new IDQuery<SKUInfo>("SKUParentSKUID").WhereContains("SKUName", nameOrNumber)
                                                                                                   .Or()
                                                                                                   .WhereContains("SKUNumber", nameOrNumber))));
        }

        // Append site condition
        if (allowGlobalProducts && (siteElem.SiteID != UniSelector.US_GLOBAL_AND_SITE_RECORD))
        {
            // Restrict SKUSiteID only for products not for product section (full listing mode)
            int selectedSiteID = (siteElem.SiteID > 0) ? siteElem.SiteID : 0;
            where.Where(w => w.Where("ISNULL(SKUSiteID, 0) = " + selectedSiteID).Or().WhereNull("SKUID"));
        }

        // Append department condition
        if (departmentElem.SelectedID > 0)
        {
            where.WhereEquals("SKUDepartmentID", departmentElem.SelectedID);
        }
        else if (departmentElem.SelectedID == -5)
        {
            where.WhereNull("SKUDepartmentID");
        }

        // Append one level condition
        if (ParentNode != null)
        {
            if (!chkShowAllChildren.Checked)
            {
                where.WhereEquals("NodeParentID", ParentNode.NodeID).And().WhereEquals("NodeLevel", ParentNode.NodeLevel + 1);
            }
        }

        // Handle advanced mode fields
        if (IsAdvancedMode)
        {
            // Append product type condition
            if ((selectProductTypeElem.Value != null) && (selectProductTypeElem.Value.ToString() != "ALL"))
            {
                where.WhereEquals("SKUProductType", selectProductTypeElem.Value);
            }

            // Manufacturer value
            if (manufacturerElem.SelectedID != UniSelector.US_ALL_RECORDS)
            {
                where.Where("ISNULL(SKUManufacturerID, 0) = " + manufacturerElem.SelectedID);
            }

            // Supplier value
            if (supplierElem.SelectedID != UniSelector.US_ALL_RECORDS)
            {
                where.Where("ISNULL(SKUSupplierID, 0) = " + supplierElem.SelectedID);
            }

            // Internal status value
            if (internalStatusElem.SelectedID != UniSelector.US_ALL_RECORDS)
            {
                where.Where("ISNULL(SKUInternalStatusID, 0) = " + internalStatusElem.SelectedID);
            }

            // Store status value
            if (publicStatusElem.SelectedID != UniSelector.US_ALL_RECORDS)
            {
                where.Where("ISNULL(SKUPublicStatusID, 0) = " + publicStatusElem.SelectedID);
            }

            // Append needs shipping condition
            int needsShipping = ValidationHelper.GetInteger(ddlNeedsShipping.SelectedValue, -1);
            if (needsShipping >= 0)
            {
                where.Where("ISNULL(SKUNeedsShipping, 0) = " + needsShipping);
            }

            // Append allow for sale condition
            int allowForSale = ValidationHelper.GetInteger(ddlAllowForSale.SelectedValue, -1);
            if (allowForSale >= 0)
            {
                where.WhereEquals("SKUEnabled", allowForSale);
            }

            // When in document mode
            if (ParentNode != null)
            {
//.........这里部分代码省略.........

    /// <summary>
    /// Loads export histories list.
    /// </summary> 
    private void LoadExportHistories()
    {
        lstExports.Items.Clear();
        var where = new WhereCondition();

        int siteId = ValidationHelper.GetInteger(siteSelector.Value, 0);
        if (siteId != 0)
        {
            where.Where("ExportSiteID", QueryOperator.Equals ,siteSelector.Value);
        }
        else
        {
            where.WhereNull("ExportSiteID");
        }

        var histories = ExportHistoryInfoProvider.GetExportHistories()
            .Where(where)
            .OrderByDescending("ExportDateTime")
            .Columns(new [] { "ExportDateTime", "ExportFileName", "ExportID" });

        if (!DataHelper.DataSourceIsEmpty(histories))
        {
            radExport.Enabled = true;
            foreach (DataRow dr in histories.Tables[0].Rows)
            {
                lstExports.Items.Add(new ListItem(ValidationHelper.GetString(dr["ExportDateTime"], "yyyy-mm-ddd") + " - " + ValidationHelper.GetString(dr["ExportFileName"], "filename"), ValidationHelper.GetString(dr["ExportID"], null)));
            }
        }
    }