public Location(XmlNode locationNode) { theVehicle = new Vehicle(locationNode.SelectSingleNode("/location_request/vehicle").InnerXml); //string sqlConnString = "Host=192.168.1.120;Service=6032;Server=mads_se;User ID=net_book;password=Mickey;Database=/usr/taxi/mads"; string sqlConnString = ConfigurationSettings.AppSettings.Get("MadsOBC"); IfxConnection conn = new IfxConnection(sqlConnString); conn.Open(); using (IfxCommand ct = conn.CreateCommand()) { string sqlQuery = "select vh_gps_long,vh_gps_lat from vehicle where vh_nbr=" + theVehicle.VehNbr.ToString(); ct.CommandText = sqlQuery; IfxDataReader dr = ct.ExecuteReader(); if (dr.Read()) { theVehicle.X = Convert.ToInt32(dr["vh_gps_long"]); theVehicle.Y = Convert.ToInt32(dr["vh_gps_lat"]); } else { theVehicle.X = 0; theVehicle.Y = 0; } } conn.Close(); }
public IActionResult Index() { DataTable table = new DataTable(); using (IfxConnection Con = new IfxConnection(connString)) { string query = "SELECT SUM(TotalAmount) FROM Cart"; IfxCommand cmd = new IfxCommand(query, Con); Con.Open(); int sum = 0; try { IfxDataReader rows = cmd.ExecuteReader(); while (rows.Read()) { sum = Convert.ToInt32(rows[0]); } rows.Close(); cmd.Dispose(); } catch (IfxException ex) { } finally { Con.Close(); } table.Columns.Add("TotalAmount", typeof(int)); { table.Rows.Add(sum); } } return(View(table)); }
public void GetPosition(out int x, out int y) { x = 0; y = 0; string strConn = ConfigurationSettings.AppSettings.Get("MadsConnect"); IfxConnection conn = new IfxConnection(strConn); try { conn.Open(); } catch { return; } using (IfxCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select vh_gps_lat,vh_gps_long from vehicle where vh_nbr=" + this.VehicleID; IfxDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { x = Int32.Parse(rdr["vh_gps_long"].ToString()); y = Int32.Parse(rdr["vh_gps_lat"].ToString()); } } conn.Close(); }
public override int EjecutarCmdPreparado(List <CamposTabla> unRegistro) { int resultado = 0; try { foreach (CamposTabla item in unRegistro) { if (this.comandoPreparado.Parameters.Count > 0) { if (this.comandoPreparado.Parameters.Contains("@" + item.Nombre)) { this.comandoPreparado.Parameters[item.Nombre].Value = item.Valor; } } } //EJECUCION DE COMANDO if (exeAutoNum) { //CAPTURAMOS VALOR AUTONUMERICO DE TIPO SEQUENCE CUANDO EXEAUTONUM == TRUE IfxDataReader rd = (IfxDataReader)ExecuteReader(this.comandoPreparado); rd.Read(); resultado = Convert.ToInt32(rd["valorActual"]); rd.Close(); rd.Dispose(); rd = null; } else { //DEVOLVEMOS EL NUMERO DE REGISTROS AFECTADOS CUANDO EXEAUTONUM == FALSE resultado = ExecuteNonQuery(this.comandoPreparado); } } catch (IfxException) { //throw new Exception("Error " + mensaje + " Registro \nDetalle: ", ex); throw; } catch (Exception) { //throw new Exception("Error " + mensaje + " Registro \nDetalle: ", ex); throw; } //DEVOLUCION DE RESULTADO return(resultado); }
public IList <List <string> > GetDataReader() { ifxCommand = new IfxCommand(sql, myConnection); ifxDataReader = ifxCommand.ExecuteReader(); while (ifxDataReader.Read()) { List <string> column = new List <string>(); column.Add(ifxDataReader["id"].ToString()); column.Add(ifxDataReader["surname"].ToString()); column.Add(ifxDataReader["name"].ToString()); column.Add(ifxDataReader["patronymicName"].ToString()); listcolumn.Add(column); } return(listcolumn); }
static List <uspGetListOfSequenceNrGaps_Result> GetDuplicateTransactions(IfxConnection connection) { Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}"); var missing = new List <uspGetListOfSequenceNrGaps_Result>(); using (var dataContext = new DatabaseSyncDataContext()) { IfxCommand command = connection.CreateCommand(); Log.LogInfoMessage($"Querying duplicate transactions for month : {CurrentMonth.ToString("yyyy-MM-dd 00:00:00")}"); command.CommandText = $"select count(*),ln_id, tx_seq_nr " + $"from informix.p_trans " + $"where dt_concluded >= TO_DATE('{CurrentMonth.ToString("yyyy-MM-dd 00:00:00")}', '%Y-%m-%d %H:%M:%S') " + $"and dt_concluded < TO_DATE('{FollowingMonth.ToString("yyyy-MM-dd 00:00:00")}','%Y-%m-%d %H:%M:%S') " + $"Group by ln_id,tx_seq_nr "; Log.LogInfoMessage(command.CommandText); IfxDataReader dataReader = command.ExecuteReader(); List <string> s = new List <string>(); while (dataReader.Read()) { if (dataReader[0].ToString().Trim() == "1") { continue; } missing.Add(new uspGetListOfSequenceNrGaps_Result { Lane = dataReader[1].ToString().Trim(), Sequencenr = int.Parse(dataReader[2].ToString().Trim()) }); } } Log.LogInfoMessage($"Duplicate Transactions found : {missing.Count}"); Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}"); return(missing); }
public static List <T> Binding <T>(this IfxDataReader sqlDataReader) where T : class { var objects = new List <T>(); try { while (sqlDataReader.Read()) { var entity = (T)Activator.CreateInstance(typeof(T)); foreach (var property in typeof(T).GetProperties()) { var value = property.PropertyType.GetDefault(); if (!sqlDataReader.HasColumn(property.Name)) { property.SetValue(entity, value, null); continue; } var index = sqlDataReader.GetOrdinal(property.Name); if (!sqlDataReader.IsDBNull(index)) { var type = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType; value = Convert.ChangeType(sqlDataReader.GetValue(index), type); } property.SetValue(entity, value, null); } objects.Add(entity); } } catch (Exception e) { Console.WriteLine(e); return(null); } return(objects); }
/// <summary> /// Perform a simaple commit, rollback transaction operation /// </summary> private bool PerformTransaction() { bool bStatus = false; IfxTransaction tx; try { // ********************* Demo for Transaction Commit ******************** tx = _connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); _command = _connection.CreateCommand(); _command.Transaction = tx; _command.CommandText = "insert into transactiontable values(1, 'ORIGINAL NAME');"; WriteLine("Inserting into table with the command: " + _command.CommandText); _command.ExecuteNonQuery(); _command.CommandText = "update transactiontable set name='NAME UPDATED' where id='1';"; WriteLine("Update table with the command: " + _command.CommandText); _command.ExecuteNonQuery(); tx.Commit(); WriteLine("Transaction Commited. Observe table contents"); _command.CommandText = "select * from transactiontable"; WriteLine("Results after executing the command: " + _command.CommandText); _dataReader = _command.ExecuteReader(); while (_dataReader.Read()) { WriteLine(String.Format("{0} ----- {1}", _dataReader[0], _dataReader[1])); } _dataReader.Close(); // ********************* Demo for Transaction Rollback ******************** tx = _connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); _command = _connection.CreateCommand(); _command.Transaction = tx; _command.CommandText = "update transactiontable set name='NAME ROLLEDBACK' where id='1';"; WriteLine("Update table with the command: " + _command.CommandText); _command.ExecuteNonQuery(); WriteLine("Observe table contents before rollback"); _command.CommandText = "select * from transactiontable"; WriteLine("Results after executing the command: " + _command.CommandText); _dataReader = _command.ExecuteReader(); while (_dataReader.Read()) { WriteLine(String.Format("{0} ----- {1}", _dataReader[0], _dataReader[1])); } _dataReader.Close(); tx.Rollback(); WriteLine("Observe table contents after rollback"); _command.CommandText = "select * from transactiontable"; WriteLine("Results after executing the command: " + _command.CommandText); _dataReader = _command.ExecuteReader(); while (_dataReader.Read()) { WriteLine(String.Format("{0} ----- {1}", _dataReader[0], _dataReader[1])); } _dataReader.Close(); bStatus = true; } catch (Exception excep) { WriteLine(String.Format("Insert into table failed : {0}", excep.Message)); bStatus = false; } return(bStatus); }
public ActionResult CreateOrder() { using (IfxConnection Con = new IfxConnection(connString)) { Con.Open(); DataTable cartTable = new DataTable(); IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM cart", Con); ifx.Fill(cartTable); Con.Close(); List <MyOrders> orderList = new List <MyOrders>(); for (int i = 0; i < cartTable.Rows.Count; i++) { MyOrders order = new MyOrders(); order.PurchaseDate = DateTime.UtcNow.ToString(); order.SLNo = Convert.ToInt32(cartTable.Rows[i]["SLNo"]); order.MobileName = cartTable.Rows[i]["MobileName"].ToString(); order.Description = cartTable.Rows[i]["Description"].ToString(); order.PicURL = cartTable.Rows[i]["PicURL"].ToString(); order.Model = cartTable.Rows[i]["Model"].ToString(); order.Features = cartTable.Rows[i]["Features"].ToString(); order.Color = cartTable.Rows[i]["Color"].ToString(); order.SimType = cartTable.Rows[i]["SimType"].ToString(); order.Price = Convert.ToDecimal(cartTable.Rows[i]["Price"]); order.Quantity = Convert.ToInt32(cartTable.Rows[i]["Quantity"]); order.TotalAmount = Convert.ToDecimal(cartTable.Rows[i]["TotalAmount"]); orderList.Add(order); } foreach (MyOrders order in orderList) { Con.Open(); int SLNo = order.SLNo; int availableQuantity = 0; string selectMobileDetails = "select Quantity from mobiles where SLNo = ?"; IfxCommand cmd = new IfxCommand(selectMobileDetails, Con); cmd.Parameters.Add("slno", IfxType.Serial).Value = SLNo; try { IfxDataReader rows = cmd.ExecuteReader(); while (rows.Read()) { availableQuantity = Convert.ToInt32(rows[0]); } rows.Close(); } catch (IfxException ex) { Con.Close(); order.ErrorMessage = "Error : " + ex.Message; } if (order.Quantity > availableQuantity) { Con.Close(); order.ErrorMessage = "Cannot purchase " + order.Quantity + " quantities, available quantities are : " + availableQuantity; } else { int newMobileQuantity = availableQuantity - order.Quantity; string updateMobileQuantity = "UPDATE Mobiles SET Quantity = ? Where SLNo = ?"; IfxCommand cmd1 = new IfxCommand(updateMobileQuantity, Con); cmd1.Parameters.Add("quantity", IfxType.Int).Value = newMobileQuantity; cmd1.Parameters.Add("slno", IfxType.Serial).Value = SLNo; cmd1.ExecuteNonQuery(); try { insertNewOrder(Con, order); } catch (Exception ex) { string createOrderTable = "Create table orderdetails (orderid serial PRIMARY KEY, SLNo int, MobileName nvarchar(100) NULL, " + " Description nvarchar(250) NULL, PicURL nvarchar(250) NULL, Model nvarchar(50) NULL, Features nvarchar(200) NULL, " + "Color nvarchar(20) NULL, SimType nvarchar(10) NULL, PurchaseDate varchar(50), Price decimal(18, 2), Quantity int NULL, TotalAmount decimal(18,2))"; IfxCommand cmd2 = new IfxCommand(createOrderTable, Con); cmd2.ExecuteNonQuery(); insertNewOrder(Con, order); } finally { Con.Close(); order.ErrorMessage = "Purchase successful"; } } Con.Close(); } Con.Open(); string delQuery = "DELETE FROM Cart"; IfxCommand delCmd = new IfxCommand(delQuery, Con); delCmd.ExecuteNonQuery(); Con.Close(); } return(RedirectToAction("Index")); }
private int EjecutarSQLAutonumerico(string cadenaSQL, List <CamposTabla> lp, object unaConexion = null, object unaTransaccion = null) { bool exeAutoNumerico = false; //OBJETO PARA MANIPULACION DE CADENA SQL StringBuilder sqlNueva = new StringBuilder(cadenaSQL); //VERIFICACION DE CAMPOS DEFINIDOS COMO AUTONUMERICOS if (lp.Count(x => x.Autonumerico == true) == 1) { exeAutoNumerico = true; } else { exeAutoNumerico = false; } //DETERMINAMOS NOMBRE DE TABLA /*if (cadenaSQL.Contains("insert into")) * { * mensaje = "agregando"; * } * if (cadenaSQL.Contains("update ")) * { * mensaje = "actualizando"; exeAutoNumerico = false; * } * if (cadenaSQL.Contains("delete from")) * { * mensaje = "eliminando"; exeAutoNumerico = false; * }*/ //CREAMOS OBJETO COMANDO IfxCommand command = new IfxCommand(); command.CommandType = System.Data.CommandType.Text; //SI ES EJECUCION DE AUTONUMERICO ESTABLECE LA DEVOLUCION DEL VALOR DE LA SECUENCIA if (exeAutoNumerico) { sqlNueva.Replace("{%}", "coalesce"); } //CREACION DE PARAMETROS EN EL COMANDO Y DESCARTE DE CAMPOS AUTONUMERICOS foreach (CamposTabla item in lp) { if (!item.Nombre.Contains("#")) { IfxParameter parametro = new IfxParameter(); parametro.ParameterName = "@" + item.Nombre; if (item.TipoEstablecido) { parametro.DbType = item.Tipo; parametro.Size = item.Tamaño; } parametro.Direction = item.Direccion; parametro.Value = item.Valor; command.Parameters.Add(parametro); } } //command.CommandText = sqlNueva.Replace("@", ":").ToString(); command.CommandText = sqlNueva.ToString(); //ASIGNACION DE CONEXION Y TRANSACCION if (unaConexion == null) { command.Connection = this.Conexion; } else { command.Connection = (IfxConnection)unaConexion; } if (unaConexion != null && unaTransaccion != null) { command.Transaction = (IfxTransaction)unaTransaccion; } //RESULTADO DEVUELTO int resultado = 0; try { if (unaConexion == null) { if (Conexion.State == System.Data.ConnectionState.Closed) { Conexion.Open(); } } //EJECUCION DE COMANDO if (exeAutoNumerico) { //CAPTURAMOS VALOR AUTONUMERICO DE TIPO SEQUENCE CUANDO EXEAUTONUM == TRUE IfxDataReader rd = (IfxDataReader)ExecuteReader(command); rd.Read(); resultado = Convert.ToInt32(rd["valorActual"]); rd.Close(); rd.Dispose(); rd = null; } else { //DEVOLVEMOS EL NUMERO DE REGISTROS AFECTADOS CUANDO EXEAUTONUM == FALSE resultado = ExecuteNonQuery(command); } } catch (IfxException) { //throw new Exception("Error " + mensaje + " Registro \nDetalle: " + ex.ToString(), ex); throw; } /*catch (Exception) * { * //throw new Exception("Error " + mensaje + " Registro \nDetalle: " + ex.ToString(), ex); * throw; * }*/ finally { //DESTRUCCION DE OBJETOS Y CIERRE DE CONEXION command.Dispose(); if (unaConexion == null) { if (conexionLocal != null) { if (Conexion.State == System.Data.ConnectionState.Open) { Conexion.Close(); conexionLocal = null; } } } //mensaje = null; sqlNueva = null; } //DEVOLUCION DE RESULTADO return(resultado); }
public ActionResult Create(Mobiles mobilesModel) { // To create a Unique file name and URL everytime when User upload a new picture string ImageFileName = Path.GetFileNameWithoutExtension(mobilesModel.ImageFile.FileName); string ImageFileExtension = Path.GetExtension(mobilesModel.ImageFile.FileName); string FinalImageName = ImageFileName + DateTime.Now.ToString("yymmssfff") + ImageFileExtension; mobilesModel.PicURL = FinalImageName; // To save that newly uploaded image to Disk location inside wwwroot/Images folder var uploads = Path.Combine(hostingEnvironment.WebRootPath, "Images"); var imagePath = Path.Combine(uploads, FinalImageName); FileStream fileStream = new FileStream(imagePath, FileMode.Create); mobilesModel.ImageFile.CopyTo(fileStream); fileStream.Close(); //string fileByteArray = null; //var fileBytes = 0; /* * if (mobilesModel.ImageFile.Length > 0) * { * using (var ms = new MemoryStream()) * { * //mobilesModel.ImageFile.CopyTo(ms); * //var fileBytes = ms.ToArray(); * //fileByteArray = Convert.ToBase64String(); * // act on the Base64 data * * // To save the newly added Mobile and the Image disk imagePath to Database table (Mobiles) */ using (IfxConnection Con = new IfxConnection(connString)) { Con.Open(); // Insert the form data into mobiles table but not the picture string query = "INSERT INTO Mobiles (MobileName, Price, Quantity, Description, PicURL, Model, Features, Color, SimType) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"; IfxCommand cmd = new IfxCommand(query, Con); cmd.Parameters.Add("mobilename", IfxType.VarChar).Value = mobilesModel.MobileName; cmd.Parameters.Add("price", IfxType.Decimal).Value = mobilesModel.Price; cmd.Parameters.Add("quantity", IfxType.Int).Value = mobilesModel.Quantity; cmd.Parameters.Add("description", IfxType.VarChar).Value = mobilesModel.Description; cmd.Parameters.Add("picurl", IfxType.VarChar).Value = mobilesModel.PicURL; cmd.Parameters.Add("model", IfxType.VarChar).Value = mobilesModel.Model; cmd.Parameters.Add("features", IfxType.VarChar).Value = mobilesModel.Features; cmd.Parameters.Add("color", IfxType.VarChar).Value = mobilesModel.Color; cmd.Parameters.Add("simtype", IfxType.VarChar).Value = mobilesModel.SimType; cmd.ExecuteNonQuery(); cmd.Dispose(); // Getting the latest inserted row's slno to insert the picture in the same row string selQuery = "Select max(slno) from Mobiles"; IfxCommand selcmd = new IfxCommand(selQuery, Con); int serialnumber = -1; try { IfxDataReader rows = selcmd.ExecuteReader(); while (rows.Read()) { serialnumber = Convert.ToInt32(rows[0]); } rows.Close(); selcmd.Dispose(); string updatePicQuery = "update mobiles set(imagefile) = (Filetoblob(" + "'" + imagePath + "'" + ", 'client', 'mobiles', 'imagefile')) where slno = ?"; IfxCommand insertPiccmd = new IfxCommand(updatePicQuery, Con); insertPiccmd.Parameters.Add("slno", IfxType.Int).Value = serialnumber; insertPiccmd.ExecuteNonQuery(); insertPiccmd.Dispose(); // Delete the temprary created image file from Disk FileInfo file = new FileInfo(imagePath); if (file.Exists) { file.Delete(); } } catch (IfxException ex) { } finally { Con.Close(); } } return(RedirectToAction("Index")); }
static void CheckSequenceGaps() { Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}"); try { using (IfxConnection connection = EstablishConnection()) using (var dataContext = new DatabaseSyncDataContext()) { var missingTransactions = dataContext.GetTransactionSequenceNrGaps(CurrentMonth, FollowingMonth); foreach (var missingtrans in missingTransactions) { try { Log.LogTrace($"Requesting {missingtrans.Lane}:{missingtrans.Sequencenr}"); IfxCommand command = connection.CreateCommand(); command.CommandText = $"SELECT * FROM p_trans WHERE ln_id='{missingtrans.Lane}' AND tx_seq_nr='{missingtrans.Sequencenr}'"; IfxDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { Log.LogTrace($"Found {missingtrans.Lane}:{missingtrans.Sequencenr}"); var trans = new StagingTransaction(); try { int i = 0; trans.pl_id = dataReader[i].ToString().Trim(); trans.ln_id = dataReader[++i].ToString().Trim(); trans.dt_concluded = ExtractDatetimeValue(dataReader[++i]).Value; trans.tx_seq_nr = int.TryParse(dataReader[++i].ToString(), out var outInt) ? outInt : throw new InvalidDataException("Invalid transaction sequence number."); trans.ts_seq_nr = ExtractIntegerValue(dataReader[++i].ToString()); trans.us_id = dataReader[++i].ToString().Trim(); trans.ent_plz_id = dataReader[++i].ToString().Trim(); trans.ent_lane_id = dataReader[++i].ToString().Trim(); trans.dt_started = ExtractDatetimeValue(dataReader[++i]); trans.next_inc = ExtractIntegerValue(dataReader[++i].ToString()); trans.prev_inc = ExtractIntegerValue(dataReader[++i].ToString()); trans.ft_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.pg_group = ExtractIntegerValue(dataReader[++i].ToString()); trans.cg_group = ExtractIntegerValue(dataReader[++i].ToString()); trans.vg_group = dataReader[++i].ToString().Trim(); trans.mvc = dataReader[++i].ToString().Trim(); trans.avc = dataReader[++i].ToString().Trim(); trans.svc = dataReader[++i].ToString().Trim(); trans.loc_curr = dataReader[++i].ToString().Trim(); trans.loc_value = dataReader.GetIfxDecimal(++i).ToString(); trans.ten_curr = dataReader[++i].ToString().Trim(); trans.ten_value = dataReader.GetIfxDecimal(++i).ToString(); trans.loc_change = dataReader.GetIfxDecimal(++i).ToString(); trans.variance = dataReader.GetIfxDecimal(++i).ToString(); trans.er_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.pm_id = dataReader[++i].ToString().Trim(); trans.card_nr = dataReader[++i].ToString().Trim(); trans.mask_nr = dataReader[++i].ToString().Trim(); trans.bin_nr = dataReader[++i].ToString().Trim(); trans.serv_code = dataReader[++i].ToString().Trim(); trans.ca_id = dataReader[++i].ToString().Trim(); trans.ct_id = dataReader[++i].ToString().Trim(); trans.it_id = dataReader[++i].ToString().Trim(); trans.sec_card_nr = dataReader[++i].ToString().Trim(); trans.lm_id = dataReader[++i].ToString().Trim(); trans.as_id = dataReader[++i].ToString().Trim(); trans.reg_nr = dataReader[++i].ToString().Trim(); trans.vouch_nr = ExtractIntegerValue(dataReader[++i].ToString()); trans.ac_nr = dataReader[++i].ToString().Trim(); trans.rec_nr = dataReader[++i].ToString().Trim(); trans.tick_nr = dataReader[++i].ToString().Trim(); trans.bp_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.fg_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.dg_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.rd_id = dataReader[++i].ToString().Trim(); trans.rep_indic = dataReader[++i].ToString().Trim(); trans.maint_indic = dataReader[++i].ToString().Trim(); trans.req_indic = ExtractIntegerValue(dataReader[++i].ToString()); trans.iv_prt_indic = dataReader[++i].ToString().Trim(); trans.ts_dt_started = ExtractDatetimeValue(dataReader[++i]); trans.iv_nr = ExtractIntegerValue(dataReader[++i].ToString()); trans.td_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.avc_seq_nr = ExtractIntegerValue(dataReader[++i].ToString()); trans.update_us_id = dataReader[++i].ToString().Trim(); trans.card_bank = dataReader[++i].ToString().Trim(); trans.card_ac_nr = dataReader[++i].ToString().Trim(); trans.tg_mfg_id = dataReader[++i].ToString().Trim(); trans.tg_post_bal = dataReader.GetIfxDecimal(++i).ToString(); trans.tg_reader = dataReader[++i].ToString().Trim(); trans.tg_us_cat = dataReader[++i].ToString().Trim(); trans.tg_card_type = dataReader[++i].ToString().Trim(); trans.tg_serv_prov_id = dataReader[++i].ToString().Trim(); trans.tg_issuer = dataReader[++i].ToString().Trim(); trans.tg_tx_seq_nr = ExtractIntegerValue(dataReader[++i].ToString()); trans.etc_context_mrk = dataReader[++i].ToString().Trim(); trans.etc_manufac_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.etc_beacon_id = ExtractIntegerValue(dataReader[++i].ToString()); trans.etc_contract_pv = dataReader[++i].ToString().Trim(); trans.avc_dt_concluded = ExtractDatetimeValue(dataReader[++i]); trans.avc_status = dataReader[++i].ToString().Trim(); trans.anpr_vln = dataReader[++i].ToString().Trim(); trans.anpr_conf = ExtractIntegerValue(dataReader[++i].ToString()); trans.lvc = dataReader[++i].ToString().Trim(); trans.inc_ind = ExtractIntegerValue(dataReader[++i].ToString()); trans.id_vl = dataReader[++i].ToString().Trim(); trans.vl_vln = dataReader[++i].ToString().Trim(); trans.anpr_seq_nr = ExtractIntegerValue(dataReader[++i].ToString()); if (!dataContext.ImportedTransactions.Any(x => x.ln_id == trans.ln_id && x.tx_seq_nr == trans.tx_seq_nr && x.dt_concluded == trans.dt_concluded)) { dataContext.ImportedTransactions.Insert(trans); } else { Log.LogTrace($"Trans already exists {trans.ln_id} : {trans.tx_seq_nr} : {trans.dt_concluded}"); } } catch (Exception ex) { Log.LogException(ex); } } dataContext.Save(); dataReader.Close(); } catch (Exception e) { Console.WriteLine(e); } } } } catch (Exception ex) { Log.LogException(ex); } Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}"); }
private static void TableCounts() { using (var dataContext = new DatabaseSyncDataContext()) using (IfxConnection connection = EstablishConnection()) { IfxCommand command = connection.CreateCommand(); command.CommandText = $"select count(*), ln_id, tx_seq_nr " + $"from informix.p_trans " + $"where dt_concluded >= TO_DATE('2018-11-01 00:00:00', '%Y-%m-%d %H:%M:%S') " + $"and dt_concluded < TO_DATE('2018-12-01 00:00:00','%Y-%m-%d %H:%M:%S') and ln_id='04RS' " + $"Group by ln_id,tx_seq_nr " + $"order by tx_seq_nr "; IfxDataReader dataReader = command.ExecuteReader(); List <string> s = new List <string>(); while (dataReader.Read()) { var d = $"{dataReader[0].ToString().Trim()} : {dataReader[1].ToString().Trim()}"; s.Add(d); //if (!dataContext.ImportedTransactions.Any(x => x.ln_id == "03RS" && x.tx_seq_nr.ToString() == d)) // Console.WriteLine(d); } File.WriteAllLines("seqNrs.txt", s); /*IfxCommand command2 = connection.CreateCommand(); * command2.CommandText = * $"select count(*) from informix.p_trans where dt_concluded >= TO_DATE('2018-11-01 00:00:00', '%Y-%m-%d %H:%M:%S') and dt_concluded<TO_DATE('2018-12-01 00:00:00','%Y-%m-%d %H:%M:%S')"; * * * * * IfxDataReader dataReader2 = command2.ExecuteReader(); * * while (dataReader2.Read()) * { * Console.WriteLine(dataReader2[0].ToString().Trim()); * } * * IfxCommand command3 = connection.CreateCommand(); * command3.CommandText = * $"select count(*) from informix.p_trans where dt_concluded >= TO_DATE('2018-12-01 00:00:00', '%Y-%m-%d %H:%M:%S') and dt_concluded<TO_DATE('2019-01-01 00:00:00','%Y-%m-%d %H:%M:%S')"; * * * * * IfxDataReader dataReader3 = command3.ExecuteReader(); * * while (dataReader3.Read()) * { * Console.WriteLine(dataReader3[0].ToString().Trim()); * } * * IfxCommand command4 = connection.CreateCommand(); * command4.CommandText = * $"select count(*) from informix.p_trans where dt_concluded >= TO_DATE('2019-01-01 00:00:00', '%Y-%m-%d %H:%M:%S') and dt_concluded<TO_DATE('2019-02-01 00:00:00','%Y-%m-%d %H:%M:%S')"; * * * * * IfxDataReader dataReader4 = command4.ExecuteReader(); * * while (dataReader4.Read()) * { * Console.WriteLine(dataReader4[0].ToString().Trim()); * }*/ } }
public Search Select(string sqlCount, string sql) { String _connectionString = "Server=ol_svr_custom;" + "Host=localhost;" + "Service=turbo;" + "Database=registration;" + "User ID=informix;" + "Password=123456;" + "Client Locale=ru_ru.CP1251;" + "Database Locale=ru_ru.915;" + "Max Pool Size=500;" + "Pooling=True;" + "Protocol=olsoctcp;" + "Connection Lifetime=1200;" + "Connection Timeout=1;"; try { IfxConnection _Connection = new IfxConnection() { ConnectionString = _connectionString }; _Connection.Open(); try { Search _Search = new Search { Total = "0", ListCitizen = new List <Citizen>() }; IfxCommand _command = new IfxCommand { Connection = _Connection, CommandText = sqlCount }; IfxDataReader _dataReader = _command.ExecuteReader(); while (_dataReader.Read()) { _Search.Total = _dataReader.GetString(0); } _dataReader.Close(); _command.CommandText = sql; _dataReader = _command.ExecuteReader(); while (_dataReader.Read()) { if (!_dataReader.IsDBNull(0)) { Citizen citizen = new Citizen(); citizen.Id = _dataReader.GetString(0); citizen.LastName = _dataReader.GetString(1); citizen.FirstName = _dataReader.GetString(2); citizen.MiddleName = _dataReader.GetString(3); string dt = _dataReader.GetString(4); if (dt.Length == 10) { citizen.DateOfBirth = _dataReader.GetDateTime(4).ToString("d"); } _Search.ListCitizen.Add(citizen); } } if (_dataReader != null) { _dataReader.Close(); } if (_Connection != null) { _Connection.Close(); } return(_Search); } catch { return(null); } } catch { return(null); } }
public ViewResult Purchase(OrderDetailsModel orderDetailsModel) { using (IfxConnection Con = new IfxConnection(connString)) { Con.Open(); int availableQuantity = 0; string selectProductDetails = "select Count from product where productid = ?"; IfxCommand cmd = new IfxCommand(selectProductDetails, Con); cmd.Parameters.Add("productid", IfxType.Serial).Value = orderDetailsModel.ProductID; try { IfxDataReader rows = cmd.ExecuteReader(); while (rows.Read()) { availableQuantity = Convert.ToInt32(rows[0]); } rows.Close(); } catch (IfxException ex) { Con.Close(); orderDetailsModel.ErrorMessage = "Error : " + ex.Message; } if (orderDetailsModel.Count > availableQuantity) { Con.Close(); orderDetailsModel.ErrorMessage = "Cannot purchase " + orderDetailsModel.Count + " quantities, available quantities are : " + availableQuantity; } else { int newProductQuantity = availableQuantity - orderDetailsModel.Count; string updateProductQuantity = "UPDATE Product SET count = ? Where productid = ?"; IfxCommand cmd1 = new IfxCommand(updateProductQuantity, Con); cmd1.Parameters.Add("count", IfxType.Int).Value = newProductQuantity; cmd1.Parameters.Add("productid", IfxType.Serial).Value = orderDetailsModel.ProductID; cmd1.ExecuteNonQuery(); try { insertNewOrder(Con, orderDetailsModel); } catch (Exception ex) { string createOrderTable = "Create table orderdetails (orderid serial PRIMARY KEY, productid int, productname varchar(50), price decimal(18,2), count int, totalamount decimal(18,2))"; IfxCommand cmd2 = new IfxCommand(createOrderTable, Con); cmd2.ExecuteNonQuery(); insertNewOrder(Con, orderDetailsModel); } finally { Con.Close(); orderDetailsModel.ErrorMessage = "Purchase successful"; } } return(View(orderDetailsModel)); //return RedirectToAction("Index"); } }