public void GetProductListCatMatch()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Items]([ItemID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[VendorID] [int] NOT NULL,[IsActive] [bit] NULL,[Description] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[QuantityAvailable] [int] NULL,[Price] [money] NULL,[PhotoName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PhotoLocation] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MinQuantity] [int] NULL,[CostPrice] [money] NULL,[RecommendedPrice] [money] NULL,[UPC] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ProductName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ProductCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Size] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([ItemID] ASC,[VendorID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]", null);
CreateProductTable(connection);
var item = new ProductItem
{
ProductCode = "ATS77848"
};
var reader = new ProductItemDA
{
WorkingItem = item
};
string connStr = String.Format(
"Data Source='{0}'; database={1}; user id={2}; password={3}",
Server, Database, UserName, UserPassword);
reader.SetupConnectionString(connStr);
var items = reader.Execute();
Assert.AreEqual(1, items.Count);
}
finally
{
connection.ExecuteCmd("Drop Table Items", null);
}
}
private static void DoProductInsert(string P_line, DAConnect P_connection)
{
string[] parts = P_line.Split('\t');
var item = new ProductItem {VendorID = 1};
if (parts[1].Length < 50)
{
item.ItemID = parts[1];
item.ProductCode = parts[1];
}
else
{
item.ItemID = parts[1].Substring(0, 50);
item.ProductCode = parts[1].Substring(0, 50);
}
if (parts[2].Length < 50)
{
item.Category = parts[2];
}
else
{
item.Category = parts[2].Substring(0, 50);
}
if (parts[4].Length < 50)
{
item.ProductName = parts[4];
}
else
{
item.ProductName = parts[4].Substring(0, 50);
}
if (parts[5].Length < 50)
{
item.ProductDescription = parts[5];
}
else
{
item.ProductDescription = parts[5].Substring(0, 50);
}
if (parts[6].Length < 50)
{
item.Picture = parts[6];
}
else
{
item.Picture = parts[6].Substring(0, 50);
}
if (parts[7].Length < 50)
{
item.ProductSize = parts[7];
}
else
{
item.ProductSize = parts[7].Substring(0, 50);
}
item.Cost = Convert.ToDecimal(parts[8]);
item.UPC = parts[10];
var itemDA = new ProductItemDA {WorkingItem = item, InsertUpdateData = true};
itemDA.SetupConnectionString(P_connection.GetConnectionString());
itemDA.Execute();
}
public void TestProductItem()
{
var item = new ProductItem
{
Category = "ABC",
Cost = 5.54M,
VendorID = 10,
Picture = "Testing",
ProductCode = "PCode",
ProductDescription = "Desc",
ProductName = "Name",
ProductSize = "Size",
Section = "Section",
ShippingSurcharge = 3.43M,
UPC = "UPCData"
};
Assert.AreEqual("ABC", item.Category);
Assert.AreEqual(5.54, item.Cost);
Assert.AreEqual(10, item.VendorID);
Assert.AreEqual("Testing", item.Picture);
Assert.AreEqual("PCode", item.ProductCode);
Assert.AreEqual("Desc", item.ProductDescription);
Assert.AreEqual("Name", item.ProductName);
Assert.AreEqual("Size", item.ProductSize);
Assert.AreEqual("Section", item.Section);
Assert.AreEqual(3.43, item.ShippingSurcharge);
Assert.AreEqual("UPCData", item.UPC);
}
public void TestProductItemUpdate2()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Items]([ItemID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[VendorID] [int] NOT NULL,[IsActive] [bit] NULL,[Description] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[QuantityAvailable] [int] NULL,[Price] [money] NULL,[PhotoName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PhotoLocation] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MinQuantity] [int] NULL,[CostPrice] [money] NULL,[RecommendedPrice] [money] NULL,[UPC] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ProductName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ProductCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Size] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED ([ItemID] ASC,[VendorID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]", null);
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Categories]([CategoryID] [int] NOT NULL,[CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CategoryPhoto] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ([CategoryID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]",
null);
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[ItemCategories]([ItemID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[VendorID] [int] NOT NULL,[CategoryID] [int] NOT NULL,CONSTRAINT [PK_ItemCategories] PRIMARY KEY CLUSTERED ([ItemID] ASC,[VendorID] ASC,[CategoryID] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]",
null);
var item = new ProductItem
{
ItemID = "WERT",
Category = "ABC",
Subcategory = "DEF",
Cost = 5.54M,
VendorID = 10,
Picture = "Testing",
ProductCode = "PCode",
ProductDescription = "Desc",
ProductName = "Name",
ProductSize = "Size",
Section = "Section",
ShippingSurcharge = 3.43M,
UPC = "UPCData"
};
var reader = new ProductItemDA
{
WorkingItem = item,
InsertUpdateData = true
};
string connStr = String.Format(
"Data Source='{0}'; database={1}; user id={2}; password={3}",
Server, Database, UserName, UserPassword);
reader.SetupConnectionString(connStr);
var items = reader.Execute();
Assert.AreEqual(0, items.Count);
item = new ProductItem
{
ItemID = "KJHG",
Category = "ABC",
Subcategory = "DEF",
Cost = 5.54M,
VendorID = 10,
Picture = "Testing",
ProductCode = "PCode",
ProductDescription = "New Desc",
ProductName = "Name",
ProductSize = "Size",
Section = "Section",
ShippingSurcharge = 3.43M,
UPC = "UPCData"
};
reader = new ProductItemDA
{
WorkingItem = item,
InsertUpdateData = true
};
reader.SetupConnectionString(connStr);
reader.Execute();
var productItemReader = new ProductItemDA
{
GetAll = true
};
productItemReader.SetupConnectionString(connStr);
var productItems = productItemReader.Execute();
Assert.AreEqual(2, productItems.Count);
var categoryReader = new CategoryDA
{
GetAll = true
};
categoryReader.SetupConnectionString(connStr);
var catItems = categoryReader.Execute();
Assert.AreEqual(1, catItems.Count);
var itemCategoryReader = new ItemCategoriesDA
{
GetAll = true
};
itemCategoryReader.SetupConnectionString(connStr);
var itemCatItems = itemCategoryReader.Execute();
Assert.AreEqual(2, itemCatItems.Count);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Items", null);
connection.ReturnSQLDataReader("Drop Table Categories", null);
connection.ReturnSQLDataReader("Drop Table ItemCategories", null);
}
}
/// <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);
}