public IList <Inquilino> ObtenerTodos()
{
IList <Inquilino> res = new List <Inquilino>();
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT Id, Nombre, Apellido, Dni, Telefono, Email, DireccionTrabajo" +
$" FROM inquilinos";
using (var command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Inquilino p = new Inquilino
{
Id = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
DireccionTrabajo = reader.GetString(6),
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public Inquilino ObtenerPorId(int id)
{
Inquilino p = null;
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"SELECT Id, Nombre, Apellido, Dni, Telefono, Email, DireccionTrabajo FROM inquilinos" +
$" WHERE Id=@id";
using (var command = new MySqlCommand(sql, connection))
{
command.Parameters.Add("@id", MySqlDbType.Int32).Value = id;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
p = new Inquilino
{
Id = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
DireccionTrabajo = reader.GetString(6),
};
return(p);
}
connection.Close();
}
}
return(p);
}
public int Modificacion(Inquilino i)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Inquilino SET nombreI=@nombreI, apellidoI=@apellidoI, dniI=@dniI, domicilioI=@domicilioI, telefonoI=@telefonoI,oficioI=@oficioI,nombreG=@nombreG,apellidoG=@apellidoG,dniG=@dniG,telefonoG=@telefonoG,domicilioG=@domicilioG" +
$" WHERE id_Inquilino=@id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nombreI", i.NombreI);
command.Parameters.AddWithValue("@apellidoI", i.ApellidoI);
command.Parameters.AddWithValue("@domicilioI", i.DomicilioI);
command.Parameters.AddWithValue("@dniI", i.DniI);
command.Parameters.AddWithValue("@telefonoI", i.TelefonoI);
command.Parameters.AddWithValue("@oficioI", i.OficioI);
command.Parameters.AddWithValue("@nombreG", i.NombreG);
command.Parameters.AddWithValue("@apellidoG", i.ApellidoG);
command.Parameters.AddWithValue("@dniG", i.DniG);
command.Parameters.AddWithValue("@telefonoG", i.TelefonoG);
command.Parameters.AddWithValue("@domicilioG", i.DomicilioG);
command.Parameters.AddWithValue("@id", i.Id_Inquilino);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
connection.Close();
}
}
return(res);
}
public int Alta(Inquilino i)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Inquilino (nombreI,apellidoI,dniI,domicilioI,telefonoI,oficioI,nombreG,apellidoG,dniG,telefonoG,domicilioG) " +
$"VALUES (@nombreI, @apellidoI, @dniI, @domicilioI, @telefonoI, @oficioI, @nombreG, @apellidoG, @dniG, @telefonoG, @domicilioG);" +
$"SELECT SCOPE_IDENTITY();"; //devuelve el id insertado
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nombreI", i.NombreI);
command.Parameters.AddWithValue("@apellidoI", i.ApellidoI);
command.Parameters.AddWithValue("@domicilioI", i.DomicilioI);
command.Parameters.AddWithValue("@dniI", i.DniI);
command.Parameters.AddWithValue("@telefonoI", i.TelefonoI);
command.Parameters.AddWithValue("@oficioI", i.OficioI);
command.Parameters.AddWithValue("@nombreG", i.NombreG);
command.Parameters.AddWithValue("@apellidoG", i.ApellidoG);
command.Parameters.AddWithValue("@dniG", i.DniG);
command.Parameters.AddWithValue("@telefonoG", i.TelefonoG);
command.Parameters.AddWithValue("@domicilioG", i.DomicilioG);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
i.Id_Inquilino = res;
connection.Close();
}
}
return(res);
}
public int Modificacion(Inquilino p)
{
int res = -1;
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"UPDATE inquilinos SET Nombre='{p.Nombre}', Apellido='{p.Apellido}', Dni='{p.Dni}', Telefono='{p.Telefono}', Email='{p.Email}', DireccionTrabajo='{p.DireccionTrabajo}' " +
$"WHERE Id = {p.Id}";
using (var command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public int Alta(Inquilino p)
{
int res = -1;
using (var connection = new MySqlConnection(connectionString))
{
string sql = $"INSERT INTO inquilinos (Nombre, Apellido, Dni, Telefono, Email, DireccionTrabajo) " +
$"VALUES ('{p.Nombre}', '{p.Apellido}','{p.Dni}','{p.Telefono}','{p.Email}', '{p.DireccionTrabajo}')";
using (var command = new MySqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
res = command.ExecuteNonQuery();
command.CommandText = "SELECT LAST_INSERT_ID()";
p.Id = Convert.ToInt32(command.ExecuteScalar());
connection.Close();
}
}
return(res);
}
public Inquilino ObtenerPorId(int id)
{
Inquilino i = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT id_Inquilino,nombreI,apellidoI,dniI,domicilioI,telefonoI,oficioI,nombreG,apellidoG,dniG,telefonoG,domicilioG" +
$" From Inquilino WHERE id_Inquilino=@id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@id", id);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
i = new Inquilino
{
Id_Inquilino = reader.GetInt32(0),
NombreI = reader.GetString(1),
ApellidoI = reader.GetString(2),
DniI = reader.GetString(3),
DomicilioI = reader.GetString(4),
TelefonoI = reader.GetString(5),
OficioI = reader.GetString(6),
NombreG = reader.GetString(7),
ApellidoG = reader.GetString(8),
DniG = reader.GetString(9),
TelefonoG = reader.GetString(10),
DomicilioG = reader.GetString(11),
};
return(i);
}
connection.Close();
}
}
return(i);
}
public IList <Inquilino> ObtenerTodos()
{
IList <Inquilino> res = new List <Inquilino>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT id_Inquilino, nombreI,apellidoI,dniI,domicilioI,telefonoI,oficioI,nombreG,apellidoG,dniG,telefonoG,domicilioG" +
$" FROM Inquilino";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Inquilino i = new Inquilino()
{
Id_Inquilino = reader.GetInt32(0),
NombreI = reader.GetString(1),
ApellidoI = reader.GetString(2),
DniI = reader.GetString(3),
DomicilioI = reader.GetString(4),
TelefonoI = reader.GetString(5),
OficioI = reader.GetString(6),
NombreG = reader.GetString(7),
ApellidoG = reader.GetString(8),
DniG = reader.GetString(9),
TelefonoG = reader.GetString(10),
DomicilioG = reader.GetString(11),
};
res.Add(i);
}
connection.Close();
}
}
return(res);
}