public Pago ObtenerPorId(int id)
{
Pago pa = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = $"SELECT pa.IdPago, NroPago, FechaPago, Importe, ContratoId, c.InmuebleId, c.InquilinoId, " +
" c.FechaInicio, c.FechaFin, c.Monto, c.Estado " +
$" FROM Pago pa INNER JOIN Contrato c ON pa.IdCon = c.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),
ContratoId = reader.GetInt32(4),
Contrato = new Contrato
{
IdContrato = reader.GetInt32(4),
InmuebleId = reader.GetInt32(5),
Inmueble = new Inmueble
{
IdInmueble = reader.GetInt32(5),
},
InquilinoId = reader.GetInt32(6),
Inquilino = new Inquilino
{
IdInquilino = reader.GetInt32(6),
},
FecInicio = reader.GetDateTime(7),
FecFin = 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, ContratoId=@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.ContratoId);
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, ContratoId) " +
$" 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.ContratoId);
connection.Open();
res = Convert.ToInt32(command.ExecuteScalar());
pa.IdPago = res;
connection.Close();
}
}
return(res);
}
