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