/// <summary>
/// Obtener Lista de Clientes
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static ClienteList Get(int page, int pageSize)
{
ClienteList res = new ClienteList();
SqlCommand cmd = null;
SqlDataReader dr = null;
string query = @"SELECT * FROM Cliente
INNER JOIN Persona ON Cliente.IdPersona=Persona.IdPersona
WHERE Persona.Borrado=0 ORDER BY Nombre ASC
OFFSET @pageSize * (@page - 1) ROWS FETCH NEXT @pageSize ROWS ONLY";
try
{
cmd = Methods.CreateBasicCommand(query);
cmd.Parameters.AddWithValue("@page", page);
cmd.Parameters.AddWithValue("@pageSize", pageSize);
dr = Methods.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
res.Add(new Cliente()
{
IdPersona = dr.GetInt32(0),
Nit = dr.GetString(1),
Razon = dr.GetString(2),
FechaRegistro = dr.GetDateTime(3),
Ci = dr.GetString(5),
Nombre = dr.GetString(6),
PrimerApellido = dr.GetString(7),
SegundoApellido = dr.GetString(8),
Sexo = SexoDal.Get(dr.GetInt32(9)),
FechaNacimiento = dr.GetDateTime(10),
Usuario = UsuarioDal.Get(dr.GetInt32(11)),
Correos = CorreoDal.GetList(dr.GetInt32(0)),
Telefonos = TelefonoDal.GetList(dr.GetInt32(0)),
Direcciones = DireccionDal.GetList(dr.GetInt32(0))
});
}
}
catch (SqlException ex)
{
//Methods.GenerateLogsRelease("KeyValuePacienteListDal", "Obtener", ex.Message + " " + ex.StackTrace);
throw ex;
}
catch (Exception ex)
{
//Methods.GenerateLogsRelease("KeyValuePacienteListDal", "Obtener", ex.Message + " " + ex.StackTrace);
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}
/// <summary>
/// Obtiene un Cliente de la base de datos
/// </summary>
/// <param name="ci"></param>
/// <returns></returns>
public static Cliente GetCI(string ci)
{
Cliente res = new Cliente();
SqlCommand cmd = null;
SqlDataReader dr = null;
string query = @"Select Persona.*,
Cliente.Nit, Cliente.Razon, Cliente.FechaRegistro
FROM Persona
INNER JOIN Cliente ON Persona.IdPersona=Cliente.IdPersona
WHERE Persona.Ci=@ci";
try
{
cmd = Methods.CreateBasicCommand(query);
cmd.Parameters.AddWithValue("@ci", ci);
dr = Methods.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
res = new Cliente()
{
IdPersona = dr.GetInt32(0),
Ci = dr.GetString(1),
Nombre = dr.GetString(2),
PrimerApellido = dr.GetString(3),
SegundoApellido = dr.GetString(4),
Sexo = SexoDal.Get(dr.GetInt32(5)),
FechaNacimiento = dr.GetDateTime(6),
Usuario = UsuarioDal.Get(dr.GetInt32(7)),
Borrado = dr.GetBoolean(8),
Direcciones = DireccionDal.GetList(dr.GetInt32(0)),
Telefonos = TelefonoDal.GetList(dr.GetInt32(0)),
Correos = CorreoDal.GetList(dr.GetInt32(0)),
Nit = dr.GetString(9),
Razon = dr.GetString(10),
FechaRegistro = dr.GetDateTime(11)
};
}
}
catch (Exception ex)
{
Methods.GenerateLogsRelease("ClienteDal", "Obtenet(Get)", string.Format("{0} {1} Error: {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), ex.Message));
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}
/// <summary>
/// Obtiene la informacion de una persona
/// </summary>
/// <param name="id">identificador de persona </param>
/// <returns></returns>
public static Persona Get(int id)
{
Persona res = new Persona();
SqlCommand cmd = null;
SqlDataReader dr = null;
string query = "SELECT * FROM Persona WHERE IdPersona = @id";
try
{
cmd = Methods.CreateBasicCommand(query);
cmd.Parameters.AddWithValue("@id", id);
dr = Methods.ExecuteDataReaderCommand(cmd);
while (dr.Read())
{
res = new Persona()
{
IdPersona = dr.GetInt32(0),
Ci = dr.GetString(1),
Nombre = dr.GetString(2),
PrimerApellido = dr.GetString(3),
SegundoApellido = dr.GetString(4),
Sexo = SexoDal.Get(dr.GetInt32(5)),
FechaNacimiento = dr.GetDateTime(6),
Correos = CorreoDal.GetList(id),
Usuario = UsuarioDal.Get(dr.GetInt32(7)),
Direcciones = DireccionDal.GetList(id),
Telefonos = TelefonoDal.GetList(id),
Borrado = dr.GetBoolean(8)
};
}
}
catch (Exception ex)
{
Methods.GenerateLogsRelease("PersonaDal", "Obtener", string.Format("{0} {1} Error: {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), ex.Message));
throw ex;
}
finally
{
cmd.Connection.Close();
}
return(res);
}