private void btnGetLog_Click(object sender, EventArgs e) { //ceonnect to the database try { connection = new iDB2Connection("DataSource=deathstar.gtc.edu;DefaultCollection=FLIGHT2019"); command = connection.CreateCommand(); //grab the data command.CommandText = "SELECT * FROM MAINTLOG " + "WHERE PLANENO = '" + txtPlaneNum.Text.ToUpper() + "'"; connection.Open(); dataReader = command.ExecuteReader(); //Display to listbox if (dataReader.Read()) { listBox1.Items.Add("Log Number: " + dataReader.GetString(1)); listBox1.Items.Add("Task ID: " + dataReader.GetString(2)); listBox1.Items.Add("Maintence Start Date: " + dataReader.GetString(3)); listBox1.Items.Add("Maintence End Date: " + dataReader.GetString(4)); listBox1.Items.Add("Status: " + dataReader.GetString(5)); listBox1.Items.Add("Maintence Cost: " + dataReader.GetString(6)); } else { txtPlaneNum.Text = "No results found for specified Plane Number id."; } dataReader.Close(); } catch (Exception ex) { txtPlaneNum.Text = ex.Message; } }
private void btnGetTask_Click(object sender, EventArgs e) { try { connection = new iDB2Connection("DataSource=deathstar.gtc.edu"); command = connection.CreateCommand(); command.CommandText = "SELECT taskDesc " + "FROM Tasks T JOIN WorkOrder WO ON T.taskId = WO.taskId " + "WHERE WO.orderId = '" + txtWorkOrder.Text.ToUpper() + "'"; connection.Open(); reader = command.ExecuteReader(); if (reader.Read()) { txtTask.Text = reader.GetString(0); } else { txtTask.Text = "No results found for specified work order id."; } reader.Close(); } catch (Exception ex) { txtTask.Text = ex.Message; } }
/// <summary> /// Returns downloaded Dictioanry with UPC codes matching items. /// </summary> public Dictionary <string, string> DownloadUpcForItemsAsync(BindableCollection <IpgModel> _ipgsCollection) { Console.WriteLine("Trying to connect to Reflex for downloading UPC codes..."); // Dictionary for holding Ean to Upc map. Dictionary <string, string> Ean_Upc = new Dictionary <string, string>(); // Preparing a formatted list of items. string _items = ConcatenateItemsIntoList(_ipgsCollection); try { conn.Open(); if (conn != null) { Console.WriteLine("Successfully connected to Reflex for downloading UPC codes"); // Below are DB2 functions needed for executing query string _queryString = $"SELECT VICART, VICIVL FROM {Environment}.HLVLIDP WHERE VICART IN {_items} and VICTYI = 'EAN_1' Order by VICIVL "; iDB2Command comm = conn.CreateCommand(); comm.CommandText = _queryString; iDB2DataReader reader = comm.ExecuteReader(); // Reader in while goes through all rows of results from Reflex. while (reader.Read()) { // Adds new key-value to a Dictionary. Ean_Upc.Add(reader.GetString(0).ToString().Trim(), reader.GetString(1).ToString().Trim()); } Console.WriteLine("Dictionary EAN_UPC created"); // Some cleaning needed. reader.Close(); comm.Dispose(); // Return Dictionary return(Ean_Upc); } } catch (Exception ex) { Console.WriteLine("Error : " + ex); Console.WriteLine(ex.StackTrace); return(null); } finally { conn.Close(); } // This will never reach by needs to be here because of error "Not all is returning value". return(Ean_Upc); }
/// <summary> /// Ends the transaction and disposes any unhandled transaction object and closes the reader object /// </summary> /// <param name="err">The error object as ref</param> /// <param name="givenTransaction">The given transaction.</param> /// <param name="readerToClose">The reader to close.</param> public void EndTransaction(DestinationDatabase destinationDatabase, ref ErrorObj err, iDB2Transaction givenTransaction, iDB2DataReader readerToClose) { if (!(givenTransaction.Connection == null)) { givenTransaction.Dispose(); givenTransaction = null; } if (!(readerToClose == null)) { if (!(readerToClose.IsClosed)) { readerToClose.Close(); } readerToClose.Dispose(); } err = ConnectionByDestinationDBClose(destinationDatabase); }
//This function returns true if the invoice found in the FSTINV table private bool InvoiceExists(iDB2Command db2Command, string st) { bool exists = false; using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT * FROM FSTINV WHERE SIID = {0}", st); iDB2DataReader reader = db2Command.ExecuteReader(); exists = reader.HasRows; reader.Close(); db2Command.Transaction.Dispose(); } return(exists); }
//Update Invoice in service Trade to Done private void SendInvoiceUpdates(iDB2Command db2Command) { st.insertLog("Sending invoice update to Service Trade.", "info", "NA", LogId); Dictionary <string, Invoice> jdeInvList = new Dictionary <string, Invoice>(); using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction()) { db2Command.Transaction = db2Transaction; db2Command.CommandText = string.Format("SELECT DISTINCT SIID, SIAN8, SIJOB, SIDOCO, SIDCTO, SICRDT FROM FSTINV WHERE SISTAT = 'P'"); iDB2DataReader reader = db2Command.ExecuteReader(); while (reader.Read()) { Invoice temp = new Invoice(); temp.SIID = ((string)reader["SIID"]).Trim(); temp.type = "invoice"; //always set this to processed status to avoid breaking the status in ST temp.status = "processed"; temp.jobId = Int32.Parse((string)reader["SIJOB"]); //Change Invoice Number (Concat JDE Document Number and Document Type with -) temp.invoiceNumber = string.Format("{0}-{1}", reader["SIDOCO"], reader["SIDCTO"]); temp.transactionDate = (Int32)((DateTime)reader["SICRDT"] - unixBaseTime).TotalSeconds; //temp.assignedUserId= Int32.Parse((string)reader["SITECH"]); //09/18/2015 - This limits the invoices being sent for update in ST. //if (!jdeInvList.ContainsKey((string)reader["SIAN8"])) //{ // jdeInvList.Add((string)reader["SIAN8"], temp); // sent++; //} if (reader["SIAN8"] != null) { //12/19/16 JC: This will avoid duplicate records. if (!jdeInvList.ContainsKey(temp.SIID)) { jdeInvList.Add((string)temp.SIID, temp); } } } reader.Close(); } db2Command.Transaction.Dispose(); if (jdeInvList.Count > 0) { st.insertLog(string.Format("Found {0} invoices for update", jdeInvList.Count), "Info", "NA", LogId); foreach (var jde in jdeInvList) { //var status = jde.Value.status; //Query the single invoice from the service Trade var invoice = serviceTrade.GetInvoice(jde.Key); //Mark the invoice as done in FSTINV, if the status of the invoice is void in Service Trade if (invoice.Status == "void" || invoice.Status != "pending_accounting") { st.insertLog(string.Format("The status of invoice number {0} is not <pending_accounting> in service Trade.", jde.Key), "Error", jde.Key.ToString(), LogId); SetInvoiceToDone(db2Command, jde.Value); } else { if (serviceTrade.UpdateInvoice(jde.Value)) { st.insertLog(string.Format("The status of invoice number {0} is set to processed in Service Trade.", jde.Key), "Info", jde.Key.ToString(), LogId); SetInvoiceToDone(db2Command, jde.Value); } } } } else { st.insertLog("No invoice update to be sent.", "Info", "NA", LogId); } }
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."); } }
//WCSACCDB /// <summary> /// Returns true if HD exist, and saves this hd in OriginalHdModel instance created in this class. /// If hd is Unknown returns false. /// </summary> public bool DownloadHdFromReflex(string _hd) { Console.WriteLine("Trying to connect to Reflex for downloading HD informations."); try { conn.Open(); if (conn != null) { Console.WriteLine("Successfully connected to Reflex for downloading HD informations."); // Below are DB2 functions needed for executing query // Query join table where we can see lines by items. A2CFAN is telling to DB2 to show only lines value. // You can change it to show CAPO or something else. Result will be in field A2CFAR. // This also needs to be ordered by Item. string _queryString = $"SELECT GECART, GECQAL, A.A2CFAR, GEQGEI, B.A2CFAR FROM {Environment}.HLGEINP " + $"inner join {Environment}.HLCDFAP A on GECART = A.A2CART " + $"inner join {Environment}.HLCDFAP B on GECART = B.A2CART " + $"WHERE GENSUP = '{_hd}' and A.A2CFAN = 'LINE' AND B.A2CFAN = 'STAGIONE'" + "Order by GECART"; iDB2Command comm = conn.CreateCommand(); comm.CommandText = _queryString; iDB2DataReader reader = comm.ExecuteReader(); // Below if checks if there is some data in result. If no then it return false. // That means that HD is Unknown. if (reader.HasRows) { // Reader in while goes through all rows of results from Reflex. while (reader.Read()) { // Here we are adding new IPG to HD object. OriginalHdModel.ListOfIpgs.Add(new IpgModel() { Item = reader.GetString(0).ToString().Trim(), Grade = reader.GetString(1).ToString().Trim(), // Lines is an enum so we need parse string to enum here. Line = (Lines)Enum.Parse(typeof(Lines), reader.GetString(2)), Quantity = reader.GetInt32(3), Season = reader.GetString(4).ToString().Trim() }); } // some cleaning. reader.Close(); comm.Dispose(); // Returns true so we have our data in "OriginalHdModel" instance. return(true); } else { // When there is no data from Reflex return(false); } } } catch (Exception ex) { Console.WriteLine("Error : " + ex); Console.WriteLine(ex.StackTrace); return(false); } finally { conn.Close(); } // This will never reach but needs to be here because of error "Not all is returning value". return(false); }
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(); }