public Boolean Insertar()
{
Boolean flag = false;
String query = @"Insert_Pasante";
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(query, cn, trn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@userName", user_name));
cmd.Parameters.Add(new SqlParameter("@userPassword", password));
flag = (int)cmd.ExecuteScalar() == 1;
trn.Commit();
}
catch (SqlException e)
{
trn.Rollback();
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error" + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(flag);
}
public Pasante FindByName(string username)
{
Pasante p = null;
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
String query = @"SELECT userName FROM PASANTE WHERE userName = @username";
SqlCommand cmd = new SqlCommand(query, cn, trn);
cmd.Parameters.Add(new SqlParameter("@username", username));
p = new Pasante {
user_name = (String)cmd.ExecuteScalar(),
password = null
};
}
catch (SqlException e)
{
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(p);
}
public Boolean Eliminar()
{
Boolean flag = false;
String stringCommand = @"Delete_Pasante";
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(stringCommand, cn, trn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@userName", user_name));
cmd.Parameters.Add(new SqlParameter("@userPassword", password));
flag = (int)cmd.ExecuteScalar() == 1;
trn.Commit();
}
catch (SqlException ex)
{
trn.Rollback();
Debug.WriteLine("Error al Eliminar " + ex.Message);
}
catch (Exception ex)
{
Debug.WriteLine("Otro Error " + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(flag);
}
public override Boolean Modificar()
{
Boolean flag = false;
if (!base.Es_Nueva())
{
String query = @"Update_Vivienda";
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
precio_final = CalcularPrecio();
try
{
SqlCommand cmd = new SqlCommand(query, cn, trn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@id", id));
cmd.Parameters.Add(new SqlParameter("@calle", calle));
cmd.Parameters.Add(new SqlParameter("@nro_puerta", nro_puerta));
cmd.Parameters.Add(new SqlParameter("@descripcion", descripcion));
cmd.Parameters.Add(new SqlParameter("@nro_banios", nro_banios));
cmd.Parameters.Add(new SqlParameter("@nro_dormitorios", nro_dormitorios));
cmd.Parameters.Add(new SqlParameter("@superficie", superficie));
cmd.Parameters.Add(new SqlParameter("@anio_construccion", anio_construccion));
cmd.Parameters.Add(new SqlParameter("@precio_base", precio_base));
cmd.Parameters.Add(new SqlParameter("@habilitada", convertBooleanToChar(habilitada)));
cmd.Parameters.Add(new SqlParameter("@vendida", convertBooleanToChar(vendida)));
cmd.Parameters.Add(new SqlParameter("@barrio", barrio.nombre));
cmd.Parameters.Add(new SqlParameter("@precio_final", precio_final));
cmd.Parameters.Add(new SqlParameter("@contribucion", contribucion));
cmd.Parameters.Add(new SqlParameter("@tipo", tipo));
flag = (int)cmd.ExecuteScalar() == 1;
trn.Commit();
}
catch (SqlException e)
{
trn.Rollback();
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error" + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
}
return(flag);
}
public override Boolean Insertar()
{
Boolean flag = false;
if (base.Es_Nueva())
{
String query = @"Insert_VNUEVA";
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
flag = base.AuxInsertar(trn, cn);
if (flag)
{
SqlCommand cmd = new SqlCommand(query, cn, trn);
cmd.CommandType = CommandType.StoredProcedure;
precio_final = CalcularPrecio();
cmd.Parameters.Add(new SqlParameter("@id", id));
cmd.Parameters.Add(new SqlParameter("@precio_final", precio_final));
flag = (int)cmd.ExecuteScalar() > 0;
trn.Commit();
}
}
catch (SqlException e)
{
trn.Rollback();
Console.WriteLine("Se ha producido un error " + e.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
}
return(flag);
}
public Parametro FindByName(string nombre)
{
Parametro p = null;
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
String query = @"SELECT nombre, valor FROM PARAMETRO WHERE nombre = @nombre";
SqlCommand cmd = new SqlCommand(query, cn, trn);
cmd.Parameters.Add(new SqlParameter("@nombre", nombre));
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
p = new Parametro
{
nombre = dr["nombre"].ToString(),
valor = dr["valor"].ToString()
};
}
}
dr.Close();
}
catch (SqlException e)
{
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(p);
}
public IEnumerable <Parametro> FindAll()
{
List <Parametro> listaParametros = new List <Parametro>();
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
String query = @"SELECT nombre, valor FROM PARAMETRO";
SqlCommand cmd = new SqlCommand(query, cn, trn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listaParametros.Add(new Parametro
{
nombre = dr["nombre"].ToString(),
valor = dr["valor"].ToString()
});
}
}
dr.Close();
}
catch (SqlException e)
{
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(listaParametros);
}
public IEnumerable <Pasante> FindAll()
{
List <Pasante> listaPasantes = new List <Pasante>();
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
String query = @"SELECT userName FROM PASANTE";
SqlCommand cmd = new SqlCommand(query, cn, trn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listaPasantes.Add(new Pasante
{
user_name = dr["userName"].ToString(),
password = ""
});
}
}
dr.Close();
}
catch (SqlException e)
{
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(listaPasantes);
}
public Boolean Modificar()
{
Boolean flag = false;
String query = @"Update_Barrio";
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(query, cn, trn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@nombre", nombre));
cmd.Parameters.Add(new SqlParameter("@descripcion", descripcion));
flag = (int)cmd.ExecuteScalar() == 1;
trn.Commit();
}
catch (SqlException e)
{
trn.Rollback();
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(flag);
}
public IEnumerable <Vivienda> FindAll()
{
List <Vivienda> listaViviendas = new List <Vivienda>();
SqlConnection cn = UtilidadesDB.CreateConnection();
UtilidadesDB.OpenConnection(cn);
SqlTransaction trn = cn.BeginTransaction();
try
{
String query = @"SELECT *
FROM VIVIENDA V INNER JOIN VUSADA VU ON V.id = VU.vivienda;";
SqlCommand cmd = new SqlCommand(query, cn, trn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listaViviendas.Add(new VUsada
{
calle = dr["calle"].ToString(),
nro_puerta = Convert.ToInt32(dr["nro_puerta"]),
barrio = repoBarrio.FindByName(dr["barrio"].ToString()),
descripcion = dr["descripcion"].ToString(),
nro_banios = Convert.ToInt32(dr["nro_banios"]),
nro_dormitorios = Convert.ToInt32(dr["nro_dormitorios"]),
superficie = Convert.ToDouble(dr["superficie"]),
precio_base = Convert.ToDouble(dr["precio_base"]),
anio_construccion = Convert.ToInt32(dr["anio_construccion"]),
vendida = ConvertCharToBoolean(Convert.ToChar(dr["vendida"].ToString())),
habilitada = ConvertCharToBoolean(Convert.ToChar(dr["habilitada"].ToString())),
id = Convert.ToInt32(dr["id"]),
precio_final = Convert.ToDouble(dr["precio_base"]),
tipo = Convert.ToInt32(dr["tipo"]),
contribucion = Convert.ToDouble(dr["contribucion"])
});
}
}
dr.Close();
query = @"SELECT *
FROM VIVIENDA V INNER JOIN VNUEVA VN ON V.id = VN.vivienda;";
cmd = new SqlCommand(query, cn, trn);
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
listaViviendas.Add(new VNueva
{
calle = dr["calle"].ToString(),
nro_puerta = Convert.ToInt32(dr["nro_puerta"]),
barrio = repoBarrio.FindByName(dr["barrio"].ToString()),
descripcion = dr["descripcion"].ToString(),
nro_banios = Convert.ToInt32(dr["nro_banios"]),
nro_dormitorios = Convert.ToInt32(dr["nro_dormitorios"]),
superficie = Convert.ToDouble(dr["superficie"]),
precio_base = Convert.ToDouble(dr["precio_base"]),
anio_construccion = Convert.ToInt32(dr["anio_construccion"]),
vendida = ConvertCharToBoolean(Convert.ToChar(dr["vendida"].ToString())),
habilitada = ConvertCharToBoolean(Convert.ToChar(dr["habilitada"].ToString())),
id = Convert.ToInt32(dr["id"]),
precio_final = Convert.ToDouble(dr["precio_base"]),
tipo = Convert.ToInt32(dr["tipo"])
});
}
}
}
catch (SqlException e)
{
Console.WriteLine("Se ha producido un error " + e.Message);
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex.Message);
}
finally
{
UtilidadesDB.CloseConnection(cn);
cn.Dispose();
}
return(listaViviendas);
}