/// <summary> /// Begins the transaction and returns the transaction object with open state connection. /// Make sure to call EndTransaction method to close transaction /// </summary> /// <param name="destinationDatabase"></param> /// <param name="err">The err.</param> /// <param name="givenIsolationLevel">The given isolation level.</param> /// <returns></returns> public iDB2Transaction BeginTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, IsolationLevel?givenIsolationLevel = null) { string ModuleName = GetModuleNameByDestinationDB(destinationDatabase); iDB2Transaction db2Trans = null; this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName); Settings.ModuleName = ModuleName; DBDB2Access DBDataAccessEntity = new DBDB2Access(); DBDataAccessEntity.Settings = Settings; DBDataAccessEntity.CommandElement = _commandElements; if (givenIsolationLevel == null) { db2Trans = DBDataAccessEntity.BeginTransaction(destinationDatabase, ref err); } else { db2Trans = DBDataAccessEntity.BeginTransaction(destinationDatabase, ref err); } if (err.HasError) { db2Trans = null; } //'End If return(db2Trans); }
/// <summary> /// Use this for Insert, Update and Delete /// Functionality to provide the gateway to access the Data Access Layer with Transaction object /// If any exception transaction will be rollbacked here /// </summary> /// <param name="destinationDatabase"></param> /// <param name="givenTransaction">The given transaction.</param> /// <returns></returns> public ErrorObj DB2Access(DestinationDatabase destinationDatabase, iDB2Transaction givenTransaction) { string ModuleName = GetModuleNameByDestinationDB(destinationDatabase); ErrorObj err = new ErrorObj(); this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName); Settings.ModuleName = ModuleName; DBDB2Access DBDataAccessEntity = new DBDB2Access(); DBDataAccessEntity.Settings = Settings; DBDataAccessEntity.CommandElement = _commandElements; err = DBDataAccessEntity.AccessWithTransaction(givenTransaction); if (!err.HasError && !(DBDataAccessEntity.ResultDataSet == null)) { ResultDataSet = DBDataAccessEntity.ResultDataSet; } else { givenTransaction.Rollback(); //before call this get the previous error details string errMessage = err.ErrorMessage; err = EndTransaction(destinationDatabase, givenTransaction); if (err.HasError) { errMessage = errMessage + ";" + err.ErrorMessage; } //whether end transaction gives error or not //always assign err object has error err.HasError = true; err.ErrorMessage = errMessage; } return(err); }
protected int DB2Access(TalentDB2Access talentDB2AccessDetail, iDB2Transaction givenTransaction) { int affectedRows = 0; if (settings.EnableDB2AccessQueue) { settings.DB2AccessQueue.Add(talentDB2AccessDetail); } else { ErrorObj err = new ErrorObj(); if (givenTransaction == null) { err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT); } else { err = talentDB2AccessDetail.DB2Access(DestinationDatabase.TALENTTKT, givenTransaction); } if ((!(err.HasError)) && (!(talentDB2AccessDetail.ResultDataSet == null))) { affectedRows = System.Convert.ToInt32(talentDB2AccessDetail.ResultDataSet.Tables[0].Rows[0][0]); } } return(affectedRows); }
public void ComenzarTransaccion() { if (_Transaccion == null) { _Transaccion = _Conexion.BeginTransaction(); } }
public void CompleteTransaction() { useTransaction = false; _transaction.Commit(); _transaction.Dispose(); _transaction = null; }
public void AbortTransaction() { useTransaction = false; _transaction.Rollback(); _transaction.Dispose(); _transaction = null; }
public void EndTransaction(bool blnCommit) { try { if (blnCommit == true) { objTransaction.Commit(); } else { objTransaction.Rollback(); } } catch (Exception ex) { objException = ex; } finally { //objClear.CleanupPooledConnections(); objConnection.Close(); objTransaction.Dispose(); objTransaction = null; blnTrans = false; if (objCommand is object) { objCommand.Dispose(); objCommand = null; } } }
/// <summary> /// Deletes payment type information from MD501 /// </summary> /// <param name="type51"></param> /// <param name="code51"></param> /// <param name="givenTransaction"></param> /// <returns></returns> public int DeleteData(string type51, string code51, iDB2Transaction givenTransaction = null) { int affectedRows = 0; TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); ErrorObj err = new ErrorObj(); try { string commandText = "UPDATE MD501 SET ACTR51 = 'D',USER51=@USER51,PGMD51=@PGMD51,UPDT51=@UPDT51 WHERE CONO51 = @CompanyCode AND TYPE51 = @Type51 AND CODE51 = @Code51"; talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery; talentDB2AccessDetail.CommandElements.CommandText = commandText; talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Type51", type51, 4)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@Code51", code51, 15)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@USER51", settings.AgentName, 10)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@PGMD51", settings.StoredProcedure, 10)); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDT51", settings.iSeriesTodaysDate, iDB2DbType.iDB2Decimal)); //Execute affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction); } catch (Exception) { throw; } finally { talentDB2AccessDetail = null; } return(affectedRows); }
//This Function inserts comments to the FSTINVC Table private void InsertComments(iDB2Command db2Command, FSTINV inv) { st.insertLog(string.Format("Inserting notes for {0} to FSTINVC table.", inv.SIID), "Info", inv.SIID.ToString(), HillerServiceDataMigrator.LogId); string tableName = "FSTINVC"; using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("INSERT INTO {0} (SCID, SCCMTS, SCCRDT) VALUES(@p1, @p2, @p3) WITH NONE", tableName); db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID)); db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Notes.Replace("'", "''"))); db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat())); // st.insertLog(db2Command.CommandText, "FSTINVC: Insert Query", "NA",LogId); var recordsAffected = db2Command.ExecuteNonQuery(); db2Command.Transaction.Commit(); st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId); } }
public void Commit() { Transaction.Commit(); Connection.Dispose(); Connection.Close(); Transaction = null; Connection = null; }
public void Rollback() { transaction.Rollback(); connection.Dispose(); connection.Close(); transaction = null; connection = null; }
public void Commit() { transaction.Commit(); connection.Dispose(); connection.Close(); transaction = null; connection = null; }
public void Rollback() { this.Transaction.Rollback(); this.Connection.Dispose(); this.Connection.Close(); this.Transaction = null; this.Connection = null; }
/// <summary> /// Execute an iDB2Command (that returns no resultset) against an existing database transaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new iDB2Parameter(":prodid", 24)); /// </remarks> /// <param name="trans">an existing database transaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of iDB2Paramters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(iDB2Transaction trans, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters) { iDB2Command cmd = new iDB2Command(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return(val); }
/// <summary> /// Ends the transaction and close the reader by passing the object to DBDataAccess /// </summary> /// <param name="destinationDatabase"></param> /// <param name="givenTransaction">The given transaction.</param> /// <param name="readerToClose">The reader to close.</param> /// <returns>Error Object</returns> public ErrorObj EndTransaction(DestinationDatabase destinationDatabase, iDB2Transaction givenTransaction, iDB2DataReader readerToClose) { string ModuleName = GetModuleNameByDestinationDB(destinationDatabase); ErrorObj err = new ErrorObj(); this.GetConnectionDetails(Settings.BusinessUnit, "", ModuleName); Settings.ModuleName = ModuleName; DBDB2Access DBDataAccessEntity = new DBDB2Access(); DBDataAccessEntity.Settings = Settings; DBDataAccessEntity.CommandElement = _commandElements; DBDataAccessEntity.EndTransaction(destinationDatabase, ref err, givenTransaction, readerToClose); return(err); }
public static long GetSequenceTrans(iDB2Connection conn, string tableName) { //iDB2Transaction trans = conn.BeginTransaction(IsolationLevel.RepeatableRead); iDB2Transaction trans = conn.BeginTransaction(); long id = 0; try { string sql = "SELECT VALUE FROM SEQ_TABLE WHERE KEY=@KEY"; iDB2Parameter[] parms = new iDB2Parameter[] { new iDB2Parameter("@KEY", "USER") }; Object obj = ExecuteScalar(trans, CommandType.Text, sql, parms); if (obj != null) { id = Convert.ToInt32(obj); string sqlUpdate = "UPDATE SEQ_TABLE SET VALUE=@NEW_ID WHERE VALUE=@OLD_ID AND KEY=@KEY"; iDB2Parameter[] parmsUpdate = new iDB2Parameter[] { new iDB2Parameter("@NEW_ID", id + 1), new iDB2Parameter("@OLD_ID", id), new iDB2Parameter("@KEY", tableName) }; ExecuteNonQuery(trans, CommandType.Text, sqlUpdate, parmsUpdate); } else { id = 1; string sqlInsert = "INSERT INTO SEQ_TABLE(KEY,VALUE) VALUES(@KEY,@VALUE)"; iDB2Parameter[] parmsInsert = new iDB2Parameter[] { new iDB2Parameter("@KEY", tableName), new iDB2Parameter("@VALUE", id + 1) }; ExecuteNonQuery(trans, CommandType.Text, sqlInsert, parmsInsert); } trans.Commit(); return(id); } catch (Exception ex) { log.Error(ex.Message); log.Error(ex.StackTrace); trans.Rollback(); return(0); } }
//This function returns true if the invoice found in the FSTINV table private bool InvoiceExists(iDB2Command db2Command, string st) { bool exists = false; using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT * FROM FSTINV WHERE SIID = {0}", st); iDB2DataReader reader = db2Command.ExecuteReader(); exists = reader.HasRows; reader.Close(); db2Command.Transaction.Dispose(); } return(exists); }
/// <summary> /// Execute the command obejct with transaction /// </summary> /// <param name="givenTransaction">The given transaction.</param> /// <returns>Error Object</returns> public ErrorObj AccessWithTransaction(iDB2Transaction givenTransaction) { ErrorObj err = new ErrorObj(); iDB2Command db2CommandEntity = new iDB2Command(); db2CommandEntity.Transaction = givenTransaction; db2CommandEntity.Connection = givenTransaction.Connection; err = PrepareCommandElement(db2CommandEntity); if (!(err.HasError)) { try { if (_commandElement.CommandExecutionType == CommandExecution.ExecuteDataSet) { this.ResultDataSet = ExecuteDataSet(db2CommandEntity); } else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteNonQuery) { this.ResultDataSet = ExecuteNonQuery(db2CommandEntity); } else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteReader) { } else if (_commandElement.CommandExecutionType == CommandExecution.ExecuteScalar) { } } catch (iDB2Exception db2Ex) { err.HasError = true; err.ErrorMessage = db2Ex.Message + "; Err Code:" + System.Convert.ToString(db2Ex.ErrorCode) + "; MessageDetails:" + db2Ex.MessageDetails + "; Source:" + db2Ex.Source; Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, db2Ex.StackTrace.ToString(), err.ErrorMessage); } catch (Exception ex) { err.HasError = true; err.ErrorMessage = ex.Message; Utilities.InsertErrorLog(Settings, logSourceClass, System.Reflection.MethodBase.GetCurrentMethod().Name, logCode, Settings.BusinessUnit, logFilter2, logFilter3, logFilter4, ex.StackTrace.ToString(), err.ErrorMessage); } } return(err); }
/// <summary> /// Opens the connection and create and returns the transaction object /// </summary> /// <param name="err">The error object as ref</param> /// <param name="givenIsolationLevel">The given isolation level.</param> /// <returns>iDB2Transaction instance</returns> public iDB2Transaction BeginTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, IsolationLevel?givenIsolationLevel = null) { iDB2Transaction db2Trans = null; err = ConnectionByDestinationDBOpen(destinationDatabase); if (!err.HasError) { if (givenIsolationLevel == null) { db2Trans = conTALENTTKT.BeginTransaction(); } else { db2Trans = conTALENTTKT.BeginTransaction((IsolationLevel)givenIsolationLevel); } } else { db2Trans = null; } return(db2Trans); }
//Mark the Status to Done "D" on FSTINV private void SetInvoiceToDone(iDB2Command db2Command, Invoice inv) { st.insertLog("Set " + inv.SIID + " invoice to done", "info", inv.SIID.ToString(), LogId); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos)) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("UPDATE FSTINV SET SISTAT = @status WHERE SIID = {0}", inv.SIID); db2Command.DeriveParameters(); db2Command.Parameters["@status"].Value = 'D'; // st.insertLog(db2Command.CommandText, "FSTINV Update Query", "NA", LogId); var recordsAffected = db2Command.ExecuteNonQuery(); db2Command.Transaction.Commit(); db2Command.Transaction.Dispose(); st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId); } }
/// <summary> /// Execute a iDB2Command (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">The stored procedure name or PL/SQL command</param> /// <param name="commandParameters">An array of iDB2Paramters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(iDB2Transaction transaction, CommandType commandType, string commandText, params iDB2Parameter[] commandParameters) { if (transaction == null) { throw new ArgumentNullException("transaction"); } if (transaction != null && transaction.Connection == null) { throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); } // Create a command and prepare it for execution iDB2Command cmd = new iDB2Command(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); // Execute the command & return the results object retval = cmd.ExecuteScalar(); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return(retval); }
/// <summary> /// Internal function to prepare a command for execution by the database /// </summary> /// <param name="cmd">Existing command object</param> /// <param name="conn">Database connection object</param> /// <param name="trans">Optional transaction object</param> /// <param name="cmdType">Command type, e.g. stored procedure</param> /// <param name="cmdText">Command test</param> /// <param name="commandParameters">Parameters for the command</param> private static void PrepareCommand(iDB2Command cmd, iDB2Connection conn, iDB2Transaction trans, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters) { //Open the connection if required if (conn.State != ConnectionState.Open) { conn.Open(); } //Set up the command cmd.Connection = conn; cmd.CommandText = cmdText; cmd.CommandType = cmdType; //Bind it to the transaction if it exists if (trans != null) { cmd.Transaction = trans; } // Bind the parameters passed in if (commandParameters != null) { foreach (iDB2Parameter parm in commandParameters) { cmd.Parameters.Add(parm); } } /*if (commandParameters != null) * { * cmd.DeriveParameters(); * //cmd.Parameters["@myKEY"].Value = "USER_NO"; * foreach (var parm in commandParameters) * cmd.Parameters["@"+ parm.Key].Value = parm.Value; * }*/ }
public void StartTransaction() { // Apre una transazione su cui eseguire comandi // -------------------------------------------- try { objConnection = new iDB2Connection(strConn); objConnection.Open(); // Creiamo un timestamp da usare negli aggiornamenti // ------------------------------------------------- CreateTimestamp(); objTransaction = objConnection.BeginTransaction(); blnTrans = true; objCommand = new iDB2Command(); objCommand.CommandTimeout = 180; objCommand.Connection = objConnection; objCommand.Transaction = objTransaction; } catch (Exception ex) { objException = ex; } }
//Update Invoice in service Trade to Done private void SendInvoiceUpdates(iDB2Command db2Command) { st.insertLog("Sending invoice update to Service Trade.", "info", "NA", LogId); Dictionary <string, Invoice> jdeInvList = new Dictionary <string, Invoice>(); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT DISTINCT SIID, SIAN8, SIJOB, SIDOCO, SIDCTO, SICRDT FROM FSTINV WHERE SISTAT = 'P'"); iDB2DataReader reader = db2Command.ExecuteReader(); while (reader.Read()) { Invoice temp = new Invoice(); temp.SIID = ((string)reader["SIID"]).Trim(); temp.type = "invoice"; //always set this to processed status to avoid breaking the status in ST temp.status = "processed"; temp.jobId = Int32.Parse((string)reader["SIJOB"]); //Change Invoice Number (Concat JDE Document Number and Document Type with -) temp.invoiceNumber = string.Format("{0}-{1}", reader["SIDOCO"], reader["SIDCTO"]); temp.transactionDate = (Int32)((DateTime)reader["SICRDT"] - unixBaseTime).TotalSeconds; //temp.assignedUserId= Int32.Parse((string)reader["SITECH"]); //09/18/2015 - This limits the invoices being sent for update in ST. //if (!jdeInvList.ContainsKey((string)reader["SIAN8"])) //{ // jdeInvList.Add((string)reader["SIAN8"], temp); // sent++; //} if (reader["SIAN8"] != null) { //12/19/16 JC: This will avoid duplicate records. if (!jdeInvList.ContainsKey(temp.SIID)) { jdeInvList.Add((string)temp.SIID, temp); } } } reader.Close(); } db2Command.Transaction.Dispose(); if (jdeInvList.Count > 0) { st.insertLog(string.Format("Found {0} invoices for update", jdeInvList.Count), "Info", "NA", LogId); foreach (var jde in jdeInvList) { //var status = jde.Value.status; //Query the single invoice from the service Trade var invoice = serviceTrade.GetInvoice(jde.Key); //Mark the invoice as done in FSTINV, if the status of the invoice is void in Service Trade if (invoice.Status == "void" || invoice.Status != "pending_accounting") { st.insertLog(string.Format("The status of invoice number {0} is not <pending_accounting> in service Trade.", jde.Key), "Error", jde.Key.ToString(), LogId); SetInvoiceToDone(db2Command, jde.Value); } else { if (serviceTrade.UpdateInvoice(jde.Value)) { st.insertLog(string.Format("The status of invoice number {0} is set to processed in Service Trade.", jde.Key), "Info", jde.Key.ToString(), LogId); SetInvoiceToDone(db2Command, jde.Value); } } } } else { st.insertLog("No invoice update to be sent.", "Info", "NA", LogId); } }
//This function inserts the invoice into the FSTINV Table private void InsertInvoiceToJDE(iDB2Command db2Command, FSTINV inv) { st.insertLog(string.Format("Inserting {0} to FSTINV table.", inv.Name), "Info", inv.SIID.ToString(), LogId); string tableName = inv.GetType().Name; if (inv.Items.Length > 0) { for (int x = 0; x < inv.Items.Length; x++) { //10.12.16 JC: Check whether this is a recurring service (parameter send invoice and a invoice item) // inv.Items[x].IsRecurringService = serviceTrade.IsRecurringService(inv, inv.Items[x]); // st.insertLog(string.Format("New vs Recurring Check result: {0}, {1}, {2}", inv.SIID, inv.Items[x].LibItem.Name, inv.Items[x].IsRecurringService), "Info", "NA", LogId); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("INSERT INTO {0} (SIID, SIAN8, SILAN8, SISSTS, SIINV, SITECH, SILOC, SITYPE, SITOTL, SIPO, SIITEM, SIITYP, SIDESC, SIQTY, SIPRIC, SICRDT, SIJOB, SISLSM, SISLS2, SISLS3, SIDFLG, SIUSER1) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22) WITH NONE", tableName); //Recurring service // db2Command.CommandText = db2Command.CommandText.Replace("@p22", string.Format("'{0}'", inv.Items[x].IsRecurringService ? "Yes" : "No")); db2Command.CommandText = db2Command.CommandText.Replace("@p22", string.Format("'{0}'", "NA")); //Flag to Determine Service Type db2Command.CommandText = db2Command.CommandText.Replace("@p21", string.Format("'{0}'", inv.FPMARActive ? "FPMAR" : "")); //Salesman 3 db2Command.CommandText = db2Command.CommandText.Replace("@p20", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 1) ? inv.Technician[1].SITECH : null)); //Salesman 2 db2Command.CommandText = db2Command.CommandText.Replace("@p19", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 1) ? inv.Tags[1] : null)); //Salesman 1 db2Command.CommandText = db2Command.CommandText.Replace("@p18", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 0) ? inv.Tags[0] : null)); //Service Trade Job ID db2Command.CommandText = db2Command.CommandText.Replace("@p17", string.Format("'{0}'", inv.Job.JobId)); //Created Date db2Command.CommandText = db2Command.CommandText.Replace("@p16", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat())); //Item Price db2Command.CommandText = db2Command.CommandText.Replace("@p15", string.Format("'{0}'", inv.Items[x].Price)); //Item Quanity db2Command.CommandText = db2Command.CommandText.Replace("@p14", string.Format("'{0}'", Math.Round(inv.Items[x].Quantity, 2))); //6.23.16 JC - Hiller wants to limit the item qty to two decimal places. //Service Trade Item Description db2Command.CommandText = db2Command.CommandText.Replace("@p13", string.Format("'{0}'", inv.Items[x].Description.Length <= 50 ? (inv.Items[x].Description).Replace("'", "''") : (inv.Items[x].Description.Substring(0, 50)).Replace("'", "''"))); //Service Trade Item Type db2Command.CommandText = db2Command.CommandText.Replace("@p12", string.Format("'{0}'", inv.Items[x].LibItem.Type)); //ITEM ID db2Command.CommandText = db2Command.CommandText.Replace("@p11", string.Format("'{0}'", inv.Items[x].LibItem.Code)); //Customer PO db2Command.CommandText = db2Command.CommandText.Replace("@p10", string.Format("'{0}'", string.IsNullOrEmpty(inv.CustomerPo) ? string.Empty : inv.CustomerPo.Replace("'", string.Empty))); //Service Trade ID db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID)); //JDE Address Book number for the Company db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Customer.External.JdeId)); //JDE Address Book number for the Location db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", inv.Location.External.JdeId)); //Service Trade invoice Status db2Command.CommandText = db2Command.CommandText.Replace("@p4", string.Format("'{0}'", inv.Status)); //Service Trade Invoice number db2Command.CommandText = db2Command.CommandText.Replace("@p5", string.Format("'{0}'", inv.InvoiceNumber)); //Service Trade Technician db2Command.CommandText = db2Command.CommandText.Replace("@p6", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 0) ? inv.Technician[0].SITECH : null)); //Service Trade Location (e.g. FT. Walton 123, Pensacola 122) db2Command.CommandText = db2Command.CommandText.Replace("@p7", string.Format("'{0}'", inv.ServicingOffice != null ? inv.ServicingOffice.OfficeId : null)); //Service Trade Invoice Type db2Command.CommandText = db2Command.CommandText.Replace("@p8", string.Format("'{0}'", inv.Type)); //Inovice Total Amount db2Command.CommandText = db2Command.CommandText.Replace("@p9", string.Format("'{0}'", inv.TotalPrice)); // st.insertLog(db2Command.CommandText, "FSTINV: Insert Query multiple items", "NA", LogId); var recordsAffected = db2Command.ExecuteNonQuery(); db2Command.Transaction.Commit(); st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId); } } } else { using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("INSERT INTO {0} (SIID, SIAN8, SISSTS, SIINV, SITECH, SILOC, SITOTL, SIPO, SICRDT, SIJOB, SISLSM, SISLS2, SISLS3, SIDFLG) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14) WITH NONE", tableName); db2Command.CommandText = db2Command.CommandText.Replace("@p14", string.Format("'{0}'", inv.FPMARActive ? "FPMAR" : "")); db2Command.CommandText = db2Command.CommandText.Replace("@p13", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 1) ? inv.Technician[1].SITECH : null)); db2Command.CommandText = db2Command.CommandText.Replace("@p12", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 1) ? inv.Tags[1] : null)); db2Command.CommandText = db2Command.CommandText.Replace("@p11", string.Format("'{0}'", (inv.Tags != null && inv.Tags.Count > 0) ? inv.Tags[0] : null)); db2Command.CommandText = db2Command.CommandText.Replace("@p10", string.Format("'{0}'", inv.Job.JobId)); db2Command.CommandText = db2Command.CommandText.Replace("@p1", string.Format("'{0}'", inv.SIID)); db2Command.CommandText = db2Command.CommandText.Replace("@p2", string.Format("'{0}'", inv.Customer.CompanyId)); db2Command.CommandText = db2Command.CommandText.Replace("@p3", string.Format("'{0}'", inv.Status)); db2Command.CommandText = db2Command.CommandText.Replace("@p4", string.Format("'{0}'", inv.InvoiceNumber)); db2Command.CommandText = db2Command.CommandText.Replace("@p5", string.Format("'{0}'", (inv.Technician != null && inv.Technician.Length > 0) ? inv.Technician[0].SITECH : null)); db2Command.CommandText = db2Command.CommandText.Replace("@p6", string.Format("'{0}'", inv.ServicingOffice != null ? inv.ServicingOffice.OfficeId : null)); db2Command.CommandText = db2Command.CommandText.Replace("@p7", string.Format("'{0}'", inv.TotalPrice)); db2Command.CommandText = db2Command.CommandText.Replace("@p8", string.Format("'{0}'", string.IsNullOrEmpty(inv.CustomerPo) ? string.Empty : inv.CustomerPo.Replace("'", string.Empty))); db2Command.CommandText = db2Command.CommandText.Replace("@p9", string.Format("'{0}'", new iDB2TimeStamp(inv.CreatedDateTime.ToString("MM/dd/yyyy h:mm:ss.ffffff")).ToNativeFormat())); // st.insertLog(db2Command.CommandText, "FSTINV: Insert Query no items", "NA", LogId); var recordsAffected = db2Command.ExecuteNonQuery(); db2Command.Transaction.Commit(); st.insertLog(string.Format("Rows affected: {0}", recordsAffected), "Info", "NA", LogId); } } //Onces inserted, change the status to processed for updating ServiceTrade inv.Status = "processed"; }
private void MigrateUnprocessedLocations(iDB2Command db2Command) { Dictionary <string, Location> jdeLocList = new Dictionary <string, Location>(); // log.Debug("Migrating Location"); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT * FROM FSTLOCN WHERE SLSTAT != '{0}'", "P"); iDB2DataReader reader = db2Command.ExecuteReader(); while (reader.Read()) { Location temp = new Location(); temp.slan8 = ((string)reader["SLAN8"]).Trim(); temp.slpan8 = ((string)reader["SLPAN8"]).Trim(); temp.name = ((string)reader["SLNAME"]).Trim(); temp.addressStreet = ((string)reader["SLADDR"]).Trim(); temp.addressCity = ((string)reader["SLCITY"]).Trim(); temp.addressState = ((string)reader["SLST"]).Trim(); temp.addressPostalCode = ((string)reader["SLZIP"]).Trim(); temp.phoneNumber = ((string)reader["SLPHON"]).Trim(); temp.type = Char.Parse(((string)reader["SLACTN"]).Trim()); temp.officeIds = new int[] { Int32.Parse(((string)reader["SLMCU"]).Trim()) }; jdeLocList.Add(temp.slan8, temp); } reader.Close(); db2Command.Transaction.Dispose(); } if (jdeLocList.Count > 0) { // log.Info(string.Format("Found {0} locations for update", jdeLocList.Count)); foreach (var jde in jdeLocList) { // log.Debug("Migrating " + jde.Value.name); bool success = jde.Value.type == 'A' ? serviceTrade.AddLocation(jde.Value) : serviceTrade.UpdateLocation(jde.Value); if (success) { using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos)) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("UPDATE FSTLOCN SET SLSTAT = @slstat WHERE SLAN8 = @key", jde.Key); db2Command.DeriveParameters(); db2Command.Parameters["@slstat"].Value = 'P'; db2Command.Parameters["@key"].Value = jde.Key; try { db2Command.ExecuteNonQuery(); } catch (Exception ex) { // log.Error("Location update unsuccessful ", ex); } db2Command.Transaction.Dispose(); } } } } else { // log.Info("No location found for processing."); } }
private void MigrateUnprocessedCompaniesFromJDE(iDB2Command db2Command) { Dictionary <string, Company> jdeCompList = new Dictionary <string, Company>(); st.insertLog("Migrating Companies", "Info", "NA", LogId); iDB2DataReader reader; using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT * FROM FSTCOMP WHERE SCSTAT != '{0}'", "P"); reader = db2Command.ExecuteReader(); while (reader.Read()) { Company temp = new Company(); temp.scan8 = (string)reader["SCAN8"]; temp.type = Char.Parse((string)reader["SCACTN"]); //Company Name temp.name = ((string)reader["SCNAME"]).Trim(); temp.addressStreet = ((string)reader["SCADDR"]).Trim(); temp.addressCity = ((string)reader["SCCITY"]).Trim(); temp.addressState = ((string)reader["SCST"]).Trim(); temp.addressPostalCode = ((string)reader["SCZIP"]).Trim(); temp.customer = Int32.Parse((string)reader["SCCUST"]) == 1 ? true : false; temp.vendor = Int32.Parse((string)reader["SCVEND"]) == 1 ? true : false; temp.phoneNumber = ((string)reader["SCPHON"]).Trim(); temp.status = ((string)reader["SCSSTS"]).Trim(); jdeCompList.Add((string)reader["SCAN8"], temp); } reader.Close(); db2Command.Transaction.Dispose(); } if (jdeCompList.Count > 0) { st.insertLog(string.Format("Found {0} companies for update", jdeCompList.Count), "Info", "NA", HillerServiceDataMigrator.LogId); foreach (var jde in jdeCompList) { st.insertLog("Migrating " + jde.Value.name, "Info", "NA", HillerServiceDataMigrator.LogId); bool success = jde.Value.type == 'A' ? serviceTrade.AddCompany(jde.Value) : serviceTrade.UpdateCompany(jde.Value); if (success) { // log.Debug("Updating Migrated Companies."); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos)) { db2Command.Transaction = db2Transaction; db2Command.CommandText = "UPDATE FSTCOMP SET SCSTAT = @scstat WHERE SCAN8 = @key"; db2Command.DeriveParameters(); db2Command.Parameters["@scstat"].Value = 'P'; db2Command.Parameters["@key"].Value = jde.Key; // log.Debug(db2Command.CommandText); try { db2Command.ExecuteNonQuery(); } catch (Exception ex) { // log.Error("Company update unsuccessful ", ex); } db2Command.Transaction.Dispose(); } } } } else { // log.Info("No company found for processing."); } }
/// <summary> /// XML Comment /// </summary> /// <param name="businessUnit"></param> /// <param name="variableKey1Value"></param> /// <param name="configs"></param> /// <param name="givenTransaction"></param> /// <returns></returns> /// <remarks></remarks> private int UpdateData(string businessUnit, string variableKey1Value, System.Collections.Generic.List <ConfigurationItem> configs, iDB2Transaction givenTransaction) { int affectedRows = 0; TalentDB2Access talentDB2AccessDetail = new TalentDB2Access(); ErrorObj err = new ErrorObj(); try { string[] setValue = new string[configs.Count - 1 + 1]; for (int i = 0; i <= configs.Count - 1; i++) { setValue[i] = configs[i].DefaultName + " = @UPDATED_VALUE" + i.ToString(); } string values = string.Join(",", setValue); string commandText = string.Format("Update CD00A SET {0} WHERE CONO0A = @CompanyCode", values); talentDB2AccessDetail.Settings = settings; talentDB2AccessDetail.CommandElements.CommandExecutionType = CommandExecution.ExecuteNonQuery; talentDB2AccessDetail.CommandElements.CommandText = commandText; talentDB2AccessDetail.CommandElements.CommandParameter.Clear(); talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@CompanyCode", companyCode, 3)); for (int i = 0; i <= configs.Count - 1; i++) { talentDB2AccessDetail.CommandElements.CommandParameter.Add(ConstructDB2Parameter("@UPDATED_VALUE" + i.ToString(), System.Convert.ToString(configs[i].UpdatedValue))); } //Execute affectedRows = DB2Access(talentDB2AccessDetail, givenTransaction); } catch (Exception) { throw; } finally { talentDB2AccessDetail = null; } return(affectedRows); }
public override int UpdateCurrentValueDB2(System.Collections.Generic.List <ConfigurationItem> configs, iDB2Transaction givenTransaction = null) { return(UpdateData(businessUnit, variableKey1Value, configs, givenTransaction)); }
public Session(string connectionString) { this.Connection = new iDB2Connection(connectionString); this.Connection.Open(); this.Transaction = this.Connection.BeginTransaction(); }