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