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));
}
private IDataReader EjecutarQuery(string cadenaSQL, List <CamposTabla> lp, object unaConexion, object unaTransaccion = null)
{
//IfxCommand command = new IfxCommand(cadenaSQL.Replace("@", ":"));
//IDataReader rd = null;
IfxCommand command = new IfxCommand();
command.Connection = (IfxConnection)unaConexion;
if (unaTransaccion != null)
{
command.Transaction = (IfxTransaction)unaTransaccion;
}
command.CommandType = System.Data.CommandType.Text;
foreach (CamposTabla item in lp)
{
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 = cadenaSQL;
try
{
if (unaConexion == null)
{
if (Conexion.State == System.Data.ConnectionState.Closed)
{
Conexion.Open();
}
}
//rd = ExecuteReader(command.Clone());
return(ExecuteReader(command));
}
catch (Exception)
{
//throw new Exception("Error obteniendo registros \nDetalle: " + ex.ToString(), ex);
throw;
}
finally
{
if (command != null)
{
command.Dispose();
}
command = null;
}
//return rd;
}
public override void BorrarCmdPreparado()
{
exeAutoNum = false;
if (comandoPreparado != null)
{
comandoPreparado.Dispose();
}
comandoPreparado = null;
}
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"));
}