public override void Add(Document doc, NorthwindConfig config, ref List<TransactionResult> result) { TransactionResult tmpTransactionResult; List<TransactionResult> transactionResult = new List<TransactionResult>(); OrderDocument orderDoc = doc as OrderDocument; #region check input values if (orderDoc == null) { result.Add(doc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_DocumentTypeNotSupported)); return; } string customerID; if (orderDoc.accountid.IsNull) { result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_AccountIDMadatory)); return; } customerID = (string)orderDoc.accountid.Value; if (!customerID.StartsWith(Constants.CustomerIdPrefix)) { result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_AddOrdersForCustomersOnly)); return; } #endregion DataSets.OrderTableAdapters.OrdersTableAdapter tableAdapter; DataSets.OrderTableAdapters.Order_DetailsTableAdapter detailsTableAdapter; DataSets.Order order = new DataSets.Order(); DataSets.Order.OrdersRow newOrder = order.Orders.NewOrdersRow(); customerID = customerID.Substring(Constants.CustomerIdPrefix.Length); newOrder.CustomerID = customerID; #region get Company Name DataSet dataSet = new DataSet(); OleDbDataAdapter dataAdapter; string sqlQuery = "Select CompanyName from Customers where CustomerID = '" + customerID + "'"; try { using (OleDbConnection connection = new OleDbConnection(config.ConnectionString)) { dataAdapter = new OleDbDataAdapter(sqlQuery, connection); if (dataAdapter.Fill(dataSet, "Customers") == 0) { result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_AccountNotFound)); return; } newOrder.ShipName = dataSet.Tables[0].Rows[0][0].ToString(); } } catch (Exception e) { orderDoc.Id = ""; throw; } #endregion #region get Sels rep if (orderDoc.salesrepr.IsNull) newOrder.SetEmployeeIDNull(); else { try { newOrder.EmployeeID = int.Parse((string)orderDoc.salesrepr.Value); } catch (Exception) { newOrder.SetEmployeeIDNull(); } if (newOrder.IsEmployeeIDNull()) { try { dataSet = new DataSet(); sqlQuery = "SELECT Employees.EmployeeID FROM Employees where Employees.FirstName + ' ' + Employees.LastName = ? "; using (OleDbConnection connection = new OleDbConnection(config.ConnectionString)) { dataAdapter = new OleDbDataAdapter(sqlQuery, connection); OleDbParameter parameter = new OleDbParameter("Name", (string)orderDoc.salesrepr.Value); dataAdapter.SelectCommand.Parameters.Add(parameter); if (dataAdapter.Fill(dataSet, "Employees") > 0) newOrder.EmployeeID = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]); else newOrder.EmployeeID = 1; } } catch (Exception e) { orderDoc.Id = ""; throw; } } } #endregion #region fill dataset from document try { if (orderDoc.opened.IsNull) newOrder.SetOrderDateNull(); else newOrder.OrderDate = (DateTime)orderDoc.opened.Value; if (orderDoc.deliverydate.IsNull) newOrder.SetRequiredDateNull(); else newOrder.RequiredDate = (DateTime)orderDoc.deliverydate.Value; if (orderDoc.shippedvia.IsNull) newOrder.SetShipViaNull(); else newOrder.ShipVia = (int)orderDoc.shippedvia.Value; if (orderDoc.shipaddress.IsNull) { newOrder.SetShipAddressNull(); newOrder.SetShipCityNull(); newOrder.SetShipCountryNull(); newOrder.SetShipPostalCodeNull(); } else { OrderAddress orderAddress = new OrderAddress(); orderAddress.CrmOrderAddress = (string)orderDoc.shipaddress.Value; newOrder.ShipAddress = (string)orderAddress.NorthwindAddress; newOrder.ShipCity = (string)orderAddress.NorthwindCity; newOrder.ShipPostalCode = (string)orderAddress.NorthwindZipCode; newOrder.ShipCountry = (string)orderAddress.NorthwindCountry; } if (orderDoc.freight.IsNull) newOrder.Freight = (decimal)0; else newOrder.Freight = (decimal)orderDoc.freight.Value; newOrder.CreateUser = config.CrmUser; newOrder.ModifyUser = config.CrmUser; newOrder.CreateID = config.SequenceNumber; newOrder.ModifyID = config.SequenceNumber; } catch (Exception e) { orderDoc.Id = ""; #warning Check error message result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, e.ToString())); return; } #endregion using (OleDbConnection connection = new OleDbConnection(config.ConnectionString)) { OleDbTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); tableAdapter = new DataSets.OrderTableAdapters.OrdersTableAdapter(); tableAdapter.Connection = connection; detailsTableAdapter = new DataSets.OrderTableAdapters.Order_DetailsTableAdapter(); detailsTableAdapter.Connection = connection; tableAdapter.SetTransaction(transaction); detailsTableAdapter.SetTransaction(transaction); order.Orders.AddOrdersRow(newOrder); tableAdapter.Update(order.Orders); OleDbCommand Cmd = new OleDbCommand("SELECT @@IDENTITY", connection); Cmd.Transaction = transaction; object lastid = Cmd.ExecuteScalar(); orderDoc.Id = ((int)lastid).ToString(); // add line Items DataSets.Order.Order_DetailsRow detailRow; Hashtable addedProductsProducts; addedProductsProducts = new Hashtable(); int productID; foreach (LineItemDocument lineItemDoc in orderDoc.orderitems) { try { try { productID = (int)lineItemDoc.productid.Value; } catch (Exception) { #warning only to test unsupported products productID = 0; } if (addedProductsProducts.Contains(productID)) { transaction.Rollback(); orderDoc.Id = ""; result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_OrderContainsProductTwice)); return; } addedProductsProducts.Add(productID, productID); detailRow = order.Order_Details.NewOrder_DetailsRow(); lineItemDoc.Id = orderDoc.Id + "-" + productID.ToString(); detailRow.OrderID = Convert.ToInt32(orderDoc.Id); detailRow.ProductID = productID; detailRow.Quantity = Convert.ToInt16(lineItemDoc.quantity.Value); detailRow.UnitPrice = (Decimal)lineItemDoc.listprice.Value; if ((lineItemDoc.discountsum.IsNull) || (detailRow.Quantity == 0) || (detailRow.UnitPrice == 0)) { detailRow.Discount = (float)0; } else { // discountPC = discountsum / qunatity * listprice //detailRow.Discount = Convert.ToSingle((decimal)lineItemDoc.discountsum.Value / ((decimal)detailRow.Quantity * detailRow.UnitPrice)); float discount = Convert.ToSingle((decimal)lineItemDoc.discountsum.Value / (detailRow.UnitPrice)); if (discount > 1) discount = 0; detailRow.Discount = discount; } detailRow.CreateUser = config.CrmUser; detailRow.ModifyUser = config.CrmUser; detailRow.CreateID = config.SequenceNumber; detailRow.ModifyID = config.SequenceNumber; } // this error occours in case of invalid data types catch (Exception e) { transaction.Rollback(); orderDoc.Id = ""; result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, e.Message)); return; } order.Order_Details.AddOrder_DetailsRow(detailRow); lineItemDoc.SetTransactionStatus(TransactionStatus.Success); } // here could an error ouucour in case on broken database connection // or of same invalid constraints which are unhandled before try { detailsTableAdapter.Update(order.Order_Details); } catch (Exception e) { transaction.Rollback(); orderDoc.Id = ""; result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, e.Message)); return; } transaction.Commit(); orderDoc.GetTransactionResult(ref result); } catch (Exception transactionException) { if (transaction != null) transaction.Rollback(); orderDoc.Id = ""; throw; } } }
public override void Add(Document doc, NorthwindConfig config, ref List <TransactionResult> result) { TransactionResult tmpTransactionResult; List <TransactionResult> transactionResult = new List <TransactionResult>(); OrderDocument orderDoc = doc as OrderDocument; #region check input values if (orderDoc == null) { result.Add(doc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_DocumentTypeNotSupported)); return; } string customerID; if (orderDoc.accountid.IsNull) { result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_AccountIDMadatory)); return; } customerID = (string)orderDoc.accountid.Value; if (!customerID.StartsWith(Constants.CustomerIdPrefix)) { result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_AddOrdersForCustomersOnly)); return; } #endregion DataSets.OrderTableAdapters.OrdersTableAdapter tableAdapter; DataSets.OrderTableAdapters.Order_DetailsTableAdapter detailsTableAdapter; DataSets.Order order = new DataSets.Order(); DataSets.Order.OrdersRow newOrder = order.Orders.NewOrdersRow(); customerID = customerID.Substring(Constants.CustomerIdPrefix.Length); newOrder.CustomerID = customerID; #region get Company Name DataSet dataSet = new DataSet(); OleDbDataAdapter dataAdapter; string sqlQuery = "Select CompanyName from Customers where CustomerID = '" + customerID + "'"; try { using (OleDbConnection connection = new OleDbConnection(config.ConnectionString)) { dataAdapter = new OleDbDataAdapter(sqlQuery, connection); if (dataAdapter.Fill(dataSet, "Customers") == 0) { result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_AccountNotFound)); return; } newOrder.ShipName = dataSet.Tables[0].Rows[0][0].ToString(); } } catch (Exception e) { orderDoc.Id = ""; throw; } #endregion #region get Sels rep if (orderDoc.salesrepr.IsNull) { newOrder.SetEmployeeIDNull(); } else { try { newOrder.EmployeeID = int.Parse((string)orderDoc.salesrepr.Value); } catch (Exception) { newOrder.SetEmployeeIDNull(); } if (newOrder.IsEmployeeIDNull()) { try { dataSet = new DataSet(); sqlQuery = "SELECT Employees.EmployeeID FROM Employees where Employees.FirstName + ' ' + Employees.LastName = ? "; using (OleDbConnection connection = new OleDbConnection(config.ConnectionString)) { dataAdapter = new OleDbDataAdapter(sqlQuery, connection); OleDbParameter parameter = new OleDbParameter("Name", (string)orderDoc.salesrepr.Value); dataAdapter.SelectCommand.Parameters.Add(parameter); if (dataAdapter.Fill(dataSet, "Employees") > 0) { newOrder.EmployeeID = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]); } else { newOrder.EmployeeID = 1; } } } catch (Exception e) { orderDoc.Id = ""; throw; } } } #endregion #region fill dataset from document try { if (orderDoc.opened.IsNull) { newOrder.SetOrderDateNull(); } else { newOrder.OrderDate = (DateTime)orderDoc.opened.Value; } if (orderDoc.deliverydate.IsNull) { newOrder.SetRequiredDateNull(); } else { newOrder.RequiredDate = (DateTime)orderDoc.deliverydate.Value; } if (orderDoc.shippedvia.IsNull) { newOrder.SetShipViaNull(); } else { newOrder.ShipVia = (int)orderDoc.shippedvia.Value; } if (orderDoc.shipaddress.IsNull) { newOrder.SetShipAddressNull(); newOrder.SetShipCityNull(); newOrder.SetShipCountryNull(); newOrder.SetShipPostalCodeNull(); } else { OrderAddress orderAddress = new OrderAddress(); orderAddress.CrmOrderAddress = (string)orderDoc.shipaddress.Value; newOrder.ShipAddress = (string)orderAddress.NorthwindAddress; newOrder.ShipCity = (string)orderAddress.NorthwindCity; newOrder.ShipPostalCode = (string)orderAddress.NorthwindZipCode; newOrder.ShipCountry = (string)orderAddress.NorthwindCountry; } if (orderDoc.freight.IsNull) { newOrder.Freight = (decimal)0; } else { newOrder.Freight = (decimal)orderDoc.freight.Value; } newOrder.CreateUser = config.CrmUser; newOrder.ModifyUser = config.CrmUser; newOrder.CreateID = config.SequenceNumber; newOrder.ModifyID = config.SequenceNumber; } catch (Exception e) { orderDoc.Id = ""; #warning Check error message result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, e.ToString())); return; } #endregion using (OleDbConnection connection = new OleDbConnection(config.ConnectionString)) { OleDbTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); tableAdapter = new DataSets.OrderTableAdapters.OrdersTableAdapter(); tableAdapter.Connection = connection; detailsTableAdapter = new DataSets.OrderTableAdapters.Order_DetailsTableAdapter(); detailsTableAdapter.Connection = connection; tableAdapter.SetTransaction(transaction); detailsTableAdapter.SetTransaction(transaction); order.Orders.AddOrdersRow(newOrder); tableAdapter.Update(order.Orders); OleDbCommand Cmd = new OleDbCommand("SELECT @@IDENTITY", connection); Cmd.Transaction = transaction; object lastid = Cmd.ExecuteScalar(); orderDoc.Id = ((int)lastid).ToString(); // add line Items DataSets.Order.Order_DetailsRow detailRow; Hashtable addedProductsProducts; addedProductsProducts = new Hashtable(); int productID; foreach (LineItemDocument lineItemDoc in orderDoc.orderitems) { try { try { productID = (int)lineItemDoc.productid.Value; } catch (Exception) { #warning only to test unsupported products productID = 0; } if (addedProductsProducts.Contains(productID)) { transaction.Rollback(); orderDoc.Id = ""; result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, Resources.ErrorMessages_OrderContainsProductTwice)); return; } addedProductsProducts.Add(productID, productID); detailRow = order.Order_Details.NewOrder_DetailsRow(); lineItemDoc.Id = orderDoc.Id + "-" + productID.ToString(); detailRow.OrderID = Convert.ToInt32(orderDoc.Id); detailRow.ProductID = productID; detailRow.Quantity = Convert.ToInt16(lineItemDoc.quantity.Value); detailRow.UnitPrice = (Decimal)lineItemDoc.listprice.Value; if ((lineItemDoc.discountsum.IsNull) || (detailRow.Quantity == 0) || (detailRow.UnitPrice == 0)) { detailRow.Discount = (float)0; } else { // discountPC = discountsum / qunatity * listprice //detailRow.Discount = Convert.ToSingle((decimal)lineItemDoc.discountsum.Value / ((decimal)detailRow.Quantity * detailRow.UnitPrice)); float discount = Convert.ToSingle((decimal)lineItemDoc.discountsum.Value / (detailRow.UnitPrice)); if (discount > 1) { discount = 0; } detailRow.Discount = discount; } detailRow.CreateUser = config.CrmUser; detailRow.ModifyUser = config.CrmUser; detailRow.CreateID = config.SequenceNumber; detailRow.ModifyID = config.SequenceNumber; } // this error occours in case of invalid data types catch (Exception e) { transaction.Rollback(); orderDoc.Id = ""; result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, e.Message)); return; } order.Order_Details.AddOrder_DetailsRow(detailRow); lineItemDoc.SetTransactionStatus(TransactionStatus.Success); } // here could an error ouucour in case on broken database connection // or of same invalid constraints which are unhandled before try { detailsTableAdapter.Update(order.Order_Details); } catch (Exception e) { transaction.Rollback(); orderDoc.Id = ""; result.Add(orderDoc.SetTransactionStatus(TransactionStatus.UnRecoverableError, e.Message)); return; } transaction.Commit(); orderDoc.GetTransactionResult(ref result); } catch (Exception transactionException) { if (transaction != null) { transaction.Rollback(); } orderDoc.Id = ""; throw; } } }