C# Query.WHERE方法代码示例

 public TimesheetBillingCityRateCollection FetchAllActiveByTimesheetId(int timesheetId)
 {
     TimesheetBillingCityRateCollection coll = new TimesheetBillingCityRateCollection();
     Query qry = new Query(TimesheetBillingCityRate.Schema);
     qry.WHERE(TimesheetBillingCityRate.Columns.IsDeleted, false);
     qry.WHERE(TimesheetBillingCityRate.Columns.TimesheetId, timesheetId);
     coll.LoadAndCloseReader(qry.ExecuteReader());
     return coll;
 }

 public BillingCityRateCollection FetchAllActive()
 {
     BillingCityRateCollection coll = new BillingCityRateCollection();
     Query qry = new Query(BillingCityRate.Schema);
     qry.WHERE(BillingCityRate.Columns.IsDeleted, false);
     coll.LoadAndCloseReader(qry.ExecuteReader());
     return coll;
 }

 public TimesheetCollection FetchAllByPeriodAccountId(int periodAccountId)
 {
     TimesheetCollection coll = new TimesheetCollection();
     Query qry = new Query(Timesheet.Schema);
     qry.WHERE(Timesheet.Columns.Periodaccountid, periodAccountId);
     coll.LoadAndCloseReader(qry.ExecuteReader());
     return coll;
 }

        public static ShoutCollection GetPage(int hostID, int? toUserID, int? chatID, int pageIndex, int pageSize)
        {
            Query query = new Query(Shout.Schema).WHERE(Shout.Columns.HostID, hostID).ORDER_BY(Shout.Columns.CreatedOn, "DESC");
            if (toUserID.HasValue)
                query = query.WHERE(Shout.Columns.ToUserID, toUserID.Value);
            else
                query = query.WHERE(Shout.Columns.ToUserID, Comparison.Is, null);

            if (chatID.HasValue)
                query = query.WHERE(Shout.Columns.ChatID, chatID.Value);
            else
                query = query.WHERE(Shout.Columns.ChatID, Comparison.Is, null);

            query.PageIndex = pageIndex;
            query.PageSize = pageSize;

            ShoutCollection shouts = new ShoutCollection();
            shouts.Load(query.ExecuteReader());
            return shouts;
        }

    //
    public DataTable get_datatable_subsonic()
    {
        //using a collection as it's the only way to directly apply a query string!
        //query to grid
        //Int32 _page = this.gridOrder.PageIndex;
        //Int32 _pagesize = this.gridOrder.SettingsPager.PageSize;
        //
        String _companyid = "";
        String _filter = "";

        //check to see if we have a company id
        //user MUST be logged in for advanced searches
        Query _qry = new Query(Views.ViewOrder);

        //need to switch this off when we are testing!
        Page.Session.Remove("user");
        if (Page.Session["user"] != null)
        {
            _companyid = ((UserClass)Page.Session["user"]).CompanyId.ToString();
            _qry.WHERE("CompanyID = " + _companyid);
            //_query = "WHERE CompanyId = " + _companyid; 
        }

        if (Session["filter"] != null)
        {
            _filter = Session["filter"].ToString();
            if (_companyid != string.Empty) { _qry.AND(_filter); } else { _qry.WHERE(_filter); }
            //if (_companyid != string.Empty) { _query+= "AND " + _filter; } else {_query = " WHERE " + _filter ; }

        }

        //InlineQuery _qry = new InlineQuery();

        //_qry.PageIndex = _page+1; //gridview page is 0 based, but subsonic is 1 based
        //_qry.PageSize = _pagesize;

        ViewOrderCollection _order = new ViewOrderCollection();
        _order.LoadAndCloseReader(_qry.ExecuteReader());
        DataTable _dt = (DataTable)_order.ToDataTable();

        return _dt;
    }

        /// <summary>
        /// Data retrieval
        /// </summary>
        /// <returns></returns>
        private DataSet GenerateReturnSet()
        {
            DataSet result = null;

            if(_url != null)
            {
                Query q;

                if(!String.IsNullOrEmpty(_url.TableName))
                {
                    q = new Query(_url.TableName);
                    TableSchema.Table schema = q.Schema;

                    if(_url.PrimaryKey != null)
                        q.WHERE(q.Schema.PrimaryKey.ParameterName, _url.PrimaryKey);

                    if(_url.Parameters != null)
                    {
                        IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
                        TableSchema.TableColumn column;

                        string paramName;
                        object paramValue;

                        while(loopy.MoveNext())
                        {
                            paramName = loopy.Key.ToString();
                            paramValue = loopy.Value;

                            if(paramName.ToLowerInvariant() == "pagesize" || paramName.ToLowerInvariant() == "pageindex")
                            {
                                if(paramName.ToLowerInvariant() == "pagesize")
                                    q.PageSize = int.Parse(paramValue.ToString());

                                if(paramName.ToLowerInvariant() == "pageindex")
                                    q.PageIndex = int.Parse(paramValue.ToString());
                            }
                            else
                            {
                                Comparison comp;
                                EvalComparison(paramName, out paramName, out comp);
                                column = schema.GetColumn(paramName);

                                //if this column is a string, by default do a fuzzy search
                                if(comp == Comparison.Like || column.IsString)
                                {
                                    comp = Comparison.Like;
                                    paramValue = String.Concat("%", paramValue, "%");
                                }
                                else if(paramValue.ToString().ToLower() == "null")
                                    paramValue = DBNull.Value;

                                q.WHERE(column.ColumnName, comp, paramValue);
                            }
                        }
                    }
                    result = q.ExecuteDataSet();
                }
                else if(!String.IsNullOrEmpty(_url.SpName))
                {
                    StoredProcedure sp = new StoredProcedure(_url.SpName);

                    if(_url.Parameters != null)
                    {
                        IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
                        while(loopy.MoveNext())
                            sp.Command.AddParameter(loopy.Key.ToString(), loopy.Value, DbType.AnsiString);
                    }
                    result = sp.GetDataSet();
                }
            }
            return result;
        }

    //end get file name
    /// <summary>
    /// 
    /// </summary>
    /// <param name="despatchno"></param>
    /// <param name="filename"></param>
    /// <param name="asndata"></param>
    /// <returns></returns>
    public static bool process_asn_titles(int despatchnoteid, string filename)
    {

        bool _result = false;
        //all datetimes in the .xsd schema manually converted to strings otherwise we can't format them as required in ASN specification
        DateTime _now = DateTime.Now;
        //issuedatetime
        string _issue = _now.ToString("yyyy-MM-dd\"T\"HH:mm:sszzz"); //for formatting issue date to 2014-01-30T13:07:45+00:00
        //string _issue = "yyyy-MM-dd\"T\"HH:mm:sszzz"; //for formatting issue date to 2014-01-30T13:07:45+00:00
        int _npallets = 0; //total number of pallets
        int _nlines = 0; //total of line numbers
        int _nunits = 0; //total number of books
        //constants
        const string _carriercode = "Shipper";
        const string _carriernamecode = "PUBLISHIP";
        const decimal _asnversion = 0.9M;
        const string _purpose = "Original";
        const int _perlayer = 10; //parcels per layer default value
        //*************************
        //get datareader for despatch note details and titles

        Query _q = new Query(DAL.Logistics.ViewAsnConsignment.Schema);
        _q.WHERE("despatch_note_id", Comparison.Equals, despatchnoteid);

        DAL.Logistics.ViewAsnConsignmentCollection _v = new ViewAsnConsignmentCollection();
        _v.LoadAndCloseReader(_q.ExecuteReader());
        DataTable _dt = _v.ToDataTable(); 
        //**************************
        
        //should ONLY be 1 printer and buyer, but just in case step though and create a seperate pda for each printer
        //get distinct printers
        string[] _col = { "printer_name", "printer_addr1", "printer_addr2", "printer_addr3", "printer_postcode", 
                          "buyer_name", "buyer_addr1", "buyer_addr2", "buyer_addr3", "buyer_postcode"};
        DataTable _pr = _dt.DefaultView.ToTable(true, _col); 
        
        for (int _pn = 0; _pn < _pr.Rows.Count; _pn++)
        {
            //class in xsds folder DAL.Logistics namespace
            //DAL.Logistics.PrintersDespatchAdvice _xml = new DAL.Logistics.PrintersDespatchAdvice  
            PrintersDespatchAdvice _pda = new PrintersDespatchAdvice();
            
            //header data
            _pda.Header = new PrintersDespatchAdviceHeader();
            _pda.Header.DespatchAdviceNumber = despatchnoteid; //file number? // orderno; //despatch number? 
            _pda.version = _asnversion;
            _pda.Header.IssueDateTime = _issue;
            _pda.Header.PurposeCode = _purpose;
            //reference coded elements
            _pda.Header.ReferenceCoded = new PrintersDespatchAdviceHeaderReferenceCoded();
            _pda.Header.ReferenceCoded.ReferenceNumber = "PDC20033"; ;
            _pda.Header.ReferenceCoded.ReferenceTypeCode = "BuyersReference";
            //datecoded elements
            _pda.Header.DateCoded = new PrintersDespatchAdviceHeaderDateCoded();
            _pda.Header.DateCoded.Date = _now.ToString("yyyyMMdd"); ///format yyyyMMdd 
            _pda.Header.DateCoded.DateQualifierCode = "Not yet shipped";
            //buyer party elements 1-6 address lines  (client? in publiship db)
            _pda.Header.BuyerParty = new PrintersDespatchAdviceHeaderBuyerParty();
            _pda.Header.BuyerParty.PartyName = new PrintersDespatchAdviceHeaderBuyerPartyPartyName();
            _pda.Header.BuyerParty.PartyName.NameLine = _pr.Rows[_pn].IsNull("buyer_name") ? "" : _pr.Rows[_pn]["buyer_name"].ToString(); //"Pearson Education Ltd";
            _pda.Header.BuyerParty.PostalAddress = new PrintersDespatchAdviceHeaderBuyerPartyPostalAddress();
            _pda.Header.BuyerParty.PostalAddress.AddressLine = new string[] { 
                _pr.Rows[_pn].IsNull("buyer_addr1") ? "": _pr.Rows[_pn]["buyer_addr1"].ToString(), 
                _pr.Rows[_pn].IsNull("buyer_addr2") ? "": _pr.Rows[_pn]["buyer_addr2"].ToString(),
                _pr.Rows[_pn].IsNull("buyer_addr3") ? "": _pr.Rows[_pn]["buyer_addr3"].ToString()
            }; //{ "Halley Court", "Jordan Hill", "Oxford" };
            _pda.Header.BuyerParty.PostalAddress.PostalCode = _pr.Rows[_pn].IsNull("buyer_postcode") ? "" : _pr.Rows[_pn]["buyer_postcode"].ToString(); //"OX2 8EJ";
            //seller party elements 1-6 address lines (PRINTER? in publiship db)
            _pda.Header.SellerParty = new PrintersDespatchAdviceHeaderSellerParty();
            _pda.Header.SellerParty.PartyName = new PrintersDespatchAdviceHeaderSellerPartyPartyName();
            _pda.Header.SellerParty.PartyName.NameLine = _pr.Rows[_pn].IsNull("printer_name") ? "" : _pr.Rows[_pn]["printer_name"].ToString(); //"Jiwabaru";
            _pda.Header.SellerParty.PostalAddress = new PrintersDespatchAdviceHeaderSellerPartyPostalAddress();
            _pda.Header.SellerParty.PostalAddress.AddressLine = new string[] { 
                 _pr.Rows[_pn].IsNull("printer_addr1") ? "": _pr.Rows[_pn]["printer_addr1"].ToString(), 
                 _pr.Rows[_pn].IsNull("printer_addr2") ? "": _pr.Rows[_pn]["printer_addr2"].ToString(), 
                 _pr.Rows[_pn].IsNull("printer_addr3") ? "": _pr.Rows[_pn]["printer_addr3"].ToString()
            }; //{ "NO: 2, JALAN P/8, KAWASAN MIEL FASA 2", "BANDAR BARU BANGI", "SELANGOR DARUL EHSAN", "43650", "Malaysia" };
            _pda.Header.SellerParty.PostalAddress.PostalCode = _pr.Rows[_pn].IsNull("printer_postcode") ? "" : _pr.Rows[_pn]["printer_postcode"].ToString(); 
            //ship to party party id elements
            _pda.Header.ShipToParty = new PrintersDespatchAdviceHeaderShipToParty();
            //required
            _pda.Header.ShipToParty.PartyID = new PrintersDespatchAdviceHeaderShipToPartyPartyID();
            _pda.Header.ShipToParty.PartyID.PartyIDType = "EAN";
            _pda.Header.ShipToParty.PartyID.Identifier = "PEAR011";
            //
            _pda.Header.ShipToParty.PartyName = new PrintersDespatchAdviceHeaderShipToPartyPartyName();
            _pda.Header.ShipToParty.PartyName.NameLine = "Pearson Distribution Centre";
            _pda.Header.ShipToParty.PostalAddress = new PrintersDespatchAdviceHeaderShipToPartyPostalAddress();
            _pda.Header.ShipToParty.PostalAddress.AddressLine = new string[] { "Unit 1", "Castle Mound Way", "Rugby", "Warwickshire", "UK" };
            _pda.Header.ShipToParty.PostalAddress.PostalCode = "CV23 0WB";
            //delivery elements
            _pda.Header.Delivery = new PrintersDespatchAdviceHeaderDelivery();
            _pda.Header.Delivery.TrailerNumber = "PU1"; //from database?
//.........这里部分代码省略.........

    /// <summary>
    /// publiship advance labels
    /// </summary>
    /// <param name="orderid"></param>
    /// <returns></returns>
    public static string advance_labels(Int32 orderid)
    {
        string _msg = ""; //returns error message or nothing if no error

        Query _q = new Query(ViewAdvanceDelivery.Schema);
        _q.WHERE("OrderID", Comparison.Equals, orderid);

        DAL.Logistics.ViewAdvanceDeliveryCollection _v = new ViewAdvanceDeliveryCollection();
        _v.LoadAndCloseReader(_q.ExecuteReader());

        if (_v != null && _v.Count > 0)
        {
            Document _doc = new Document();

            try
            {

                System.IO.MemoryStream _mem = new System.IO.MemoryStream();
                PdfWriter _pdf = PdfWriter.GetInstance(_doc, _mem);
                Chunk _dl = new Chunk(new iTextSharp.text.pdf.draw.DottedLineSeparator());

                _doc.Open();

                int _cartons = _v.Count;
                string _txt = "";
                int _br = 0;

                for (int _ix = 0; _ix < _cartons; _ix++)
                {
                    PdfPTable _tbl = new PdfPTable(5);
                    _tbl.TotalWidth = _doc.PageSize.Width - _doc.LeftMargin - _doc.RightMargin;
                    
                    ////paragraphs is one way of positioning tables on page by adding spacing but inconsistent page breaks 
                    ////don't work for us. use absolute positioning instead
                    //Paragraph _par = new Paragraph();
                    //_par.SpacingBefore = 150f;
                    
                    //format logo
                    iTextSharp.text.Image _img = iTextSharp.text.Image.GetInstance(HttpContext.Current.Server.MapPath("~/images/PublishipAdvanceGif2.gif"));
                    _img.ScalePercent(24f); //scale to 300dpi for print quality?

                    //top row publiship advance logo and order id
                    //publiship advance logo and order id
                    PdfPCell _c0 = new PdfPCell(_img);
                    _c0.Padding = 3;
                    _c0.Colspan = 3;
                    _c0.BorderWidthLeft = 0;
                    _c0.BorderWidthTop = 0;
                    _tbl.AddCell(_c0);

                    PdfPCell _c1 = new PdfPCell(new Phrase("CONSIGNMENT REF:" + Environment.NewLine + _v[_ix].OrderID.ToString()));
                    _c1.Padding = 3;
                    _c1.Colspan = 2;
                    _c1.HorizontalAlignment = Element.ALIGN_RIGHT;
                    _tbl.AddCell(_c1);
                    //end top row

                    //delivery address 
                    _txt = !string.IsNullOrEmpty(_v[_ix].DeliveryAddress) ? _v[_ix].DeliveryAddress.ToString() : "";
                    _txt += !string.IsNullOrEmpty(_v[_ix].DestinationCountry) ? Environment.NewLine + _v[_ix].DestinationCountry.ToString() : "";
                    PdfPCell _c2 = new PdfPCell(new Phrase("DELIVERY TO:")); //"delivery to: " + Environment.Newline + address.tostring();
                    _c2.MinimumHeight = 100;
                    _c2.Padding = 3;
                    _c2.Colspan = 1;
                    _tbl.AddCell(_c2);

                    _c2 = new PdfPCell(new Phrase(_txt));
                    _c2.MinimumHeight = 100;
                    _c2.Padding = 3;
                    _c2.Colspan = 4;
                    _tbl.AddCell(_c2);
                    //end address

                    //fao 
                    _txt = !string.IsNullOrEmpty(_v[_ix].Fao) ? _v[_ix].Fao.ToString() : "";
                    PdfPCell _c3 = new PdfPCell(new Phrase("ATTENTION:"));
                    _c3.Padding = 3;
                    _c3.Colspan = 1;
                    _tbl.AddCell(_c3);

                    _c3 = new PdfPCell(new Phrase(_txt));
                    _c3.Padding = 3;
                    _c3.Colspan = 4;
                    _tbl.AddCell(_c3);
                    //end fao

                    //title 
                    _txt = !string.IsNullOrEmpty(_v[_ix].Title) ? _v[_ix].Title.ToString() : "";
                    PdfPCell _c4 = new PdfPCell(new Phrase("TITLE:"));
                    _c4.Padding = 3;
                    _c4.Colspan = 1;
                    _tbl.AddCell(_c4);

                    _c4 = new PdfPCell(new Phrase(_txt));
                    _c4.Padding = 3;
//.........这里部分代码省略.........

    /// <summary>
    /// pass filter field AND contact id for selecting by user
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void objdsOrders_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        Int32 _userid = Page.Session["user"] != null ? (Int32)((UserClass)Page.Session["user"]).UserId : -1;
        Int32 _companyid = Page.Session["user"] != null ? (Int32)((UserClass)Page.Session["user"]).CompanyId : -1;
        
        //e.InputParameters["ContactID"] = _userid;
        Query _qry = new Query(PublishipAdvanceOrderTable.Schema);
        if (_companyid != -1)
        {
            _qry.WHERE("ContactID", Comparison.Equals, _userid);
        }

        if (this.dxcbofields.Text.ToString() != string.Empty && this.txtQuickSearch.Text.ToString() != string.Empty)
        {
            string _fieldname = this.dxcbofields.SelectedItem.GetValue("fieldname").ToString();
            string _txtsearch = this.txtQuickSearch.Text.ToLower();

            _qry.AND(_fieldname, Comparison.Equals, _txtsearch);
        }

        e.InputParameters["qry"] = _qry;
    }