using System; 
using System.Data; 
using System.Data.Common; 
using System.Data.SqlClient; 
using System.Collections.Generic; 
using System.Text; 
using AdventureWorksTraderEntities; 
using Microsoft.Practices.EnterpriseLibrary.Data; 

namespace AdventureWorksTraderDataAccess 
{ 
public class ProductDB 
{ 
private DataColumnMapping[] mappings = new DataColumnMapping[] { 
new DataColumnMapping("ProductID","ProductID"), 
new DataColumnMapping("Name","Name"), 
new DataColumnMapping("ProductNumber","ProductNumber"), 
new DataColumnMapping("MakeFlag","MakeFlag"), 
new DataColumnMapping("FinishedGoodsFlag","FinishedGoodsFlag"), 
new DataColumnMapping("Color","Color"), 
new DataColumnMapping("SafetyStockLevel","SafetyStockLevel"), 
new DataColumnMapping("ReorderPoint","ReorderPoint"), 
new DataColumnMapping("StandardCost","StandardCost"), 
new DataColumnMapping("ListPrice","ListPrice"), 
new DataColumnMapping("Size","Size"), 
new DataColumnMapping("SizeUnitMeasureCode","SizeUnitMeasureCode"), 
new DataColumnMapping("WeightUnitMeasureCode","WeightUnitMeasureCode"), 
new DataColumnMapping("Weight","Weight"), 
new DataColumnMapping("DaysToManufacture","DaysToManufacture"), 
new DataColumnMapping("ProductLine","ProductLine"), 
new DataColumnMapping("Class","Class"), 
new DataColumnMapping("Style","Style"), 
new DataColumnMapping("ProductSubcategoryID","ProductSubcategoryID"), 
new DataColumnMapping("ProductModelID","ProductModelID"), 
new DataColumnMapping("SellStartDate","SellStartDate"), 
new DataColumnMapping("SellEndDate","SellEndDate"), 
new DataColumnMapping("DiscontinuedDate","DiscontinuedDate"), 
new DataColumnMapping("rowguid","Rowguid"), 
new DataColumnMapping("ModifiedDate","ModifiedDate")}; 

public IList<Product> GetProducts(int productSubcategoryID) 
{ 
IList<Product> list = new List<Product>(); 
Database db = DatabaseFactory.CreateDatabase(); 
string storedProcedureName = "GetProducts"; 
DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName); 
db.AddInParameter(dbCommand, "productSubCategoryID", DbType.Int32, 
productSubcategoryID); 
using (IDataReader reader = db.ExecuteReader(dbCommand)) 
{ 
while (reader.Read()) 
{ 
Product temp = new Product(); 
Product prod = (Product)DataAccessHelper.PopulateEntity(temp, mappings, reader); 
list.Add(prod); 
} 
} 
return list; 
} 
} 
}