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