public List <Simcha> GetSimchas()
{
using (var conn = new SqlConnection(_conStr))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Simchas";
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List <Simcha> simchos = new List <Simcha>();
while (reader.Read())
{
simchos.Add(new Simcha
{
SimchaId = (int)reader["SimchaId"],
SimchaName = (string)reader["SimchaName"],
SimchaDate = (DateTime)reader["SimchaDate"]
});
}
foreach (Simcha s in simchos)
{
Simcha current = GetTotalContributedForSimcha(s.SimchaId);
s.TotalContributed = current.TotalContributed;
s.AmountContributed = current.AmountContributed * -1;
}
return(simchos);
}
}
public void AddSimcha(Simcha simcha)
{
int simchaId;
using (SqlConnection conn = new SqlConnection(_conStr))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO Simchas(SimchaName, SimchaDate)
VALUES(@name, @date)
SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@name", simcha.SimchaName);
cmd.Parameters.AddWithValue("@date", simcha.SimchaDate);
conn.Open();
simchaId = (int)(decimal)cmd.ExecuteScalar();
}
AddContributions(GetContributorsAlwaysInclude(), simchaId);
}
private Simcha GetTotalContributedForSimcha(int simchaId)
{
using (SqlConnection conn = new SqlConnection(_conStr))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT SUM(ContributionAmount) AS sum, COUNT(*) AS count FROM Contributions cn
JOIN Simchas s
ON s.SimchaId = cn.SimchaId
WHERE cn.SimchaId = @id";
cmd.Parameters.AddWithValue("@id", simchaId);
conn.Open();
Simcha simcha = new Simcha();
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
return(null);
}
return(new Simcha
{
TotalContributed = (int)reader["count"],
AmountContributed = (decimal)reader["sum"]
});
}
}