protected void cmdImport_Click(object sender, EventArgs e)
{
var connection = new SQLServerConnect();
//for live db
//connection.SetupConnectionString("Server=MyPetsFW.db.3554730.hostedresource.com;USER ID=MyPetsFW;Password=DevryWeb460;Database=MyPetsFW;Trusted_Connection=False;");
//for my local db
//connection.SetupConnectionString("JON/Chris", "", @"Jon\SQLEXPRESS", "MyPetsFW");
connection.SetupConnectionString("TestUser", "testuser", @"Jon\SQLEXPRESS", "MyPetsFW");
var reader = new Reader
{
VendorID = 1,
LogFile = @"C:\WEB460\DataImport\Output\Import09.log",
DebugLevel = 3
};
const string fileName = @"C:\WEB460\DataImport\20090310-Products.txt";
//string output = !reader.DoImport(fileName, connection) ?
// "There was an problem with the import." :
// "Import Successful.";
//Console.WriteLine(output);
lblError.Text = !reader.DoImport(fileName, connection) ? "There was an problem with the import" : "Import Successful";
}
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 SQLServerBadDBUserLogin()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString("User1", "User1",
Server, Database);
connection.ReturnSQLDataReader("Create Table TestTable (Name char(10))", null);
}
public void InsertVendor()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Vendor]( [VendorID] [int] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_Vendor_IsActive] DEFAULT ((1)), [VendorName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MainPhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ContactName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ContactEmail] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ContactPhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Website] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Address2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Zip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Country] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Vendor] PRIMARY KEY CLUSTERED ( [VendorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]",
null);
var item = new Vendor
{
VendorID = 1,
VendorName = "Vendor 1",
IsActive = true,
MainPhone = "XXX.XXX.XXXX",
ContactName = "Testing",
ContactEmail = "PCode",
ContactPhone = "Desc",
Website = "Name",
Address = "Size",
Address2 = "Section",
City = "City",
State = "State",
Zip = "Zip",
Country = "Country"
};
var reader = new VendorDA
{
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 vendorList = reader.Execute();
Assert.AreEqual(0, vendorList.Count);
reader = new VendorDA
{
GetAll = true
};
reader.SetupConnectionString(connStr);
vendorList = reader.Execute();
Assert.AreEqual(1, vendorList.Count);
Assert.AreEqual("Vendor 1", vendorList[0].VendorName);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Vendor", 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);
}
}
public void InsertVendor()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
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 ItemCategories
{
ItemID = "Item 1",
VendorID = 1,
CategoryID = 1
};
var reader = new ItemCategoriesDA
{
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 ItemCategoriesDA
{
GetAll = true
};
reader.SetupConnectionString(connStr);
list = reader.Execute();
Assert.AreEqual(1, list.Count);
Assert.AreEqual("Item 1", list[0].ItemID);
}
finally
{
connection.ReturnSQLDataReader("Drop Table ItemCategories", null);
}
}
protected void btnFileUpload_Click(object sender, EventArgs e)
{
//this does not work yet, meant to browse for the file to be Imported.
var connection = new SQLServerConnect();
connection.SetupConnectionString("TestUser", "testuser", @"Jon\SQLEXPRESS", "MyPetsFW");
var reader = new Reader
{
VendorID = 1,
LogFile = @"C:\WEB460\DataImport\Output\Import01.log",
DebugLevel = 3
};
if (myFileUpload.HasFile)
{
myFileUpload.SaveAs(Server.MapPath("uploads/" + myFileUpload.FileName));
}
lblError.Text = !reader.DoImport(myFileUpload.ToString(), connection) ? "There was an problem with the import" : "Import Successful";
}
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 GetVendorList()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
connection.ReturnSQLDataReader("CREATE TABLE [dbo].[Vendor]( [VendorID] [int] NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_Vendor_IsActive] DEFAULT ((1)), [VendorName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MainPhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ContactName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ContactEmail] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ContactPhone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Website] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Address2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Zip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Country] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,CONSTRAINT [PK_Vendor] PRIMARY KEY CLUSTERED ( [VendorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]",
null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Vendor] ([VendorID], [VendorName], [MainPhone], [ContactName], [ContactEmail], [ContactPhone],[Website],[Address],[Address2],[City],[State],[Zip],[Country])VALUES(1, 'Vendor 1', '(xx) xxx-xxxx', 'Contact 1', 'Contact Email', '(ZZZ) ZZZ-ZZZZ', 'Website', 'Address', 'Address2', 'City', 'AZ', 'XXXX-XXXX', 'Country')", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Vendor] ([VendorID], [VendorName], [MainPhone], [ContactName], [ContactEmail], [ContactPhone],[Website],[Address],[Address2],[City],[State],[Zip],[Country])VALUES(2, 'Vendor 2', '(xx) xxx-xxxx', 'Contact 2', 'Contact Email', '(ZZZ) ZZZ-ZZZZ', 'Website', 'Address', 'Address2', 'City', 'AZ', 'XXXX-XXXX', 'Country')", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[Vendor] ([VendorID], [VendorName], [MainPhone], [ContactName], [ContactEmail], [ContactPhone],[Website],[Address],[Address2],[City],[State],[Zip],[Country])VALUES(3, 'Vendor 3', '(xx) xxx-xxxx', 'Contact 3', 'Contact Email', '(ZZZ) ZZZ-ZZZZ', 'Website', 'Address', 'Address2', 'City', 'AZ', 'XXXX-XXXX', 'Country')", null);
var reader = new VendorDA
{
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].VendorID);
Assert.AreEqual("Vendor 1", vendorList[0].VendorName);
Assert.AreEqual(2, vendorList[1].VendorID);
Assert.AreEqual("Vendor 2", vendorList[1].VendorName);
Assert.AreEqual(3, vendorList[2].VendorID);
Assert.AreEqual("Vendor 3", vendorList[2].VendorName);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Vendor", 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 SQLServerDBUserLogin()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
connection.ReturnSQLDataReader("Create Table TestTable (Name char(10))", null);
connection.ReturnSQLDataReader("Drop Table TestTable", null);
}
public void UpdateCategory()
{
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 vendorList = reader.Execute();
Assert.AreEqual(0, vendorList.Count);
var item2 = new Category
{
CategoryID = 1,
Name = "Category 1",
Picture = "Picture 2"
};
reader = new CategoryDA
{
WorkingItem = item2,
InsertUpdateData = true
};
reader.SetupConnectionString(connStr);
reader.Execute();
reader = new CategoryDA
{
GetAll = true
};
reader.SetupConnectionString(connStr);
vendorList = reader.Execute();
Assert.AreEqual(1, vendorList.Count);
Assert.AreEqual("Picture 2", vendorList[0].Picture);
}
finally
{
connection.ReturnSQLDataReader("Drop Table Categories", null);
}
}
public void QueryVendor()
{
var connection = new SQLServerConnect();
connection.SetupConnectionString(UserName, UserPassword,
Server, Database);
try
{
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);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[ItemCategories]([ItemID],[VendorID],[CategoryID])VALUES('Testing 1', 1, 1)", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[ItemCategories]([ItemID],[VendorID],[CategoryID])VALUES('Testing 2', 2, 2)", null);
connection.ReturnSQLDataReader("INSERT INTO [dbo].[ItemCategories]([ItemID],[VendorID],[CategoryID])VALUES('Testing 3', 3, 3)", null);
var item = new ItemCategories
{
ItemID = "Testing 2",
};
var reader = new ItemCategoriesDA
{
WorkingItem = item
};
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(1, vendorList.Count);
Assert.AreEqual(2, vendorList[0].CategoryID);
Assert.AreEqual("Testing 2", vendorList[0].ItemID);
}
finally
{
connection.ReturnSQLDataReader("Drop Table ItemCategories", null);
}
}