/// <summary> /// Execute the SQL statement does not return results, such as: delete, update, insert operation /// </summary> /// <param name="strSQL">SQL String of a non Query Type</param> /// <returns>success or failure</returns> public bool UpdateOrderHeader(OrderHeaderData pOrderHeader, List <string> pOrders) { bool _resultState = false; string _strSQL = CONST_ORDERUPDATEHEADER_SQL + " WHERE "; // for all the OrderIds passed create a where clause for (int i = 0; i < pOrders.Count - 1; i++) { _strSQL += " OrderID = " + pOrders[i] + " OR"; } _strSQL += " OrderID = " + pOrders[pOrders.Count - 1]; TrackerDb _TDB = new TrackerDb(); _TDB.AddParams(pOrderHeader.CustomerID, DbType.Int64, "@CustomerID"); _TDB.AddParams(pOrderHeader.OrderDate, DbType.Date, "@OrderDate"); _TDB.AddParams(pOrderHeader.RoastDate, DbType.Date, "@RoastDate"); _TDB.AddParams(pOrderHeader.ToBeDeliveredBy, DbType.Int64, "@ToBeDeliveredBy"); _TDB.AddParams(pOrderHeader.RequiredByDate, DbType.Date, "@RequiredByDate"); _TDB.AddParams(pOrderHeader.Confirmed, DbType.Boolean, "@Confirmed"); _TDB.AddParams(pOrderHeader.Done, DbType.Boolean, "@Done"); _TDB.AddParams(pOrderHeader.InvoiceDone, DbType.Boolean, "@InvoiceDone"); _TDB.AddParams(pOrderHeader.PurchaseOrder, DbType.String, "@PurchaseOrder"); _TDB.AddParams(pOrderHeader.Notes, DbType.String, "@Notes"); _resultState = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_strSQL)); _TDB.Close(); return(_resultState); }
public string Update(PriceLevelsTbl pPriceLevelsTbl, int pOrignal_PriceLevelID) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region UpdateParameters if (pOrignal_PriceLevelID > 0) { _TDB.AddWhereParams(pOrignal_PriceLevelID, DbType.Int32); // check this line it assumes id field is int32 } else { _TDB.AddWhereParams(pPriceLevelsTbl.PriceLevelID, DbType.Boolean, "@PriceLevelID"); } _TDB.AddParams(pPriceLevelsTbl.PriceLevelDesc, DbType.String, "@PriceLevelDesc"); _TDB.AddParams(Math.Round(pPriceLevelsTbl.PricingFactor, 3), DbType.Double, "@PricingFactor"); _TDB.AddParams(pPriceLevelsTbl.Enabled, DbType.Int32, "@Enabled"); _TDB.AddParams(pPriceLevelsTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TDB.Close(); return(_result); }
public string InsertTransactionType(TransactionTypesTbl pTransactionType) { TrackerDb _TDB = new TrackerDb(); _TDB.AddParams(pTransactionType.TransactionID, DbType.Int32); _TDB.AddParams(pTransactionType.TransactionType); _TDB.AddParams(pTransactionType.Notes); string _Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_Result); }
public string Insert(PriceLevelsTbl pPriceLevelsTbl) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region InsertParameters _TDB.AddParams(pPriceLevelsTbl.PriceLevelDesc, DbType.String, "@PriceLevelDesc"); _TDB.AddParams(Math.Round(pPriceLevelsTbl.PricingFactor, 3), DbType.Single, "@PricingFactor"); _TDB.AddParams(pPriceLevelsTbl.Enabled, DbType.Int32, "@Enabled"); _TDB.AddParams(pPriceLevelsTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_result); }
public string InsertCityPrepDay(CityPrepDaysTbl objCityPrepDaysTbl) { string _Result = String.Empty; TrackerDb _TDB = new TrackerDb(); /// CityID, PrepDayOfWeekID, DeliveryDelayDays, DeliveryOrder _TDB.AddParams(objCityPrepDaysTbl.CityID, System.Data.DbType.Int32); _TDB.AddParams(objCityPrepDaysTbl.PrepDayOfWeekID, System.Data.DbType.Byte); _TDB.AddParams(objCityPrepDaysTbl.DeliveryDelayDays, System.Data.DbType.Int32); _TDB.AddParams(objCityPrepDaysTbl.DeliveryOrder, System.Data.DbType.Int32); _Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_Result); }
/* * string _connectionStr = ConfigurationManager.ConnectionStrings[CONST_CONSTRING].ConnectionString; * * using (OleDbConnection _conn = new OleDbConnection(_connectionStr)) * { * string _sqlCmd = CONST_SQL_SELECT; * OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the query we have built * _conn.Open(); * OleDbDataReader _DataReader = _cmd.ExecuteReader(); * if (_DataReader.Read()) * { * _DataItem.SCEMTID = (_DataReader["SCEMTID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["SCEMTID"]); * _DataItem.Header = (_DataReader["Header"] == DBNull.Value) ? string.Empty : _DataReader["Header"].ToString(); * _DataItem.Body = (_DataReader["Body"] == DBNull.Value) ? string.Empty : _DataReader["Body"].ToString(); * _DataItem.Footer = (_DataReader["Footer"] == DBNull.Value) ? string.Empty : _DataReader["Footer"].ToString(); * _DataItem.DateLastChange = (_DataReader["DateLastChange"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["DateLastChange"]); * _DataItem.Notes = (_DataReader["Notes"] == DBNull.Value) ? string.Empty : _DataReader["Notes"].ToString(); * } * } * return _DataItem; * } */ public string UpdateTexts(SendCheckEmailTextsData pEmailTextsData, int pOriginalID) { string _errString = ""; TrackerDb _TDB = new TrackerDb(); #region Parameters // Add data sent _TDB.AddParams(pEmailTextsData.Header, DbType.String, "@Header"); _TDB.AddParams(pEmailTextsData.Body, DbType.String, "@Body"); _TDB.AddParams(pEmailTextsData.Footer, DbType.String, "@Footer"); _TDB.AddParams(DateTime.Now.Date, DbType.Date, "@DateLastChange"); _TDB.AddParams(pEmailTextsData.Notes, DbType.String, "@Notes"); // " WHERE SCEMTID = ?)"; _TDB.AddWhereParams(pOriginalID, DbType.Int32, "@SCEMTID"); #endregion _errString = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TDB.Close(); return(_errString); }
public string DeleteRepair(int RepairID) { string _result = String.Empty; TrackerDb _TrackerDb = new TrackerDb(); _TrackerDb.AddParams(RepairID, DbType.Int32); _result = _TrackerDb.ExecuteNonQuerySQL(CONST_SQL_DELETE); return(_result); }
/* * OleDbConnection _conn = new OleDbConnection(_connectionString); * // custoemr ZZname = 9 is a general customer name so condition must be to that * string _sqlCmd = "SELECT [ItemTypeID], [QuantityOrdered], [PackagingID], [OrderID] FROM [OrdersTbl] WHERE "; * if (CustomerId == 9) * _sqlCmd += "([CustomerId] = 9) AND ([RequiredByDate] = ?) AND ([Notes] = ?)"; * else * _sqlCmd += "([CustomerId] = ?) AND ([RequiredByDate] = ?)"; * // attach the command * OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); * if (CustomerId == 9) * { * _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate }); * _cmd.Parameters.Add(new OleDbParameter { Value = Notes }); * } * else * { * _cmd.Parameters.Add(new OleDbParameter { Value = CustomerId }); * _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate }); * } * try * { * _conn.Open(); * OleDbDataReader _DataReader = _cmd.ExecuteReader(); * while (_DataReader.Read()) * { * OrderDetailData od = new OrderDetailData(); * * od.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : (Int32)_DataReader["ItemTypeID"]; * od.PackagingID = (_DataReader["PackagingID"] == DBNull.Value) ? 0 : (Int32)_DataReader["PackagingID"]; * od.OrderID = (Int32)_DataReader["OrderId"]; // this is the PK cannot be null * od.QuantityOrdered = (_DataReader["QuantityOrdered"] == DBNull.Value) ? 1 : Math.Round(Convert.ToDouble(_DataReader["QuantityOrdered"]),2); * * oDetails.Add(od); * } * _DataReader.Close(); * } * catch (Exception _ex) * { * lastError = _ex.Message; * } * finally * { * _conn.Close(); * } * return oDetails; * } */ /// <summary> /// Update Order Details, using the orderID update the line info. /// </summary> /// <param name="ItemTypeID"></param> /// <param name="QuantityOrdered"></param> /// <param name="PackagingID"></param> /// <param name="OrderID"></param> /// <returns></returns> public bool UpdateOrderDetails(Int64 OrderID, Int64 CustomerID, Int32 ItemTypeID, DateTime DeliveryDate, double QuantityOrdered, Int32 PackagingID) { bool _Success = false; string _sqlCmd = "UPDATE OrdersTbl SET ItemTypeID = ?, QuantityOrdered = ?, PackagingID = ? WHERE (OrderId = ?)"; TrackerDb _TDB = new TrackerDb(); // check if the item is a group item then get the next group. TrackerTools _TT = new TrackerTools(); ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, DeliveryDate); _TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID"); _TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered"); _TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID"); _TDB.AddWhereParams(OrderID, DbType.Int64, "@OrderID"); _Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd)); _TDB.Close(); return(_Success); }
public string Update(SysDataTbl SysDataItem, int orig_ID) { string _Result = String.Empty; if (orig_ID > 0) { TrackerDb _TDB = new TrackerDb(); // LastReoccurringDate = ?, DoReoccuringOrders = ?, DateLastPrepDateCalcd = ?, MinReminderDate = ?, GroupItemTypeID = ?" + _TDB.AddParams(SysDataItem.LastReoccurringDate, DbType.Date); _TDB.AddParams(SysDataItem.DoReoccuringOrders, DbType.Boolean); _TDB.AddParams(SysDataItem.DateLastPrepDateCalcd, DbType.Date); _TDB.AddParams(SysDataItem.MinReminderDate, DbType.Date); _TDB.AddParams(SysDataItem.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); // "WHERE (SysDataTbl.ID = ?) _TDB.AddWhereParams(orig_ID, DbType.Int32); _Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATEBYID); _TDB.Close(); } return(_Result); }
public string UpdateCityPrepDay(CityPrepDaysTbl objCityPrepDaysTbl, int origCityPrepDaysID) { string _Result = String.Empty; if (origCityPrepDaysID > 0) { TrackerDb _TDB = new TrackerDb(); /// CityID, PrepDayOfWeekID, DeliveryDelayDays, DeliveryOrder _TDB.AddParams(objCityPrepDaysTbl.CityID, System.Data.DbType.Int32); _TDB.AddParams(objCityPrepDaysTbl.PrepDayOfWeekID, System.Data.DbType.Byte); _TDB.AddParams(objCityPrepDaysTbl.DeliveryDelayDays, System.Data.DbType.Int32); _TDB.AddParams(objCityPrepDaysTbl.DeliveryOrder, System.Data.DbType.Int32); /// Where CityPrepDayID _TDB.AddWhereParams(origCityPrepDaysID, DbType.Int32); _Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATEBYID); _TDB.Close(); } return(_Result); }
public string Update(PaymentTermsTbl pPaymentTermsTbl, int pOrignal_PaymentTermID) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region UpdateParameters if (pOrignal_PaymentTermID > 0) { _TDB.AddWhereParams(pOrignal_PaymentTermID, DbType.Int32); // check this line it assumes id field is int32 } else { _TDB.AddWhereParams(pPaymentTermsTbl.PaymentTermID, DbType.Boolean, "@PaymentTermID"); } _TDB.AddParams(pPaymentTermsTbl.PaymentTermDesc, DbType.String, "@PaymentTermDesc"); _TDB.AddParams(pPaymentTermsTbl.PaymentDays, DbType.Int32, "@PaymentDays"); _TDB.AddParams(pPaymentTermsTbl.DayOfMonth, DbType.Int32, "@DayOfMonth"); _TDB.AddParams(pPaymentTermsTbl.UseDays, DbType.Boolean, "@UseDays"); _TDB.AddParams(pPaymentTermsTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pPaymentTermsTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TDB.Close(); return(_result); }
public string Update(UsedItemGroupTbl pUsedItemGroupTbl, int pOrignal_UsedItemGroupID) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region UpdateParameters if (pOrignal_UsedItemGroupID > 0) { _TDB.AddWhereParams(pOrignal_UsedItemGroupID, DbType.Int32); // check this line it assumes id field is int32 } else { _TDB.AddWhereParams(pUsedItemGroupTbl.UsedItemGroupID, DbType.Int32, "@UsedItemGroupID"); } _TDB.AddParams(pUsedItemGroupTbl.ContactID, DbType.Int64, "@ContactID"); _TDB.AddParams(pUsedItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeID, DbType.Int32, "@LastItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeSortPos, DbType.Int32, "@LastItemTypeSortPos"); _TDB.AddParams(pUsedItemGroupTbl.LastItemDateChanged, DbType.Date, "@LastItemDateChanged"); _TDB.AddParams(pUsedItemGroupTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); // UPDATE UsedItemGroupTbl SET ContactID = ?, GroupItemTypeID = ?, LastItemTypeID = ?, LastItemTypeSortPos = ?, LastItemDateChanged = ?, Notes = ? WHERE (UsedItemGroupID = ?)"; _TDB.Close(); return(_result); }
public static void UpdateTransaction(TransactionTypesTbl pTransactionType, int pOrignal_TransactionID) { TrackerDb _TDB = new TrackerDb(); _TDB.AddParams(pTransactionType.TransactionType); _TDB.AddParams(pTransactionType.Notes); if (pOrignal_TransactionID > 0) { _TDB.AddWhereParams(pOrignal_TransactionID, DbType.Int32); } else { _TDB.AddWhereParams(pTransactionType.TransactionID, DbType.Int32); } string _Result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TDB.Close(); // return _Result; }
public string Update(InvoiceTypeTbl pInvoiceTypeTbl, int pOrignal_InvoiceTypeID) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region UpdateParameters if (pOrignal_InvoiceTypeID > 0) { _TDB.AddWhereParams(pOrignal_InvoiceTypeID, DbType.Int32); // check this line it assumes id field is int32 } else { _TDB.AddWhereParams(pInvoiceTypeTbl.InvoiceTypeID, DbType.Boolean, "@InvoiceTypeID"); } _TDB.AddParams(pInvoiceTypeTbl.InvoiceTypeDesc, DbType.String, "@InvoiceTypeDesc"); _TDB.AddParams(pInvoiceTypeTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pInvoiceTypeTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TDB.Close(); return(_result); }
public string Insert(InvoiceTypeTbl pInvoiceTypeTbl) { string _result = string.Empty; if (pInvoiceTypeTbl.InvoiceTypeID > 0) { // they have called insert by mistake ( or the environment has) _result = Update(pInvoiceTypeTbl, 0); } else { TrackerDb _TDB = new TrackerDb(); #region InsertParameters _TDB.AddParams(pInvoiceTypeTbl.InvoiceTypeDesc, DbType.String, "@InvoiceTypeDesc"); _TDB.AddParams(pInvoiceTypeTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pInvoiceTypeTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); } return(_result); }
public string InsertLogItem(SentRemindersLogTbl pSentRemindersLog) { string _ErrString = String.Empty; TrackerDb _TDB = new TrackerDb(); _TDB.AddParams(pSentRemindersLog.CustomerID, DbType.Int64); _TDB.AddParams(pSentRemindersLog.DateSentReminder, DbType.Date); _TDB.AddParams(pSentRemindersLog.NextPrepDate, DbType.Date); _TDB.AddParams(pSentRemindersLog.ReminderSent, DbType.Boolean); _TDB.AddParams(pSentRemindersLog.HadAutoFulfilItem, DbType.Boolean); _TDB.AddParams(pSentRemindersLog.HadReoccurItems, DbType.Boolean); _ErrString = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_ErrString); }
public string Insert(PaymentTermsTbl pPaymentTermsTbl) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region InsertParameters _TDB.AddParams(pPaymentTermsTbl.PaymentTermDesc, DbType.String, "@PaymentTermDesc"); _TDB.AddParams(pPaymentTermsTbl.PaymentDays, DbType.Int32, "@PaymentDays"); _TDB.AddParams(pPaymentTermsTbl.DayOfMonth, DbType.Int32, "@DayOfMonth"); _TDB.AddParams(pPaymentTermsTbl.UseDays, DbType.Boolean, "@UseDays"); _TDB.AddParams(pPaymentTermsTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pPaymentTermsTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_result); }
public string Insert(UsedItemGroupTbl pUsedItemGroupTbl) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); #region InsertParameters _TDB.AddParams(pUsedItemGroupTbl.ContactID, DbType.Int64, "@ContactID"); _TDB.AddParams(pUsedItemGroupTbl.GroupItemTypeID, DbType.Int32, "@GroupItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeID, DbType.Int32, "@LastItemTypeID"); _TDB.AddParams(pUsedItemGroupTbl.LastItemTypeSortPos, DbType.Int32, "@LastItemTypeSortPos"); _TDB.AddParams(pUsedItemGroupTbl.LastItemDateChanged, DbType.Date, "@LastItemDateChanged"); _TDB.AddParams(pUsedItemGroupTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); return(_result); }
public string UpdateRepair(RepairsTbl RepairItem, int orig_RepairID) { string _result = String.Empty; TrackerDb _TrackerDb = new TrackerDb(); _TrackerDb.AddParams(RepairItem.CustomerID, DbType.Int64); _TrackerDb.AddParams(RepairItem.ContactName, DbType.String); _TrackerDb.AddParams(RepairItem.ContactEmail, DbType.String); _TrackerDb.AddParams(RepairItem.JobCardNumber, DbType.String); _TrackerDb.AddParams(RepairItem.DateLogged, DbType.Date); _TrackerDb.AddParams(RepairItem.LastStatusChange, DbType.Date); _TrackerDb.AddParams(RepairItem.MachineTypeID, DbType.Int32); _TrackerDb.AddParams(RepairItem.MachineSerialNumber, DbType.String); _TrackerDb.AddParams(RepairItem.SwopOutMachineID, DbType.Int32); _TrackerDb.AddParams(RepairItem.MachineConditionID, DbType.Int32); _TrackerDb.AddParams(RepairItem.TakenFrother, DbType.Boolean); _TrackerDb.AddParams(RepairItem.TakenBeanLid, DbType.Boolean); _TrackerDb.AddParams(RepairItem.TakenWaterLid, DbType.Boolean); _TrackerDb.AddParams(RepairItem.BrokenFrother, DbType.Boolean); _TrackerDb.AddParams(RepairItem.BrokenBeanLid, DbType.Boolean); _TrackerDb.AddParams(RepairItem.BrokenWaterLid, DbType.Boolean); _TrackerDb.AddParams(RepairItem.RepairFaultID, DbType.Int32); _TrackerDb.AddParams(RepairItem.RepairFaultDesc, DbType.String); _TrackerDb.AddParams(RepairItem.RepairStatusID, DbType.Int32); _TrackerDb.AddParams(RepairItem.RelatedOrderID, DbType.Int64); _TrackerDb.AddParams(RepairItem.Notes, DbType.String); _TrackerDb.AddWhereParams(orig_RepairID, DbType.Int32); _result = _TrackerDb.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TrackerDb.Close(); return(_result); }
public string Insert(CustomersAccInfoTbl pCustomersAccInfoTbl) { string _result = string.Empty; if (pCustomersAccInfoTbl.CustomerID == 0) { _result = "error: customer number cannot be 0"; } else { TrackerDb _TDB = new TrackerDb(); #region InsertParameters _TDB.AddParams(pCustomersAccInfoTbl.CustomerID, DbType.Int64, "@CustomerID"); _TDB.AddParams(pCustomersAccInfoTbl.RequiresPurchOrder, DbType.Int32, "@RequiresPurchOrder"); _TDB.AddParams(pCustomersAccInfoTbl.CustomerVATNo, DbType.String, "@CustomerVATNo"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr1, DbType.String, "@BillAddr1"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr2, DbType.String, "@BillAddr2"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr3, DbType.String, "@BillAddr3"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr4, DbType.String, "@BillAddr4"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr5, DbType.String, "@BillAddr5"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr1, DbType.String, "@ShipAddr1"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr2, DbType.String, "@ShipAddr2"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr3, DbType.String, "@ShipAddr3"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr4, DbType.String, "@ShipAddr4"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr5, DbType.String, "@ShipAddr5"); _TDB.AddParams(pCustomersAccInfoTbl.AccEmail, DbType.String, "@AccEmail"); _TDB.AddParams(pCustomersAccInfoTbl.AltAccEmail, DbType.String, "@AltAccEmail"); _TDB.AddParams(pCustomersAccInfoTbl.PaymentTermID, DbType.Int32, "@PaymentTermID"); _TDB.AddParams(pCustomersAccInfoTbl.Limit, DbType.Currency, "@Limit"); _TDB.AddParams(pCustomersAccInfoTbl.FullCoName, DbType.String, "@FullCoName"); _TDB.AddParams(pCustomersAccInfoTbl.AccFirstName, DbType.String, "@AccFirstName"); _TDB.AddParams(pCustomersAccInfoTbl.AccLastName, DbType.String, "@AccLastName"); _TDB.AddParams(pCustomersAccInfoTbl.AltAccFirstName, DbType.String, "@AltAccFirstName"); _TDB.AddParams(pCustomersAccInfoTbl.AltAccLastName, DbType.String, "@AltAccLastName"); _TDB.AddParams(pCustomersAccInfoTbl.PriceLevelID, DbType.Int32, "@PriceLevelID"); _TDB.AddParams(pCustomersAccInfoTbl.InvoiceTypeID, DbType.Int32, "@InvoiceTypeID"); _TDB.AddParams(pCustomersAccInfoTbl.RegNo, DbType.String, "@RegNo"); _TDB.AddParams(pCustomersAccInfoTbl.BankAccNo, DbType.String, "@BankAccNo"); _TDB.AddParams(pCustomersAccInfoTbl.BankBranch, DbType.String, "@BankBranch"); _TDB.AddParams(pCustomersAccInfoTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pCustomersAccInfoTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_INSERT); _TDB.Close(); } return(_result); }
public string Update(CustomersAccInfoTbl pCustomersAccInfoTbl, long pOrignal_CustomersAccInfoID) { string _result = string.Empty; TrackerDb _TDB = new TrackerDb(); if (pCustomersAccInfoTbl.CustomerID == 0) { _result = "error: CustomerID cannot be 0"; } else { #region UpdateParameters if (pOrignal_CustomersAccInfoID > 0) { _TDB.AddWhereParams(pOrignal_CustomersAccInfoID, DbType.Int64); // check this line it assumes id field is int32 } else { _TDB.AddWhereParams(pCustomersAccInfoTbl.CustomersAccInfoID, DbType.Int64, "@CustomersAccInfoID"); } _TDB.AddParams(pCustomersAccInfoTbl.CustomerID, DbType.Int64, "@CustomerID"); _TDB.AddParams(pCustomersAccInfoTbl.RequiresPurchOrder, DbType.Int32, "@RequiresPurchOrder"); _TDB.AddParams(pCustomersAccInfoTbl.CustomerVATNo, DbType.String, "@CustomerVATNo"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr1, DbType.String, "@BillAddr1"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr2, DbType.String, "@BillAddr2"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr3, DbType.String, "@BillAddr3"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr4, DbType.String, "@BillAddr4"); _TDB.AddParams(pCustomersAccInfoTbl.BillAddr5, DbType.String, "@BillAddr5"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr1, DbType.String, "@ShipAddr1"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr2, DbType.String, "@ShipAddr2"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr3, DbType.String, "@ShipAddr3"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr4, DbType.String, "@ShipAddr4"); _TDB.AddParams(pCustomersAccInfoTbl.ShipAddr5, DbType.String, "@ShipAddr5"); _TDB.AddParams(pCustomersAccInfoTbl.AccEmail, DbType.String, "@AccEmail"); _TDB.AddParams(pCustomersAccInfoTbl.AltAccEmail, DbType.String, "@AltAccEmail"); _TDB.AddParams(pCustomersAccInfoTbl.PaymentTermID, DbType.Int32, "@PaymentTermID"); _TDB.AddParams(pCustomersAccInfoTbl.Limit, DbType.Double, "@Limit"); _TDB.AddParams(pCustomersAccInfoTbl.FullCoName, DbType.String, "@FullCoName"); _TDB.AddParams(pCustomersAccInfoTbl.AccFirstName, DbType.String, "@AccFirstName"); _TDB.AddParams(pCustomersAccInfoTbl.AccLastName, DbType.String, "@AccLastName"); _TDB.AddParams(pCustomersAccInfoTbl.AltAccFirstName, DbType.String, "@AltAccFirstName"); _TDB.AddParams(pCustomersAccInfoTbl.AltAccLastName, DbType.String, "@AltAccLastName"); _TDB.AddParams(pCustomersAccInfoTbl.PriceLevelID, DbType.Int32, "@PriceLevelID"); _TDB.AddParams(pCustomersAccInfoTbl.InvoiceTypeID, DbType.Int32, "@InvoiceTypeID"); _TDB.AddParams(pCustomersAccInfoTbl.RegNo, DbType.String, "@RegNo"); _TDB.AddParams(pCustomersAccInfoTbl.BankAccNo, DbType.String, "@BankAccNo"); _TDB.AddParams(pCustomersAccInfoTbl.BankBranch, DbType.String, "@BankBranch"); _TDB.AddParams(pCustomersAccInfoTbl.Enabled, DbType.Boolean, "@Enabled"); _TDB.AddParams(pCustomersAccInfoTbl.Notes, DbType.String, "@Notes"); #endregion // Now we have the parameters excute the SQL _result = _TDB.ExecuteNonQuerySQL(CONST_SQL_UPDATE); _TDB.Close(); } return(_result); }
public bool InsertRepair(RepairsTbl DataItem) { bool _result = false; TrackerDb _TrackerDb = new TrackerDb(); _TrackerDb.AddParams(DataItem.CustomerID, DbType.Int64); _TrackerDb.AddParams(DataItem.ContactName, DbType.String); _TrackerDb.AddParams(DataItem.ContactEmail, DbType.String); _TrackerDb.AddParams(DataItem.JobCardNumber, DbType.String); _TrackerDb.AddParams(DataItem.DateLogged, DbType.Date); _TrackerDb.AddParams(DataItem.LastStatusChange, DbType.Date); _TrackerDb.AddParams(DataItem.MachineTypeID, DbType.Int32); _TrackerDb.AddParams(DataItem.MachineSerialNumber, DbType.String); _TrackerDb.AddParams(DataItem.SwopOutMachineID, DbType.Int32); _TrackerDb.AddParams(DataItem.MachineConditionID, DbType.Int32); _TrackerDb.AddParams(DataItem.TakenFrother, DbType.Boolean); _TrackerDb.AddParams(DataItem.TakenBeanLid, DbType.Boolean); _TrackerDb.AddParams(DataItem.TakenWaterLid, DbType.Boolean); _TrackerDb.AddParams(DataItem.BrokenFrother, DbType.Boolean); _TrackerDb.AddParams(DataItem.BrokenBeanLid, DbType.Boolean); _TrackerDb.AddParams(DataItem.BrokenWaterLid, DbType.Boolean); _TrackerDb.AddParams(DataItem.RepairFaultID, DbType.Int32); _TrackerDb.AddParams(DataItem.RepairFaultDesc, DbType.String); _TrackerDb.AddParams(DataItem.RepairStatusID, DbType.Int32); _TrackerDb.AddParams(DataItem.RelatedOrderID, DbType.Int64); _TrackerDb.AddParams(DataItem.Notes, DbType.String); _result = String.IsNullOrEmpty(_TrackerDb.ExecuteNonQuerySQL(CONST_SQL_INSERT)); _TrackerDb.Close(); return(_result); }
/* * try * { * _conn.Open(); * if (_cmd.ExecuteNonQuery() > 0) * * * OleDbConnection _conn = new OleDbConnection(_connectionString); * * // add parameters in the order they appear in the update command * OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); * _cmd.Parameters.Add(new OleDbParameter { Value = ItemTypeID }); * _cmd.Parameters.Add(new OleDbParameter { Value = Math.Round(QuantityOrdered,2) }); * _cmd.Parameters.Add(new OleDbParameter { Value = PackagingID }); * _cmd.Parameters.Add(new OleDbParameter { Value = OrderID }); * * try * { * _conn.Open(); * if (_cmd.ExecuteNonQuery() > 0) * return false; * } * catch (OleDbException ex) * { * string _ErrStr = ex.Message; * return _ErrStr == ""; * } * finally * { * _conn.Close(); * } * * return true; * } */ public bool InsertOrderDetails(Int64 CustomerID, DateTime OrderDate, DateTime RoastDate, Int32 ToBeDeliveredBy, DateTime RequiredByDate, Boolean Confirmed, Boolean Done, String Notes, double QuantityOrdered, Int32 PackagingID, Int32 ItemTypeID) { string _sqlCmd = "INSERT INTO OrdersTbl (CustomerId, OrderDate, RoastDate, RequiredByDate, ToBeDeliveredBy, Confirmed, Done, Notes, " + " ItemTypeID, QuantityOrdered, PackagingID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; //1 2 3 4 5 6 7 8 9 10 11 bool _Success = false; // check if the item is a group item then get the next group. TrackerTools _TT = new TrackerTools(); ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, RequiredByDate); TrackerDb _TDB = new TrackerDb(); // first summary data _TDB.AddParams(CustomerID, DbType.Int64, "@CustomerID"); _TDB.AddParams(OrderDate, DbType.Date, "@OrderDate"); _TDB.AddParams(RoastDate, DbType.Date, "@RoastDate"); _TDB.AddParams(RequiredByDate, DbType.Date, "@RequiredByDate"); _TDB.AddParams(ToBeDeliveredBy, DbType.Int32, "@ToBeDeliveredBy"); _TDB.AddParams(Confirmed, DbType.Boolean, "@Confirmed"); _TDB.AddParams(Done, DbType.Boolean, "@Done"); _TDB.AddParams(Notes, DbType.String, "@Notes"); // Now line data _TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID"); _TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered"); _TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID"); _Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd)); _TDB.Close(); return(_Success); }
public bool SetReoccuringItemsLastDate() { bool _Success = false; //string _connectionStr = ConfigurationManager.ConnectionStrings[QOnT.classes.TrackerDb.CONST_CONSTRING].ConnectionString; ; List <ReoccuringOrderTbl> _ReoccuringOrderItems = new List <ReoccuringOrderTbl>(); TrackerDb _TDB = new TrackerDb(); //using (OleDbConnection _conn = new OleDbConnection(_connectionStr)) //{ string _sqlCmd = CONST_SQL_GETITEMSLASTDATE; // not needed if (!String.IsNullOrEmpty(SortBy)) _sqlCmd += " ORDER BY " + SortBy; // Add order by string //OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn); // run the qurey we have built //_conn.Open(); // read all the data from the query and post it into the DataList //OleDbDataReader _DataReader = _cmd.ExecuteReader(); IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd); while (_DataReader.Read()) { ReoccuringOrderTbl _DataItem = new ReoccuringOrderTbl(); _DataItem.CustomerID = (_DataReader["CustomerID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["CustomerID"]); _DataItem.ItemRequiredID = (_DataReader["ItemRequiredID"] == DBNull.Value) ? 0 : Convert.ToInt32(_DataReader["ItemRequiredID"]); _DataItem.DateLastDone = (_DataReader["LastDatePerItem"] == DBNull.Value) ? System.DateTime.Now : Convert.ToDateTime(_DataReader["LastDatePerItem"]); _ReoccuringOrderItems.Add(_DataItem); } // while //now update the table //_conn.Close(); //_conn.Dispose(); //_cmd.Dispose(); _DataReader.Close(); _TDB.Close(); //} for (int i = 0; i < _ReoccuringOrderItems.Count; i++) { //using (OleDbConnection _conn = new OleDbConnection(_connectionStr)) //{ _TDB = new TrackerDb(); //OleDbCommand _cmd = new OleDbCommand(CONST_UPDATE_ITEMSLASTDATE, _conn); #region Parameters // Add data SET data DateLastDone //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].DateLastDone }); _TDB.AddParams(_ReoccuringOrderItems[i].DateLastDone, DbType.Date); // Where data //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].CustomerID }); //_cmd.Parameters.Add(new OleDbParameter { Value = _ReoccuringOrderItems[i].ItemRequiredID }); _TDB.AddWhereParams(_ReoccuringOrderItems[i].CustomerID, DbType.Int32); _TDB.AddWhereParams(_ReoccuringOrderItems[i].ItemRequiredID, DbType.Int32); #endregion _Success = _TDB.ExecuteNonQuerySQL(CONST_UPDATE_ITEMSLASTDATE) == string.Empty; //try //{ // _conn.Open(); // _Success = (_cmd.ExecuteNonQuery() >= 0); //} //catch (OleDbException oleErr) //{ _Success = oleErr.ErrorCode != 0; } //finally //{ // _conn.Close(); // _conn.Dispose(); // _cmd.Dispose(); //} _TDB.Close(); } return(_Success); }