public List <User> findAll() { string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" ORDER BY USERNAME"; using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, null)) { if (rdr.Read()) { List <User> list = new List <User>(); do { User user = new User(); user.userNo = rdr.GetInt32(0); user.username = rdr.GetString(1); user.password = rdr.GetString(2); list.Add(user); } while (rdr.Read()); return(list); } else { return(null); } } }
public List <T> FindAll(string sql) { List <T> list = new List <T>(); using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql)) { if (rdr.Read()) { do { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } list.Add(t); } while (rdr.Read()); } } return(list); }
public List <T> nativeQuerySql(string sql, IDictionary <string, object> parms) { iDB2Parameter[] db2Parms = null; List <T> list = new List <T>(); if (parms != null) { List <iDB2Parameter> parmsList = new List <iDB2Parameter>(); foreach (var item in parms) { Debug.Assert(item.Value != null); parmsList.Add(new iDB2Parameter('@' + item.Key, item.Value)); } db2Parms = parmsList.ToArray(); } try { using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms)) { if (rdr.Read()) { do { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } list.Add(t); } while (rdr.Read()); } } } catch (Exception ex) { log.Error("SQL错误:" + ex.Message); log.Error("SQL错误:" + sql); if (parms != null) { log.Error("SQL错误:" + parms.toJson()); } throw ex; } return(list); }
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; } }
public string EjecutarSelectResult(string consulta) { try { AS400ConnectionString.Open(); string datoDevuelto = string.Empty; iDB2Command command = new iDB2Command(); command.Connection = AS400ConnectionString; command.CommandType = System.Data.CommandType.Text; command.CommandText = consulta; command.CommandTimeout = 600000; iDB2DataReader dr = command.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { datoDevuelto = dr.GetString(0); break; } } return(datoDevuelto); } catch (Exception ex) { return("0"); //throw new Exception("Se produjo un problema al reaizar un select en AS400: ", ex); } }
public MaintenanceLogs() { InitializeComponent(); LogBox.Items.Clear(); LogBox.Items.Add("Maint Logs"); try { SQL = "select * from flight2019.MaintLog"; connection = new iDB2Connection("datasource=deathstar.gtc.edu"); command = new iDB2Command(SQL, connection); connection.Open(); dataReader = command.ExecuteReader(); while (dataReader.Read()) { LogBox.Items.Add("Log#: " + dataReader.GetString(1)); LogBox.Items.Add("Plane#: " + dataReader.GetString(2)); LogBox.Items.Add("TaskID: " + dataReader.GetString(3)); LogBox.Items.Add("Maint Start Date: " + dataReader.GetString(4)); LogBox.Items.Add("Maint End Date: " + dataReader.GetString(5)); LogBox.Items.Add("Status: " + dataReader.GetString(6)); } connection.Close(); } catch (Exception ex) { LogBox.Items.Add(ex.Message); } }
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); }
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 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 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 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 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); } }
public T FindByid(string sql, string parmName, string parmValue) { Debug.Assert(!string.IsNullOrWhiteSpace(parmName)); Debug.Assert(!string.IsNullOrWhiteSpace(parmValue)); T result = default(T); iDB2Parameter[] db2Parms = new iDB2Parameter[] { new iDB2Parameter('@' + parmName, parmValue) }; using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms)) { if (rdr.Read()) { T t = Activator.CreateInstance <T>(); PropertyInfo[] propertyInfo = t.GetType().GetProperties(); int i = 0; foreach (PropertyInfo pi in propertyInfo) { try { pi.SetValue(t, rdr.GetValue(i++)); } catch (Exception ex) { log.Error("字段类型错误," + pi.Name + ":" + ex.Message); } } result = t; } } return(result); }
private IEnumerable <Models.Item> GetItems(ItemSearch newsearch) { iDB2DataReader readerITM = null; Item item = new Item(); List <Models.Item> itemlist = new List <Models.Item>(); item.List(HttpContext.Session["SecurityKey"].ToString(), newsearch, ref readerITM); if (readerITM != null) { while (readerITM.Read()) { var newitem = new Models.Item(); string formatYMD; newitem.ItemID = readerITM["ITMITM"].ToString(); newitem.Brand = readerITM["ITMPG3"].ToString(); newitem.Size = readerITM["ITMPG4"].ToString(); newitem.ItemDescEng = readerITM["ITMDSE"].ToString(); if (readerITM["ITMDT1"].ToString().Length == 5) { formatYMD = "0" + readerITM["ITMDT1"].ToString(); } else { formatYMD = readerITM["ITMDT1"].ToString(); } newitem.Date = DateTime.ParseExact(formatYMD, "yyMMdd", CultureInfo.InvariantCulture); itemlist.Add(newitem); } } return(itemlist); }
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); } } }
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); } } }
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); } }
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); }
// GET api/values/5 public IHttpActionResult GetContactosById(int id) { List <Contacto> oContacto = new List <Contacto>(); string connString = ConfigurationManager.ConnectionStrings["PUB400"].ConnectionString; iDB2Connection connection = new iDB2Connection(connString); string query = "select * from davidec1.contactos where id = @id"; iDB2Command db2Command = new iDB2Command(); db2Command.Connection = connection; db2Command.CommandText = query; db2Command.Parameters.AddWithValue("@id", id); connection.Open(); iDB2DataReader reader = db2Command.ExecuteReader(); while (reader.Read()) { oContacto.Add(new Contacto() { Id = Convert.ToInt32(reader.GetValue(0).ToString()), Nombre = reader.GetValue(1).ToString(), Telefono = reader.GetValue(2).ToString(), Correo = reader.GetValue(3).ToString(), Edad = Convert.ToInt32(reader.GetValue(4).ToString()) }); } return(Ok(oContacto)); }
public IEnumerable <iDB2DataReader> _queryReader(iDB2Connection connection, string queryString, params KeyValuePair <string, object>[] queryParams) { using (iDB2DataReader reader = GenerateCommand(connection, queryString, queryParams).ExecuteReader()) { while (reader.Read()) { yield return(reader); } } }
public Models.Item MapItem(iDB2DataReader readerITM) { Models.Item item = new Models.Item(); while (readerITM.Read()) { string id = readerITM["ITMITM"].ToString(); item.ItemID = id; } return(item); }
public IEnumerable <iDB2DataReader> _queryReader(iDB2Connection connection, string queryString) { using (iDB2DataReader reader = GenerateCommand(connection, queryString).ExecuteReader()) { while (reader.Read()) { yield return(reader); } } }
public List <PendientesLlenos> ListaPendientesLlenos(string tipo) { List <PendientesLlenos> dtResult = new List <PendientesLlenos>(); iDB2Connection cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["cnnRansa"].ConnectionString); iDB2Command cmd; cmd = new iDB2Command("SP_INTEGRACIONTDEPOT_JMY", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("XTIPO", iDB2DbType.iDB2VarChar).Value = tipo; try { cn.Open(); iDB2DataReader dr = cmd.ExecuteReader(); while (dr.Read()) { PendientesLlenos a = new PendientesLlenos(); a.NUMID = dr.IsDBNull(dr.GetOrdinal("NUMID")) ? 0 : dr.GetInt32(dr.GetOrdinal("NUMID")); a.VesselIMO = dr.IsDBNull(dr.GetOrdinal("CINDVP")) ? "" : dr.GetString(dr.GetOrdinal("CINDVP")); a.OceanCarrier = dr.IsDBNull(dr.GetOrdinal("CLINE")) ? "" : dr.GetString(dr.GetOrdinal("CLINE")); a.CustomerERPCode = dr.IsDBNull(dr.GetOrdinal("NRUCA")) ? "" : dr.GetString(dr.GetOrdinal("NRUCA")); a.YardCode = dr.IsDBNull(dr.GetOrdinal("CTPDEP")) ? "" : dr.GetString(dr.GetOrdinal("CTPDEP")); a.ActionType = dr.IsDBNull(dr.GetOrdinal("CACTTP")) ? "" : dr.GetString(dr.GetOrdinal("CACTTP")); a.VoyageNumber = dr.IsDBNull(dr.GetOrdinal("VOYNUM")) ? "" : dr.GetString(dr.GetOrdinal("VOYNUM")); a.VoyageStopExternalCode = dr.IsDBNull(dr.GetOrdinal("CRECAL")) ? "" : dr.GetString(dr.GetOrdinal("CRECAL")); a.ReferenceType = dr.IsDBNull(dr.GetOrdinal("CTDCRF")) ? "" : dr.GetString(dr.GetOrdinal("CTDCRF")); a.ReferenceNumber = dr.IsDBNull(dr.GetOrdinal("NDCREF")) ? "" : dr.GetString(dr.GetOrdinal("NDCREF")); a.OperationType = dr.IsDBNull(dr.GetOrdinal("CTIPOP")) ? "" : dr.GetString(dr.GetOrdinal("CTIPOP")); a.PortOfDischarge = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN")); a.PlaceOfDelivery = dr.IsDBNull(dr.GetOrdinal("CPRLLN")) ? "" : dr.GetString(dr.GetOrdinal("CPRLLN")); a.PlaceOfReceipt = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1")); a.PortOfLoading = dr.IsDBNull(dr.GetOrdinal("CPROR1")) ? "" : dr.GetString(dr.GetOrdinal("CPROR1")); a.ShipmentType = dr.IsDBNull(dr.GetOrdinal("CTPCNC")) ? "" : dr.GetString(dr.GetOrdinal("CTPCNC")); a.ActivityCode = dr.IsDBNull(dr.GetOrdinal("CACTVC")) ? "" : dr.GetString(dr.GetOrdinal("CACTVC")); a.direction = dr.IsDBNull(dr.GetOrdinal("CDIREC")) ? "" : dr.GetString(dr.GetOrdinal("CDIREC")); a.Status = dr.IsDBNull(dr.GetOrdinal("CSTSMV")) ? "" : dr.GetString(dr.GetOrdinal("CSTSMV")); a.VehiclePlate = dr.IsDBNull(dr.GetOrdinal("NPLCUN")) ? "" : dr.GetString(dr.GetOrdinal("NPLCUN")); a.ExecutionDate = dr.IsDBNull(dr.GetOrdinal("FCHMOV")) ? "" : dr.GetString(dr.GetOrdinal("FCHMOV")); a.EquipmentNumber = dr.IsDBNull(dr.GetOrdinal("NROCON")) ? "" : dr.GetString(dr.GetOrdinal("NROCON")); a.EquipmentTypeSizeISOCode = dr.IsDBNull(dr.GetOrdinal("ISOCODE")) ? "" : dr.GetString(dr.GetOrdinal("ISOCODE")); dtResult.Add(a); } } catch (Exception ex) { dtResult = null; } finally { cn.Close(); } return(dtResult); }
/// <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); }
public IDictionary <string, string> FindAllDic(string sql) { IDictionary <string, string> list = new Dictionary <string, string>(); using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql)) { if (rdr.Read()) { do { try { list.Add(rdr.GetString(0), rdr.GetString(1)); } catch (Exception ex) { log.Error("字段类型错误," + rdr.GetValue(0).ToString() + ":" + rdr.GetValue(1).ToString() + ":" + ex.Message); } } while (rdr.Read()); } } return(list); }
private Models.Item GetItemsbyID(string ItemID) { iDB2DataReader readerITM = null; Item item = new Item(); Models.Item newitem = new Models.Item(); item.ListbyItemNumber(HttpContext.Session["SecurityKey"].ToString(), ItemID, ref readerITM); if (readerITM != null) { while (readerITM.Read()) { string id = readerITM["ITMITM"].ToString(); newitem.ItemID = id; } } return(newitem); }
private void StoreQueryResultToVariables(Dictionary <String, String> parameters) { using (iDB2Command command = new iDB2Command(parameters["SelectQuery"], _dB2connection)) { command.CommandTimeout = 500000; if (parameters.ContainsKey("ParameterVariables")) { foreach (String str in parameters["ParameterVariables"].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { if (EngineState.VariableDictionary.ContainsKey(str)) { if (Regex.IsMatch(EngineState.VariableDictionary[str].ToString(), "^[1-9]{1}[0-9]{0,}([.]{1}[0-9]{1,}){0,1}$", RegexOptions.Compiled)) { command.Parameters.AddWithValue(str, EngineState.VariableDictionary[str].ToString()); } else { command.Parameters.AddWithValue(str, String.Format("'{0}'", EngineState.VariableDictionary[str].ToString())); } } } } using (iDB2DataReader reader = command.ExecuteReader()) { if (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (EngineState.VariableDictionary.ContainsKey(reader.GetName(i))) { EngineState.VariableDictionary[reader.GetName(i)] = reader[i]; } else { EngineState.VariableDictionary.Add(reader.GetName(i), reader[i]); } } } } } }
/// <summary> /// Using the sql param retrieve the given customer orders /// </summary> /// <param name="sql"></param> /// <returns></returns> private static List <CustOrder> RetrieveOrders(string sql) { LogIt("In RetrieveOrders " + sql); List <CustOrder> data = new List <CustOrder>(); try { using (iDB2Connection conn = Config.Conn) { dr = ExecuteSql(sql, conn); if (dr.HasRows) { while (dr.Read()) { CustOrder tmp = RowToDict(dr); if (tmp != null) { data.Add(tmp); } } } else { data.Add(new CustOrder() { CustomerCode = customer, OrderNumber = ornu, StatusText = "Order(s) not found" }); } } } catch (iDB2CommErrorException ex) { throw new FaultException <DatabaseFault>(new DatabaseFault() { DbOperation = "Connection to database", DbReason = "Exception accessing database", DbMessage = ex.Message }, "Database connection issue"); } return(data); }
public void loadARCodes() { try { SQL = "select ARCD,arcitynm from flight2019.airport"; connection = new iDB2Connection("datasource=deathstar.gtc.edu"); command = new iDB2Command(SQL, connection); arcodes = new SortedDictionary <string, string>(); connection.Open(); dataReader = command.ExecuteReader(); while (dataReader.Read()) { arcodes.Add(dataReader.GetString(0), dataReader.GetString(1)); } connection.Close(); } catch (Exception ex) { AnDBox.Items.Add(ex.Message); } }
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(); }