public IEnumerable <Simcha> GetSimchas()
{
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT s.*, SUM(c.Amount) As TotalContributions, COUNT(c.Id) As ContributorCount
FROM Simchas s
LEFT JOIN Contributions c
ON s.Id = c.SimchaId
GROUP BY s.Id, s.Date, s.Name";
connection.Open();
List <Simcha> simchas = new List <Simcha>();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Simcha simcha = new Simcha
{
Id = (int)reader["Id"],
Name = (string)reader["Name"],
Date = (DateTime)reader["Date"],
TotalContributions = (reader["TotalContributions"] != DBNull.Value) ? (decimal)reader["TotalContributions"] : 0,
ContributorCount = (reader["ContributorCount"] != DBNull.Value) ? (int)reader["ContributorCount"] : 0
};
simchas.Add(simcha);
}
return(simchas);
}
public void AddSimcha(Simcha Simcha)
{
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO Simchas VALUES(@name, @date)";
command.Parameters.AddWithValue("@name", Simcha.Name);
command.Parameters.AddWithValue("@date", Simcha.Date);
connection.Open();
command.ExecuteNonQuery();
}
public Simcha GetSimcha(int Id)
{
SqlConnection connection = new SqlConnection(_connectionString);
SqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT * FROM Simchas
WHERE Id = @id";
command.Parameters.AddWithValue("@id", Id);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
Simcha s = new Simcha
{
Id = (int)reader["Id"],
Name = (string)reader["Name"],
Date = (DateTime)reader["Date"]
};
return(s);
}
