C# Select.ExecuteReader方法代码示例

        public List<IActivity> GetActivities()
        {
            var userConnection = TerrasoftApi.GetuserConnection();
            var activityList = new List<IActivity>();

            var select = new Select(userConnection)
                                        .Column("Id")
                                        .Column("ProjectId")
                                        .Column("StartDate")
                                        .Column("DueDate")
                                    .From("Activity")
                                    .Where("ProjectId").IsEqual(Column.Const(ChildId))
                                    as Select;

            var dbConverter = userConnection.DBTypeConverter;
            using (DBExecutor dbExecutor = userConnection.EnsureDBConnection())
            {
                using (System.Data.IDataReader dr = select.ExecuteReader(dbExecutor))
                {
                    while (dr.Read())
                    {
                        var id = (Guid)dr.GetValue(dr.GetOrdinal("Id"));
                        var projectId = (Guid)dr.GetValue(dr.GetOrdinal("ProjectId"));
                        var dueDate = (DateTime)dr.GetValue(dr.GetOrdinal("DueDate"));
                        var startDate = (DateTime)dr.GetValue(dr.GetOrdinal("StartDate"));

                        var newActivity = new Activity()
                        {
                            Id = id,
                            ProjectId = projectId,
                            DueDate = dueDate,
                            StartDate = startDate,
                            Project = this
                        };
                        Activities.Add(newActivity);
                    }
                }
            }
            return activityList;
        }

    //end callback
    #endregion

    #region allocation functions 
    protected double[] get_allocated(int orderid)
    {
        double[] _allocated = { 0, 0, 0 };
        //aggregate values
        //this emulates the query used in Access database (OrderQuantityAllocatedToContainerQuery), but we don't need all this
        //we only need the aggregated number of packages, weight and volumne from container sub table, the other data can just be derived from order table
        //SubSonic.Aggregate[] _ags = {
        //                            Aggregate.Count(DAL.Logistics.ContainerSubTable.ContainerSubIDColumn),
        //                            Aggregate.GroupBy(DAL.Logistics.ContainerSubTable.OrderNumberColumn), 
        //                            Aggregate.GroupBy(DAL.Logistics.ContainerSubTable.PackagesColumn), 
        //                            Aggregate.GroupBy(DAL.Logistics.OrderTable.OrderIDColumn),
        //                            Aggregate.GroupBy(DAL.Logistics.OrderTable.NumberOfPackagesColumn),
        //                            Aggregate.GroupBy(DAL.Logistics.OrderTable.ActualWeightColumn),
        //                            Aggregate.GroupBy(DAL.Logistics.OrderTable.ActualWeightColumn),
        //                            Aggregate.Sum(DAL.Logistics.ContainerSubTable.WeightColumn), 
        //                            Aggregate.Sum(DAL.Logistics.ContainerSubTable.CbmColumn), 
        //                           };
        //SqlQuery _qry = new Select(_ags).From(DAL.Logistics.Tables.OrderTable).
        //      LeftOuterJoin(DAL.Logistics.ContainerSubTable.OrderIDColumn, DAL.Logistics.OrderTable.OrderIDColumn).
        //      Where(DAL.Logistics.OrderTable.OrderIDColumn).IsEqualTo(orderid);   

        //why do we group for packages column and not sum? check with Dave
        SubSonic.Aggregate[] _ags = {
                                    Aggregate.Count(DAL.Logistics.ContainerSubTable.ContainerSubIDColumn,"ContainerSubID"),
                                    Aggregate.GroupBy(DAL.Logistics.ContainerSubTable.PackagesColumn,"SumPackages"), 
                                    Aggregate.Sum(DAL.Logistics.ContainerSubTable.WeightColumn, "SumWeight"), 
                                    Aggregate.Sum(DAL.Logistics.ContainerSubTable.CbmColumn, "SumCbm") 
                                    };

        SqlQuery _qry = new Select(_ags).From(DAL.Logistics.Tables.ContainerSubTable).Where(DAL.Logistics.ContainerSubTable.OrderIDColumn).IsEqualTo(orderid);   

        //DataSet _set = _qry.ExecuteDataSet(); //useful for testing
        //get totals aggregate query might return multiple rows
        IDataReader _rdr = _qry.ExecuteReader();
        while (_rdr.Read())
        {
            _allocated[0] += wwi_func.vdouble(_rdr["SumWeight"].ToString());
            _allocated[1] += wwi_func.vdouble(_rdr["SumPackages"].ToString());
            _allocated[2] += wwi_func.vdouble(_rdr["SumCbm"].ToString());
        }

        return _allocated; 
    }

    //end bind dll terms
    /// <summary>
    /// origin port combo filtered by VoyageID
    /// </summary>
    /// <param name="voyageid">from voyage combobox int</param>
    protected void bind_origin_port(int voyageId)
    {
        if (voyageId > 0) //don't call if no voyageid
        {
            ASPxComboBox _combo = (ASPxComboBox)this.fmvContainer.FindControl("dxcboOriginPort");
            if (_combo != null)
            {
                string[] _cols = { "VoyageETSSubTable.OriginPortID", "PortTable.PortName" };
                string[] _order = { "PortName" };


                SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.VoyageTable).
                    InnerJoin(DAL.Logistics.VoyageETSSubTable.VoyageIDColumn, DAL.Logistics.VoyageTable.VoyageIDColumn).
                    InnerJoin(DAL.Logistics.PortTable.PortIDColumn, DAL.Logistics.VoyageETSSubTable.OriginPortIDColumn).
                    Where(DAL.Logistics.VoyageTable.VoyageIDColumn).IsEqualTo(voyageId);

                //rebind origin ports
                IDataReader _rd = _qry.ExecuteReader();
                _combo.DataSource = _rd;
                _combo.ValueField = "OriginPortID";
                _combo.ValueType = typeof(int);
                _combo.TextField = "PortName";
                _combo.DataBindItems();
            }
        }
    }

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

    /// <summary>
    /// destination controller
    /// </summary>
    protected void bind_destination_controller()
    {
        ASPxComboBox _combo = (ASPxComboBox)this.fmvContainer.FindControl("dxcboDestController");
        if (_combo != null)
        {
            string[] _cols = { "EmployeeID, Name" };
            string[] _order = { "Name" };
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).Where("Live").IsEqualTo(true).OrderAsc(_order);

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

    //end bind origin port
    protected void bind_dest_port()
    {
        ASPxComboBox _dxcboDestPort = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboDestinationPortID");
        if (_dxcboDestPort != null)
        {
            string[] _cols = { "PortID, PortName" };
            string[] _order = { "PortName" };
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.PortTable).OrderAsc(_order);

            IDataReader _rd1 = _qry.ExecuteReader();
            _dxcboDestPort.DataSource = _rd1;
            _dxcboDestPort.ValueField = "PortID";
            _dxcboDestPort.ValueType = typeof(int);
            _dxcboDestPort.TextField = "PortName";
            _dxcboDestPort.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 fcl lcl dll
    protected void bind_package_type()
    {
        try
        {
            string[] _cols = { "PackageTypeID, PackageType" };
            string[] _order = { "PackageType" };
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.PackageTypeTable).OrderAsc(_order);

            //order controller
            ASPxComboBox _cb = (ASPxComboBox)this.fmvShipment.FindControl("dxcboPackageTypeID");
            if (_cb != null)
            {
                IDataReader _rd1 = _qry.ExecuteReader();
                _cb.DataSource = _rd1;
                _cb.ValueType = typeof(int);
                _cb.ValueField = "PackageTypeID";
                _cb.TextField = "PackageType";
                _cb.DataBindItems();
            }

        }
        catch (Exception ex)
        {
            string _err = ex.Message.ToString();
            this.dxlblErr.Text = _err;
            this.dxpnlErr.ClientVisible = true;
        }

    }

    //end bind combos

    protected void bind_employee_names(int officeid)
    {
        string[] _cols = { "EmployeeID, Name" };
        string[] _order = { "Name" };
        
        
        //order controller employee must be live and in office where order is created
        //int _officeid = this.dxhfOfficeID.Contains("officeid") ? wwi_func.vint(this.dxhfOfficeID.Get("officeid").ToString()) : 0;
        SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).Where("OfficeID").IsEqualTo(officeid).And("Live").IsEqualTo(true).OrderAsc(_order);
        ASPxComboBox _dxcboController = (ASPxComboBox)this.formOrder.FindControl("dxcboController");
        if (_dxcboController != null)
        {
            IDataReader _rd1 = _qry.ExecuteReader();
            _dxcboController.DataSource = _rd1;
            _dxcboController.ValueField = "EmployeeID";
            _dxcboController.ValueType = typeof(int); 
            _dxcboController.TextField = "Name";
            _dxcboController.DataBindItems();
        }

        _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).Where("Live").IsEqualTo(true).OrderAsc(_order);
        //operations controller
        ASPxComboBox _dxcboOps = (ASPxComboBox)this.formOrder.FindControl("dxcboOps");
        if (_dxcboOps != null)
        {
            IDataReader _rd2 = _qry.ExecuteReader();
            _dxcboOps.DataSource = _rd2;
            _dxcboOps.ValueField = "EmployeeID";
            _dxcboOps.ValueType = typeof(int);
            _dxcboOps.TextField = "Name";
            _dxcboOps.DataBindItems();
        }

    }

 public static string lookup_value(string fieldname, string tablename, string idname, int? id, bool firstpopulated)
 {
     string _result = "";
     bool _populated = false;
     SqlQuery _qry = new Select(fieldname).From(tablename).Where(idname).IsEqualTo(id);
     IDataReader _rd = _qry.ExecuteReader();
     while (_rd.Read() && !_populated)
     {
         if (_rd[fieldname].ToString() != "") { _result = _rd[fieldname].ToString(); _populated = true; }
     }
     return _result;
 }

        public static string lookup_multi_values(string fieldnames, string tablename, string idname, int? id, string delimiter)
        {
            string _result = "";
            string[] _cols = fieldnames.Split(",".ToCharArray());

            SqlQuery _qry = new Select(_cols).From(tablename).Where(idname).IsEqualTo(id);
            IDataReader _rd = _qry.ExecuteReader();

            while (_rd.Read())
            {
                for (int _cx = 0; _cx < _cols.Length; _cx++)
                {
                    string _f = _cols[_cx];
                    _result += _rd[_f].ToString() + delimiter;
                }
            }
            return _result;
        }

        //end get object to ienum 

        public static int lookup_match(string fieldname, string tablename, string fieldvalue)
        {
            int _result = 0;

            SqlQuery _qry = new Select(fieldname).From(tablename).Where(fieldname).IsEqualTo(fieldvalue);
            IDataReader _rd = _qry.ExecuteReader();
            while (_rd.Read())
            {
                _result += 1; //_rd[fieldname].ToString();
            }
            return _result;
        }

    /// <summary>
    /// not in use
    /// </summary>
    /// <param name="sourceform"></param>
    protected void bind_filter_fields(string sourceform)
    {
        if (sourceform != "")
        {
            string[] _cols = { "filter_caption, field_name, column_type" };
            string[] _order = { "sort_order" };

            //order controller employee must be live and in office where order is created
            //int _officeid = this.dxhfOfficeID.Contains("officeid") ? wwi_func.vint(this.dxhfOfficeID.Get("officeid").ToString()) : 0;
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.DbFilterField).Where("applies_to").IsEqualTo(sourceform).And("is_active").IsEqualTo(true).OrderAsc(_order);
            ASPxComboBox _dxcboController = (ASPxComboBox)this.form1.FindControl("dxcboadvfields");
            if (_dxcboController != null)
            {
                IDataReader _rd1 = _qry.ExecuteReader();
                //DataTable _dt = _qry.ExecuteDataSet().Tables[0];  
                _dxcboController.DataSource = _rd1;
                _dxcboController.ValueField = "field_name";
                _dxcboController.ValueType = typeof(string);
                _dxcboController.TextField = "filter_caption";
                _dxcboController.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();
            }
        }
    }

    protected void bind_office()
    {
        ASPxComboBox _cbo = (ASPxComboBox)this.fmvEmployee.FindControl("dxcboOfficeID");
        if (_cbo != null) {
            string[] _cols = { "OfficeTable.OfficeID, OfficeTable.OfficeName", "OfficeTable.CountryID" };
            string[] _order = { "OfficeName" };
            SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.OfficeTable).OrderAsc(_order); 

            IDataReader _rd1 = _qry.ExecuteReader();
            _cbo.DataSource = _rd1;
            _cbo.ValueField = "OfficeID";
            _cbo.ValueType = typeof(int);
            _cbo.TextField = "OfficeName";
            _cbo.DataBindItems();
        }
    }