public EntSucursal Obtener(int id)
{
EntSucursal entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
IfxCommand cmd = new IfxCommand(string.Empty, Conexion);
cmd.CommandText = "SELECT a.id_sucursal, a.desc_sucursal,a.id_empresa, b.razon_social FROM informix.sucursales a left join informix.empresa b on a.id_empresa = b.id_empresa WHERE a.id_sucursal=?";
cmd.Parameters.Add(new IfxParameter()).Value = id;
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
entidad = new EntSucursal();
entidad.id_sucursal = int.Parse(dr["id_sucursal"].ToString());
entidad.desc_sucursal = dr["desc_sucursal"].ToString();
entidad.id_empresa = int.Parse(dr["id_empresa"].ToString());
entidad.razon_social = dr["razon_social"].ToString();
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(entidad);
}
public EntDepartamento Obtener(int id)
{
EntDepartamento entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
IfxCommand cmd = new IfxCommand(string.Empty, Conexion);
cmd.CommandText = "SELECT id_departamento, desc_departamento FROM informix.departamentos WHERE id_departamento=?";
cmd.Parameters.Add(new IfxParameter()).Value = id;
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
entidad = new EntDepartamento();
entidad.id_departamento = int.Parse(dr["id_departamento"].ToString());
entidad.desc_departamento = dr["desc_departamento"].ToString();
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(entidad);
}
public List <EntSucursal> ObtenerTodos()
{
List <EntSucursal> Lista = new List <EntSucursal>();
EntSucursal entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
var sql = "SELECT a.id_sucursal, a.desc_sucursal,a.id_empresa, b.razon_social FROM informix.sucursales a left join informix.empresa b on a.id_empresa = b.id_empresa";
IfxCommand cmd = new IfxCommand(sql, Conexion);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
entidad = new EntSucursal();
entidad.id_sucursal = int.Parse(dr["id_sucursal"].ToString());
entidad.desc_sucursal = dr["desc_sucursal"].ToString();
entidad.id_empresa = int.Parse(dr["id_empresa"].ToString());
entidad.razon_social = dr["razon_social"].ToString();
Lista.Add(entidad);
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(Lista);
}
//public List<EntEnrolamiento> ObtenerTodosEnrolados(int id_empleado)
//{
// List<EntEnrolamiento> Lista = new List<EntEnrolamiento>();
// EntEnrolamiento entidad = null;
// try
// {
// AbrirConexion();
// StringBuilder CadenaSql = new StringBuilder();
// var sql = "SELECT id_enrolamiento, id_empleado, id_dispositivo,enrollnumber FROM informix.enrolamiento WHERE id_empleado=?";
// IfxCommand cmd = new IfxCommand(sql, Conexion);
// using (var dr = cmd.ExecuteReader())
// {
// while (dr.Read())
// {
// entidad = new EntEnrolamiento();
// entidad.id_enrolamiento = int.Parse(dr["id_enrolamiento"].ToString());
// entidad.id_empleado = int.Parse(dr["id_empleado"].ToString());
// entidad.id_dispositivo = int.Parse(dr["id_dispositivo"].ToString());
// entidad.enrollnumber = int.Parse(dr["enrollnumber"].ToString());
// Lista.Add(entidad);
// }
// }
// }
// catch (Exception exc)
// {
// throw exc;
// }
// finally
// {
// CerrarConexion();
// }
// return Lista;
//}
public List <EntEnrolamiento> ObtenerTodosporEmpleado(int id_empleado)
{
List <EntEnrolamiento> Lista = new List <EntEnrolamiento>();
EntEnrolamiento entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
var sql = "SELECT id_enrolamiento, id_empleado, id_dispositivo,enrollnumber FROM informix.enrolamiento WHERE id_empleado=?";
IfxCommand cmd = new IfxCommand(sql, Conexion);
cmd.Parameters.Add(new IfxParameter()).Value = id_empleado;
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
entidad = new EntEnrolamiento();
entidad.id_enrolamiento = int.Parse(dr["id_enrolamiento"].ToString());
entidad.id_empleado = int.Parse(dr["id_empleado"].ToString());
entidad.id_dispositivo = int.Parse(dr["id_dispositivo"].ToString());
entidad.enrollnumber = int.Parse(dr["enrollnumber"].ToString());
Lista.Add(entidad);
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(Lista);
}
public IActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
try
{
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM orderdetails", Con);
ifx.Fill(table);
}
catch (Exception ex)
{
string createTable = "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 cmd = new IfxCommand(createTable, Con);
cmd.ExecuteNonQuery();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM orderdetails", Con);
ifx.Fill(table);
}
finally
{
Con.Close();
}
return(View(table));
}
}
public static DataTable RunSPReturnDataTable(string spName, Paras paras, IfxConnection conn)
{
try
{
IfxCommand salesCMD = new IfxCommand(spName, conn);
salesCMD.CommandType = CommandType.StoredProcedure;
/// 加上他们的餐数
foreach (Para para in paras)
{
IfxParameter myParm = salesCMD.Parameters.Add(para.ParaName, para.DAType);
myParm.Value = para.val;
}
//selectCMD.CommandTimeout =60;
IfxDataAdapter sda = new IfxDataAdapter(salesCMD);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
DataTable dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
return(dt);
}
catch (System.Exception ex)
{
throw ex;
}
}
public EntEnrolamiento Obtener(int id_enrolamiento)
{
EntEnrolamiento entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
IfxCommand cmd = new IfxCommand(string.Empty, Conexion);
cmd.CommandText = "SELECT id_enrolamiento, id_empleado, id_dispositivo,enrollnumber FROM informix.enrolamiento WHERE id_enrolamiento=?";
cmd.Parameters.Add(new IfxParameter()).Value = id_enrolamiento;
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
entidad = new EntEnrolamiento();
entidad.id_enrolamiento = int.Parse(dr["id_enrolamiento"].ToString());
entidad.id_empleado = int.Parse(dr["id_empleado"].ToString());
entidad.id_dispositivo = int.Parse(dr["id_dispositivo"].ToString());
entidad.enrollnumber = int.Parse(dr["enrollnumber"].ToString());
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(entidad);
}
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 List <EntVistas> ObtenerTodos()
{
List <EntVistas> Lista = new List <EntVistas>();
EntVistas entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
var sql = "SELECT id_vista, ventana, url FROM informix.vistas";
IfxCommand cmd = new IfxCommand(sql, Conexion);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
entidad = new EntVistas();
entidad.id_vista = int.Parse(dr["id_vista"].ToString());
entidad.ventana = dr["ventana"].ToString();
entidad.url = dr["url"].ToString();
Lista.Add(entidad);
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(Lista);
}
public List <EntRolesVista> ObtenerTodos()
{
List <EntRolesVista> Lista = new List <EntRolesVista>();
EntRolesVista entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
var sql = "SELECT id_rol_vista, id_rol, id_vista FROM informix.roles_vista";
IfxCommand cmd = new IfxCommand(sql, Conexion);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
entidad = new EntRolesVista();
entidad.id_rol_vista = int.Parse(dr["id_rol_vista"].ToString());
entidad.id_rol = int.Parse(dr["id_rol"].ToString());
entidad.id_vista = int.Parse(dr["id_vista"].ToString());
Lista.Add(entidad);
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(Lista);
}
public List <EntJornadaEmpleado> ObtenerTodos()
{
List <EntJornadaEmpleado> Lista = new List <EntJornadaEmpleado>();
EntJornadaEmpleado entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
var sql = "SELECT id_jornada_empleado, id_jornada, id_empleado FROM informix.jornada_empleado";
IfxCommand cmd = new IfxCommand(sql, Conexion);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
entidad = new EntJornadaEmpleado();
entidad.id_jornada_empleado = int.Parse(dr["id_jornada_empleado"].ToString());
entidad.id_jornada = int.Parse(dr["id_jornada"].ToString());
entidad.id_empleado = int.Parse(dr["id_empleado"].ToString());
Lista.Add(entidad);
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(Lista);
}
public bool Eliminar(int id)
{
bool respuesta = false;
try
{
AbrirConexion();
var sql = "execute procedure dml_roles_vista (?,NULL,?,NULL);";
using (var cmd = new IfxCommand(sql, Conexion))
{
cmd.Connection = Conexion;
cmd.Parameters.Add(new IfxParameter()).Value = "DELETE";
cmd.Parameters.Add(new IfxParameter()).Value = id;
cmd.ExecuteNonQuery();
}
respuesta = true;
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(respuesta);
}
public EntRolesVista Obtener(int id)
{
EntRolesVista entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
IfxCommand cmd = new IfxCommand(string.Empty, Conexion);
cmd.CommandText = "SELECT id_rol_vista, id_rol, id_vista FROM informix.roles_vista WHERE id_rol_vista=?";
cmd.Parameters.Add(new IfxParameter()).Value = id;
using (var dr = cmd.ExecuteReader())
{
if (dr.Read())
{
entidad = new EntRolesVista();
entidad.id_rol_vista = int.Parse(dr["id_rol_vista"].ToString());
entidad.id_rol = int.Parse(dr["id_rol"].ToString());
entidad.id_vista = int.Parse(dr["id_vista"].ToString());
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(entidad);
}
private bool UpdateMISHotlist(IMISHotlistUpdate update, string encryptedIdentifier)
{
Log.LogInfoMessage($"[Enter] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
try
{
using (IfxConnection connection = EstablishConnection())
{
IfxCommand command = connection.CreateCommand();
if (update.Change == "Add")
{
command.CommandText = $"INSERT INTO Hotlist VALUES ('{encryptedIdentifier}') ";
}
else if (update.Change == "Delete")
{
command.CommandText = $"DELETE FROM Hotlist WHERE ac_nr = '{encryptedIdentifier}'";
}
}
}
catch (Exception ex)
{
Log.LogException(ex);
Log.LogTrace(ex.Message + ". Check error log for more details.");
return(false);
}
Log.LogInfoMessage($"[Exit] {System.Reflection.MethodBase.GetCurrentMethod().Name}");
return(true);
}
public static Response ExecuteNonQuery(string query, Connection connection, bool call = true)
{
using (var ifxConnection = new IfxConnection(connection.ConnectionString))
{
try
{
ifxConnection.Open();
var ifxCommand = new IfxCommand(query)
{
Connection = ifxConnection,
CommandTimeout = 0
};
return(new Response(ifxCommand.ExecuteNonQuery()));
}
catch (Exception exception)
{
if (!call)
{
return(new Response(Status.Exception, exception.Message));
}
ExecuteNonQuery(query, connection, false);
return(new Response(Status.Exception, exception.Message));
}
finally
{
ifxConnection.Close();
}
}
}
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 ActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
try
{
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Product", Con);
ifx.Fill(table);
}
catch (Exception ex)
{
string createProductTable = "Create table Product (productid serial PRIMARY KEY, productname varchar(50), price decimal(18,2), count int)";
IfxCommand cmd = new IfxCommand(createProductTable, Con);
cmd.ExecuteNonQuery();
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 cmd1 = new IfxCommand(createOrderTable, Con);
cmd1.ExecuteNonQuery();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Product", Con);
ifx.Fill(table);
}
}
return(View(table));
}
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 IActionResult Index()
{
DataTable table = new DataTable();
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
try
{
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Mobiles", Con);
ifx.Fill(table);
}
catch (Exception ex)
{
string createTable = "Create table Mobiles (SLNo serial PRIMARY KEY, MobileName nvarchar(100) NULL, Price decimal(18, 2)," +
" Quantity int 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, ImageFile Blob)";
IfxCommand cmd = new IfxCommand(createTable, Con);
cmd.ExecuteNonQuery();
IfxDataAdapter ifx = new IfxDataAdapter("SELECT * FROM Mobiles", Con);
ifx.Fill(table);
}
finally
{
Con.Close();
}
}
return(View(table));
}
public List <EntDepartamento> ObtenerTodos()
{
List <EntDepartamento> Lista = new List <EntDepartamento>();
EntDepartamento entidad = null;
try
{
AbrirConexion();
StringBuilder CadenaSql = new StringBuilder();
var sql = "SELECT id_departamento, desc_departamento FROM informix.departamentos";
IfxCommand cmd = new IfxCommand(sql, Conexion);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
entidad = new EntDepartamento();
entidad.id_departamento = int.Parse(dr["id_departamento"].ToString());
entidad.desc_departamento = dr["desc_departamento"].ToString();
Lista.Add(entidad);
}
}
}
catch (Exception exc)
{
throw exc;
}
finally
{
CerrarConexion();
}
return(Lista);
}
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 void button1_Click(object sender, RoutedEventArgs e)
{
if (textBoxUserName.Text.Length == 0)
{
errormessage.Text = "Enter a user name.";
textBoxUserName.Focus();
}
else
{
string username = textBoxUserName.Text;
string password = passwordBox1.Password;
DataSet dataSet = new DataSet();
string selectSQL = "Select * from user where username='******' and password='******'";
con = new IfxConnection(cs);
con.Open();
try
{
IfxCommand cmd = new IfxCommand(selectSQL, con);
cmd.CommandType = CommandType.Text;
IfxDataAdapter adapter = new IfxDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataSet);
if (dataSet.Tables[0].Rows.Count > 0)
{
string name = dataSet.Tables[0].Rows[0]["FirstName"].ToString() + " " + dataSet.Tables[0].Rows[0]["LastName"].ToString();
string userType = dataSet.Tables[0].Rows[0]["UserType"].ToString();
if (userType == "Admin")
{
AllUserDetails allUser = new AllUserDetails(name);
allUser.Show();
}
else
{
UserProfile userProfile = new UserProfile();
userProfile.TextBlockName.Text = name;
userProfile.Show();
}
Close();
}
else
{
errormessage.Text = "Sorry! Please enter correct username/password.";
}
}
catch (Exception ex)
{
errormessage.Text = "You need to register first before login.";
}
finally
{
con.Close();
}
}
}
public bool Update(EntDispositivo entidad)
{
bool respuesta = false;
try
{
var sql = string.Empty;
AbrirConexion();
if (entidad.imagen != null)
{
sql = "execute procedure dml_dispositivo (?,?,?,?,?,?,?,?,?,?);";
}
else
{
sql = "execute procedure dml_dispositivo (?,?,?,?,?,?,?,NULL,?,?);";
}
using (var cmd = new IfxCommand(sql, Conexion))
{
cmd.Connection = Conexion;
cmd.Parameters.Add(new IfxParameter()).Value = "UPDATE";
cmd.Parameters.Add(new IfxParameter()).Value = entidad.id_dispositivo;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.nombre_dispositivo;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.numero_serie;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.ip_dispositivo;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.puerto;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.id_sucursal;
if (entidad.imagen != null)
{
cmd.Parameters.Add(new IfxParameter()).Value = entidad.imagen;
}
cmd.Parameters.Add(new IfxParameter()).Value = entidad.rh;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.numeroequipo;
cmd.ExecuteNonQuery();
}
respuesta = true;
}
catch (InvalidCastException ex)
{
ApplicationException excepcion = new ApplicationException("Se genero un error con el siguiente mensaje: " + ex.Message, ex);
excepcion.Source = "Update Dispositivo";
throw excepcion;
}
catch (Exception ex)
{
ApplicationException excepcion = new ApplicationException("Se genero un error de aplicación con el siguiente mensaje: " + ex.Message, ex);
excepcion.Source = "Update Dispositivo";
throw excepcion;
}
finally
{
CerrarConexion();
}
return(respuesta);
}
public bool Insert(EntEmpleado entidad)
{
bool respuesta = false;
try
{
var sql = string.Empty;
AbrirConexion();
if (entidad.imagen != null)
{
sql = "execute procedure dml_empleados (?,NULL,?,?,?,?,?,?,?,?);";
}
else
{
sql = "execute procedure dml_empleados (?,NULL,?,?,?,?,?,?,NULL,?);";
}
using (var cmd = new IfxCommand(sql, Conexion))
{
cmd.Connection = Conexion;
cmd.Parameters.Add(new IfxParameter()).Value = "INSERT";
cmd.Parameters.Add(new IfxParameter()).Value = entidad.nombre;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.ap_paterno;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.ap_materno;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.id_departamento;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.id_sucursal;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.enrollnumber;
if (entidad.imagen != null)
{
cmd.Parameters.Add(new IfxParameter()).Value = entidad.imagen;
}
cmd.Parameters.Add(new IfxParameter()).Value = entidad.id_dispositivo;
cmd.ExecuteNonQuery();
}
respuesta = true;
}
catch (InvalidCastException ex)
{
ApplicationException excepcion = new ApplicationException("Se genero un error con el siguiente mensaje: " + ex.Message, ex);
excepcion.Source = "Insert Empleados";
throw excepcion;
}
catch (Exception ex)
{
ApplicationException excepcion = new ApplicationException("Se genero un error de aplicación con el siguiente mensaje: " + ex.Message, ex);
excepcion.Source = "Insert Empleados";
throw excepcion;
}
finally
{
CerrarConexion();
}
return(respuesta);
}
public void Conn()
{
using (IfxConnection connection = new IfxConnection())
{
string sqlcmd = "";
using (IfxCommand cmd = new IfxCommand())
{
}
}
}
public IActionResult Index()
{
string mycmd = "select * from Mobiles";
dt = new DataTable();
try
{
dt = ecomDAL.SelactAll(mycmd);
}
catch (Exception ex)
{
string createTable = "Create table Mobiles (SLNo serial PRIMARY KEY, MobileName nvarchar(100) NULL, Price decimal(18, 2)," +
" Quantity int 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, ImageFile Blob)";
Boolean status = ecomDAL.DDLOpperation(createTable);
if (status)
{
dt = ecomDAL.SelactAll(mycmd);
}
}
List <Mobiles> list = new List <Mobiles>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Mobiles mob = new Mobiles();
mob.SLNo = Convert.ToInt32(dt.Rows[i]["SLNo"]);
mob.MobileName = dt.Rows[i]["MobileName"].ToString();
mob.Price = Convert.ToDecimal(dt.Rows[i]["Price"]);
mob.Description = dt.Rows[i]["Description"].ToString();
mob.PicURL = dt.Rows[i]["PicURL"].ToString();
list.Add(mob);
// Downloading the photo from databse and storing it on the disk
// To save that newly uploaded image to Disk location inside wwwroot/Images folder
var downloads = Path.Combine(hostingEnvironment.WebRootPath, "DownloadImages");
var imagePath = Path.Combine(downloads, mob.PicURL);
FileInfo file = new FileInfo(imagePath);
if (file.Exists)
{
file.Delete();
}
using (IfxConnection Con = new IfxConnection(connString))
{
Con.Open();
string selectImage = "select LOTOFILE (imagefile, " + "'" + imagePath + "!'" + ", 'client') from mobiles where slno = ?";
IfxCommand selectImagecmd = new IfxCommand(selectImage, Con);
selectImagecmd.Parameters.Add("slno", IfxType.Serial).Value = mob.SLNo;
selectImagecmd.ExecuteScalar();
Con.Close();
}
}
return(View(list));
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder1.Focus();
Application.DoEvents();
// Try to execute the query using current database connection:
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
{
IfxCommand command = (IfxCommand)queryBuilder1.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder1.SQL;
// handle the query parameters
if (queryBuilder1.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
{
IfxParameter parameter = new IfxParameter();
parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
parameter.DbType = queryBuilder1.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
IfxDataAdapter adapter = new IfxDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
public static int RunSP(string spName, IfxConnection conn)
{
IfxCommand cmd = new IfxCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
return(cmd.ExecuteNonQuery());
}
public bool Update(EntEmpresa entidad)
{
bool respuesta = false;
try
{
var sql = string.Empty;
AbrirConexion();
if (entidad.imagen != null)
{
sql = "execute procedure dml_empresa (?,?,?,?,?,?,?);";
}
else
{
sql = "execute procedure dml_empresa (?,?,?,?,?,?,NULL);";
}
using (var cmd = new IfxCommand(sql, Conexion))
{
cmd.Connection = Conexion;
cmd.Parameters.Add(new IfxParameter()).Value = "UPDATE";
cmd.Parameters.Add(new IfxParameter()).Value = entidad.id_empresa;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.razon_social;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.direccion;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.estado;
cmd.Parameters.Add(new IfxParameter()).Value = entidad.municipio;
if (entidad.imagen != null)
{
cmd.Parameters.Add(new IfxParameter()).Value = entidad.imagen;
}
cmd.ExecuteNonQuery();
}
respuesta = true;
}
catch (InvalidCastException ex)
{
ApplicationException excepcion = new ApplicationException("Se genero un error con el siguiente mensaje: " + ex.Message, ex);
excepcion.Source = "Update Empresa";
throw excepcion;
}
catch (Exception ex)
{
ApplicationException excepcion = new ApplicationException("Se genero un error de aplicación con el siguiente mensaje: " + ex.Message, ex);
excepcion.Source = "Update Empresa";
throw excepcion;
}
finally
{
CerrarConexion();
}
return(respuesta);
}
public List<FACSHeader> GetFacility(string tel_num)
{
try
{
List<FACSHeader> facilities = new List<FACSHeader>();
using (IfxConnection ifxcon = new IfxConnection(INFORMIXDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter
IfxCommand cmd;
IfxDataAdapter da;
// Open Connection and call stored procedure.
ifxcon.Open();
cmd = new IfxCommand("ht_one_facs_dash", ifxcon);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("p_tel_no ", IfxType.VarChar, 256).Value = tel_num;
// Fill Dataset using DataAdapter
da = new IfxDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
FACSHeader fac = new FACSHeader();
fac.Tel_Num = dr[0].ToString().Trim();
fac.Status = dr[1].ToString().Trim();
fac.Remarks = dr[2].ToString().Trim();
fac.Cable_Pair = dr[3].ToString().Trim();
fac.Port = dr[4].ToString().Trim();
facilities.Add(fac);
}
ifxcon.Close();
}
return facilities;
}
catch (Exception)
{
throw;
}
}
public List<FACSDetail> GetFacilityDetail(string tel_num)
{
try
{
List<FACSDetail> facDetails = new List<FACSDetail>();
using (IfxConnection ifxcon = new IfxConnection(INFORMIXDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter
IfxCommand cmd;
IfxDataAdapter da;
// Open Connection and call stored procedure.
ifxcon.Open();
cmd = new IfxCommand("ht_one_facs", ifxcon);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("p_tel_no ", IfxType.VarChar, 256).Value = tel_num;
// Fill Dataset using DataAdapter
da = new IfxDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
FACSDetail facD = new FACSDetail();
facD.Tel_Num = dr[0].ToString().Trim();
facD.Status = dr[1].ToString().Trim();
facD.Native_ACO = dr[2].ToString().Trim();
facD.Current_ACO = dr[3].ToString().Trim();
facD.Switch_Type = dr[4].ToString().Trim();
facD.CLLI = dr[5].ToString().Trim();
facD.Terminal = dr[6].ToString().Trim();
facD.Port = dr[7].ToString().Trim();
facD.Facilities = dr[8].ToString().Trim();
facD.XBox = dr[9].ToString().Trim();
facD.InterIsland_PIC = dr[11].ToString().Trim();
facD.InterNational_PIC = dr[10].ToString().Trim();
facD.Address = dr[12].ToString().Trim();
facD.Remarks = dr[13].ToString().Trim();
facD.Service_Type = dr[14].ToString().Trim();
facD.MITS = dr[15].ToString().Trim();
facD.SR_TN = dr[16].ToString().Trim();
facDetails.Add(facD);
}
ifxcon.Close();
}
return facDetails;
}
catch (Exception)
{
throw;
}
}
public Pic GetPic(string id, string tn, int index)
{
Pic selectedPic = new Pic();
try
{
List<Pic> pics = new List<Pic>();
using (OracleConnection con = new OracleConnection(KENANDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter.
OracleCommand cmd;
OracleDataAdapter da;
// Open Connection and call stored procedure.
con.Open();
cmd = new OracleCommand("ARBOR.HT_ONE_PKG.GET_PIC", con);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("iAccountNo", OracleType.VarChar, 20).Value = id;
cmd.Parameters["iAccountNo"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("oPicInfo", OracleType.Cursor).Direction = ParameterDirection.Output;
// Fill Dataset using DataAdapter
da = new OracleDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Pic pic = new Pic();
pic.subscr_no = dr[0].ToString().Trim();
pic.tn = dr[1].ToString().Trim();
pic.formattedTn = dr[2].ToString().Trim();
pic.mainland = dr[3].ToString().Trim();
pic.interisland = dr[4].ToString().Trim();
pic.international = dr[5].ToString().Trim();
pics.Add(pic);
}
selectedPic = pics.Where(a => a.tn == tn).Single<Pic>();
selectedPic.index = index;
con.Close();
}
}
catch (Exception)
{
throw;
}
try
{
List<NBPic> facDetails = new List<NBPic>();
using (IfxConnection ifxcon = new IfxConnection(INFORMIXDB))
{
DataSet ds = new DataSet();
// Initialize Command and DataAdapter
IfxCommand cmd;
IfxDataAdapter da;
// Open Connection and call stored procedure.
ifxcon.Open();
cmd = new IfxCommand("ht_one_facs", ifxcon);
cmd.CommandType = CommandType.StoredProcedure;
// Stored procedure parameters.
cmd.Parameters.Add("p_tel_no ", IfxType.VarChar, 256).Value = tn;
// Fill Dataset using DataAdapter
da = new IfxDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
NBPic facD = new NBPic();
facD.InterIsland_PIC = dr[11].ToString().Trim();
facD.InterNational_PIC = dr[10].ToString().Trim();
facDetails.Add(facD);
}
ifxcon.Close();
}
selectedPic.NBPics = facDetails;
}
catch (Exception)
{
throw;
}
return selectedPic;
}
public DbDataReader readQuery(String query)
{
DbCommand command = null;
if (currentDBType == DBType.Postgres)
command = new NpgsqlCommand(query, (NpgsqlConnection)dbConn);
else
command = new IfxCommand(query, (IfxConnection)dbConn);
DbDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch
{
}
return reader;
}
/// <summary>
/// Execute des requêtes INSERT / UPDATE / DELETE
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sqlQuery)
{
lock (this.dbConnexion)
{
int returnValue = -1;
if (this.options.ConnexionODBC == true)
{
OdbcCommand odbcCommand = new OdbcCommand(sqlQuery, (this.dbConnexion as OdbcConnection));
returnValue = odbcCommand.ExecuteNonQuery();
}
else
{
if (this.typeBase == Type.Informix)
{
IfxCommand ifxCommand = new IfxCommand(sqlQuery, (this.dbConnexion as IfxConnection));
returnValue = ifxCommand.ExecuteNonQuery();
}
else if (this.typeBase == Type.SQLServer)
{
SqlCommand sqlCommand = new SqlCommand(sqlQuery, (this.dbConnexion as SqlConnection));
returnValue = sqlCommand.ExecuteNonQuery();
}
}
return returnValue;
}
}
/// <summary>
/// Exécuter une requête SQL
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public IDataReader ExecuteReader(string sqlQuery)
{
if (this.dbConnexion != null)
{
lock (this.dbConnexion)
{
if (this.dataReader != null)
{
this.dataReader.Close();
}
this.dataReader = null;
this.dbCommand = null;
if (this.options.ConnexionODBC == true)
{
OdbcCommand odbcCommand = new OdbcCommand(sqlQuery, (this.dbConnexion as OdbcConnection));
OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader(CommandBehavior.Default);
this.dbCommand = odbcCommand;
this.dataReader = odbcDataReader;
}
else
{
if (this.typeBase == Type.Informix)
{
IfxCommand ifxCommand = new IfxCommand(sqlQuery, (this.dbConnexion as IfxConnection));
IfxDataReader ifxDataReader = ifxCommand.ExecuteReader(CommandBehavior.Default);
this.dbCommand = ifxCommand;
this.dataReader = ifxDataReader;
}
else if (this.typeBase == Type.SQLServer)
{
SqlCommand sqlCommand = new SqlCommand(sqlQuery, (this.dbConnexion as SqlConnection));
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.Default);
this.dbCommand = sqlCommand;
this.dataReader = sqlDataReader;
}
}
}
}
return this.dataReader;
}
