public IList <Propietarios> ObtenerTodos()
{
IList <Propietarios> res = new List <Propietarios>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT IdProp, Nombre, Apellido, Dni, Telefono, Email" +
$" FROM Propietarios";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
Propietarios p = new Propietarios
{
IdProp = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public IList <Propietarios> BuscarPorNombre(string nombre)
{
List <Propietarios> res = new List <Propietarios>();
Propietarios p = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT IdProp, Nombre, Apellido, Dni, Telefono, Email FROM Propietarios" +
$" WHERE Nombre LIKE %@nombre% OR Apellido LIKE %@nombre";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@nombre", SqlDbType.VarChar).Value = nombre;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
p = new Propietarios
{
IdProp = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
};
res.Add(p);
}
connection.Close();
}
}
return(res);
}
public int Alta(Propietarios p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Propietarios (Nombre, Apellido, Dni, Telefono, Email) " +
$"VALUES (@nombre, @apellido, @dni, @telefono, @email);" +
$"SELECT SCOPE_IDENTITY();";//devuelve el id insertado
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nombre", p.Nombre);
command.Parameters.AddWithValue("@apellido", p.Apellido);
command.Parameters.AddWithValue("@dni", p.Dni);
command.Parameters.AddWithValue("@telefono", p.Telefono);
command.Parameters.AddWithValue("@email", p.Email);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
p.IdProp = res;
connection.Close();
}
}
return(res);
}
public Propietarios ObtenerPorEmail(string email)
{
Propietarios p = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT IdProp, Nombre, Apellido, Dni, Telefono, Email FROM Propietarios" +
$" WHERE Email=@email";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
p = new Propietarios
{
IdProp = reader.GetInt32(0),
Nombre = reader.GetString(1),
Apellido = reader.GetString(2),
Dni = reader.GetString(3),
Telefono = reader.GetString(4),
Email = reader.GetString(5),
};
}
connection.Close();
}
}
return(p);
}
public int Modificacion(Propietarios p)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Propietarios SET Nombre=@nombre, Apellido=@apellido, Dni=@dni, Telefono=@telefono, Email=@email " +
$"WHERE IdProp = @id";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nombre", p.Nombre);
command.Parameters.AddWithValue("@apellido", p.Apellido);
command.Parameters.AddWithValue("@dni", p.Dni);
command.Parameters.AddWithValue("@telefono", p.Telefono);
command.Parameters.AddWithValue("@email", p.Email);
command.Parameters.AddWithValue("@id", p.IdProp);
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}