public IEnumerable <Contribution> GetContributions(int?Id) { SqlConnection connection = new SqlConnection(_connectionString); SqlCommand command = connection.CreateCommand(); command.CommandText = @"SELECT * FROM Contributions "; if (Id != null) { command.CommandText += "WHERE SimchaId = @id"; command.Parameters.AddWithValue("@id", Id); } connection.Open(); SqlDataReader reader = command.ExecuteReader(); List <Contribution> contributions = new List <Contribution>(); while (reader.Read()) { Contribution c = new Contribution { ContributorId = (int)reader["ContributorId"], Amount = (decimal)reader["Amount"], Date = (DateTime)reader["Date"] }; contributions.Add(c); } return(contributions); }
public IEnumerable <Contribution> GetTransactions(int ContributorId) { SqlConnection connection = new SqlConnection(_connectionString); SqlCommand command = connection.CreateCommand(); command.CommandText = @"SELECT AMOUNT, Date, NULL AS SimchaName FROM Deposits WHERE ContributorId = @id UNION ALL SELECT C.Amount, C.Date, s.Name AS SimchaName FROM Contributions c JOIN Simchas s ON ContributorId = s.Id WHERE ContributorId = @id ORDER BY Date"; connection.Open(); command.Parameters.AddWithValue("@id", ContributorId); SqlDataReader reader = command.ExecuteReader(); List <Contribution> deposits = new List <Contribution>(); while (reader.Read()) { Contribution d = new Contribution { Amount = (decimal)reader["Amount"], Date = (DateTime)reader["Date"], SimchaName = (reader["SimchaName"] == DBNull.Value) ? "Deposit" : (string)reader["SimchaName"] }; deposits.Add(d); } return(deposits); }
public void AddContribution(Contribution contribution) { SqlConnection connection = new SqlConnection(_connectionString); SqlCommand command = connection.CreateCommand(); command.CommandText = "INSERT INTO Contributions VALUES(@amount, @contributorId, @simchaId, @date)"; command.Parameters.AddWithValue("@amount", contribution.Amount); command.Parameters.AddWithValue("@contributorId", contribution.ContributorId); command.Parameters.AddWithValue("@simchaId", contribution.SimchaId); command.Parameters.AddWithValue("@date", DateTime.Now); connection.Open(); command.ExecuteNonQuery(); }