void impostaarticolo()
{
Caricamento carica = new Caricamento();
carica.Show();
Application.DoEvents();
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT ararti, ardsar" +
" FROM $emiedati.art00f a " +
" order by ardsar";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
CMB_Articolo.DataSource = dt;
CMB_Articolo.DisplayMember = "ardsar";
CMB_Articolo.ValueMember = "ararti";
carica.Close();
DBCONN.Close();
}
/// <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;
}
private int PrimoNumeroLibero()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT max(o.prog) " +
" FROM $EMIEDATI.oda200f o " +
" where NORD = " + _n;
iDB2DataReader myReader = myCommand.ExecuteReader();
if (myReader.HasRows)
{
while (myReader.Read())
{
try
{ return(myReader.GetInt32(0)); }
catch
{ return(0); }
}
}
else
{
return(0);
}
DBCONN.Close();
return(0);
}
private void SceltaFornitore_Load(object sender, EventArgs e)
{
Caricamento carica = new Caricamento();
carica.Show();
Application.DoEvents();
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT acscon, acrag1" +
" FROM $d_emil.acf00f a " +
" where actpcd = 'F'" +
" order by acrag1";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
Cmb_Fornitore.DataSource = dt;
Cmb_Fornitore.DisplayMember = "acrag1";
Cmb_Fornitore.ValueMember = "acscon";
carica.Close();
DBCONN.Close();
//primo numero libero
lbl_ordine.Text = Convert.ToString(PrimoNumeroLibero() + 1);
}
private void getGroupIds()
{
//remove all preexisting data from the list
groupIds.Clear();
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "Select group_id from ISSYSDTA.SHORETELINBOUNDGROUPS";
cmd.Connection = cn;
cn.Open();
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results
while (readFile.Read())
{
groupIds.Add(readFile.GetiDB2Integer(0));
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
}
private void SubmitBtn_Click(object sender, EventArgs e)
{
if (AnDText.Text.Length > 0 && AnDText.Text != null)
{
AnDBox.Items.Clear();
AnDBox.Items.Add("FLIGHTS");
try
{
SQL = "select f.FTRTID, r.rtardepart,r.rtararrivl,f.flightno from flight2019.flight f inner join flight2019.route r on f.FTRTID = r.RTID where f.FLIGHTNO='";
SQL += AnDText.Text + "'";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
String departcity = "ERROR IF UNCHANGED";
String arrivalcity = "ERROR IF UNCHANGED";
arcodes.TryGetValue(dataReader.GetString(1), out departcity);
arcodes.TryGetValue(dataReader.GetString(2), out arrivalcity);
AnDBox.Items.Add("Flight ID: " + dataReader.GetString(0) + " Departing: " + departcity.Trim() + " Arriving at: " + arrivalcity.Trim());
}
connection.Close();
}
catch (Exception ex)
{
AnDBox.Items.Add(ex.Message);
}
}
}
private void DspPlane_Click(object sender, EventArgs e)
{
try
{
//Select everything within the AIRPLANE table
SQL = "Select * FROM AIRPLANE";
comm = new iDB2Command(SQL, conn);
conn.Open();
reader = comm.ExecuteReader();
while (reader.Read())
{
//Use reader and the listbox to display all non null values in the table
DisplayPlane.Items.Add("Plane#: " + reader.GetString(0) + " Max Flight Dist: "
+ reader.GetString(1) + " FClass: " + reader.GetString(2) + " CClass: "
+ reader.GetString(3) + " PAvail: " + reader.GetString(4) + " MaintMiles: "
+ reader.GetString(5) + " PlaneModel: " + reader.GetString(6) + " PlaneMake: "
+ reader.GetString(7) + " APARCD: " + reader.GetString(8) + " Date Maintenence: "
+ reader.GetString(12));
}
conn.Close();
}
//Any DB error catches
catch (iDB2Exception ie)
{
DisplayPlane.Items.Add(ie.Message);
}
catch (Exception ex)
{
DisplayPlane.Items.Add(ex.Message);
}
}
public string ObtenerNaveViaje(string ordenServicio)
{
string NaveViaje = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_OBTENERNAVE_VIAJE", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("XNORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
NaveViaje = (dr.IsDBNull(dr.GetOrdinal("TCMPVP")) ? "" : dr.GetString(dr.GetOrdinal("TCMPVP")).Trim()) + " - " + (dr.IsDBNull(dr.GetOrdinal("NVJES")) ? "" : dr.GetString(dr.GetOrdinal("NVJES")));
}
}
catch (Exception ex)
{
NaveViaje = "";
}
finally
{
cn.Close();
}
return(NaveViaje);
}
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 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 string ObtenerRazonSocialAS400(string ruc)
{
string dResultado = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
try
{
iDB2Command cmd = new iDB2Command("SELECT * FROM RZZM01 WHERE NRUC = @Ruc FETCH FIRST 1 ROWS ONLY", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Ruc", SqlDbType.VarChar).Value = ruc;
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dResultado = dr.IsDBNull(dr.GetOrdinal("TCMPCL")) ? "" : dr.GetString(dr.GetOrdinal("TCMPCL"));
}
}
catch (Exception ex)
{
dResultado = "";
}
finally
{
cn.Close();
}
return(dResultado);
}
private void CaricaFornitore()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT o.qsta,o.Nord," +
"concat(concat(substring(o.dord, 7, 2), '/') , " +
"concat(concat(substring(o.dord, 5, 2) , '/') , " +
"substring(o.dord, 1, 4)))," +
" a.acrag1 " +
" FROM $EMIEDATI.oda200f o " +
" left join $d_emil.acf00f a " +
" on o.dtip = a.actpcd and o.cfor = a.acscon " +
" where o.qsta <> 'X'" +
" group by o.qsta,o.Nord,o.dord, a.acrag1";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
//dataGridView1.DataSource = dt;
//dataGridView1.Columns[0].HeaderText = "Stato";
//dataGridView1.Columns[1].HeaderText = "Numero Ordine";
//dataGridView1.Columns[2].HeaderText = "Data Ordine";
//dataGridView1.Columns[3].HeaderText = "Fornitore";
//for (int i = 0; i < dataGridView1.Columns.Count; i++)
// dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
}
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);
}
public T QuerySingleValue <T>(iDB2Connection connection, string queryString, params KeyValuePair <string, KeyValuePair <iDB2DbType, object> >[] queryParams)
{
iDB2Command selectCommand = GenerateCommand(connection, queryString, queryParams);
object value = selectCommand.ExecuteScalar();
return((T)Convert.ChangeType(value, typeof(T)));
}
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 iDB2DataReader GetDataReaderFromQuery(string strQuery, CommandType intCommandType)
{
var objCmd = default(iDB2Command);
iDB2DataReader objDr;
var objConn = default(iDB2Connection);
try
{
objConn = new iDB2Connection(strConn);
objCmd = new iDB2Command(strQuery, objConn);
objCmd.CommandType = intCommandType;
objConn.Open();
objDr = objCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
objDr = default;
if (objCmd is object)
{
objCmd.Dispose();
}
if (objConn is object)
{
objConn.Close();
objConn.Dispose();
}
objException = ex;
}
return(objDr);
}
public T QuerySingleValue <T>(string queryString, params KeyValuePair <string, object>[] queryParams)
{
using (iDB2Connection connection = GetConnection())
{
try
{
iDB2Command selectCommand = GenerateCommand(connection, queryString, queryParams);
object value = selectCommand.ExecuteScalar();
try
{
return((T)Convert.ChangeType(value, typeof(T)));
}
catch
{
return(default(T));
}
}
catch (Exception ex)
{
WriteLog(ex, queryString, queryParams);
throw;
}
finally
{
ConnectionClose(connection);
}
}
}
public iDB2DataReader GetDataReaderFromProcedureOnTrans(string strSQLWithoutCALLString, iDB2Parameter[] sqlParameters)
{
var objCmd = default(iDB2Command);
iDB2DataReader drProcedure;
try
{
objCmd = new iDB2Command("{CALL " + strSQLWithoutCALLString + "}", objConnection);
objCmd.Transaction = objTransaction;
objCmd.CommandType = CommandType.StoredProcedure;
foreach (var objPar in sqlParameters)
{
objCmd.Parameters.Add(objPar);
}
drProcedure = objCmd.ExecuteReader();
}
catch (Exception ex)
{
drProcedure = default;
if (objCmd is object)
{
objCmd.Dispose();
}
objException = ex;
}
return(drProcedure);
}
/// <summary>
/// With the sql string and the active connection retrieve the data
/// </summary>
/// <param name="sql"></param>
/// <param name="conn"></param>
/// <returns></returns>
private static iDB2DataReader ExecuteSql(string sql, iDB2Connection conn)
{
iDB2DataReader d_rdr = null;
try
{
using (iDB2Command cmd = new iDB2Command(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
d_rdr = cmd.ExecuteReader();
}
}
catch (iDB2DCFunctionErrorException ex)
{
throw new FaultException <DatabaseFault>(new DatabaseFault()
{
DbOperation = "Reading the database",
DbReason = "Exception accessing database",
DbMessage = ex.InnerException.Message
}, "Reading db error");
}
catch (iDB2SQLErrorException ex)
{
throw new FaultException <DatabaseFault>(new DatabaseFault()
{
DbOperation = "Sql error",
DbReason = "Exception accessing database",
DbMessage = ex.Message
}, "Sql error");
}
return(d_rdr);
}
//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);
}
}
private void SubmitBtn2_Click(object sender, EventArgs e)
{
if (CrewText.Text != null && CrewText.Text.Length > 0)
{
CrewBox.Items.Clear();
CrewBox.Items.Add("CREW FOR FLIGHT: " + CrewText.Text);
try
{
SQL = "select f.flightno, c.pilots,c.attendants,c.resclerk,c.maint,c.janitor,c.refueling from flight2019.flight f inner join flight2019.crew c on f.flightno = c.flightno where f.FLIGHTNO='";
SQL += CrewText.Text + "'";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
CrewBox.Items.Add("Pilots: " + dataReader.GetString(1));
CrewBox.Items.Add("Attendants: " + dataReader.GetString(2));
CrewBox.Items.Add("ResClerks: " + dataReader.GetString(3));
CrewBox.Items.Add("Maint: " + dataReader.GetString(4));
CrewBox.Items.Add("Janitor: " + dataReader.GetString(5));
CrewBox.Items.Add("Refueling: " + dataReader.GetString(6));
}
connection.Close();
}
catch (Exception ex)
{
CrewBox.Items.Add(ex.Message);
}
}
}
public T QuerySingleValue <T>(iDB2Connection connection, string queryString)
{
iDB2Command selectCommand = GenerateCommand(connection, queryString);
object value = selectCommand.ExecuteScalar();
return((T)Convert.ChangeType(value, typeof(T)));
}
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 static DataSet GetDataSet(string commandText, CommandType commandType, iDB2Parameter[] parameters = null)
{
try
{
using (iDB2Connection connection = new iDB2Connection(ConfigurationManager.ConnectionStrings["DB2"].ConnectionString))
{
connection.Open();
using (iDB2Command command = new iDB2Command(commandText, commandType, connection))
{
command.CommandTimeout = 300;
if (parameters != null)
{
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
var dataset = new DataSet();
iDB2DataAdapter dataAdaper = new iDB2DataAdapter(command);
dataAdaper.Fill(dataset);
connection.Close();
return(dataset);
}
}
}
catch (Exception ex)
{
throw;
}
}
private int PrimoNumeroLibero()
{
iDB2Connection DBCONN = new iDB2Connection(Program.myConnString);
DBCONN.Open();
iDB2Command myCommand = new iDB2Command();
myCommand.Connection = DBCONN;
myCommand.CommandText = "SELECT max(o.nord)" +
" FROM $EMIEDATI.oda200f o ";
iDB2DataReader myReader = myCommand.ExecuteReader();
DataTable dt = new DataTable();
if (myReader.HasRows)
{
return(myReader.GetInt32(0));
}
else
{
return(0);
}
}
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
/// <returns>DataTable</returns>
public Object GetValue(string sqlStatement, Action <iDB2ParameterCollection> parameters)
{
Object obj;
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null)
{
parameters(cmd.Parameters);
}
try
{
obj = cmd.ExecuteScalar();
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return(obj);
}
private string obtenerTipoOperacion(string ordenServicio)
{
string tipoOperacion = "";
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SELECT CTPOOP FROM [email protected] WHERE NORSRN = @NORSRN", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@NORSRN", iDB2DbType.iDB2VarChar).Value = ordenServicio;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tipoOperacion = (dr.IsDBNull(dr.GetOrdinal("CTPOOP")) ? 1 : dr.GetInt32(dr.GetOrdinal("CTPOOP"))) == 1 ? "I" : "E";
}
}
catch (Exception ex)
{
tipoOperacion = "I";
}
finally
{
cn.Close();
}
return(tipoOperacion);
}
/// <summary>
/// Executes a stored procedure that returns a result set.
/// </summary>
/// <param name="connString">The conn string.</param>
/// <param name="sqlStatement">The SQL statement.</param>
/// <param name="parameters">The parameters.</param>
public DataTable ExecuteStoredProcedureWithResultSet(string sqlStatement, Action <iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
parameters(cmd.Parameters);
}
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return(dt);
}
public AllFlights()
{
InitializeComponent();
loadARCodes();
FlightBox.Items.Clear();
FlightBox.Items.Add("FLIGHTS");
try
{
SQL = "select f.FTRTID, r.rtardepart,r.rtararrivl from flight2019.flight f inner join flight2019.route r on FTRTID = RTID";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
String departcity = "ERROR IF UNCHANGED";
String arrivalcity = "ERROR IF UNCHANGED";
arcodes.TryGetValue(dataReader.GetString(1), out departcity);
arcodes.TryGetValue(dataReader.GetString(2), out arrivalcity);
FlightBox.Items.Add("Flight ID: " + dataReader.GetString(0) + " Departing: " + departcity.Trim() + " Arriving at: " + arrivalcity.Trim());
}
connection.Close();
}
catch (Exception ex)
{
FlightBox.Items.Add(ex.Message);
}
}
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
/// <returns>DataTable</returns>
public DataTable GetData(string sqlStatement, Action <iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null)
{
parameters(cmd.Parameters);
}
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return(dt);
}
public List <PendientesLlenos> ListaStockLlenos()
{
List <PendientesLlenos> dtResult = new List <PendientesLlenos>();
iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString);
iDB2Command cmd;
cmd = new iDB2Command("SP_CONSULTA_DSREPSTOCKCONTLLENOS_V2", cn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cn.Open();
iDB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
PendientesLlenos a = new PendientesLlenos();
a.YardCode = dr.IsDBNull(dr.GetOrdinal("NUMIDE")) ? "" : dr.GetString(dr.GetOrdinal("NUMIDE"));
a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON"));
dtResult.Add(a);
}
}
catch (Exception ex)
{
dtResult = null;
}
finally
{
cn.Close();
}
return(dtResult);
}
public AllAirports()
{
InitializeComponent();
AirportBox.Items.Clear();
AirportBox.Items.Add("AIRPORTS");
AirportBox.Items.Add("AIRPORT CODE, AIRPORT NAME");
try
{
SQL = "select ARCD,arnm from flight2019.airport";
connection = new iDB2Connection("datasource=deathstar.gtc.edu");
command = new iDB2Command(SQL, connection);
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
AirportBox.Items.Add(dataReader.GetString(0) + ", " + dataReader.GetString(1));
}
connection.Close();
}
catch (Exception ex)
{
AirportBox.Items.Add(ex.Message);
}
}
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
/// <returns>DataTable</returns>
public DataTable GetData(string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null) { parameters(cmd.Parameters); }
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return dt;
}
private void getCampaignData()
{
//remove all preexisting data from the list
campaignData.Clear();
// Create a DataSet to hold data from iSeries Table
DataSet dataStructure = new DataSet();
//Create a table to hold the iSeries data
DataTable dt = new DataTable("Campaigns");
dt.Columns.Add("CampaignName");
dt.Columns.Add("CampaignDisplayName");
dt.Columns.Add("CallMethod");
dt.Columns.Add("AccountsRemaining");
dt.Columns.Add("InitialAccounts");
//Add the datatable to the data set
dataStructure.Tables.Add(dt);
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "select m.rmp2prjt "
+ ",m.CallMethod "
+ ",COALESCE(c.cnt, 0) AS nowAccounts "
+ ",COALESCE(m.cnt, 0 ) AS initialAccounts "
+ "FROM "
+ "(SELECT rmp2prjt "
+ ",case when rmp2prjt = rmp3prjt Then 'I' when rmp2rflg in ('1', 'H') Then 'P' else 'M' end as CallMethod "
+ ",count(*) as cnt "
+ "FROM monprddta.crmmascopy "
+ "WHERE (rmp2prjt like 'MAS%' or rmp2prjt in ('MS2BW210', 'MSCYC21', 'M2GEN', 'M2GSP')) and rmncdt = @Date and rmp2rflg in ('1','H','M','h') and rmdnc = '' "
+ "GROUP BY rmp2prjt, case when rmp2prjt = rmp3prjt then 'I' when rmp2rflg in ('1','H') Then 'P' else 'M' end) m "
+ "LEFT JOIN "
+ "(SELECT rmp2prjt "
+ ",case when rmp2prjt = rmp3prjt Then 'I' when rmp2rflg in ('1', 'H') Then 'P' else 'M' end as CallMethod "
+ ",count(*) as cnt "
+ "FROM cdqfil.crmmas "
+ "WHERE (rmp2prjt like 'MAS%' or rmp2prjt in ('MS2BW210', 'MSCYC21', 'M2GEN', 'M2GSP')) and rmncdt = @Date and rmp2rflg in ('1','H','M','h') and rmdnc = '' "
+ "GROUP BY rmp2prjt, case when rmp2prjt = rmp3prjt then 'I' when rmp2rflg in ('1','H') Then 'P' else 'M' end) c on c.rmp2prjt = m.rmp2prjt and c.callmethod = m.callmethod";
cmd.Connection = cn;
//prepare for Date parameter
String date = long.Parse(DateTime.Today.Date.ToString("yyyyMMdd")).ToString();
cn.Open();
cmd.DeriveParameters();
cmd.Parameters["@Date"].Value = date;
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results into the data set
while (readFile.Read())
{
//Create a row to hold data
DataRow datarow = dataStructure.Tables["Campaigns"].NewRow();
datarow["CampaignName"] = readFile.GetString(0).Trim();
datarow["CallMethod"] = readFile.GetString(1);
datarow["AccountsRemaining"] = readFile.GetiDB2Integer(2);
datarow["InitialAccounts"] = readFile.GetiDB2Integer(3);
//add the row to the data table Campaigns
dataStructure.Tables["Campaigns"].Rows.Add(datarow);
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
foreach (DataRow row in dt.Rows)
{
switch (row["CampaignName"].ToString())
{
case "MAS":
row["CampaignDisplayName"] = "MAS";
sortId = 0;
break;
case "MASSP":
row["CampaignDisplayName"] = "MAS SP";
sortId = 1;
break;
case "MASSS":
row["CampaignDisplayName"] = "ALT";
sortId = 2;
break;
case "MASSPS":
row["CampaignDisplayName"] = "ALT SP";
sortId = 3;
break;
case "MASFC":
row["CampaignDisplayName"] = "FOCUS";
sortId = 4;
break;
case "MASSPFC":
row["CampaignDisplayName"] = "FOCUS SP";
sortId = 5;
break;
case "MASWC":
row["CampaignDisplayName"] = "WC";
sortId = 6;
break;
case "MASWCSP":
row["CampaignDisplayName"] = "WC SP";
sortId = 7;
break;
case "M2GEN":
row["CampaignDisplayName"] = "2G";
sortId = 8;
break;
case "M2GSP":
row["CampaignDisplayName"] = "2G SP";
sortId = 9;
break;
case "MASPS":
row["CampaignDisplayName"] = "BULK";
sortId = 10;
break;
case "2NDPASS":
row["CampaignDisplayName"] = "2NDPASS";
sortId = 11;
break;
case "INBOUND":
row["CampaignDisplayName"] = "INBOUND";
sortId = 12;
break;
default:
//nothing
break;
}
campaignData.Add(new Campaign(sortId
, row["CampaignName"].ToString()
, row["CampaignDisplayName"].ToString()
, row["CallMethod"].ToString()
, row["InitialAccounts"].ToString()
, row["AccountsRemaining"].ToString(), "0", "0"));
}
}
public List<PaymentDetail> GetGeniusCheques(string paymentIDs)
{
if (logger.IsDebugEnabled)
{
logger.Debug(string.Format("GetGeniusCheques({0})", paymentIDs));
}
this.BuildConnectionString();
List<PaymentDetail> paymentDetails = null;
ArgumentCheck.ArgumentNullOrEmptyCheck(this.GeniusConnectionString, Constants.ERRORMESSAGE_GENIUS_CONNECTIONSTRING);
ArgumentCheck.ArgumentNullOrEmptyCheck(this.GeniusQuery, Constants.ERRORMESSAGE_GENIUS_QUERY);
try
{
using (iDB2Connection conn = new iDB2Connection(this.GeniusConnectionString))
{
string selectSql = String.Format(this.GeniusQuery, this.GeniusSchema, paymentIDs);
using (iDB2Command cmd = new iDB2Command(selectSql, conn))
{
if (logger.IsDebugEnabled)
{
logger.Debug(string.Format("Genius Sql:", selectSql));
}
iDB2DataReader reader = null;
try
{
cmd.CommandTimeout = this.CommandTimeout ;
conn.Open();
if (logger.IsDebugEnabled)
{
logger.Debug("Connection opened");
}
reader = cmd.ExecuteReader();
while (reader.Read())
{
PaymentDetail paymentDetail = new PaymentDetail();
paymentDetail.PaymentRequestID = reader.GetInt64(0);
paymentDetail.ChequeNumber = reader.GetString(1).TrimEnd();
if (paymentDetails == null)
{
paymentDetails = new List<PaymentDetail>();
}
paymentDetails.Add(paymentDetail);
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetGeniusCheques - {0}", ex.Message));
throw ex;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetGeniusCheques - {0}", ex.Message));
throw ex;
}
if (logger.IsDebugEnabled)
{
logger.Debug(string.Format("AddErrorLogHeader({0}) => returns: paymnet details",paymentIDs));
}
return paymentDetails;
}
private void getCampaignMetricData()
{
//remove all preexisting data from the list
campaignMetrics.Clear();
//Create a DataSet to hold data from iSeries Table
DataSet dataStructure = new DataSet();
//Create a table to hold the iSeries data
DataTable dt = new DataTable("CampaignMetric");
dt.Columns.Add("CampaignName");
dt.Columns.Add("CampaignDisplayName");
dt.Columns.Add("CallMethod");
dt.Columns.Add("Metric");
//Add the datatable to the data set
dataStructure.Tables.Add(dt);
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "select * from monprddta.campaignmetrics";
cmd.Connection = cn;
cn.Open();
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results into the data set
while (readFile.Read())
{
//Create a row to hold data
DataRow datarow = dataStructure.Tables["CampaignMetric"].NewRow();
datarow["CallMethod"] = readFile.GetString(0).Trim();
datarow["CampaignName"] = readFile.GetString(1).Trim();
datarow["Metric"] = readFile.GetiDB2Decimal(2);
//add the row to the data table Campaigns
dataStructure.Tables["CampaignMetric"].Rows.Add(datarow);
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
foreach (DataRow row in dt.Rows)
{
switch (row["CampaignName"].ToString())
{
case "MAS":
row["CampaignDisplayName"] = "MAS";
break;
case "MASSP":
row["CampaignDisplayName"] = "MAS SP";
break;
case "MASSS":
row["CampaignDisplayName"] = "ALT";
break;
case "MASSPS":
row["CampaignDisplayName"] = "ALT SP";
break;
case "MASFC":
row["CampaignDisplayName"] = "FOCUS";
break;
case "MASSPFC":
row["CampaignDisplayName"] = "FOCUS SP";
break;
case "MASWC":
row["CampaignDisplayName"] = "WC";
break;
case "MASWCSP":
row["CampaignDisplayName"] = "WC SP";
break;
case "M2GEN":
row["CampaignDisplayName"] = "2G";
break;
case "M2GSP":
row["CampaignDisplayName"] = "2G SP";
break;
case "MASPS":
row["CampaignDisplayName"] = "BULK";
break;
case "2NDPASS":
row["CampaignDisplayName"] = "2NDPASS";
break;
case "INBOUND":
row["CampaignDisplayName"] = "INBOUND";
break;
default:
//nothing
break;
}
campaignMetrics.Add(new CampaignMetric(row["CampaignName"].ToString(), row["CampaignDisplayName"].ToString(), row["CallMethod"].ToString(), row["Metric"].ToString()));
}
}
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;
}
}
}
/// <summary>
/// Executes a stored procedure that returns a result set.
/// </summary>
/// <param name="connString">The conn string.</param>
/// <param name="sqlStatement">The SQL statement.</param>
/// <param name="parameters">The parameters.</param>
public DataTable ExecuteStoredProcedureWithResultSet(string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
DataTable dt = new DataTable();
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null) { parameters(cmd.Parameters); }
try
{
using (iDB2DataAdapter da = new iDB2DataAdapter(cmd)) { da.Fill(dt); }
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return dt;
}
static void Main(string[] args)
{
//Nomi tabelle
//SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DBNAME';
//Nomi colonne
//SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=? ORDER BY ORDINAL_POSITION;
//numerocolonne
//SELECT max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?;
Console.WriteLine("host: ");
string host = Console.ReadLine();
Console.WriteLine("username: "******"password: "******"DB Name: ");
string dbname = Console.ReadLine();
Console.WriteLine("Namespace: ");
string namespacename = Console.ReadLine();
conn = new iDB2Connection("Data Source="+host+";user id="+user+";password="******";");
conn.Open();
cmd = new iDB2Command("", conn);
cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + dbname + "'";
row = cmd.ExecuteReader();
List<string> tablenames = new List<string>();
while (row.Read())
{
tablenames.Add(row["TABLE_NAME"].ToString());
}
row.Close();
foreach (var nome in tablenames)
{
Console.WriteLine(nome);
cmd = new iDB2Command("", conn);
cmd.CommandText = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= '" + nome + "' AND TABLE_SCHEMA = '" + dbname + "' ORDER BY ORDINAL_POSITION";
List<string> columnNames = new List<string>();
List<string> columnDefaults = new List<string>();
List<string> areNullables = new List<string>();
List<string> dataTypes = new List<string>();
row = cmd.ExecuteReader();
while (row.Read())
{
columnNames.Add(row["COLUMN_NAME"].ToString());
columnDefaults.Add(row["COLUMN_DEFAULT"].ToString());
areNullables.Add(row["IS_NULLABLE"].ToString());
dataTypes.Add(row["DATA_TYPE"].ToString());
}
row.Close();
for (int k = 0; k < columnNames.Count; k++)
{
if (dataTypes[k].ToLower() == "varchar" || dataTypes[k].ToLower() == "date" || dataTypes[k].ToLower() == "timestamp" || dataTypes[k].ToLower() == "text" || dataTypes[k].ToLower() == "datetime" || dataTypes[k].ToLower() == "national character large object" || dataTypes[k].ToLower() == "national character varying")
dataTypes[k] = "string";
if (dataTypes[k].ToLower() == "tinyint" || dataTypes[k].ToLower() == "bigint" || dataTypes[k].ToLower() == "smallint" || dataTypes[k].ToLower() == "mediumint" || dataTypes[k].ToLower() == "bit" || dataTypes[k].ToLower() == "integer")
dataTypes[k] = "int";
if (dataTypes[k].ToLower() == "decimal" || dataTypes[k].ToLower() == "numeric")
dataTypes[k] = "double";
if (dataTypes[k].ToLower() == "character" || dataTypes[k].ToLower() == "character varying")
dataTypes[k] = "string";
if (columnNames[k].ToLower() == "class")
columnNames[k] = "class_var";
if (columnNames[k].ToLower() == "int")
columnNames[k] = "int_var";
}
string pathstring = "./";
pathstring = Path.Combine(pathstring, nome + ".cs");
if (!File.Exists(pathstring))
{
StreamWriter sw = File.AppendText(pathstring);
sw.WriteLine("using System;");
sw.WriteLine("using System.Collections.Generic;");
sw.WriteLine("using System.Linq;");
sw.WriteLine("using System.Threading.Tasks;");
sw.WriteLine("using System.Text;");
sw.WriteLine("using IBM.Data.DB2.iSeries;");
sw.WriteLine("");
sw.WriteLine("namespace " + namespacename);
sw.WriteLine("{");
sw.WriteLine("\tclass " + UppercaseFirst(nome));
sw.WriteLine("\t{");
int i = 0;
foreach (var columnName in columnNames)
{
string defaultvalue = "null";
if (columnDefaults[i] != "")
{
if (Regex.IsMatch(columnDefaults[i], @"\d"))
defaultvalue = columnDefaults[i];
else
defaultvalue = "\"" + columnDefaults[i] + "\"";
}
if ((dataTypes[i].ToLower() == "int" || dataTypes[i].ToLower() == "double") && columnDefaults[i] == "")
defaultvalue = "0";
if ((dataTypes[i].ToLower() == "char" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "char" && columnDefaults[i].ToLower() == ""))
defaultvalue = "Char.MinValue";
if ((dataTypes[i].ToLower() == "char" && columnDefaults[i] != ""))
defaultvalue = "'" + columnDefaults[i] + "'";
if (((dataTypes[i].ToLower() == "float" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "float" && columnDefaults[i] == "")) || ((dataTypes[i].ToLower() == "double" && columnDefaults[i].ToLower() == "null") || (dataTypes[i].ToLower() == "double" && columnDefaults[i] == "")))
defaultvalue = "0.0f";
if ((dataTypes[i].ToLower() == "float" && columnDefaults[i] != "") || (dataTypes[i].ToLower() == "double" && columnDefaults[i] != ""))
defaultvalue = columnDefaults[i] + "f";
if (defaultvalue == "\"''\"" || defaultvalue == "\"CURRENT_DATE\"" || defaultvalue == "\"' '\"")
defaultvalue = "\"\"";
sw.WriteLine("\t\t" + dataTypes[i] + " " + columnName.ToLower() + " { get; set; } = " + defaultvalue + ";");
sw.WriteLine("\t\t" + dataTypes[i] + " OLD_" + columnName.ToLower() + " = " + defaultvalue + ";");
sw.WriteLine("");
i++;
}
sw.WriteLine("\t\tiDB2Connection conn = new iDB2Connection(\"Data Source=<HOST>;user id=<USER>;password=<PASSWORD>;\");");
sw.WriteLine("");
List<string> parametri = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
parametri.Add(dataTypes[k] + " _" + columnNames[k].ToLower());
}
var parametristring = String.Join(", ", parametri);
sw.WriteLine("\t\tpublic " + UppercaseFirst(nome) + "() { }");
sw.WriteLine("");
sw.WriteLine("\t\tpublic " + UppercaseFirst(nome) + "(" + parametristring + ")");
sw.WriteLine("\t\t{");
for (int k = 0; k < parametri.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + " = _" + columnNames[k].ToLower() + ";");
}
sw.WriteLine("\t\t\tupdateOldValues();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tpublic void delete()");
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tconn.Open();");
sw.WriteLine("\t\t\tiDB2Command cmd = new iDB2Command(\"\", conn);");
List<string> whereStatementArray = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
whereStatementArray.Add(columnNames[k] + " = @" + columnNames[k]);
}
var whereStatementString = String.Join(" AND ", whereStatementArray);
sw.WriteLine("\t\t\tcmd.CommandText = \"DELETE FROM " + dbname + "." + nome + " WHERE " + whereStatementString + "\";");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter " + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);"); //always varchar so i don't have problem to handle strings
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + "Parameter.Value = " + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(" + columnNames[k].ToLower() + "Parameter);");
}
sw.WriteLine("\t\t\tcmd.ExecuteNonQuery();");
sw.WriteLine("\t\t\tconn.Close();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tpublic void update()");
sw.WriteLine("\t\t{");
sw.WriteLine("\t\t\tconn.Open();");
sw.WriteLine("\t\t\tiDB2Command cmd = new iDB2Command(\"\", conn);");
List<string> updateStatementArray = new List<string>();
for (int k = 0; k < columnNames.Count; k++)
{
updateStatementArray.Add(columnNames[k] + " = @new" + columnNames[k]);
}
var updateStatementString = String.Join(", ", updateStatementArray);
sw.WriteLine("\t\t\tcmd.CommandText = \"UPDATE " + dbname + "." + nome + " SET " + updateStatementString + " WHERE " + whereStatementString + "\";");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter OLD_" + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tiDB2Parameter " + columnNames[k].ToLower() + "Parameter = new iDB2Parameter(\"@new" + columnNames[k] + "\", iDB2DbType.iDB2VarChar, 0);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\t" + columnNames[k].ToLower() + "Parameter.Value = " + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tOLD_" + columnNames[k].ToLower() + "Parameter.Value = OLD_" + columnNames[k].ToLower() + ";");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(" + columnNames[k].ToLower() + "Parameter);");
}
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tcmd.Parameters.Add(OLD_" + columnNames[k].ToLower() + "Parameter);");
}
sw.WriteLine("\t\t\tcmd.ExecuteNonQuery();");
sw.WriteLine("\t\t\tconn.Close();");
sw.WriteLine("\t\t\tupdateOldValues();");
sw.WriteLine("\t\t}");
sw.WriteLine("");
sw.WriteLine("\t\tprivate void updateOldValues()");
sw.WriteLine("\t\t{");
for (int k = 0; k < columnNames.Count; k++)
{
sw.WriteLine("\t\t\tOLD_" + columnNames[k].ToLower() + " = " + columnNames[k].ToLower() + ";");
}
sw.WriteLine("\t\t}");
sw.WriteLine("\t}");
sw.WriteLine("}");
sw.Close();
}
}
conn.Close();
Console.ReadLine();
}
private void getCampaignBucketData()
{
//remove all preexisting data from the list
campaignBuckets.Clear();
//Create a DataSet to hold data from iSeries Table
DataSet dataStructure = new DataSet();
//Create a table to hold the iSeries data
DataTable dt = new DataTable("CampaignBuckets");
dt.Columns.Add("Campaign");
dt.Columns.Add("Bucket");
dt.Columns.Add("CallMethod");
dt.Columns.Add("Count");
//Add the datatable to the data set
dataStructure.Tables.Add(dt);
//Create connection to the iSeries
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = connectionString;
//Create query
iDB2Command cmd = new iDB2Command();
cmd.CommandText = "SELECT bucket, CCPPRJ, 'P' as type , count(rmkey) FROM cdqfil.obpcmt left join monprddta.buckets on char(bucket)<>ccpprj left join cdqfil.crmmas on ccpprj=rmp2prjt and rmacono between digits(bucketmin) and digits(bucketmax) and rmncdt=@Date and rmp2rflg in ('1','H') and rmdnc='' WHERE ccpprj in ('MAS', 'MASSP', 'MASSS', 'MASSPS', 'MASFC', 'MASSPFC') GROUP BY bucket, ccpprj UNION SELECT bucket, CCPPRJ, 'M' as type , count(rmkey) FROM cdqfil.obpcmt left join monprddta.buckets on char(bucket) <> ccpprj left join cdqfil.crmmas on ccpprj = rmp2prjt and rmacono between digits(bucketmin) and digits(bucketmax) and rmncdt = @Date and rmp2rflg in ('M', 'h') and rmdnc = '' WHERE ccpprj in ('MAS', 'MASSP', 'MASSS', 'MASSPS', 'MASFC', 'MASSPFC') GROUP BY bucket, ccpprj ORDER BY type, ccpprj, bucket";
cmd.Connection = cn;
//prepare for Date parameter
String date = long.Parse(DateTime.Today.Date.ToString("yyyyMMdd")).ToString();
cn.Open();
cmd.DeriveParameters();
cmd.Parameters["@Date"].Value = date;
//Execute the sql statement. Get a Data Reader object
iDB2DataReader readFile = cmd.ExecuteReader();
//Read each row from the table and output the results into the data set
while (readFile.Read())
{
//Create a row to hold data
DataRow datarow = dataStructure.Tables["CampaignBuckets"].NewRow();
datarow["Bucket"] = readFile.GetString(0).Trim();
datarow["Campaign"] = readFile.GetString(1).Trim();
datarow["CallMethod"] = readFile.GetString(2);
datarow["Count"] = readFile.GetString(3);
//add the row to the data table Campaigns
dataStructure.Tables["CampaignBuckets"].Rows.Add(datarow);
}
//Clean up - Close connections
readFile.Close();
cmd.Dispose();
cn.Close();
foreach (DataRow row in dt.Rows)
{
switch (row["Campaign"].ToString())
{
case "MAS":
//do nothing as it's named correctly already
break;
case "MASSP":
row["Campaign"] = "MAS SP";
break;
case "MASSS":
row["Campaign"] = "ALT";
break;
case "MASSPS":
row["Campaign"] = "ALT SP";
break;
case "MASFC":
row["Campaign"] = "FOCUS";
break;
case "MASSPFC":
row["Campaign"] = "FOCUS SP";
break;
default:
//nothing
break;
}
campaignBuckets.Add(new CampaignBucket(row["Campaign"].ToString(), row["Bucket"].ToString(), row["CallMethod"].ToString(), row["Count"].ToString()));
}
}
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 GetAepmMaster()
{
var dt = new DataTable();
var dset = new DataSet();
//retrieve any records needing updating
try
{
using (var conn = new iDB2Connection(ConfigurationManager.AppSettings["AS400ConnectionString"]))
{
// string sql = @"SELECT G_GUID, G_ITEM, G_RETRN FROM CGJLEGAC.GETMSTRL1 WHERE G_RETRN != 'R' and G_RETRN != 'S'";
string sql = GetUnprocessMasterRecsString();
var objDataAdapter = new iDB2DataAdapter();
var cmd = new iDB2Command(sql, conn);
objDataAdapter.SelectCommand = cmd;
objDataAdapter.SelectCommand.CommandTimeout = 0;
dt.Clear();
dset.Clear();
objDataAdapter.Fill(dt);
objDataAdapter.Fill(dset, "currentSelections");
var cb = new iDB2CommandBuilder(objDataAdapter);
AddParameters(cb);
//update records to 'S' for submitted
for (int i = 0; i < dset.Tables["currentSelections"].Rows.Count; i++)
{
dset.Tables["currentSelections"].Rows[i]["G_RETRN"] = 'S';
}
objDataAdapter.Update(dset, "currentSelections");
var taskList = new Task[dt.Rows.Count];
//submit records asynch
for (int i = 0; i < dt.Rows.Count; i++)
{
taskList[i] = TestAsync(dt, i);
}
Task.WaitAll(taskList);
}
}
catch (Exception ex)
{
WriteToEventLog(ex);
}
}
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 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();
}
}
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();
}
/// <summary>
/// Gets the data from the IBM i.
/// </summary>
/// <param name="connString">The connection string</param>
/// <param name="sqlStatement">The SQL statement</param>
/// <param name="parameters">The parameters (iDB2Parameter)</param>
/// <returns>DataTable</returns>
public Object GetValue(string sqlStatement, Action<iDB2ParameterCollection> parameters)
{
Object obj;
using (iDB2Command cmd = new iDB2Command(sqlStatement, conn))
{
if (parameters != null) { parameters(cmd.Parameters); }
try
{
obj = cmd.ExecuteScalar();
}
catch (iDB2SQLErrorException e)
{
throw e;
}
catch (Exception e)
{
throw e;
}
}
return obj;
}
