public IEnumerable <Simcha> GetSimchas()
{
var simchas = new List <Simcha>();
using (var connection = new SqlConnection(connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT *, (
SELECT ISNull(SUM(Amount), 0)
FROM Contributions
WHERE SimchaId = s.Id
) as 'Total', (
SELECT COUNT(*)
FROM Contributions
WHERE SimchaId = s.Id
) as 'ContributorAmount' FROM Simchas s";
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var simcha = new Simcha();
simcha.Id = (int)reader["Id"];
simcha.Date = (DateTime)reader["Date"];
simcha.Name = (string)reader["Name"];
simcha.ContributorAmount = (int)reader["ContributorAmount"];
simcha.Total = (decimal)reader["Total"];
simchas.Add(simcha);
}
}
return(simchas);
}
public void 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();
simcha.Id = (int)(decimal)command.ExecuteScalar();
}
}