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 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 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 static bool HasColumn(this IfxDataReader reader, string columnName)
{
for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i).Equals(columnName, StringComparison.CurrentCultureIgnoreCase))
{
return(true);
}
}
return(false);
}
private static string ExtractBCDValue(IfxDataReader reader, int index)
{
try
{
return(reader[index].ToString().Trim());
}
catch (Exception)
{
return("-");
}
}
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);
}
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 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");
}
}
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}");
}
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"));
}
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 static void DataReaderToDataTableTest(MainForm frm)
{
PFInformix db = new PFInformix();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
db.ServerName = frm.txtServerName.Text;
db.DatabaseName = frm.txtDatabaseName.Text;
db.PortNumber = frm.txtPortNumber.Text;
db.Username = frm.txtUsername.Text;
db.Password = frm.txtPassword.Text;
connectionString = db.ConnectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
db.OpenConnection();
db.SQLQuery = frm.txtSQLQuery.Text;
if (frm.chkIsStoredProcedure.Checked)
{
db.CommandType = CommandType.StoredProcedure;
}
else
{
db.CommandType = CommandType.Text;
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
IfxDataReader rdr = (IfxDataReader)db.RunQueryDataReader();
DataTable tab = db.ConvertDataReaderToDataTable(rdr);
Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString());
rdr.Close();
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow r = tab.Rows[i];
_msg.Length = 0;
int maxColInx = tab.Columns.Count - 1;
for (int ci = 0; ci <= maxColInx; ci++)
{
_msg.Append(tab.Columns[ci].ColumnName);
_msg.Append(": ");
_msg.Append(r[ci].ToString());
if (ci < maxColInx)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Table read time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
frm.OutputErrorMessageToLog(ex);
}
finally
{
db.CloseConnection();
db = null;
}
}
public static void DataReaderTest(MainForm frm)
{
PFInformix db = new PFInformix();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
db.ServerName = frm.txtServerName.Text;
db.DatabaseName = frm.txtDatabaseName.Text;
db.PortNumber = frm.txtPortNumber.Text;
db.Username = frm.txtUsername.Text;
db.Password = frm.txtPassword.Text;
connectionString = db.ConnectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
db.OpenConnection();
db.SQLQuery = frm.txtSQLQuery.Text;
if (frm.chkIsStoredProcedure.Checked)
{
db.CommandType = CommandType.StoredProcedure;
}
else
{
db.CommandType = CommandType.Text;
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
Program._messageLog.WriteLine("\r\nRunning data extract tests ...\r\n");
db.returnResultAsString += new PFInformix.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderDelimitedTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
sw.Start();
IfxDataReader rdr = (IfxDataReader)db.RunQueryDataReader();
db.ExtractDelimitedDataFromDataReader(rdr, ",", "\r\n", true);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Delimiated Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderFixedLengthTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
rdr.Close();
sw.Start();
rdr = (IfxDataReader)db.RunQueryDataReader();
db.ExtractFixedLengthDataFromDataReader(rdr, true, true, false);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Fixed Length Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
rdr.Close();
rdr = (IfxDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlFile(rdr, @"c:\temp\Testrdr.xml");
rdr.Close();
rdr = (IfxDataReader)db.RunQueryDataReader();
db.SaveDataReaderWithSchemaToXmlFile(rdr, @"c:\temp\Testrdrplus.xml");
rdr.Close();
rdr = (IfxDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlSchemaFile(rdr, @"c:\temp\Testrdr.xsd");
rdr.Close();
rdr = (IfxDataReader)db.RunQueryDataReader();
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(db.ConvertDataReaderToDataTable(rdr));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
rdr.Close();
}
catch (System.Exception ex)
{
frm.OutputErrorMessageToLog(ex);
}
finally
{
db.CloseConnection();
db = null;
}
}
/// <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);
}
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());
* }*/
}
}
