public void ParmListWithOutObject()
{
var parmList = new ParmList();
parmList.Add("Int", 1);
parmList.Add("String", "Testing");
parmList.Add("Decimal", 3.54M);
var list = parmList.Items;
Assert.AreEqual(list[0].ParmName, "Int");
Assert.AreEqual(list[0].ParmObj, 1);
Assert.AreEqual(list[1].ParmName, "String");
Assert.AreEqual(list[1].ParmObj, "Testing");
Assert.AreEqual(list[2].ParmName, "Decimal");
Assert.AreEqual(list[2].ParmObj, 3.54M);
}
/// <summary>
/// This method will take in a stored procedure call
/// and return a SqlDataReader
/// </summary>
/// <param name="P_cmd">
/// Stored Procedure to execute
/// </param>
/// <param name="P_parmList">
/// List of parameters
/// </param>
public void ExecuteCmd(string P_cmd, ParmList P_parmList)
{
var dtResults = new DataTable();
var sqlConnection = new SqlConnection(GetConnectionString());
var cmd = new SqlCommand
{
CommandText = P_cmd,
Connection = sqlConnection,
CommandTimeout = DatabaseCommandTimeOut
};
if ((P_parmList != null) && (P_parmList.Items.Count > 0))
{
foreach (ParmObject obj in P_parmList.Items)
{
cmd.Parameters.AddWithValue(obj.ParmName, obj.ParmObj);
}
}
var sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dtResults);
}
/// <summary>
/// This method will get a list of products. Any attributes set
/// will be use for the where clause. If no attributes are selected
/// then all products will be returned.
/// </summary>
/// <returns>
/// List of products selected
/// </returns>
public List<ProductItem> GetProductList(DAConnect P_connection)
{
var retVal = new List<ProductItem>();
string whereClause = "";
var parmList = new ParmList();
if (VendorID != -1)
{
parmList.Add(ProductItem.ParmVendorID, VendorID);
AddToWhereClause(ref whereClause, ProductItem.VendorColumn,
ProductItem.ParmVendorID);
}
if (ProductCode != null)
{
parmList.Add(ProductItem.ParmProdCode, ProductCode);
AddToWhereClause(ref whereClause, ProductItem.ProdCodeColumn,
ProductItem.ParmProdCode);
}
if (Section != null)
{
parmList.Add(ProductItem.ParmSection, Section);
AddToWhereClause(ref whereClause, ProductItem.SectionColumn,
ProductItem.ParmSection);
}
if (Category != null)
{
parmList.Add(ProductItem.ParmCategory, Category);
AddToWhereClause(ref whereClause, ProductItem.CategoryColumn,
ProductItem.ParmCategory);
}
if (ProductName != null)
{
parmList.Add(ProductItem.ParmProductName, ProductName);
AddToWhereClause(ref whereClause, ProductItem.ProdNameColumn,
ProductItem.ParmProductName);
}
if (ProductDescription != null)
{
parmList.Add(ProductItem.ParmDescription, ProductDescription);
AddToWhereClause(ref whereClause, ProductItem.DescriptionColumn,
ProductItem.ParmDescription);
}
if (Picture != null)
{
parmList.Add(ProductItem.ParmPicture, Picture);
AddToWhereClause(ref whereClause, ProductItem.PictureColumn,
ProductItem.ParmPicture);
}
if (ProductSize != null)
{
parmList.Add(ProductItem.ParmProductSize, ProductSize);
AddToWhereClause(ref whereClause, ProductItem.SizeColumn,
ProductItem.ParmProductSize);
}
if (Cost != -1M)
{
parmList.Add(ProductItem.ParmCost, Cost);
AddToWhereClause(ref whereClause, ProductItem.CostColumn,
ProductItem.ParmCost);
}
if (ShippingSurcharge != -1M)
{
parmList.Add(ProductItem.ParmShippingSurcharge, ShippingSurcharge);
AddToWhereClause(ref whereClause, ProductItem.ShippingChargeColumn,
ProductItem.ParmShippingSurcharge);
}
if (UPC != null)
{
parmList.Add(ProductItem.ParmUPC, UPC);
AddToWhereClause(ref whereClause, ProductItem.UPCColumn,
ProductItem.ParmUPC);
}
if (whereClause != "")
{
whereClause = String.Format(" Where {0}", whereClause);
}
string cmd = String.Format("Select * from Product{0}", whereClause);
DataTable data = P_connection.ReturnSQLDataReader(cmd, parmList);
foreach (DataRow row in data.Rows)
{
var item = new ProductItem();
item.Category = row[ProductItem.CategoryColumn].ToString().Trim();
item.Cost = (decimal)row[ProductItem.CostColumn];
item.Picture = row[ProductItem.PictureColumn].ToString().Trim();
item.ProductCode = row[ProductItem.ProdCodeColumn].ToString().Trim();
item.ProductDescription = row[ProductItem.DescriptionColumn].ToString().Trim();
item.ProductName = row[ProductItem.ProdNameColumn].ToString().Trim();
item.ProductSize = row[ProductItem.SizeColumn].ToString().Trim();
item.Section = row[ProductItem.SectionColumn].ToString().Trim();
item.ShippingSurcharge = (decimal) row[ProductItem.ShippingChargeColumn];
item.UPC = row[ProductItem.UPCColumn].ToString().Trim();
retVal.Add(item);
}
return(retVal);
}