Class Item.cs
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Caching;
using System.Configuration;
namespace PetShop.Components {
public class Item {
private const string CACHE_KEY_ITEMS = "Items";
private const string CACHE_KEY_ITEMS_BY_PRODUCT = "ItemsByProduct:";
private const string SQL_SELECT_ITEMS = "SELECT Item.ItemId, Item.Attr1, Inventory.Qty, Item.ListPrice, Product.ProductId FROM Item INNER JOIN Inventory ON Item.ItemId = Inventory.ItemId INNER JOIN Product ON Item.ProductId = Product.ProductId";
private const string SQL_SELECT_ITEM = "SELECT Item.ItemId, Item.Attr1, Inventory.Qty, Item.ListPrice, Product.Name, Product.Descn FROM Item INNER JOIN Inventory ON Item.ItemId = Inventory.ItemId INNER JOIN Product ON Item.ProductId = Product.ProductId WHERE Item.ItemId = @ItemId";
private const string SQL_SELECT_ITEMS_BY_PRODUCT = "SELECT ItemId FROM Item WHERE ProductId = @ProductId";
private const string SQL_SELECT_ITEMS_BY_PRODUCTb = "SELECT ItemId, Attr1, ListPrice, Name FROM Item INNER JOIN Product ON Item.ProductId = Product.ProductId WHERE Item.ProductId = @ProductId";
private const string SQL_SELECT_INVENTORY = "SELECT Qty FROM Inventory WHERE ItemId = @ItemId";
private const string PARM_ITEM_ID = "@ItemId";
private const string PARM_PRODUCT_ID = "@ProductId";
private string id;
private string name;
public int quantity;
private decimal price;
private string productName;
private string productDesc;
private Product product;
public Item(string id, string name, int quantity, decimal price, Product product) {
this.id = id;
this.name = name;
this.quantity = quantity;
this.price = price;
this.product = product;
}
public Item(string id, string name, int quantity, decimal price, string productName, string productDesc) {
this.id = id;
this.name = name;
this.quantity = quantity;
this.price = price;
this.productName = productName;
this.productDesc = productDesc;
}
public Item(string id, string name, decimal price, string productName, string productDesc) {
this.id = id;
this.name = name;
this.price = price;
this.productName = productName;
this.productDesc = productDesc;
}
public string Id {
get { return id; }
}
public string Name {
get { return name; }
}
public string ProductName {
get { return productName; }
}
public string ProductDesc {
get { return productDesc; }
}
public int Quantity {
get { return quantity; }
}
public decimal Price {
get { return price; }
}
public Product Product {
get { return product; }
}
/* public static IDictionary GetItems() {
IDictionary items = (IDictionary)HttpContext.Current.Cache[CACHE_KEY_ITEMS];
if (items == null) {
items = new Hashtable();
IDictionary products = Product.GetProducts();
using (SqlDataReader rdr = Database.ExecuteReader(Database.CONN_STRING1, CommandType.Text, SQL_SELECT_ITEMS)) {
while (rdr.Read()) {
// We use a trim because the table datatype is a char and so we get padded cells at the end of the select
Item item = new Item(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetInt32(2), rdr.GetDecimal(3), (Product)products[rdr.GetString(4)]);
items.Add(item.Id, item);
}
}
HttpContext.Current.Cache.Insert(CACHE_KEY_ITEMS, items, new CacheDependency(null, Product.CACHE_DEP_PRODUCTS));
}
return items;
}*/
//public static IList GetItemsByProduct(string productId, int currentPage, int pageSize, out bool hasMore, out string productName) {
public static IList GetItemsByProduct(string productId, out string productName) {
IList itemsByProduct = new ArrayList();
productName = "";
//if(ConfigurationSettings.AppSettings["UseCaching"].Equals("true")) {
/* string cacheKey = CACHE_KEY_ITEMS_BY_PRODUCT + productId;
itemsByProduct = (IList)HttpContext.Current.Cache[cacheKey];
if (itemsByProduct == null) {
itemsByProduct = new ArrayList();
IDictionary items = GetItems();
SqlParameter parm = new SqlParameter(PARM_PRODUCT_ID, SqlDbType.Char, 10);
parm.Value = productId;
using (SqlDataReader rdr = Database.ExecuteReader(Database.CONN_STRING1, CommandType.Text, SQL_SELECT_ITEMS_BY_PRODUCT, parm)) {
while (rdr.Read())
itemsByProduct.Add(items[rdr.GetString(0).Trim()]);
}
HttpContext.Current.Cache.Insert(cacheKey, itemsByProduct, new CacheDependency(null, Product.CACHE_DEP_PRODUCTS));
}
productName = ((Item)itemsByProduct[0]).Product.Name;
*/
//}else{
SqlParameter parm = new SqlParameter(PARM_PRODUCT_ID, SqlDbType.Char, 10);
parm.Value = productId;
//int startIndex = (currentPage - 1) * pageSize;
//int num = 0;
//int count = 0;
using (SqlDataReader rdr = Database.ExecuteReader(Database.CONN_STRING1, CommandType.Text, SQL_SELECT_ITEMS_BY_PRODUCTb, parm)) {
while (rdr.Read()){
Item item = new Item(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetDecimal(2), rdr.GetString(3) , "");
//Item item = new Item(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetDecimal(2), "" , "");
itemsByProduct.Add(item);
}
/* while ((num < startIndex) && (rdr.Read())){
num++;
}
while ((count < pageSize) && (rdr.Read())){
Item item = new Item(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetDecimal(2), rdr.GetString(3) , "");
itemsByProduct.Add(item);
count++;
num++;
}
hasMore = false;
while(rdr.Read()){
hasMore=true;
}
*/
}
//}
return itemsByProduct;
}
public static Item GetItem(string itemId) {
Item item = null;
// if(ConfigurationSettings.AppSettings["UseCaching"].Equals("true")) {
/* IDictionary items = GetItems();
item = (Item)items[itemId];
SqlParameter parm = new SqlParameter(PARM_ITEM_ID, SqlDbType.Char, 10);
parm.Value = itemId;
item.quantity = (int)Database.ExecuteScalar(Database.CONN_STRING1, CommandType.Text, SQL_SELECT_INVENTORY, parm);
*/
//}else {
SqlParameter parm = new SqlParameter(PARM_ITEM_ID, SqlDbType.Char, 10);
parm.Value = itemId;
using (SqlDataReader rdr = Database.ExecuteReader(Database.CONN_STRING1, CommandType.Text, SQL_SELECT_ITEM, parm)) {
while (rdr.Read()) {
// We use a trim because the table datatype is a char and so we get padded cells at the end of the select
//item = new Item(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetInt32(2), rdr.GetDecimal(3), null);
item = new Item(rdr.GetString(0).Trim(), rdr.GetString(1), rdr.GetInt32(2), rdr.GetDecimal(3), rdr.GetString(4), rdr.GetString(5));
}
}
//}
return item;
}
}
}