private void button1_Click_1(object sender, EventArgs e) { // svuota file di passaggio iDB2Connection DBCONN = new iDB2Connection(Program.myConnString); DBCONN.Open(); iDB2Command myCommand = new iDB2Command(); myCommand.Connection = DBCONN; myCommand.CommandText = "delete from ODA201P "; myCommand.ExecuteNonQuery(); for (int i = 0; i < dataGridView1.Rows.Count; i++) { if (dataGridView1.Rows[i].Cells[3].Value != null) { myCommand.CommandText = "insert into ODA201P " + " values('" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "'," + dataGridView1.Rows[i].Cells[1].Value.ToString() + "," + "'" + dataGridView1.Rows[i].Cells[2].Value.ToString() + "'," + "'" + dataGridView1.Rows[i].Cells[3].Value.ToString() + "'," + "'" + dataGridView1.Rows[i].Cells[4].Value.ToString() + "')"; myCommand.ExecuteNonQuery(); //MessageBox.Show(dataGridView1.Rows[i].Cells[3].Value.ToString()); } } FrmStampa fsta = new FrmStampa(); fsta.ShowDialog(); }
// PUT api/values/5 public void Put(int id, [FromBody] Contacto oContacto) { string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString; iDB2Connection connection = new iDB2Connection(connString); connection.Open(); iDB2Command db2Command = new iDB2Command(); string query = "UPDATE DAVIDEC1.CONTACTOS " + "SET NOMBRE = @nombre , " + "TELEFONO = @telefono , " + "CORREO = @correo , " + "EDAD = @edad " + "WHERE ID = @id"; db2Command.Connection = connection; db2Command.CommandText = query; db2Command.Parameters.Add(new iDB2Parameter("@nombre", oContacto.Nombre)); db2Command.Parameters.Add(new iDB2Parameter("@telefono", oContacto.Telefono)); db2Command.Parameters.Add(new iDB2Parameter("@correo", oContacto.Correo)); db2Command.Parameters.Add(new iDB2Parameter("@edad", oContacto.Edad)); db2Command.Parameters.Add(new iDB2Parameter("@id", id)); db2Command.ExecuteNonQuery(); connection.Close(); }
public void Call(Session session) { string program = this.LibraryWhereProgram.Name + "." + this.Name; command = new iDB2Command(program, session.Connection, session.Transaction); command.CommandTimeout = 0; command.CommandType = CommandType.StoredProcedure; foreach (Parameter parameter in this.Parameters) { iDB2Parameter param = new iDB2Parameter(); parameter.Fill(param); this.command.Parameters.Add(param); } try { command.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } foreach (Parameter parameter in this.Parameters) { iDB2Parameter db2Parameter = command.Parameters[parameter.Name]; if (db2Parameter != null) { parameter.UpdateValue(db2Parameter); } } }
public void grabarUbicacionesLlenos(UbicacionContenedor datos, decimal fultac, decimal hultac) { iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString); iDB2Command cmd; cmd = new iDB2Command("SP_INTTRANSDEP_GRABA_UBICACION_RZIN03", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("IN_CTDCRF", iDB2DbType.iDB2VarChar).Value = datos.TipoDocumento; cmd.Parameters.Add("IN_NDCREF", iDB2DbType.iDB2VarChar).Value = datos.DocumentoReferencia; cmd.Parameters.Add("IN_CPRCNO", iDB2DbType.iDB2VarChar).Value = datos.Contenedor.Substring(0, 4); cmd.Parameters.Add("IN_NSRCNO", iDB2DbType.iDB2VarChar).Value = datos.Contenedor.Substring(4, datos.Contenedor.Length - 4); cmd.Parameters.Add("IN_TUBCAL", iDB2DbType.iDB2VarChar).Value = datos.Ubicacion; cmd.Parameters.Add("IN_NROMVM", iDB2DbType.iDB2VarChar).Value = datos.NumeroMovimientos; cmd.Parameters.Add("IN_FULTAC", iDB2DbType.iDB2Numeric).Value = fultac; cmd.Parameters.Add("IN_HULTAC", iDB2DbType.iDB2Numeric).Value = hultac; try { cn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { } finally { cn.Close(); } }
public void InsertarStockLlenos(PendientesLlenos input) { // iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString); iDB2Command cmd; cmd = new iDB2Command("SP_ACCIONES_DSSTOCK", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@NUMUID", SqlDbType.VarChar).Value = Guid.NewGuid().ToString(); cmd.Parameters.Add("@NUMID", SqlDbType.Decimal).Value = 0; cmd.Parameters.Add("@NUMIDE", SqlDbType.VarChar).Value = input.YardCode; cmd.Parameters.Add("@NROCON", SqlDbType.VarChar).Value = input.EquipmentNumber; cmd.Parameters.Add("@DOCREF", SqlDbType.VarChar).Value = ""; cmd.Parameters.Add("@TIPOPE", SqlDbType.VarChar).Value = "L"; cmd.Parameters.Add("@FECREG", SqlDbType.Decimal).Value = (DateTime.Now.ToString("yyyyMMdd")); cmd.Parameters.Add("@HRSREG", SqlDbType.Decimal).Value = (DateTime.Now.ToString("HHmmss")); cmd.Parameters.Add("@USRREG", SqlDbType.VarChar).Value = "CARGAINI"; cmd.Parameters.Add("@SESTRG", SqlDbType.VarChar).Value = "A"; cmd.Parameters.Add("@ACCION", SqlDbType.VarChar).Value = "I"; try { cn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { } finally { cn.Close(); } }
public void grabarUbicaciones(string TipDoc, string DocRef, string Sigla, string Serie, string Ubicacion, int NroMovimientos) { iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString); iDB2Command cmd; cmd = new iDB2Command("SP_INTTRANSDEP_GRABA_UBICACION_RZIN02", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("IN_CTDCRF", iDB2DbType.iDB2VarChar).Value = TipDoc; cmd.Parameters.Add("IN_NDCREF", iDB2DbType.iDB2VarChar).Value = DocRef; cmd.Parameters.Add("IN_CPRCNO", iDB2DbType.iDB2VarChar).Value = Sigla; cmd.Parameters.Add("IN_NSRCNO", iDB2DbType.iDB2VarChar).Value = Serie; cmd.Parameters.Add("IN_TUBCAL", iDB2DbType.iDB2VarChar).Value = Ubicacion; cmd.Parameters.Add("IN_NROMVM", iDB2DbType.iDB2VarChar).Value = NroMovimientos; try { cn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { } finally { cn.Close(); } }
public static void Main() { Console.WriteLine($"Profiler attached: {Samples.SampleHelpers.IsProfilerAttached()}"); var command = new iDB2Command("MyCommand"); try { command.ExecuteNonQuery(); } catch (Exception) { } try { command.ExecuteScalar(); } catch (Exception) { } try { command.ExecuteReader(); } catch (Exception) { } Console.WriteLine("Done"); }
public bool Call400Pgm(string cmdtext, IBM.Data.DB2.iSeries.iDB2Connection cn) { // ------------------------------------------------------------ // This method runs a command on the iSeries using QCMDEXC. // // cmdtext is the command or program you want to call. // cn is an open iDB2Connection the command will be run on. // If the command runs without error, this method returns true. //------------------------------------------------------------ bool rc = true; // Construct a string which contains the call to QCMDEXC. // Because QCMDEXC uses single quote characters, we must // delimit single quote characters in the command text // with an extra single quote. string pgmParm = "CALL QSYS.QCMDEXC('" + cmdtext.Replace("'", "''") + "', " + cmdtext.Length.ToString("0000000000.00000") + ")"; iDB2Command cmd = new iDB2Command(pgmParm, cn); try { cmd.ExecuteNonQuery(); } catch { rc = false; } cmd.Dispose(); return(rc); }
//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 bool SubmitJob(string jobName, string param, out string oMessage) { bool res = false; oMessage = string.Empty; try { string Cmd = "SBMJOB CMD(CALL " + jobName + " PARM('" + param + "')) "; string sql = "CALL QSYS.QCMDEXC('" + Cmd.Replace("'", "''") + "'," + Cmd.Length.ToString("0000000000.00000") + ")"; _conn = new iDB2Connection(ConnectionString); _conn.Open(); _command = new iDB2Command(sql, _conn); _command.ExecuteNonQuery(); _conn.Close(); res = true; } catch (iDB2Exception iEx) { oMessage = iEx.Message; } catch (Exception ex) { oMessage = ex.Message; } return(res); }
/// <summary> /// Runs a command on the IBM i. /// </summary> /// <param name="cmdText">The command text.</param> /// <param name="cn">The connection object.</param> /// <returns></returns> public static bool RunCmd(string cmdText, iDB2Connection cn) { bool rc = true; // Construct a string which contains the call to QCMDEXC. string pgmParm = String.Format("CALL QSYS.QCMDEXC('{0}', {1})", cmdText.Replace("'", "''").Trim(), cmdText.Trim().Length.ToString("0000000000.00000")); using (cn) { using (iDB2Command cmd = new iDB2Command(pgmParm, cn)) { try { cmd.ExecuteNonQuery(); } catch (Exception e) { rc = false; } } } // Return success or failure return rc; }
/// <summary> /// Runs a command on the IBM i. /// </summary> /// <param name="cmdText">The command text.</param> /// <param name="cn">The connection object.</param> /// <returns></returns> public static bool RunCmd(string cmdText, iDB2Connection cn) { bool rc = true; // Construct a string which contains the call to QCMDEXC. string pgmParm = String.Format("CALL QSYS.QCMDEXC('{0}', {1})", cmdText.Replace("'", "''").Trim(), cmdText.Trim().Length.ToString("0000000000.00000")); using (cn) { using (iDB2Command cmd = new iDB2Command(pgmParm, cn)) { try { cmd.ExecuteNonQuery(); } catch (Exception e) { rc = false; } } } // Return success or failure return(rc); }
/// <summary> /// Execute an iDB2Command (that returns no resultset) against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new iDB2Parameter(":prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</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(iDB2Connection connection, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters) { iDB2Command cmd = new iDB2Command(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return(val); }
public String CalcConsec(string tabla, int longitud) { String consecutivo = "0"; String consecutivoAct = "0"; StringBuilder query = new StringBuilder(); try { db.Open(); IDbTransaction idbt = db.BeginTransaction(IsolationLevel.RepeatableRead); query.Clear(); query.Append(" SELECT DIGITS(DEC(DEC(TRIM(CCDESC), " + longitud + ", 0) + 1, " + longitud + ", 0))"); query.Append(" FROM ZCC"); query.Append(" WHERE CCTABL = 'SECUENCE' AND CCCODE = '" + tabla + "'"); IDbCommand cmm = new iDB2Command(query.ToString(), db, idbt); IDataReader dr = cmm.ExecuteReader(); dr.Read(); consecutivo = dr.GetString(0); dr.Close(); query.Clear(); if (consecutivo != "0") { consecutivoAct = consecutivo; if (consecutivo == "99") { consecutivoAct = "00"; } query.Append(" UPDATE ZCC SET"); query.Append(" CCDESC = '" + consecutivoAct + "'"); query.Append(" WHERE CCTABL = 'SECUENCE' AND CCCODE = '" + tabla + "'"); cmm.CommandText = query.ToString(); int r = cmm.ExecuteNonQuery(); } idbt.Commit(); db.Close(); } catch (iDB2SQLErrorException ex) { db.Close(); string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } return(consecutivo); }
private void btnInsert_Click(object sender, EventArgs e) { try { string insertStatement = "INSERT INTO ITPA400.WorkOrder " + "(orderID, planeNo, taskID, strDat, endDat, woStat, partsLbr, lbrHours) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; iDB2Connection connection = new iDB2Connection("DataSource=deathstar.gtc.edu"); iDB2Command command = new iDB2Command(insertStatement, connection); command.Parameters.Add(new iDB2Parameter("orderID", iDB2DbType.iDB2Char, 10)); command.Parameters.Add(new iDB2Parameter("planeNo", iDB2DbType.iDB2Char, 10)); command.Parameters.Add(new iDB2Parameter("taskID", iDB2DbType.iDB2Char, 10)); command.Parameters.Add(new iDB2Parameter("strDat", iDB2DbType.iDB2Date)); command.Parameters.Add(new iDB2Parameter("endDat", iDB2DbType.iDB2Date)); command.Parameters.Add(new iDB2Parameter("woStat", iDB2DbType.iDB2Char, 10)); command.Parameters.Add(new iDB2Parameter("partsLbr", iDB2DbType.iDB2Decimal, 10)); command.Parameters.Add(new iDB2Parameter("lbrHours", iDB2DbType.iDB2Decimal, 7)); command.Parameters["orderID"].Value = txtWrkOrdID.Text; command.Parameters["planeNo"].Value = cbxPlaneNo.Text; command.Parameters["taskID"].Value = cbxTaskID.Text; command.Parameters["strDat"].Value = dteStart.Value; command.Parameters["endDat"].Value = dteEnd.Checked ? dteEnd.Value : iDB2Date.Null; command.Parameters["woStat"].Value = txtStatus.Text; command.Parameters["partsLbr"].Value = numCost.Value; command.Parameters["lbrHours"].Value = numHours.Value; command.Connection.Open(); if (command.ExecuteNonQuery() == 1) { labelResult.Visible = true; labelResult.Text = "Row successfully inserted"; labelResult.ForeColor = Color.Green; } else { labelResult.Visible = true; labelResult.Text = "Row insertion failed"; labelResult.ForeColor = Color.Red; } connection.Close(); } catch (iDB2Exception ex) { MessageBox.Show(ex.MessageDetails); } }
/// <summary> /// Execute a database query which does not include a select /// </summary> /// <param name="connString">Connection string to database</param> /// <param name="cmdType">Command type either stored procedure or SQL</param> /// <param name="cmdText">Acutall SQL Command</param> /// <param name="commandParameters">Parameters to bind to the command</param> /// <returns></returns> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params iDB2Parameter[] commandParameters) { // Create a new iDB2 command iDB2Command cmd = new iDB2Command(); //Create a connection using (iDB2Connection connection = new iDB2Connection(connectionString)) { //Prepare the command PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); //Execute the command int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return(val); } }
public string ejecutarInsertUpdateDelete(string consulta) { try { int resul = 0; AS400ConnectionString.Open(); iDB2Command command = AS400ConnectionString.CreateCommand(); command = new iDB2Command(consulta, AS400ConnectionString); resul = command.ExecuteNonQuery(); return(resul.ToString()); } catch (Exception ex) { throw new Exception("Se produjo un problema al realizar un insert en AS400: ", ex); } finally { AS400ConnectionString.Close(); } }
/// <summary> /// Executes the command object using executenonquery method and /// returns a dataset with a table with single column and row ROWS_AFFECTED /// </summary> /// <param name="commandToExecute">The command to execute.</param> /// <returns>A dataset with a table with single column and row ROWS_AFFECTED</returns> private DataSet ExecuteNonQuery(iDB2Command commandToExecute) { DataSet outputDataSet = null; int rowsAffected = commandToExecute.ExecuteNonQuery(); DataTable tempDataTable = new DataTable(); tempDataTable.Columns.Add("ROWS_AFFECTED", typeof(string)); DataRow tempDataRow = null; tempDataRow = tempDataTable.NewRow(); tempDataRow["ROWS_AFFECTED"] = rowsAffected; tempDataTable.Rows.Add(tempDataRow); outputDataSet = new DataSet(); outputDataSet.Tables.Add(tempDataTable); tempDataTable = null; return(outputDataSet); }
// DELETE api/values/5 public void Delete(int id) { string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString; iDB2Connection connection = new iDB2Connection(connString); connection.Open(); iDB2Command db2Command = new iDB2Command(); string query = "DELETE FROM DAVIDEC1.CONTACTOS " + "WHERE ID = @id"; db2Command.Connection = connection; db2Command.CommandText = query; db2Command.Parameters.Add(new iDB2Parameter("@id", id)); db2Command.ExecuteNonQuery(); connection.Close(); }
private void btn_cancella_Click(object sender, EventArgs e) { if (dataGridView1.SelectedRows.Count > 0) { Program._Stato = "M"; var riga = dataGridView1.SelectedRows[0].Index; var _risposta = MessageBox.Show("Conferma Cancellazione Ordine n." + dataGridView1[1, riga].Value.ToString() + Environment.NewLine + " Del " + dataGridView1[2, riga].Value.ToString() + Environment.NewLine + " Di " + dataGridView1[3, riga].Value.ToString(), "Cancellazione Ordine", MessageBoxButtons.YesNo); if (_risposta == DialogResult.Yes) { iDB2Connection DBCONN = new iDB2Connection(Program.myConnString); DBCONN.Open(); iDB2Command myCommand = new iDB2Command(); myCommand.Connection = DBCONN; myCommand.CommandText = "delete from " + " $emiedati.oda200f " + " where nord = " + dataGridView1[1, riga].Value.ToString(); try { myCommand.ExecuteNonQuery(); } catch (Exception err) { MessageBox.Show(err.Message); } Ricarica(); } } else { MessageBox.Show("Selezionare l'ordine da cancellare"); } }
public void ActualizarINTF(int NUMID) { iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString); try { iDB2Command cmd = new iDB2Command("UPDATE RZINTF SET ESTINT = 'C' WHERE NUMID = @NUMID", cn); cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@NUMID", SqlDbType.VarChar).Value = NUMID; cn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { } finally { cn.Close(); } }
// POST api/values public void Post([FromBody] Contacto oContacto) { string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString; iDB2Connection connection = new iDB2Connection(connString); connection.Open(); iDB2Command db2Command = new iDB2Command(); string query = "INSERT INTO DAVIDEC1.CONTACTOS " + "(NOMBRE, TELEFONO, CORREO, EDAD) " + "VALUES (@nombre, @telefono, @correo, @edad)"; db2Command.Connection = connection; db2Command.CommandText = query; db2Command.Parameters.Add(new iDB2Parameter("@nombre", oContacto.Nombre)); db2Command.Parameters.Add(new iDB2Parameter("@telefono", oContacto.Telefono)); db2Command.Parameters.Add(new iDB2Parameter("@correo", oContacto.Correo)); db2Command.Parameters.Add(new iDB2Parameter("@edad", oContacto.Edad)); db2Command.ExecuteNonQuery(); connection.Close(); }
/// <summary> /// Executes a statement on the IBM i that doesn't return data (INSERT, UPDATE, DELETE) /// </summary> /// <param name="connString">The connection string</param> /// <param name="sqlStatement">The SQL statement</param> /// <param name="parameters">The parameters (iDB2Parameter)</param> public void ExecuteNonQuery(string sqlStatement, Action <iDB2ParameterCollection> parameters) { using (iDB2Command cmd = new iDB2Command(sqlStatement, conn)) { if (parameters != null) { parameters(cmd.Parameters); } try { cmd.ExecuteNonQuery(); } catch (iDB2SQLErrorException e) { throw e; } catch (Exception e) { throw e; } } }
//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); } }
private bool ExecuteQuery(string strQuery) { try { lstResponse.Add(SetResponse(strQuery, string.Empty, ResponseStatusType.LOG)); iDB2Command sqlCMD = new iDB2Command(strQuery, objSQLCon); if (objSQLCon.State == ConnectionState.Closed) { objSQLCon.Open(); } sqlCMD.ExecuteNonQuery(); if (objSQLCon.State == ConnectionState.Open) { objSQLCon.Close(); } return(true); } catch (iDB2Exception ex) { if (objSQLCon.State == ConnectionState.Open) { objSQLCon.Close(); } lstResponse.Add(SetResponse(strQuery, ex.Message, ResponseStatusType.CRITICAL)); return(false); } catch (Exception ex) { if (objSQLCon.State == ConnectionState.Open) { objSQLCon.Close(); } lstResponse.Add(SetResponse(strQuery, ex.Message, ResponseStatusType.CRITICAL)); return(false); } }
public bool WriteData(string strSQL, CommandType intCmdType) { iDB2Connection objCn; try { objCn = new iDB2Connection(strConn); objCn.Open(); objCommand = new iDB2Command(strSQL, objCn); objCommand.CommandType = intCmdType; objCommand.CommandText = strSQL; intNumRecords = objCommand.ExecuteNonQuery(); } catch (Exception ex) { objException = ex; return(false); } finally { } return(true); }
private void button1_Click(object sender, EventArgs e) { // svuota file di passaggio iDB2Connection DBCONN = new iDB2Connection(Program.myConnString); DBCONN.Open(); iDB2Command myCommand = new iDB2Command(); myCommand.Connection = DBCONN; myCommand.CommandText = "delete from ODA202P "; myCommand.ExecuteNonQuery(); myCommand.CommandText = "SELECT ACRAG1, ACRAG2, ACVIA, ACCAP, ACCITT, ACPROV " + "FROM $d_emil.acf00f " + " WHERE ACTPCD = 'F' and ACSCON = '" + _cf + "'"; iDB2DataReader myReader = myCommand.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(myReader); string _rg1 = ""; string _rg2 = ""; string _via = ""; string _cap = ""; string _cit = ""; string _prov = ""; foreach (DataRow r in dt.Rows) { _rg1 = r[0].ToString().Replace("'", "''"); _rg2 = r[1].ToString().Replace("'", "''"); _via = r[2].ToString().Replace("'", "''"); _cap = r[3].ToString().Substring(0, 5).Replace("'", "''"); _cit = r[4].ToString().Replace("'", "''"); _prov = r[5].ToString().Substring(0, 2).Replace("'", "''"); } for (int i = 0; i < DGV_dettaglio.Rows.Count; i++) { if (DGV_dettaglio.Rows[i].Cells[3].Value != null) { myCommand.CommandText = "insert into ODA202P " + "(NORD,PROG, DORD, DTIP, CFOR, ACRAG1," + "ACRAG2, ACVIA, ACCAP, ACCIT, ACPROV, DRIC,CART," + "ARDSAR, ARUNMI, QORD) " + " values('" + _n + "','" + DGV_dettaglio.Rows[i].Cells[7].Value.ToString() + "','" + _do.Replace("/", "") + "','F'," + "'" + _cf + "','" + _f + "','" + _rg2 + "','" + _via + "','" + _cap + "','" + _cit + "','" + _prov + "','" + DGV_dettaglio.Rows[i].Cells[6].Value.ToString().Replace("/", "") + "'," + "'" + DGV_dettaglio.Rows[i].Cells[0].Value.ToString() + "'," + "'" + DGV_dettaglio.Rows[i].Cells[1].Value.ToString() + "'," + "'" + DGV_dettaglio.Rows[i].Cells[2].Value.ToString() + "','" + DGV_dettaglio.Rows[i].Cells[3].Value.ToString().Replace(",", ".") + "')"; myCommand.ExecuteNonQuery(); //MessageBox.Show(dataGridView1.Rows[i].Cells[3].Value.ToString()); } } Frm_ModuloOdf Podf = new Frm_ModuloOdf(); Podf.WindowState = FormWindowState.Maximized; Podf.ShowDialog(); }
public void upsertGroup(Group group) { //Create connection to the iSeries iDB2Connection cn = new iDB2Connection(); cn.ConnectionString = connectionString; //Create query iDB2Command cmd = new iDB2Command(); cmd.CommandText = "merge into issysdta.shoreteleccinboundgroupdaily as t " + "using ( values( cast(@groupId as smallint) " + ",cast(@groupName as varchar(40)) " + ",cast(@queuedCallsAboveTasa as smallint) " + ",cast(@queuedCalls as smallint) " + ",cast(@maxQueTime as integer) " + ",cast(@agentsLoggedIn as smallint) " + ",cast(@agentsIdle as smallint) " + ",cast(@acdInCalls as integer) " + ",cast(@tsf as smallint) " + ",cast(@abandonedCalls as integer) " + ")) " + "as zac(group_id " + ", group_name " + ", queued_calls_above_tasa " + ", queued_calls " + ", max_que_time " + ", agents_logged_in " + ", agents_idle " + ", acd_in_calls " + ", tsf " + ", abandoned_calls) " + "on t.group_id = zac.group_id " + "when matched then update " + "set t.group_name = zac.group_name " + ", t.queued_calls_above_tasa = zac.queued_calls_above_tasa " + ", t.queued_calls = zac.queued_calls " + ", t.max_que_time = zac.max_que_time " + ", t.agents_logged_in = zac.agents_logged_in " + ", t.agents_idle = zac.agents_idle " + ", t.acd_in_calls = zac.acd_in_calls " + ", t.tsf = zac.tsf " + ", t.abandoned_calls = zac.abandoned_calls " + "when not matched then " + "insert (group_id " + ", group_name " + ", queued_calls_above_tasa " + ", queued_calls " + ", max_que_time " + ", agents_logged_in " + ", agents_idle " + ", acd_in_calls " + ", tsf " + ", abandoned_calls) " + "values (zac.group_id " + ", zac.group_name " + ", zac.queued_calls_above_tasa " + ", zac.queued_calls " + ", zac.max_que_time " + ", zac.agents_logged_in " + ", zac.agents_idle " + ", zac.acd_in_calls " + ", zac.tsf " + ", zac.abandoned_calls) "; cmd.Connection = cn; cn.Open(); cmd.DeriveParameters(); cmd.Parameters["@groupName"].Value = group.GroupName; cmd.Parameters["@queuedCallsAboveTasa"].Value = Convert.ToInt32(group.InboundAboveTargetAverageSpeedAnswer); cmd.Parameters["@queuedCalls"].Value = Convert.ToInt32(group.InboundCallsInQueue); cmd.Parameters["@maxQueTime"].Value = Convert.ToInt32(group.InboundMaxInQueue); cmd.Parameters["@agentsLoggedIn"].Value = group.NumberOfAgents; cmd.Parameters["@agentsIdle"].Value = group.NumberIdle; cmd.Parameters["@acdInCalls"].Value = group.AutomaticCallDistribution; cmd.Parameters["@tsf"].Value = group.TargetServiceFactor; cmd.Parameters["@abandonedCalls"].Value = group.AbandonedCalls; cmd.Parameters["@groupId"].Value = group.Id; cmd.ExecuteNonQuery(); //Clean up - Close connections cmd.Dispose(); cn.Close(); }
private static void UpdateFoundNotPart(string guid) { using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"])) { string query = GetPartNotFoundUpdateMasterString(); var objDataAdapter = new iDB2DataAdapter(); var cmd = new iDB2Command(query, conn); cmd.Connection.Open(); objDataAdapter.UpdateCommand = cmd; objDataAdapter.UpdateCommand.CommandTimeout = 0; cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char); cmd.Parameters["@guid"].Value = guid; cmd.Parameters.Add("@retrn", iDB2DbType.iDB2Char); cmd.Parameters["@retrn"].Value = 'R'; cmd.ExecuteNonQuery(); cmd.Connection.Close(); } }
private void Btn_inserisci_Click(object sender, EventArgs e) { Stato _stato = new Stato(); if (Program._Stato == _stato.Inserisci) { iDB2Connection DBCONN = new iDB2Connection(Program.myConnString); DBCONN.Open(); iDB2Command myCommand = new iDB2Command(); myCommand.Connection = DBCONN; myCommand.CommandText = "insert into " + " $emiedati.oda200f " + " (NORD,PROG,DORD,DTIP,CFOR,DRIC,CART,QORD,QRIC,QSTA) " + " values(" + _n + "," + (PrimoNumeroLibero() + 10) + "," + Convert.ToDateTime(_do).ToString("yyyyMMdd") + "," + "'F','" + _cf + "'," + DTPRichiesta.Value.ToString("yyyyMMdd") + ",'" + CMB_Articolo.SelectedValue + "'," + Convert.ToDecimal(txt_qta.Text) + "," + 0 + ",'" + _stato.Inserisci + "'" + ") "; try { myCommand.ExecuteNonQuery(); } catch (Exception err) { MessageBox.Show(err.Message); } DBCONN.Close(); Ricarica(); } if (Program._Stato == _stato.Modifica) { if (txt_riga.Text != _r) { if (ControllaDoppi()) { MessageBox.Show("Progressivo esiste già modificare progressivo"); return; } } iDB2Connection DBCONN = new iDB2Connection(Program.myConnString); DBCONN.Open(); iDB2Command myCommand = new iDB2Command(); myCommand.Connection = DBCONN; if (chkDel.Checked) { if (MessageBox.Show("Confermi la cancellazione della riga?", "Cancella Riga", MessageBoxButtons.YesNo) == DialogResult.No) { return; } else { myCommand.CommandText = "delete from " + " $emiedati.oda200f " + " where nord = " + _n + " and prog = " + _r; } } else { myCommand.CommandText = "update " + " $emiedati.oda200f " + " set " + " cart = '" + CMB_Articolo.SelectedValue + "'," + " qord = " + txt_qta.Text.Replace(",", ".") + "," + " dric = " + DTPRichiesta.Value.ToString("yyyyMMdd") + "," + " qric = " + TxtQtaEvasione.Text.Replace(",", ".") + "," + " qsta = '" + CMB_tipoEvasione.SelectedValue + "'," + " prog = " + Convert.ToDecimal(txt_riga.Text) + " where nord = " + _n + " and prog = " + _r; } try { myCommand.ExecuteNonQuery(); } catch (Exception err) { MessageBox.Show(err.Message); return; } DBCONN.Close(); Ricarica(); StatoModifica(); //Btn_inserisci.Enabled = false; //label1.Visible = false; //label2.Visible = false; //label3.Visible = false; //DTPRichiesta.Visible = false; //CMB_Articolo.Visible = false; //txt_qta.Visible = false; } }
//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 static void UpdateFoundPart(string guid, GetMasterResult getResult) { using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"])) { string query = GetPartFoundUpdateMasterString(); var objDataAdapter = new iDB2DataAdapter(); var cmd = new iDB2Command(query, conn); cmd.Connection.Open(); objDataAdapter.UpdateCommand = cmd; objDataAdapter.UpdateCommand.CommandTimeout = 0; cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char); cmd.Parameters["@guid"].Value = guid; cmd.Parameters.Add("@usrid", iDB2DbType.iDB2Char); cmd.Parameters["@usrid"].Value = (getResult.UserID.Trim().Length > 0) ? getResult.UserID : " "; cmd.Parameters.Add("@branded", iDB2DbType.iDB2Char); cmd.Parameters["@branded"].Value = getResult.Branded; cmd.Parameters.Add("@comcode", iDB2DbType.iDB2Char); cmd.Parameters["@comcode"].Value = (getResult.Commodity_Code.Trim().Length > 0) ? getResult.Commodity_Code : " "; cmd.Parameters.Add("@level", iDB2DbType.iDB2Integer); cmd.Parameters["@level"].Value = getResult.Level; cmd.Parameters.Add("@status", iDB2DbType.iDB2Char); cmd.Parameters["@status"].Value = (getResult.Status.Trim().Length > 0) ? getResult.Status : " "; cmd.Parameters.Add("@rtnble", iDB2DbType.iDB2Char); cmd.Parameters["@rtnble"].Value = getResult.Returnable; cmd.Parameters.Add("@tariffcd", iDB2DbType.iDB2Char); cmd.Parameters["@tariffcd"].Value = (getResult.Tariff_Code.Trim().Length > 0) ? getResult.Tariff_Code : " "; cmd.Parameters.Add("@amsc", iDB2DbType.iDB2Char); cmd.Parameters["@amsc"].Value = (getResult.AMSC.Trim().Length > 0) ? getResult.AMSC : " "; cmd.Parameters.Add("@tqty", iDB2DbType.iDB2Integer); cmd.Parameters["@tqty"].Value = getResult.Technical_Qty; cmd.Parameters.Add("@svclife", iDB2DbType.iDB2Integer); cmd.Parameters["@svclife"].Value = getResult.Service_Life; cmd.Parameters.Add("@pkgcode", iDB2DbType.iDB2Char); cmd.Parameters["@pkgcode"].Value = (getResult.Package_Code.Trim().Length > 0) ? getResult.Package_Code : " "; cmd.Parameters.Add("@info", iDB2DbType.iDB2Char); cmd.Parameters["@info"].Value = (getResult.Information.Trim().Length > 0) ? getResult.Information : " "; cmd.Parameters.Add("@retrn", iDB2DbType.iDB2Char); cmd.Parameters["@retrn"].Value = 'R'; cmd.ExecuteNonQuery(); cmd.Connection.Close(); } }
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."); } }
private static void InsertCrossParts(string guid, GetMasterResult getResult) { foreach (CrossPart s in getResult.CrossPartList) { using ( var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"])) { string query = GetCrossPartInsertString(); var objDataAdapter = new iDB2DataAdapter(); var cmd = new iDB2Command(query, conn); cmd.Connection.Open(); objDataAdapter.InsertCommand = cmd; objDataAdapter.InsertCommand.CommandTimeout = 0; cmd.Parameters.Add("@guid", iDB2DbType.iDB2Char); cmd.Parameters["@guid"].Value = guid; cmd.Parameters.Add("@item", iDB2DbType.iDB2Char); cmd.Parameters["@item"].Value = s.PartNumber; cmd.Parameters.Add("@brand", iDB2DbType.iDB2Char); cmd.Parameters["@brand"].Value = s.Brand; cmd.ExecuteNonQuery(); cmd.Connection.Close(); } } }
private void updateCampaignMetricData(string metric, string callMethod, string campaign) { //Create connection to the iSeries iDB2Connection cn = new iDB2Connection(); cn.ConnectionString = connectionString; //Create query iDB2Command cmd = new iDB2Command(); cmd.CommandText = "update monprddta.campaignmetrics set Metric = @metric where CallType = @callMethod and Campaign = @campaign"; cmd.Connection = cn; cn.Open(); cmd.DeriveParameters(); cmd.Parameters["@metric"].Value = Convert.ToDecimal(metric); cmd.Parameters["@callMethod"].Value = callMethod; cmd.Parameters["@campaign"].Value = campaign; cmd.ExecuteNonQuery(); //Clean up - Close connections cmd.Dispose(); cn.Close(); }
/// <summary> /// Executes a stored procedure on the IBM i that doesn't return data. /// </summary> /// <param name="connString">The connection string</param> /// <param name="sqlStatement">The SQL statement</param> /// <param name="parameters">The parameters (iDB2Parameter)</param> public void ExecuteStoredProcedure(string sqlStatement, Action<iDB2ParameterCollection> parameters) { using (iDB2Command cmd = new iDB2Command(sqlStatement, conn)) { cmd.CommandType = CommandType.StoredProcedure; if (parameters != null) { parameters(cmd.Parameters); } try { cmd.ExecuteNonQuery(); } catch (iDB2SQLErrorException e) { throw e; } catch (Exception e) { throw e; } } }