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