public Tiquete buscarporId(int idTiquete)
{
Tiquete tiquete = new Tiquete();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader ti;
comando.CommandText = "sp_searchByIdTicket";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pId ", idTiquete);
try
{
conexion.Open();
ti = comando.ExecuteReader();
while (ti.Read())
{
tiquete.IdTiquete = (int)ti["idTiquete"];
tiquete.Moneda = (int)ti["moneda"];
tiquete.Estado = (string)ti["estado"];
tiquete.IdCliente = (string)ti["cliente"];
tiquete.IdVuelo = (int)ti["vuelo"];
tiquete.Asiento = (int)ti["asiento"];
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return tiquete;
}
public Avion buscarAvionPorId(int id)
{
Avion av = null;
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader al;
comando.CommandText = "sp_searchByIdPlane";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pId ", id);
try
{
conexion.Open();
al = comando.ExecuteReader();
while (al.Read())
{
av = new Avion();
av.IdAvion = (int)al["idAvion"];
av.Marca = (string)al["marca"];
av.Asientos = (int)al["cantidadAsientos"];
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return av;
}
public List<Avion> listarAviones()
{
List<Avion> listaavion = new List<Avion>();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader pl;
comando.CommandText = "sp_listPlane";
comando.CommandType = CommandType.StoredProcedure;
try
{
conexion.Open();
pl = comando.ExecuteReader();
while (pl.Read())
{
Avion creaavion = new Avion();
creaavion.IdAvion = (int)pl["idAvion"];
creaavion.Marca = (string)pl["marca"];
creaavion.Asientos = (int)pl["cantidadAsientos"];
listaavion.Add(creaavion);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return listaavion;
}
public Lugar buscarLugarPorId(int id)
{
Lugar lu = null;
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader al;
comando.CommandText = "sp_searchByIdPlace";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pId ", id);
try
{
conexion.Open();
al = comando.ExecuteReader();
while (al.Read())
{
lu = new Lugar();
lu.IdLugar = (int)al["idLugar"];
lu.Nombre = (string)al["nombre"];
lu.Latitud = (string)al["latitud"];
lu.Longitud = (string)al["longitud"];
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return lu;
}
public List<Vuelo> buscarVuelosPorIdoDestino(int id, string destino)
{
List<Vuelo> listaVuelos = new List<Vuelo>();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader vu;
comando.CommandText = "sp_searchByIdFlightORDestino";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pId ", id);
comando.Parameters.AddWithValue("@pDestino ", destino);
try
{
conexion.Open();
vu = comando.ExecuteReader();
while (vu.Read())
{
Vuelo creaVuelo = new Vuelo();
creaVuelo.IdVuelo = (int)vu["vuelo"];
creaVuelo.IdAvion = (int)vu["avion"];
creaVuelo.Origen = (string)vu["origen"];
creaVuelo.Destino = (string)vu["destino"];
creaVuelo.HoraPartida = (string)vu["horaSalida"];
creaVuelo.HoraLlegada = (string)vu["horaLlegada"];
creaVuelo.Precio = double.Parse(vu["precio"].ToString());
listaVuelos.Add(creaVuelo);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return listaVuelos;
}
public Cliente buscarporId(int idCliente)
{
Cliente cliente = null;
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader cl;
comando.CommandText = "sp_searchById";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pId ", idCliente);
try
{
conexion.Open();
cl = comando.ExecuteReader();
while (cl.Read())
{
cliente = definirTipoCliente((string)cl["tipoCliente"]);
cliente.Id = (string)cl["identificacion"];
cliente.Nombre = (string)cl["nombre"];
cliente.Apellido = (string)cl["apellido"];
cliente.Email = (string)cl["email"];
cliente.Telefono = (string)cl["telefono"];
cliente.Estado = (string)cl["estado"];
cliente.TipoCliente = (string)cl["tipoCliente"];
cliente.Millas = int.Parse(cl["millas"].ToString());
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return cliente;
}
public List<Lugar> listarLugares()
{
List<Lugar> listaLugar = new List<Lugar>();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader pl;
comando.CommandText = "sp_listPlace";
comando.CommandType = CommandType.StoredProcedure;
try
{
conexion.Open();
pl = comando.ExecuteReader();
while (pl.Read())
{
Lugar creaLugar = new Lugar();
creaLugar.IdLugar = (int)pl["idLugar"];
creaLugar.Nombre = (string)pl["nombre"];
creaLugar.Latitud = (string)pl["latitud"];
creaLugar.Longitud = (string)pl["longitud"];
listaLugar.Add(creaLugar);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return listaLugar;
}
public string modificarVuelo(Vuelo vuelo)
{
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader fl;
string resultado;
comando.CommandText = "sp_UpdateFlight";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pId", vuelo.IdVuelo);
comando.Parameters.AddWithValue("@idPlane", vuelo.IdAvion);
comando.Parameters.AddWithValue("@origen", vuelo.Origen);
comando.Parameters.AddWithValue("@destino", vuelo.Destino);
comando.Parameters.AddWithValue("@horaPartida", vuelo.HoraPartida);
comando.Parameters.AddWithValue("@horaLlegada", vuelo.HoraLlegada);
comando.Parameters.AddWithValue("@price", vuelo.Precio);
try
{
conexion.Open();
comando.ExecuteNonQuery();
resultado = "True";
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
resultado = ex.Message;
}
finally
{
conexion.Close();
}
return resultado;
}
public List<Vuelo> listarVuelos()
{
List<Vuelo> listaVuelos = new List<Vuelo>();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader vu;
comando.CommandText = "sp_listFlights2";
comando.CommandType = CommandType.StoredProcedure;
try
{
conexion.Open();
vu = comando.ExecuteReader();
while (vu.Read())
{
Vuelo creaVuelo = new Vuelo();
creaVuelo.IdVuelo = (int)vu["vuelo"];
creaVuelo.IdAvion = (int)vu["avion"];
//creaVuelo.Origen = (string)vu["origen"];
//creaVuelo.Destino = (string)vu["destino"];
creaVuelo.DisplayOrigen = (string)vu["origenDisplay"];
creaVuelo.DisplayDestino = (string)vu["destinoDisplay"];
creaVuelo.HoraPartida = (string)vu["horaSalida"];
creaVuelo.HoraLlegada = (string)vu["horaLlegada"];
creaVuelo.Precio = double.Parse(vu["precio"].ToString());
listaVuelos.Add(creaVuelo);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return listaVuelos;
}
public List<int> listaAsientosReservados(int idVuelo)
{
List<int> asientos = new List<int>();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader at;
comando.CommandText = "sp_listaAsientosReservados";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pIdFlight ", idVuelo);
try
{
conexion.Open();
at = comando.ExecuteReader();
while (at.Read())
{
asientos.Add((int)at["asiento"]);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return asientos;
}
public string modificarTiquete(Tiquete tiquete)
{
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader tq;
string resultado;
comando.CommandText = "sp_UpdateTicket";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue("@pId ", tiquete.IdTiquete);
comando.Parameters.AddWithValue("@pCurrency", tiquete.Moneda);
comando.Parameters.AddWithValue("@pTicketState", tiquete.Estado);
comando.Parameters.AddWithValue("@pIdClient", tiquete.IdCliente);
comando.Parameters.AddWithValue("@pIdFlight", tiquete.IdVuelo);
comando.Parameters.AddWithValue("@pNumSeat", tiquete.Asiento);
try
{
conexion.Open();
comando.ExecuteNonQuery();
resultado = "True";
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
resultado = ex.Message;
}
finally
{
conexion.Close();
}
return resultado;
}
public List<Tiquete> listarTiquetes()
{
List<Tiquete> listaTiquete = new List<Tiquete>();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader ti;
comando.CommandText = "sp_listTickets";
comando.CommandType = CommandType.StoredProcedure;
try
{
conexion.Open();
ti = comando.ExecuteReader();
while (ti.Read())
{
Tiquete creatiquete = new Tiquete();
creatiquete.IdTiquete = (int)ti["idTiquete"];
creatiquete.Moneda = (int)ti["moneda"];
creatiquete.Estado = (string)ti["estado"];
creatiquete.IdCliente = (string)ti["cliente"];
creatiquete.IdVuelo = (int)ti["vuelo"];
creatiquete.Asiento = (int)ti["asiento"];
listaTiquete.Add(creatiquete);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return listaTiquete;
}
public string guardarCliente(Cliente cliente)
{
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
string resultado;
comando.CommandText = "sp_Register";
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.Add(new SqlParameter("@pId", cliente.Id));
comando.Parameters.Add(new SqlParameter("@pName ", cliente.Nombre));
comando.Parameters.Add(new SqlParameter("@pLastname", cliente.Apellido));
comando.Parameters.Add(new SqlParameter("@pEmail", cliente.Email));
comando.Parameters.Add(new SqlParameter("@pPhone", cliente.Telefono));
comando.Parameters.Add(new SqlParameter("@pClientState", cliente.Estado));
comando.Parameters.Add(new SqlParameter("@pClientType", cliente.TipoCliente));
comando.Parameters.Add(new SqlParameter("@pmillas", cliente.Millas));
try
{
conexion.Open();
comando.ExecuteNonQuery();
resultado = "True";
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
resultado = ex.Message;
}
finally
{
conexion.Close();
}
return resultado;
}
public List<Cliente> listarClientes()
{
List<Cliente> listaCliente = new List<Cliente>();
MyConnection myConnection = new MyConnection();
SqlConnection conexion = myConnection.CreateConnection();
SqlCommand comando = myConnection.CreateCommand(conexion);
SqlDataReader cl;
comando.CommandText = "sp_listClients";
comando.CommandType = CommandType.StoredProcedure;
try
{
conexion.Open();
cl = comando.ExecuteReader();
while (cl.Read())
{
Cliente creaCliente = definirTipoCliente((string)cl["tipoCliente"]);
creaCliente.Id = (string)cl["identificacion"];
creaCliente.Nombre = (string)cl["nombre"];
creaCliente.Apellido = (string)cl["apellido"];
creaCliente.Email = (string)cl["email"];
creaCliente.Telefono = (string)cl["telefono"];
creaCliente.Estado = (string)cl["estado"];
creaCliente.TipoCliente = (string)cl["tipoCliente"];
creaCliente.Millas = int.Parse(cl["millas"].ToString());
listaCliente.Add(creaCliente);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conexion.Close();
}
return listaCliente;
}
