public void GetCategoryList()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
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("INSERT INTO [dbo].[Categories]([CategoryID],[CategoryName],[CategoryPhoto])VALUES(1, 'Testing 1', 'Picture 1')", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Categories]([CategoryID],[CategoryName],[CategoryPhoto])VALUES(2, 'Testing 2', 'Picture 2')", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Categories]([CategoryID],[CategoryName],[CategoryPhoto])VALUES(3, 'Testing 3', 'Picture 3')", null);
var reader = new CategoryDA
{
GetAll = true
};
string connStr = String.Format(
"Data Source='{0}'; database={1}; user id={2}; password={3}",
Server, Database, UserName, UserPassword);
reader.SetupConnectionString(connStr);
var vendorList = reader.Execute();
Assert.AreEqual(3, vendorList.Count);
Assert.AreEqual(1, vendorList[0].CategoryID);
Assert.AreEqual("Testing 1", vendorList[0].Name);
Assert.AreEqual(2, vendorList[1].CategoryID);
Assert.AreEqual("Testing 2", vendorList[1].Name);
Assert.AreEqual(3, vendorList[2].CategoryID);
Assert.AreEqual("Testing 3", vendorList[2].Name);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Categories", null);
}
}
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);
}
}
public void InsertCategory()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
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);
var item = new Category
{
Name = "Category 1",
Picture = "Picture 1"
};
var reader = new CategoryDA
{
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 list = reader.Execute();
Assert.AreEqual(0, list.Count);
reader = new CategoryDA
{
GetAll = true
};
reader.SetupConnectionString(connStr);
list = reader.Execute();
Assert.AreEqual(1, list.Count);
Assert.AreEqual("Category 1", list[0].Name);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Categories", null);
}
}