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