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