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"] }); } }