public IEnumerable <Contribution> GetContributionsById(int contributorId)
{
List <Contribution> contributions = new List <Contribution>();
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = @"SELECT c.*, s.Name, s.Date FROM Contributions c
JOIN Simchas s ON c.SimchaId=s.Id
WHERE ContributorId = @contributorId";
command.Parameters.AddWithValue("@contributorId", contributorId);
connection.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
var contribution = new Contribution()
{
ContributorId = (int)reader["ContributorId"],
Amount = (decimal)reader["Amount"],
SimchaId = (int)reader["SimchaId"],
SimchaName = (string)reader["Name"],
Date = (DateTime)reader["Date"]
};
contributions.Add(contribution);
}
}
return(contributions);
}
public IEnumerable <SimchaContributor> GetSimchaContributors(int simchaId)
{
IEnumerable <Contributor> contributors = GetContributors();
using (var connection = new SqlConnection(connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Contributions WHERE SimchaId = @simchaId";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
connection.Open();
var reader = cmd.ExecuteReader();
List <Contribution> contributions = new List <Contribution>();
while (reader.Read())
{
Contribution contribution = new Contribution
{
Amount = (decimal)reader["Amount"],
SimchaId = simchaId,
ContributorId = (int)reader["ContributorId"]
};
contributions.Add(contribution);
}
return(contributors.Select(contributor =>
{
var sc = new SimchaContributor();
sc.FirstName = contributor.FirstName;
sc.LastName = contributor.LastName;
sc.AlwaysInclude = contributor.AlwaysInclude;
sc.ContributorId = contributor.Id;
sc.Balance = contributor.Balance;
Contribution contribution = contributions.FirstOrDefault(c => c.ContributorId == contributor.Id);
if (contribution != null)
{
sc.Amount = contribution.Amount;
}
return sc;
}));
}
}