public List <Customer> UpdateCustomerToToysForBoys(List <Customer> modifiedProducts) { List <Customer> failedUpdates = new List <Customer>(); var manager = new ToysForBoysDbManager(); using (var conToys = manager.GetConnection()) { using (var comUpdate = conToys.CreateCommand()) { comUpdate.CommandType = CommandType.StoredProcedure; comUpdate.CommandText = "[toysforboys].[dbo].[SP_ToysForBoys_UpdateProduct]"; var parId = comUpdate.CreateParameter(); parId.ParameterName = "@id"; comUpdate.Parameters.Add(parId); var parName = comUpdate.CreateParameter(); parName.ParameterName = "@name"; comUpdate.Parameters.Add(parName); var parScale = comUpdate.CreateParameter(); parScale.ParameterName = "@streetAndNumber"; comUpdate.Parameters.Add(parScale); var parDescription = comUpdate.CreateParameter(); parDescription.ParameterName = "@city"; comUpdate.Parameters.Add(parDescription); var parInStock = comUpdate.CreateParameter(); parInStock.ParameterName = "@state"; comUpdate.Parameters.Add(parInStock); var parInOrder = comUpdate.CreateParameter(); parInOrder.ParameterName = "@postalCode"; comUpdate.Parameters.Add(parInOrder); var parPrice = comUpdate.CreateParameter(); parPrice.ParameterName = "@countryId"; comUpdate.Parameters.Add(parPrice); var parProductlineId = comUpdate.CreateParameter(); parProductlineId.ParameterName = "@email"; comUpdate.Parameters.Add(parProductlineId); conToys.Open(); foreach (Customer x in modifiedProducts) { try { parId.Value = x.Id; parName.Value = x.Name; parScale.Value = x.StreetAndNumber; parDescription.Value = x.City; parInStock.Value = x.State; parInOrder.Value = x.PostalCode; parProductlineId.Value = x.CountryId; parPrice.Value = x.Email; if (comUpdate.ExecuteNonQuery() == 0) { failedUpdates.Add(x); } } catch (Exception ex) { failedUpdates.Add(x); } } } } return(failedUpdates); }
public ObservableCollection <Customer> GetCustomersByName(String name) { ObservableCollection <Customer> products = new ObservableCollection <Customer>(); var dbManager = new ToysForBoysDbManager(); using (var conProducten = dbManager.GetConnection()) { using (var comProducten = conProducten.CreateCommand()) { comProducten.CommandType = CommandType.StoredProcedure; if (!string.IsNullOrEmpty(name)) { comProducten.CommandText = "[ToysForBoys].[dbo].[SP_GetAlLCustomers]"; var parName = comProducten.CreateParameter(); parName.ParameterName = "@name"; parName.Value = name; comProducten.Parameters.Add(parName); } else { comProducten.CommandText = "[toysforboys].[dbo].[SP_GetAllProducts]"; } conProducten.Open(); using (var rdrCustomers = comProducten.ExecuteReader()) { Int32 prodIdPos = rdrCustomers.GetOrdinal("id"); Int32 prodNamePos = rdrCustomers.GetOrdinal("name"); Int32 prodStreetAndNumberPos = rdrCustomers.GetOrdinal("streetAndNumber"); Int32 prodCityPos = rdrCustomers.GetOrdinal("city"); Int32 prodStatePos = rdrCustomers.GetOrdinal("state"); Int32 prodPostalCodePos = rdrCustomers.GetOrdinal("postalCode"); Int32 prodCountryIdPos = rdrCustomers.GetOrdinal("countryId"); Int32 prodEmailPos = rdrCustomers.GetOrdinal("email"); Int32 pId; String pName; String pStreetAndNumber; String pCity; String pState; String pPostalCode; Int32 pCountryId; String pEmail; while (rdrCustomers.Read()) { if (rdrCustomers.IsDBNull(prodIdPos)) { pId = 0; } else { pId = rdrCustomers.GetInt32(prodIdPos); } if (rdrCustomers.IsDBNull(prodNamePos)) { pName = null; } else { pName = rdrCustomers.GetString(prodNamePos); } if (rdrCustomers.IsDBNull(prodStreetAndNumberPos)) { pStreetAndNumber = null; } else { pStreetAndNumber = rdrCustomers.GetString(prodStreetAndNumberPos); } if (rdrCustomers.IsDBNull(prodCityPos)) { pCity = null; } else { pCity = rdrCustomers.GetString(prodCityPos); } if (rdrCustomers.IsDBNull(prodStatePos)) { pState = null; } else { pState = rdrCustomers.GetString(prodStatePos); } if (rdrCustomers.IsDBNull(prodPostalCodePos)) { pPostalCode = null; } else { pPostalCode = rdrCustomers.GetString(prodPostalCodePos); } if (rdrCustomers.IsDBNull(prodCountryIdPos)) { pCountryId = 0; } else { pCountryId = rdrCustomers.GetInt32(prodCountryIdPos); } if (rdrCustomers.IsDBNull(prodEmailPos)) { pEmail = null; } else { pEmail = rdrCustomers.GetString(prodEmailPos); } products.Add(new Customer(pId, pName, pStreetAndNumber, pCity, pState, pPostalCode, pCountryId, pEmail)); } // do while } // using rdrProducts } // using comProducts } // using conToysForBoys return(products); }
public List <Customer> AddNewCustomerToToysForBoys(List <Customer> newProducts) { // throw new NotImplementedException(); List <Customer> nietToegevoegdeProducts = new List <Customer>(); var manager = new ToysForBoysDbManager(); using (var conBieren = manager.GetConnection()) { using (var comInsert = conBieren.CreateCommand()) { comInsert.CommandType = CommandType.StoredProcedure; comInsert.CommandText = "SP_ToysForBoys_AddNewProduct"; var parName = comInsert.CreateParameter(); parName.ParameterName = "@name"; comInsert.Parameters.Add(parName); var parScale = comInsert.CreateParameter(); parScale.ParameterName = "@streetAndNumber"; comInsert.Parameters.Add(parScale); var parDescription = comInsert.CreateParameter(); parDescription.ParameterName = "@city"; comInsert.Parameters.Add(parDescription); var parQuantityInStock = comInsert.CreateParameter(); parQuantityInStock.ParameterName = "@state"; comInsert.Parameters.Add(parQuantityInStock); var parQuantityInOrder = comInsert.CreateParameter(); parQuantityInOrder.ParameterName = "@postalCode"; comInsert.Parameters.Add(parQuantityInOrder); var parBuyPrice = comInsert.CreateParameter(); parBuyPrice.ParameterName = "@countryId"; comInsert.Parameters.Add(parBuyPrice); var parProductlineId = comInsert.CreateParameter(); parProductlineId.ParameterName = "@email"; comInsert.Parameters.Add(parProductlineId); conBieren.Open(); foreach (Customer eenProduct in newProducts) { try { parName.Value = eenProduct.Name; parScale.Value = eenProduct.StreetAndNumber; parDescription.Value = eenProduct.City; parQuantityInStock.Value = eenProduct.State; parQuantityInOrder.Value = eenProduct.PostalCode; parBuyPrice.Value = eenProduct.CountryId; parProductlineId.Value = eenProduct.Email; if (comInsert.ExecuteNonQuery() == 0) { nietToegevoegdeProducts.Add(eenProduct); } } catch (Exception ex) { nietToegevoegdeProducts.Add(eenProduct); } } // foreach } // comInsert } // conBieren return(nietToegevoegdeProducts); }
public List <Product> UpdateProductToToysForBoys(List <Product> modifiedProducts) { List <Product> failedUpdates = new List <Product>(); var manager = new ToysForBoysDbManager(); using (var conToys = manager.GetConnection()) { using (var comUpdate = conToys.CreateCommand()) { comUpdate.CommandType = CommandType.StoredProcedure; comUpdate.CommandText = "[toysforboys].[dbo].[SP_ToysForBoys_UpdateProduct]"; var parId = comUpdate.CreateParameter(); parId.ParameterName = "@id"; comUpdate.Parameters.Add(parId); var parName = comUpdate.CreateParameter(); parName.ParameterName = "@name"; comUpdate.Parameters.Add(parName); var parScale = comUpdate.CreateParameter(); parScale.ParameterName = "@scale"; comUpdate.Parameters.Add(parScale); var parDescription = comUpdate.CreateParameter(); parDescription.ParameterName = "@description"; comUpdate.Parameters.Add(parDescription); var parInStock = comUpdate.CreateParameter(); parInStock.ParameterName = "@quantityinstock"; comUpdate.Parameters.Add(parInStock); var parInOrder = comUpdate.CreateParameter(); parInOrder.ParameterName = "@quantityinorder"; comUpdate.Parameters.Add(parInOrder); var parPrice = comUpdate.CreateParameter(); parPrice.ParameterName = "@buyprice"; comUpdate.Parameters.Add(parPrice); var parProductlineId = comUpdate.CreateParameter(); parProductlineId.ParameterName = "@productlineid"; comUpdate.Parameters.Add(parProductlineId); conToys.Open(); foreach (Product x in modifiedProducts) { try { parId.Value = x.Id; parName.Value = x.Name; parScale.Value = x.Scale; parDescription.Value = x.Description; parInStock.Value = x.QuantityInStock; parInOrder.Value = x.QuantityInOrder; parProductlineId.Value = x.ProductlineId; parPrice.Value = x.BuyPrice; if (comUpdate.ExecuteNonQuery() == 0) { failedUpdates.Add(x); } } catch (Exception ex) { failedUpdates.Add(x); } } } } return(failedUpdates); }
public ObservableCollection <Customer> GetAllCustomers() { ObservableCollection <Customer> customers = new ObservableCollection <Customer>(); var dbManager = new ToysForBoysDbManager(); using (var conCustomers = dbManager.GetConnection()) { using (var comCustomers = conCustomers.CreateCommand()) { comCustomers.CommandType = CommandType.StoredProcedure; comCustomers.CommandText = "[toysforboys].[dbo].[SP_GetAllCustomers]"; conCustomers.Open(); using (var rdrCustomers = comCustomers.ExecuteReader()) { Int32 prodIdPos = rdrCustomers.GetOrdinal("id"); Int32 prodNamePos = rdrCustomers.GetOrdinal("name"); Int32 prodStreetAndNumberPos = rdrCustomers.GetOrdinal("streetAndNumber"); Int32 prodCityPos = rdrCustomers.GetOrdinal("city"); Int32 prodStatePos = rdrCustomers.GetOrdinal("state"); Int32 prodPostalCodePos = rdrCustomers.GetOrdinal("postalCode"); Int32 prodCountryIdPos = rdrCustomers.GetOrdinal("countryId"); Int32 prodEmailPos = rdrCustomers.GetOrdinal("email"); Int32 pId; String pName; String pStreetAndNumber; String pCity; String pState; String pPostalCode; Int32 pCountryId; String pEmail; while (rdrCustomers.Read()) { if (rdrCustomers.IsDBNull(prodIdPos)) { pId = 0; } else { pId = rdrCustomers.GetInt32(prodIdPos); } if (rdrCustomers.IsDBNull(prodNamePos)) { pName = null; } else { pName = rdrCustomers.GetString(prodNamePos); } if (rdrCustomers.IsDBNull(prodStreetAndNumberPos)) { pStreetAndNumber = null; } else { pStreetAndNumber = rdrCustomers.GetString(prodStreetAndNumberPos); } if (rdrCustomers.IsDBNull(prodCityPos)) { pCity = null; } else { pCity = rdrCustomers.GetString(prodCityPos); } if (rdrCustomers.IsDBNull(prodStatePos)) { pState = null; } else { pState = rdrCustomers.GetString(prodStatePos); } if (rdrCustomers.IsDBNull(prodPostalCodePos)) { pPostalCode = null; } else { pPostalCode = rdrCustomers.GetString(prodPostalCodePos); } if (rdrCustomers.IsDBNull(prodCountryIdPos)) { pCountryId = 0; } else { pCountryId = rdrCustomers.GetInt32(prodCountryIdPos); } if (rdrCustomers.IsDBNull(prodEmailPos)) { pEmail = null; } else { pEmail = rdrCustomers.GetString(prodEmailPos); } customers.Add(new Customer(pId, pName, pStreetAndNumber, pCity, pState, pPostalCode, pCountryId, pEmail)); } // do while } // using rdrCustomers } // using comCustomers } // using conCustomers return(customers); }
public List <Product> AddNewProductToToysForBoys(List <Product> newProducts) { // throw new NotImplementedException(); List <Product> nietToegevoegdeProducts = new List <Product>(); var manager = new ToysForBoysDbManager(); using (var conBieren = manager.GetConnection()) { using (var comInsert = conBieren.CreateCommand()) { comInsert.CommandType = CommandType.StoredProcedure; comInsert.CommandText = "SP_ToysForBoys_AddNewProduct"; var parName = comInsert.CreateParameter(); parName.ParameterName = "@name"; comInsert.Parameters.Add(parName); var parScale = comInsert.CreateParameter(); parScale.ParameterName = "@scale"; comInsert.Parameters.Add(parScale); var parDescription = comInsert.CreateParameter(); parDescription.ParameterName = "@description"; comInsert.Parameters.Add(parDescription); var parQuantityInStock = comInsert.CreateParameter(); parQuantityInStock.ParameterName = "@quantityInStock"; comInsert.Parameters.Add(parQuantityInStock); var parQuantityInOrder = comInsert.CreateParameter(); parQuantityInOrder.ParameterName = "@quantityInOrder"; comInsert.Parameters.Add(parQuantityInOrder); var parBuyPrice = comInsert.CreateParameter(); parBuyPrice.ParameterName = "@buyPrice"; comInsert.Parameters.Add(parBuyPrice); var parProductlineId = comInsert.CreateParameter(); parProductlineId.ParameterName = "@productlineId"; comInsert.Parameters.Add(parProductlineId); conBieren.Open(); foreach (Product eenProduct in newProducts) { try { parName.Value = eenProduct.Name; parScale.Value = eenProduct.Scale; parDescription.Value = eenProduct.Description; parQuantityInStock.Value = eenProduct.QuantityInStock; parQuantityInOrder.Value = eenProduct.QuantityInOrder; parBuyPrice.Value = eenProduct.BuyPrice; parProductlineId.Value = eenProduct.ProductlineId; if (comInsert.ExecuteNonQuery() == 0) { nietToegevoegdeProducts.Add(eenProduct); } } catch (Exception ex) { nietToegevoegdeProducts.Add(eenProduct); } } // foreach } // comInsert } // conBieren return(nietToegevoegdeProducts); }
public ObservableCollection <Product> GetProductsByProductLineName(String name) { ObservableCollection <Product> products = new ObservableCollection <Product>(); var dbManager = new ToysForBoysDbManager(); using (var conProducten = dbManager.GetConnection()) { using (var comProducten = conProducten.CreateCommand()) { comProducten.CommandType = CommandType.StoredProcedure; if (!string.IsNullOrEmpty(name)) { comProducten.CommandText = "[ToysForBoys].[dbo].[SP_GetAllProductsOfOneProductLineName]"; var parName = comProducten.CreateParameter(); parName.ParameterName = "@name"; parName.Value = name; comProducten.Parameters.Add(parName); } else { comProducten.CommandText = "[toysforboys].[dbo].[SP_GetAllProducts]"; } conProducten.Open(); using (var rdrProducts = comProducten.ExecuteReader()) { Int32 prodIdPos = rdrProducts.GetOrdinal("id"); Int32 prodNamePos = rdrProducts.GetOrdinal("name"); Int32 prodScalePos = rdrProducts.GetOrdinal("scale"); Int32 prodDescriptionPos = rdrProducts.GetOrdinal("description"); Int32 prodQuantityInStockPos = rdrProducts.GetOrdinal("quantityInStock"); Int32 prodQuantityInOrderPos = rdrProducts.GetOrdinal("quantityInOrder"); Int32 prodBuyPricePos = rdrProducts.GetOrdinal("buyPrice"); Int32 prodLineIdPos = rdrProducts.GetOrdinal("productlineId"); Int32 pId; String pName; String pScale; String pDescription; Int32? pQuantityInStock; Int32? pQuantityInOrder; Decimal?pBuyPrice; Int32? pProductlineId; while (rdrProducts.Read()) { if (rdrProducts.IsDBNull(prodIdPos)) { pId = 0; } else { pId = rdrProducts.GetInt32(prodIdPos); } if (rdrProducts.IsDBNull(prodNamePos)) { pName = null; } else { pName = rdrProducts.GetString(prodNamePos); } if (rdrProducts.IsDBNull(prodDescriptionPos)) { pDescription = null; } else { pDescription = rdrProducts.GetString(prodDescriptionPos); } if (rdrProducts.IsDBNull(prodScalePos)) { pScale = null; } else { pScale = rdrProducts.GetString(prodScalePos); } if (rdrProducts.IsDBNull(prodQuantityInStockPos)) { pQuantityInStock = null; } else { pQuantityInStock = rdrProducts.GetInt32(prodQuantityInStockPos); } if (rdrProducts.IsDBNull(prodQuantityInOrderPos)) { pQuantityInOrder = null; } else { pQuantityInOrder = rdrProducts.GetInt32(prodQuantityInOrderPos); } if (rdrProducts.IsDBNull(prodBuyPricePos)) { pBuyPrice = null; } else { pBuyPrice = rdrProducts.GetDecimal(prodBuyPricePos); } if (rdrProducts.IsDBNull(prodLineIdPos)) { pProductlineId = null; } else { pProductlineId = rdrProducts.GetInt32(prodLineIdPos); } products.Add(new Product(pId, pName, pScale, pDescription, pQuantityInStock, pQuantityInOrder, pBuyPrice, pProductlineId)); } // do while } // using rdrProducts } // using comProducts } // using conToysForBoys return(products); }
public static List <Productline> GetProductlines() { List <Productline> prodlines = new List <Productline>(); var dbManager = new ToysForBoysDbManager(); using (var conProducten = dbManager.GetConnection()) { using (var comProducten = conProducten.CreateCommand()) { comProducten.CommandType = CommandType.StoredProcedure; comProducten.CommandText = "[toysforboys].[dbo].[SP_ProductLines]"; conProducten.Open(); using (var rdrProducts = comProducten.ExecuteReader()) { Int32 prodIdPos = rdrProducts.GetOrdinal("id"); Int32 prodNamePos = rdrProducts.GetOrdinal("name"); Int32 prodDescriptionPos = rdrProducts.GetOrdinal("description"); Int32 pId; String pName; String pDescription; while (rdrProducts.Read()) { if (rdrProducts.IsDBNull(prodIdPos)) { pId = 0; } else { pId = rdrProducts.GetInt32(prodIdPos); } if (rdrProducts.IsDBNull(prodNamePos)) { pName = null; } else { pName = rdrProducts.GetString(prodNamePos); } if (rdrProducts.IsDBNull(prodDescriptionPos)) { pDescription = null; } else { pDescription = rdrProducts.GetString(prodDescriptionPos); } prodlines.Add(new Productline(pId, pName, pDescription)); } // do while } // using rdrProducts } // using comProducts } // using conToysForBoys return(prodlines); }
public List <Order> UpdateOrdersToToysForBoys(List <Order> modifiedOrders) { List <Order> failedUpdates = new List <Order>(); var manager = new ToysForBoysDbManager(); using (var conOrders = manager.GetConnection()) { using (var comUpdate = conOrders.CreateCommand()) { comUpdate.CommandType = CommandType.StoredProcedure; comUpdate.CommandText = "[toysforboys].[dbo].[SP_ToysForBoys_UpdateOrder]"; var parId = comUpdate.CreateParameter(); parId.ParameterName = "@id"; comUpdate.Parameters.Add(parId); var parOrderDate = comUpdate.CreateParameter(); parOrderDate.ParameterName = "@orderDate"; comUpdate.Parameters.Add(parOrderDate); var parRequiredDate = comUpdate.CreateParameter(); parRequiredDate.ParameterName = "@requiredDate"; comUpdate.Parameters.Add(parRequiredDate); var parShippedDate = comUpdate.CreateParameter(); parShippedDate.ParameterName = "@shippedDate"; comUpdate.Parameters.Add(parShippedDate); var parComments = comUpdate.CreateParameter(); parComments.ParameterName = "@comments"; comUpdate.Parameters.Add(parComments); var parCustomerId = comUpdate.CreateParameter(); parCustomerId.ParameterName = "@customerId"; comUpdate.Parameters.Add(parCustomerId); var parStatus = comUpdate.CreateParameter(); parStatus.ParameterName = "@status"; comUpdate.Parameters.Add(parStatus); conOrders.Open(); foreach (Order x in modifiedOrders) { try { parId.Value = x.Id; parOrderDate.Value = x.OrderDate; parRequiredDate.Value = x.RequiredDate; parShippedDate.Value = x.ShippedDate; parComments.Value = x.Comments; parCustomerId.Value = x.CustomerId; parStatus.Value = x.Status; if (comUpdate.ExecuteNonQuery() == 0) { failedUpdates.Add(x); } } catch (Exception ex) { failedUpdates.Add(x); } } } } return(failedUpdates); }
public ObservableCollection <Order> GetAllOrders() { ObservableCollection <Order> orderlines = new ObservableCollection <Order>(); var dbManager = new ToysForBoysDbManager(); using (var conOrders = dbManager.GetConnection()) { using (var comOrders = conOrders.CreateCommand()) { comOrders.CommandType = CommandType.StoredProcedure; comOrders.CommandText = "[toysforboys].[dbo].[SP_ToysForBoys_GetAllOrders]"; conOrders.Open(); using (var rdrProducts = comOrders.ExecuteReader()) { Int32 ordIdPos = rdrProducts.GetOrdinal("id"); Int32 ordOrderDatePos = rdrProducts.GetOrdinal("orderDate"); Int32 ordRequiredDatePos = rdrProducts.GetOrdinal("requiredDate"); Int32 ordShippedDatePos = rdrProducts.GetOrdinal("shippedDate"); Int32 ordCommentsPos = rdrProducts.GetOrdinal("comments"); Int32 ordCustomerIdPos = rdrProducts.GetOrdinal("customerId"); Int32 ordStatusPos = rdrProducts.GetOrdinal("status"); Int32 pId; DateTime pOrderDate; DateTime pRequiredDate; DateTime pShippedDate; string pComments; int pCustomerId; string pStatus; while (rdrProducts.Read()) { if (rdrProducts.IsDBNull(ordIdPos)) { pId = 0; } else { pId = rdrProducts.GetInt32(ordIdPos); } if (rdrProducts.IsDBNull(ordOrderDatePos)) { pOrderDate = DateTime.MinValue; } else { pOrderDate = rdrProducts.GetDateTime(ordOrderDatePos); } if (rdrProducts.IsDBNull(ordRequiredDatePos)) { pRequiredDate = DateTime.MinValue; } else { pRequiredDate = rdrProducts.GetDateTime(ordRequiredDatePos); } if (rdrProducts.IsDBNull(ordShippedDatePos)) { pShippedDate = DateTime.MinValue; } else { pShippedDate = rdrProducts.GetDateTime(ordShippedDatePos); } if (rdrProducts.IsDBNull(ordCommentsPos)) { pComments = null; } else { pComments = rdrProducts.GetString(ordCommentsPos); } if (rdrProducts.IsDBNull(ordCustomerIdPos)) { pCustomerId = 0; } else { pCustomerId = rdrProducts.GetInt32(ordCustomerIdPos); } if (rdrProducts.IsDBNull(ordStatusPos)) { pStatus = null; } else { pStatus = rdrProducts.GetString(ordStatusPos); } orderlines.Add(new Order(pId, pOrderDate, pRequiredDate, pShippedDate, pComments, pCustomerId, pStatus)); } // do while } // using rdrProducts } // using comProducts } // using conToysForBoys return(orderlines); }