C# Select.Where方法代码示例

    //end incremental filtering agentarorigin
    /// <summary>
    /// destinatiobn controller
    /// </summary>
    /// <param name="source"></param>
    /// <param name="e"></param>
    protected void dxcboDestControl_ItemRequestedByValue(object source, ListEditItemRequestedByValueEventArgs e)
    {
        ASPxComboBox _combo = (ASPxComboBox)source;
        ASPxComboBox _destinationagent = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboAgentAtDestinationIDEdit"); 
        if (_combo != null)
        {

            string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID", " NameAndAddressBook.CompanyID" };
            string[] _order = { "Name" };
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
                InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
                InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);

            if (_destinationagent != null && _destinationagent.SelectedItem != null && _destinationagent.Value != null)
            {
                int _filter = wwi_func.vint(_destinationagent.SelectedItem.GetValue("CountryID").ToString());
                if (_filter > 0) { _qry.Where("CountryID").IsEqualTo(_filter); }
            }

            _qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);

            IDataReader _rd1 = _qry.ExecuteReader();
            _combo.DataSource = _rd1;
            _combo.ValueField = "EmployeeID";
            _combo.ValueType = typeof(int);
            _combo.TextField = "Name";
            _combo.DataBindItems();
        }
    }

    protected void bind_origin_controller(string originAgentID)
    {
        //260211 some older jobs have an origin controller but no origin agent in those cases don't display the origin controller
        //must have a filter or display nothing
        if (!string.IsNullOrEmpty(originAgentID))
        {
            ASPxComboBox _dxcboController = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboOriginPortControllerID");
            if (_dxcboController != null)
            {

                string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID", " NameAndAddressBook.CompanyID" };
                string[] _order = { "Name" };
                SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
                    InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
                    InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);

                if (!string.IsNullOrEmpty(originAgentID))
                {
                    int _filter = wwi_func.vint(originAgentID);
                    if (_filter > 0) { _qry.Where("CompanyID").IsEqualTo(_filter); }
                }

                _qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);

                IDataReader _rd1 = _qry.ExecuteReader();
                _dxcboController.DataSource = _rd1;
                _dxcboController.ValueField = "EmployeeID";
                _dxcboController.ValueType = typeof(int);
                _dxcboController.TextField = "Name";
                _dxcboController.DataBindItems();
            }
        }
    }

    //end bind origin port controller

    protected void bind_destination_controller(string destinationAgentID)
    {
        ASPxComboBox _combo = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboDestinationPortControllerID"); 
        if (_combo != null)
        {

            string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID"};
            string[] _order = { "Name" };
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
                InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn);

            if (!string.IsNullOrEmpty(destinationAgentID))
            {
                int _filter = wwi_func.vint(destinationAgentID);
                _qry.Where("OfficeID").IsEqualTo(_filter); 
            }

            _qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);

            DataTable _dt = _qry.ExecuteDataSet().Tables[0];  
            IDataReader _rd1 = _qry.ExecuteReader();
            _combo.DataSource = _rd1;
            _combo.ValueField = "EmployeeID";
            _combo.ValueType = typeof(int);
            _combo.TextField = "Name";
            _combo.DataBindItems();
        }
    }

    //end bind employees
    /// <summary>
    /// contact callback fired when company is changed
    /// </summary>
    /// <param name="companyID"></param>
    protected void bind_client_contact(string companyID)
    {
        //must have a filter or display nothing
        if (!string.IsNullOrEmpty(companyID))
        {
            ASPxComboBox _dxcboContact = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboContactID");
            if (_dxcboContact != null)
            {

                string[] _cols = { "ContactID, ContactName", "Email" };
                string[] _order = { "ContactName" };
                SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.ContactTable).OrderAsc(_order);

                int _filter = -1;
                if (!string.IsNullOrEmpty(companyID))
                {
                    _filter = wwi_func.vint(companyID);
                    if (_filter > 0) { _qry.Where("CompanyID").IsEqualTo(_filter); }
                }

                IDataReader _rd1 = _qry.ExecuteReader();
                _dxcboContact.DataSource = _rd1;
                _dxcboContact.ValueField = "ContactID";
                _dxcboContact.ValueType = typeof(int);
                _dxcboContact.TextField = "ContactName";
                _dxcboContact.DataBindItems();
            }
        }
    }

    //end bind origin
    protected void bind_origin_port(string originID)
    {
        //DevExpress.Web.ASPxCallbackPanel.ASPxCallbackPanel _call = (DevExpress.Web.ASPxCallbackPanel.ASPxCallbackPanel)this.formOrder.FindControl("dxcbkOriginGroup");
        //if (_call != null)
        //{
        //ASPxComboBox _dxcboOriginPort = (ASPxComboBox)_call.FindControl("dxcboOriginPort");
        ASPxComboBox _dxcboOriginPort = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboPortID");

        if (_dxcboOriginPort != null)
        {
            string[] _cols = { "PortID, PortName" };
            string[] _order = { "PortName" };
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.PortTable).OrderAsc(_order);

            int _filter = -1;
            if (!string.IsNullOrEmpty(originID))
            {
                _filter = wwi_func.vint(originID);
                if (_filter > 0) { _qry.Where("PortID").IsEqualTo(_filter); }
            }

            IDataReader _rd1 = _qry.ExecuteReader();
            _dxcboOriginPort.DataSource = _rd1;
            _dxcboOriginPort.ValueField = "PortID";
            _dxcboOriginPort.ValueType = typeof(int);
            _dxcboOriginPort.TextField = "PortName";
            _dxcboOriginPort.DataBindItems();
        }
        //}
    }

    }//end menu names loop


    #endregion

    #region gridview crud events
    /// <summary>
    /// on row created get lookup values
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void dxgrdCourier_HtmlRowCreated(object sender, ASPxGridViewTableRowEventArgs e)
    {
        ASPxGridView _grd = (ASPxGridView)sender;

        try
        {
            if (e.RowType == GridViewRowType.Data)
            {
                //company
                int _id = wwi_func.vint(e.GetValue("DocsDespatchID").ToString());
                string _txt = wwi_func.lookup_multi_values("CompanyName,Address1,Address2,Address3,CountryName,TelNo", "view_delivery_address", "CompanyID", _id);
                if (_txt != "")
                {
                    string[] _lx = _txt.Split(Environment.NewLine.ToCharArray());
                    //company
                    ASPxLabel _lbl = (ASPxLabel)_grd.FindRowTemplateControl(e.VisibleIndex, "dxlblDocsDespatchIDView");
                    if (_lbl != null) { _lbl.Text = _lx[0]; }
                    //address
                    _lbl = (ASPxLabel)_grd.FindRowTemplateControl(e.VisibleIndex, "dxlblDocsDespatchIDView2");
                    if (_lbl != null) { _lbl.Text = _txt.Replace(_lx[0], "").Trim(); ; }

                }

                //contact
                _id = wwi_func.vint(e.GetValue("ContactID").ToString());
                _txt = wwi_func.lookup_multi_values("ContactName,Email", "ContactTable", "ContactID", _id, "|");
                if (_txt != "")
                {
                    string[] _lx = _txt.Split("|".ToCharArray());
                    //name
                    ASPxLabel _lbl = (ASPxLabel)_grd.FindRowTemplateControl(e.VisibleIndex, "dxlblContactIDView");
                    if (_lbl != null) { _lbl.Text = _lx[0].Trim(); }
                    //email
                    _lbl = (ASPxLabel)_grd.FindRowTemplateControl(e.VisibleIndex, "dxlblContactIDView2");
                    if (_lbl != null) { _lbl.Text = _lx.Length > 0 ? _lx[1] : ""; }
                }

                //get the status value
                int _original = wwi_func.vint(e.GetValue("Original").ToString());
                string _target = _original <= 2 ? "Despatch Date" : "Date Emailed";

                //format date if original = 2 pass to docs despatched date, if 3 pass to emailed date
                string _dt = e.GetValue("DocumentationDespatched") != null ? wwi_func.vdatetime(e.GetValue("DocumentationDespatched").ToString()).ToShortDateString() : "";
                //set caption
                ASPxLabel _lb = (ASPxLabel)_grd.FindRowTemplateControl(e.VisibleIndex, "dxlblDespatchDateCaption");
                if (_lb != null) { _lb.Text = _target; }
                _lb = (ASPxLabel)_grd.FindRowTemplateControl(e.VisibleIndex, "dxlblDespatchDateView");
                if (_lb != null) { _lb.Text = _dt; }
            }
            else if( e.RowType == GridViewRowType.EditForm)
            {
                //edit form stuff
                //populate company address label 
                int _id = wwi_func.vint(e.GetValue("DocsDespatchID").ToString());
                string _txt = wwi_func.lookup_multi_values("Address1,Address2,Address3,CountryName,TelNo", "view_delivery_address", "CompanyID", _id);
                if (_txt != "")
                {
                    //string[] _lx = _txt.Split(Environment.NewLine.ToCharArray());
                    //address
                    ASPxLabel _lb = (ASPxLabel)_grd.FindEditFormTemplateControl("dxlblAddress2");
                    if (_lb != null) { _lb.Text = _txt; }
                }

                //bind contact or we lose the contact name on edit
                ASPxComboBox _editor = (ASPxComboBox)_grd.FindEditFormTemplateControl("dxcboClientContact");
                if (_editor != null)
                {
                    string[] _cols = { "ContactID, ContactName", "Email" };
                    string[] _order = { "ContactName" };
                    SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.ContactTable).OrderAsc(_order);
                    if (_id > 0) { _qry.Where("CompanyID").IsEqualTo(_id); }

                    IDataReader _rd1 = _qry.ExecuteReader();
                    _editor.DataSource = _rd1;
                    _editor.ValueField = "ContactID";
                    _editor.TextField = "ContactName";
                    _editor.DataBind();
                    _editor.SelectedItem = _editor.Items.FindByValue(e.GetValue("ContactID"));
                }
            }//end if
        }
        catch (Exception ex)
        {
            string _ex = ex.Message.ToString();
            this.dxlblErr.Text += _ex;
            this.dxpnlErr.ClientVisible = true;
        }
    }   

        public void Acc_Exec_SimpleAnd3()
        {
            DataProvider provider = DataService.GetInstance("NorthwindAccess");
            SubSonic.SqlQuery query = new
                Select(provider, Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity"))
                .From("Order Details");
                query
                .Where(Aggregate.Avg("UnitPrice"))
                .IsGreaterThan(30)
                .And(Aggregate.Avg("Quantity"))
                .IsGreaterThan(20);

            int records = query.GetRecordCount();
            string s = query.BuildSqlStatement();
            Assert.AreEqual(17, records);
        }

        public void Exec_SimpleAnd3()
        {
            SubSonic.SqlQuery query = new
                Select(Aggregate.GroupBy("ProductID"), Aggregate.Avg("UnitPrice"), Aggregate.Avg("Quantity"))
                .From("Order Details");
                query
                .Where(Aggregate.Avg("UnitPrice"))
                .IsGreaterThan(30)
                .And(Aggregate.Avg("Quantity"))
                .IsGreaterThan(20);

            int records = query.GetRecordCount();
            Assert.AreEqual(16, records);
        }

    protected void bind_destination_controller(int countryId)
    {
        ASPxComboBox _combo = (ASPxComboBox)this.fmvAddresses.FindControl("dxcboDestinationPortControllerIDEdit"); 
        if (_combo != null)
        {

            string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID"};
            string[] _order = { "Name" };
            //don't need nameandaddressbook as get countryid from officetable
            //SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
            //    InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
            //    InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
                InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn);

            if (countryId > 0)
            {
                 _qry.Where("CountryID").IsEqualTo(countryId); 
            }

            _qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(1).OrderAsc(_order);
            //string _q = _qry.ToString(); //fo testing
            //DataTable _dt = _qry.ExecuteDataSet().Tables[0];  //for testing
            
            IDataReader _rd1 = _qry.ExecuteReader();
            _combo.DataSource = _rd1;
            _combo.ValueField = "EmployeeID";
            _combo.ValueType = typeof(int);
            _combo.TextField = "Name";
            _combo.DataBindItems();
        }
    }

    /// <summary>
    /// destination port combo filtered by VoyageID
    /// </summary>
    /// <param name="voyageid">from voyage combobox int</param>
    protected void bind_destination_port(int voyageId)
    {
        ASPxComboBox _combo = (ASPxComboBox)this.fmvBol.FindControl("dxcboDestinationPort");
        if (_combo != null)
        {
            IDataReader _rd = null;

            if (voyageId > 0)
            {
                string[] _cols = { "PortTable.PortID", "PortTable.PortName", "VoyageETASubTable.ETA" };
                string[] _order = { "PortName" };

                SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.VoyageTable).
                    InnerJoin(DAL.Logistics.VoyageETASubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn).
                    InnerJoin(DAL.Logistics.PortTable.PortIDColumn, DAL.Logistics.VoyageETASubTable.DestinationPortIDColumn);


                _qry.Where(DAL.Logistics.VoyageTable.VoyageIDColumn).IsEqualTo(voyageId);
                _rd = _qry.ExecuteReader();
            }
            //rebind dest ports
            _combo.DataSource = _rd;
            _combo.ValueField = "PortID";
            _combo.ValueType = typeof(int);
            _combo.TextField = "PortName";
            _combo.DataBindItems();
        }
    }