public List <Planes> GetAll()
{
List <Planes> planes = new List <Planes>();
try
{
this.OpenConnection();
SqlCommand cmdPlanes = new SqlCommand("select * from planes", sqlConn);
SqlDataReader drPLanes = cmdPlanes.ExecuteReader();
while (drPLanes.Read())
{
Planes pl = new Planes();
pl.Id = (int)drPLanes["id_plan"];
pl.DescPlan = (string)drPLanes["desc_plan"];
pl.IdEspecialidad = (int)drPLanes["id_especialidad"];
planes.Add(pl);
}
drPLanes.Close();
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error. No se pueden recuperar los planes", Ex);
}
finally
{
this.CloseConnection();
}
return(planes);
}
public static Planes getInstance()
{
if (Planes._instance == null)
{
Planes._instance = new Planes();
}
return(Planes._instance);
}
public void Save(Planes plan)
{
if (plan.State == BusinessEntity.States.Deleted)
{
this.Delete(plan.ID);
}
else if (plan.State == BusinessEntity.States.New)
{
this.Insert(plan);
}
else if (plan.State == BusinessEntity.States.Modified)
{
this.Update(plan);
}
plan.State = BusinessEntity.States.Unmodified;
}
public void Save(Planes comision)
{
if (comision.Estado == BusinessEntity.Estados.Eliminar)
{
this.Delete(comision);
}
else if (comision.Estado == BusinessEntity.Estados.Nuevo)
{
this.Insert(comision);
}
else if (comision.Estado == BusinessEntity.Estados.Modificar)
{
this.Update(comision);
}
comision.Estado = BusinessEntity.Estados.No_Modificar;
}
public Planes GetOne(int ID)
{
Planes pln = new Planes();
try
{
OpenConnection();
SqlCommand cmdPlanes = new SqlCommand("SELECT * FROM planes p " +
"inner join especialidades e on e.id_especialidad = p.id_especialidad " +
"WHERE id_plan = @ID", sqlConn);
cmdPlanes.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
SqlDataReader drPlanes = cmdPlanes.ExecuteReader();
while (drPlanes.Read())
{
pln.ID = (int)drPlanes["id_plan"];
pln.DescPlan = (string)drPlanes["desc_plan"];
pln.IdEspecialidad = (int)drPlanes["id_especialidad"];
pln.DescEspecialidad = (string)drPlanes["desc_especialidad"];
}
drPlanes.Close();
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al recuperar datos de plan", Ex);
throw ExcepcionManejada;
}
finally
{
CloseConnection();
}
if (pln.DescPlan != null)
{
return(pln);
}
else
{
throw new Exception("El plan no existe");
}
}
protected void Update(Planes plan)
{
try
{
this.OpenConnection();
SqlCommand cmdSave = new SqlCommand("UPDATE planes SET desc_plan=@desc_plan, id_especialidad=@id_especialidad", sqlConn);
cmdSave.Parameters.Add("@id", SqlDbType.Int).Value = plan.ID;
cmdSave.Parameters.Add("@desc_plan", SqlDbType.VarChar, 50).Value = plan.Descripcion;
cmdSave.Parameters.Add("@id_especialidad", SqlDbType.Int).Value = plan.IDEspecialidad;
cmdSave.ExecuteNonQuery();
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al modificar datos del plan", Ex);
throw ExcepcionManejada;
}
finally
{
this.CloseConnection();
}
}
public void Update(Planes pl)
{
try
{
this.OpenConnection();
SqlCommand cmdUpdate = new SqlCommand(
"update planes set desc_plan = @descPlan, id_especialidad = @idEspecialidad where id_plan = @id", sqlConn);
cmdUpdate.Parameters.Add("@descPlan", SqlDbType.VarChar, 50).Value = pl.DescPlan;
cmdUpdate.Parameters.Add("@idEspecialidad", SqlDbType.Int).Value = pl.IdEspecialidad;
cmdUpdate.Parameters.Add("@id", SqlDbType.Int).Value = pl.Id;
cmdUpdate.ExecuteNonQuery();
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al modificar datos del plan", Ex);
throw ExcepcionManejada;
}
finally
{
this.CloseConnection();
}
}
protected void Delete(Planes Codigo)
{
try
{
this.OpenConnection();
SqlCommand cmdelete = new SqlCommand("delete planes where id_plan=@id_plan", SqlConn);
cmdelete.Parameters.Add("@id_plan", SqlDbType.Int).Value = Codigo.Codigo;
cmdelete.ExecuteNonQuery();
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al elimanar el plan", Ex);
throw ExcepcionManejada;
}
finally
{
if (SqlConn.State == ConnectionState.Open)
{
this.CloseConnection();
}
}
}
protected void Insert(Planes plan)
{
try
{
this.OpenConnection();
SqlCommand cmdSave = new SqlCommand("insert into planes(desc_plan,id_especialidad)" +
"values(@desc_plan,@id_especialidad)" +
"select @@identity", sqlConn);
cmdSave.Parameters.Add("@desc_plan", SqlDbType.VarChar, 50).Value = plan.Descripcion;
cmdSave.Parameters.Add("@id_especialidad", SqlDbType.Int).Value = plan.IDEspecialidad;
plan.ID = Decimal.ToInt32((decimal)cmdSave.ExecuteScalar());
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al crear plan", Ex);
throw ExcepcionManejada;
}
finally
{
this.CloseConnection();
}
}
public void Insert(Planes pl)
{
try
{
this.OpenConnection();
SqlCommand cmdInsert = new SqlCommand(
"insert into planes (desc_plan, id_especialidad)" +
"values (@desc_plan, @id_especialidad)" +
"select @@identity", sqlConn);
cmdInsert.Parameters.Add("@id_especialidad", SqlDbType.Int).Value = pl.IdEspecialidad;
cmdInsert.Parameters.Add("@desc_plan", SqlDbType.VarChar, 50).Value = pl.DescPlan;
pl.Id = Decimal.ToInt32((decimal)cmdInsert.ExecuteScalar());
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al ingresar el plan", Ex);
throw ExcepcionManejada;
}
finally
{
this.CloseConnection();
}
}
public List <Planes> GetAll()
{
List <Planes> planes = new List <Planes>();
try
{
OpenConnection();
SqlCommand cmdPlanes = new SqlCommand("SELECT * FROM planes p " +
"inner join especialidades e on e.id_especialidad = p.id_especialidad ", sqlConn);
SqlDataReader drPlanes = cmdPlanes.ExecuteReader();
while (drPlanes.Read())
{
Planes pln = new Planes();
pln.ID = (int)drPlanes["id_plan"];
pln.DescPlan = (string)drPlanes["desc_plan"];
pln.IdEspecialidad = (int)drPlanes["id_especialidad"];
pln.DescEspecialidad = (string)drPlanes["desc_especialidad"];
planes.Add(pln);
}
drPlanes.Close();
}
catch (Exception Ex)
{
Exception ExcepcionManejada = new Exception("Error al recuperar lista de planes.", Ex);
throw ExcepcionManejada;
}
finally
{
CloseConnection();
}
return(planes);
}
protected void Insert(Planes plan)
{
try
{
OpenConnection();
SqlCommand cmdSave = new SqlCommand("INSERT INTO planes (desc_plan,id_especialidad) " +
"VALUES(@descPlan,@idEspecialidad) " +
"select @@identity ", sqlConn);
cmdSave.Parameters.Add("@descPlan", SqlDbType.VarChar, 50).Value = plan.DescPlan;
cmdSave.Parameters.Add("@idEspecialidad", SqlDbType.Int).Value = plan.IdEspecialidad;
plan.ID = Decimal.ToInt32((decimal)cmdSave.ExecuteScalar());
}
catch (Exception Ex)
{
Exception ExceptionManejada = new Exception("Erro al crear el plan", Ex);
throw ExceptionManejada;
}
finally
{
CloseConnection();
}
}
