/// <summary>
/// Consulta para ejecutar un procedimiento almacenado que realiza una operacion ya sea de insercion, modificacion o eliminacion de registros en la base
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public async Task Guardar(string query, SqlParameter[] parameters)
{
using (var _con = new ConexionModels())
{
await _con.Database.ExecuteSqlCommandAsync(query, parameters);
}
}
/// <summary>
/// metodo para agregar un registro en la base
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public async Task Add <TEntity>(TEntity entity) where TEntity : class
{
using (var _con = new ConexionModels())
{
_con.Set <TEntity>().Add(entity);
await _con.SaveChangesAsync();
}
}
/// <summary>
/// listo todos los registros de una tabla de forma asincrona por Entity
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public async Task <List <TEntity> > GetAll <TEntity>() where TEntity : class
{
using (var _con = new ConexionModels())
{
var lista = await _con.Set <TEntity>().ToListAsync();
return(lista);
}
}
/// <summary>
/// listo todos los registros de una tabla de forma asincrona de acuerdo al predicado y por Entity
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public async Task <List <TEntity> > GetList <TEntity>(Expression <Func <TEntity, bool> > predicate) where TEntity : class
{
using (var _con = new ConexionModels())
{
var lista = await _con.Set <TEntity>().Where(predicate).ToListAsync();
return(lista);
}
}
/// <summary>
/// obtener el registro de una tabla por el id
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public async Task <TEntity> Find <TEntity>(object id) where TEntity : class
{
using (var _con = new ConexionModels())
{
var entidad = await _db.Set <TEntity>().FindAsync(id);
return(entidad);
}
}
/// <summary>
/// actualizar un registro
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <param name="id"></param>
/// <returns></returns>
public async Task Update <TEntity>(TEntity entity, object id) where TEntity : class
{
using (var _con = new ConexionModels())
{
var entidad = await _con.Set <TEntity>().FindAsync(id);
if (entidad != null)
{
_con.Entry(entidad).CurrentValues.SetValues(entity);
await _con.SaveChangesAsync();
}
}
}
public ActionResult Index()
{
#region Model
var model = new PaisesModels();
using (ConexionModels cshparpEntity = new ConexionModels())
{
var dbData = cshparpEntity.Paises.ToList();
model.Paises = GetSelectListItems(dbData);
}
return(View(model));
#endregion
}
public ActionResult CargarDatos()
{
try
{
using (ConexionModels _context = new ConexionModels())
{
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();
//Paging Size (10,20,50,100)
int pageSize = length != null?Convert.ToInt32(length) : 0;
int skip = start != null?Convert.ToInt32(start) : 0;
int recordsTotal = 0;
//
var PaisesDatos = (from temppais in _context.Paises
select temppais);
//Sorting
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
PaisesDatos = PaisesDatos.OrderBy(sortColumn + " " + sortColumnDir);
}
//Search
if (!string.IsNullOrEmpty(searchValue))
{
PaisesDatos = PaisesDatos.Where(m => m.Name == searchValue);
}
//total number of rows count
recordsTotal = PaisesDatos.Count();
//Paging
var data = PaisesDatos.Skip(skip).Take(pageSize).ToList();
//Returning Json Data
return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }));
}
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// traigo el primer registro de una consulta de una forma asincrona por Entity de acuerdo a las condiciones
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public async Task <TEntity> GetFirst <TEntity>(Expression <Func <TEntity, bool> > predicate) where TEntity : class
{
try
{
using (var _con = new ConexionModels())
{
var entidad = await _db.Set <TEntity>().FirstOrDefaultAsync(predicate);
return(entidad);
}
}
catch (Exception ex)
{
var x = ex.Message;
return(null);
}
}
/// <summary>
/// consulta generica para la base global que nos sirve para obtener un objeto datatable
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public async Task <DataTable> data(string query, SqlParameter[] parameters)
{
using (var _con = new ConexionModels())
{
var dt = new DataTable();
var conn = _con.Database.Connection;
var connectionState = conn.State;
try
{
if (connectionState != ConnectionState.Open)
{
await conn.OpenAsync();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
using (var reader = await cmd.ExecuteReaderAsync())
{
dt.Load(reader);
}
}
}
catch (Exception ex)
{
// error handling
throw;
}
finally
{
if (connectionState != ConnectionState.Closed)
{
conn.Close();
}
}
return(dt);
}
}
public ActionResult ConsultarEstadisticas()
{
try
{
using (ConexionModels _context = new ConexionModels())
{
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();
int FechaInicio = 0;
int FechaFin = 0;
var PaisID = Request.Form.GetValues("PaisID").FirstOrDefault();
if (Request.Form.GetValues("fecha_inicio").FirstOrDefault() != "")
{
FechaInicio = Convert.ToInt16(Request.Form.GetValues("fecha_inicio").FirstOrDefault().Substring(6, 4));
}
if (Request.Form.GetValues("fecha_fin").FirstOrDefault() != "")
{
FechaFin = Convert.ToInt16(Request.Form.GetValues("fecha_fin").FirstOrDefault().Substring(6, 4));
}
if (Request.Form.GetValues("fecha_fin").FirstOrDefault() != "")
{
FechaFin = Convert.ToInt16(Request.Form.GetValues("fecha_fin").FirstOrDefault().Substring(6, 4));
}
PaisID = PaisID.Trim();
int pageSize = length != null?Convert.ToInt32(length) : 0;
int skip = start != null?Convert.ToInt32(start) : 0;
int recordsTotal = 0;
var EstadisticasDatos = (from tempestadisticas in _context.Estadisticas
select tempestadisticas);
if (FechaInicio != 0 && FechaFin != 0)
{
EstadisticasDatos = (from tmp in _context.Estadisticas
where tmp.Fecha >= FechaInicio && tmp.Fecha <= FechaFin
select tmp);
}
//Sorting
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
EstadisticasDatos = EstadisticasDatos.OrderBy(m => m.PaisNombre);
}
//Search
if (PaisID != "")
{
EstadisticasDatos = EstadisticasDatos.Where(m => m.PaisID == PaisID);
}
//total number of rows count
recordsTotal = EstadisticasDatos.Count();
//Paging
var data = EstadisticasDatos.Skip(skip).Take(pageSize).ToList();
//Returning Json Data
return(Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }));
}
}
catch (Exception ex)
{
throw;
}
}
