public List <Simcha> GetSimchas()
{
using (var connection = new SqlConnection(_connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Simchas s LEFT JOIN Contributions c ON s.ID=c.SimchaID";
connection.Open();
var reader = command.ExecuteReader();
var simchas = new Dictionary <int, Simcha>();
while (reader.Read())
{
var id = (int)reader["ID"];
var simcha = simchas.GetValueOrDefault(id);
if (simcha == null)
{
simcha = new Simcha
{
ID = id,
Name = (string)reader["Name"],
Date = (DateTime)reader["Date"],
};
simchas.Add(id, simcha);
}
var contributedAmount = reader["Amount"];
if (contributedAmount != DBNull.Value)
{
simcha.Contributions.Add((decimal)contributedAmount);
}
}
return(simchas.Select(k => k.Value).ToList());
}
}
public int AddSimcha(Simcha simcha)
{
using (var connection = new SqlConnection(_connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = $"INSERT INTO Simchas(Name,Date) VALUES(@name,@date) SELECT SCOPE_IDENTITY()";
command.Parameters.AddWithValue("@name", simcha.Name);
command.Parameters.AddWithValue("@date", simcha.Date);
connection.Open();
return((int)(decimal)command.ExecuteScalar());
}
}