public List <Item> GetAllActiveItemsByCategory(int catId) { List <Item> items = new List <Item>(); Item item = null; using (SqlConnection connection = DBConnection.GetConnection()) { using (SqlCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * " + "FROM Item "; if (catId != -1) { cmd.CommandText += "WHERE categoryId = @catId AND state = 0"; } else { cmd.CommandText += "WHERE state = 0"; } cmd.Parameters.AddWithValue("@catId", catId); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { User seller = new User { UserName = reader["sellerUsername"].ToString() }; item = new Item { Id = (int)reader["id"], Name = CheckValue(reader["name"]).ToString(), Description = CheckValue(reader["description"]).ToString(), InitialPrice = (double)reader["initialPrice"], StartDate = (DateTime)reader["startDate"], EndDate = (DateTime)reader["endDate"], State = (int)reader["state"], SellerUser = seller, Category = DBCategory.GetItemCategory((int)reader["categoryId"]), ImagePath = (string)reader["imagePath"] }; List <Bid> winningBid = new DBBid().GetBids((int)reader["id"], true); item.WinningBid = winningBid.Count != 0 ? winningBid[0] : null; items.Add(item); } } return(items); } } }
public Item GetItemById(int id) { Item item = null; try { using (SqlConnection connection = DBConnection.GetConnection()) { using (SqlCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * " + "FROM [Item] " + "WHERE id = @id"; cmd.Parameters.AddWithValue("@id", id); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { User seller = new User { UserName = reader["sellerUsername"].ToString() }; item = new Item { Id = (int)reader["id"], Name = reader["name"].ToString(), Description = CheckValue(reader["description"]).ToString(), InitialPrice = (double)reader["initialPrice"], StartDate = (DateTime)reader["startDate"], EndDate = (DateTime)reader["endDate"], State = (int)reader["state"], SellerUser = seller, Category = DBCategory.GetItemCategory((int)reader["categoryId"]), ImagePath = (string)reader["imagePath"] }; List <Bid> winningBid = new DBBid().GetBids(item.Id, true); List <Bid> prevBids = new DBBid().GetAllBidsByItem(item.Id); item.WinningBid = winningBid.Count != 0 ? winningBid[0] : null; item.OldBids = prevBids.Count != 0 ? prevBids : null; } } } } } } catch (Exception e) { Debug.Write("\n #### ERROR IN GetItemById START #### \n"); Debug.Write("\n" + e + "\n"); Debug.Write("\n #### ERROR FOR GetItemById END #### \n"); } return(item); }
public List <Item> GetAllItems() { List <Item> items = new List <Item>(); Item item = null; using (SqlConnection connection = DBConnection.GetConnection()) { using (SqlCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * " + "FROM Item "; SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { User seller = new User { UserName = reader["sellerUsername"].ToString() }; item = new Item { Id = (int)reader["id"], Name = CheckValue(reader["name"]).ToString(), Description = CheckValue(reader["description"]).ToString(), InitialPrice = (double)reader["initialPrice"], StartDate = (DateTime)reader["startDate"], EndDate = (DateTime)reader["endDate"], State = (int)reader["state"], SellerUser = seller, Category = DBCategory.GetItemCategory((int)reader["categoryId"]), ImagePath = (string)reader["imagePath"] }; List <Bid> winningBid = new DBBid().GetBids(item.Id, true); List <Bid> prevBids = new DBBid().GetBids(item.Id, false); item.WinningBid = winningBid.Count != 0 ? winningBid[0] : null; item.OldBids = prevBids.Count != 0 ? prevBids : null; items.Add(item); } } return(items); } } }
public List <Item> SearchItems(string value, int categoryId) { List <Item> items = new List <Item>(); User seller = null; Item item = null; try { using (SqlConnection connection = DBConnection.GetConnection()) { using (SqlCommand cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * " + "FROM Item "; if (categoryId != -1) { cmd.CommandText += "WHERE(name LIKE @value OR description LIKE @value OR sellerUsername LIKE @value) AND categoryId = @catId"; } else { cmd.CommandText += "WHERE(name LIKE @value OR description LIKE @value OR sellerUsername LIKE @value)"; } cmd.Parameters.AddWithValue("@catId", categoryId); cmd.Parameters.AddWithValue("@value", "%" + value + "%"); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { seller = new User { UserName = (string)reader["sellerUsername"] }; item = new Item { Id = int.Parse(CheckValue(reader["id"]).ToString()), Name = CheckValue(reader["name"]).ToString(), Description = CheckValue(reader["description"]).ToString(), InitialPrice = (double)reader["initialPrice"], StartDate = (DateTime)reader["startDate"], EndDate = (DateTime)reader["endDate"], State = (int)reader["state"], SellerUser = seller, Category = DBCategory.GetItemCategory((int)reader["categoryId"]), ImagePath = (string)reader["imagePath"] }; List <Bid> winningBid = new DBBid().GetBids((int)reader["id"], true); item.WinningBid = winningBid.Count != 0 ? winningBid[0] : null; items.Add(item); } } } } } catch (Exception e) { Debug.Write("\n #### ERROR IN SearchItems START #### \n"); Debug.Write("\n" + e + "\n"); Debug.Write("\n #### ERROR FOR SearchItems END #### \n"); } return(items); }