public void AddSimcha(Simcha simcha) { var cManager = new ContributorManager(_connectionString); using (var sqlConnection = new SqlConnection(_connectionString)) using (var cmd = sqlConnection.CreateCommand()) { sqlConnection.Open(); cmd.CommandText = "INSERT INTO Simchos " + "VALUES (@simchaName, @date) " + "SELECT SCOPE_IDENTITY()"; cmd.Parameters.AddWithValue("@simchaName", simcha.SimchaName); cmd.Parameters.AddWithValue("@date", simcha.Date); simcha.Id = (int)(decimal)cmd.ExecuteScalar(); } var contributors = cManager.GetContributors(); foreach (Contributor contributor in contributors) { if (contributor.AlwaysInclude) { AddContribution(new Contribution { SimchaId = simcha.Id, ContributorId = contributor.Id, Amount = 5 }); } } }
private void SetSimchaTotal(Simcha simcha) { using (var connection = new SqlConnection(_connectionString)) using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT ISNull(SUM(ContributionAmount), 0) as Total, " + "Count(*) as ContributorCount " + "FROM Contributions " + "WHERE SimchaId = @simchaId"; cmd.Parameters.AddWithValue("@simchaId", simcha.Id); connection.Open(); var reader = cmd.ExecuteReader(); reader.Read(); simcha.Total = (decimal)reader["Total"]; simcha.ContributorCount = (int)reader["ContributorCount"]; } }
public IEnumerable <Simcha> GetSimchos() { using (var connection = new SqlConnection(_connectionString)) using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * FROM Simchos "; connection.Open(); var simchos = new List <Simcha>(); var reader = cmd.ExecuteReader(); while (reader.Read()) { var simcha = new Simcha { Id = (int)reader["Id"], Date = (DateTime)reader["Date"], SimchaName = (string)reader["SimchaName"] }; SetSimchaTotal(simcha); simchos.Add(simcha); } return(simchos); } }
public Simcha GetSimchaById(int simchaId) { using (var connection = new SqlConnection(_connectionString)) using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT * FROM Simchos " + "WHERE Id = @id"; cmd.Parameters.AddWithValue("@id", simchaId); connection.Open(); var reader = cmd.ExecuteReader(); if (!reader.Read()) { return(null); } var simcha = new Simcha { Id = (int)reader["Id"], Date = (DateTime)reader["Date"], SimchaName = (string)reader["SimchaName"] }; SetSimchaTotal(simcha); return(simcha); } }