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")); }