public Pago ObtenerPorId(int id)
{
Pago pa = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT pa.IdPago, NroPago, FechaPago, Importe, IdCon, ca.IdInmu, ca.IdInqui, ca.FechaInicio, ca.FechaFin, ca.Monto, ca.Estado " +
$"FROM Pago pa INNER JOIN Contrato ca ON pa.IdCon = ca.IdContrato" +
$" WHERE pa.IdPago=@idPago";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@idPago", SqlDbType.Int).Value = id;
command.CommandType = CommandType.Text;
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
pa = new Pago
{
IdPago = reader.GetInt32(0),
NroPago = reader.GetInt32(1),
FechaPago = reader.GetDateTime(2),
Importe = reader.GetDecimal(3),
IdCon = reader.GetInt32(4),
Contrato = new Contrato
{
IdContrato = reader.GetInt32(4),
IdInmu = reader.GetInt32(5),
Inmueble = new Inmueble
{
IdInmueble = reader.GetInt32(5),
},
IdInqui = reader.GetInt32(6),
Inquilino = new Inquilino
{
IdInquilino = reader.GetInt32(6),
},
FechaInicio = reader.GetDateTime(7),
FechaFin = reader.GetDateTime(8),
Monto = reader.GetDecimal(9),
Estado = reader.GetBoolean(10),
}
};
}
connection.Close();
}
}
return(pa);
}
public int Modificacion(Pago pa)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"UPDATE Pago SET NroPago=@nroPago, FechaPago=@fechaPago, " +
$"Importe=@importe, IdCon=@idContrato " +
$"WHERE IdPago = @idPago";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nroPago", pa.NroPago);
command.Parameters.AddWithValue("@fechaPago", pa.FechaPago);
command.Parameters.AddWithValue("@importe", pa.Importe);
command.Parameters.AddWithValue("@idContrato", pa.IdCon);
command.Parameters.AddWithValue("@idPago", pa.IdPago);
connection.Open();
res = command.ExecuteNonQuery();
connection.Close();
}
}
return(res);
}
public int Alta(Pago pa)
{
int res = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"INSERT INTO Pago (NroPago, FechaPago, Importe, IdCon) " +
$"VALUES (@nroPago, @fechaPago, @importe, @idcontrato);" +
$"SELECT SCOPE_IDENTITY();"; //devuelve el id insertado
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@nroPago", pa.NroPago);
command.Parameters.AddWithValue("@fechaPago", pa.FechaPago);
command.Parameters.AddWithValue("@importe", pa.Importe);
command.Parameters.AddWithValue("@idContrato", pa.IdCon);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
pa.IdPago = res;
connection.Close();
}
}
return(res);
}
