public IEnumerable <ItemsToBrowse> FindSearchRequest(string searchitem) { List <ItemsToBrowse> CatalogueItems = new List <ItemsToBrowse>(); var strings = searchitem.Split(' '); foreach (var splitString in strings) { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spGetItemsForSearchRequest", con); cmd.CommandType = CommandType.StoredProcedure; SqlParameter paramFoodName = new SqlParameter(); paramFoodName.ParameterName = "@searchItem"; paramFoodName.Value = splitString; cmd.Parameters.Add(paramFoodName); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { ItemsToBrowse CatalogueItem = new ItemsToBrowse(); CatalogueItem.sellerId = Convert.ToDecimal(rdr["sellerId"]); CatalogueItem.catalogueId = Convert.ToDecimal(rdr["catalogueId"]); CatalogueItem.itemDescription = rdr["itemDescription"].ToString(); CatalogueItem.itemId = Convert.ToDecimal(rdr["itemId"]); CatalogueItem.itemName = rdr["itemName"].ToString(); CatalogueItem.itemPrice = rdr["itemPrice"].ToString(); CatalogueItem.itemPicture = rdr["itemPicture"].ToString(); CatalogueItem.pictureA = rdr["pictureA"].ToString(); CatalogueItem.pictureB = rdr["pictureB"].ToString(); CatalogueItem.pictureC = rdr["pictureC"].ToString(); CatalogueItem.sellerName = rdr["sellerName"].ToString(); CatalogueItem.sellerAddress = rdr["sellerAddress"].ToString(); CatalogueItem.sellerPhone = rdr["sellerPhone"].ToString(); CatalogueItem.sellerWebsite = rdr["sellerWebsite"].ToString(); CatalogueItem.sellerEmail = rdr["sellerEmail"].ToString(); CatalogueItem.itemAlternatePicName = rdr["itemAlternatePicName"].ToString(); CatalogueItems.Add(CatalogueItem); } rdr.Dispose(); } } //remove deuplicates /*var MyQuery = (from item in CatalogueItems * * orderby item * * select item).Distinct();*/ var DistinctItems = CatalogueItems.GroupBy(x => x.itemId).Select(y => y.First()); return(DistinctItems); }
public IEnumerable <ItemsToBrowse> RetrieveItemsinCatalogueType(decimal id) { List <ItemsToBrowse> CatalogueItems = new List <ItemsToBrowse>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spGetItemsForCatalogueType", con); cmd.CommandType = CommandType.StoredProcedure; SqlParameter paramFoodName = new SqlParameter(); paramFoodName.ParameterName = "@catalogueId"; paramFoodName.Value = id; cmd.Parameters.Add(paramFoodName); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { ItemsToBrowse CatalogueItem = new ItemsToBrowse(); CatalogueItem.sellerId = Convert.ToDecimal(rdr["sellerId"]); CatalogueItem.catalogueId = Convert.ToDecimal(rdr["catalogueId"]); CatalogueItem.itemDescription = rdr["itemDescription"].ToString(); CatalogueItem.itemId = Convert.ToDecimal(rdr["itemId"]); CatalogueItem.itemName = rdr["itemName"].ToString(); CatalogueItem.itemPrice = rdr["itemPrice"].ToString(); CatalogueItem.itemPicture = rdr["itemPicture"].ToString(); CatalogueItem.pictureA = rdr["pictureA"].ToString(); CatalogueItem.pictureB = rdr["pictureB"].ToString(); CatalogueItem.pictureC = rdr["pictureC"].ToString(); CatalogueItem.sellerName = rdr["sellerName"].ToString(); CatalogueItem.sellerAddress = rdr["sellerAddress"].ToString(); CatalogueItem.sellerPhone = rdr["sellerPhone"].ToString(); CatalogueItem.sellerEmail = rdr["sellerEmail"].ToString(); CatalogueItem.sellerWebsite = rdr["sellerWebsite"].ToString(); CatalogueItem.itemAlternatePicName = rdr["itemAlternatePicName"].ToString(); CatalogueItems.Add(CatalogueItem); } rdr.Dispose(); } return(CatalogueItems); }