コード例 #1
0
        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);
            }
        }
コード例 #2
0
        public void GetVendorList()
        {
            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 reader = new ItemCategoriesDA
                {
                    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].ItemID);
                Assert.AreEqual(2, vendorList[1].CategoryID);
                Assert.AreEqual("Testing 2", vendorList[1].ItemID);
                Assert.AreEqual(3, vendorList[2].CategoryID);
                Assert.AreEqual("Testing 3", vendorList[2].ItemID);
            }

            finally
            {
                connection.ReturnSQLDataReader("Drop Table ItemCategories", null);
            }
        }
コード例 #3
0
        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);
            }
        }