public IEnumerable <Product> GetBySearchID(int searchID)
        {
            List <Product> products = new List <Product>();
            string         sql      = "select L.ItemID, L.SubscriberID, L.CategoryID, L.PartNumberAbbott, L.AbbottDescription, L.SizeText, L.DescriptionText, L.WebNoteText," +
                                      " L.Qty, L.Price, L.PriceUOM, L.flgOfferPrice, L.flgCertified, L.flgRoHS, S.SubscriberCode, S.SubscriberName, S.MinOrder, S.MinLine, S.Sales1," +
                                      " S.Sales2, I.MeasureSystem, I.SizeCode, I.LengthCode, I.LengthValue, I.HeadCode, I.PointCode, I.DriveCode, I.StyleCode," +
                                      " I.MaterialCode, I.FinishCode, I.Thickness, I.OutDiameter, A.ContactName, A.Street, A.City, A.StateID, A.Province, A.Zip, A.CountryID, A.Phone," +
                                      " A.Fax, A.Email" +
                                      " from ListItem L inner join SearchResult R on L.ItemID = R.ItemID inner join Subscriber S on L.SubscriberID = S.SubscriberID" +
                                      " inner join ItemAttribute I on L.ItemID = I.ItemID inner join AddressBook A on S.SubscriberID = A.SubscriberID where R.SearchID = @SearchID";
            SqlCommand             cmd       = new SqlCommand(sql, new SqlConnection(nolConnString));
            SqlParameterCollection sqlParams = cmd.Parameters;

            sqlParams.Add(new SqlParameter("@SearchID", SqlDbType.Int)).Value = searchID;
            SupplierRepository supplierRepository = new SupplierRepository();

            try
            {
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    Product          product          = BuildProduct(reader);
                    Supplier         supplier         = supplierRepository.BuildSupplier(reader);
                    ProductAttribute productAttribute = BuildProductAttribute(reader);
                    Address          address          = supplierRepository.BuildAddress(reader);
                    product.Supplier         = supplier;
                    product.Attribute        = productAttribute;
                    product.Supplier.Address = address;
                    products.Add(product);
                }
                return(products);
            }
            catch
            {
                return(null);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }