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); } }
public void GetProductListAll() { 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); CreateProductTable(connection); var reader = new ProductItemDA { GetAll = 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(53, items.Count); ProductItem checkItem = null; foreach (ProductItem item in items) { if (item.ItemID == "AII61036") { checkItem = item; } } Assert.AreEqual("Aquarium", checkItem.Category); } finally { connection.ReturnSQLDataReader("Drop Table Items", null); connection.ReturnSQLDataReader("Drop Table Categories", null); connection.ReturnSQLDataReader("Drop Table ItemCategories", 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 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); } }