public IEnumerable <Contribution> GetContributionsById(int contribId)
{
List <Contribution> contributions = new List <Contribution>();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT c.*, s.Name, s.Date FROM Contributions c
JOIN Simchas s ON c.SimchaId = s.Id
WHERE c.ContributorId = @contribId";
cmd.Parameters.AddWithValue("@contribId", contribId);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var contribution = new Contribution();
contribution.ContributorId = (int)reader["ContributorId"];
contribution.Amount = (decimal)reader["Amount"];
contribution.SimchaId = (int)reader["SimchaId"];
contribution.SimchaName = (string)reader["Name"];
contribution.Date = (DateTime)reader["Date"];
contributions.Add(contribution);
}
}
return(contributions);
}
public void AddContribution(Contribution contribution)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "Insert into Contributions(SimchaId, ContributorId, Amount)" +
" values(@SimchaId, @ContributorId, @Amount)";
cmd.Parameters.AddWithValue("@SimchaId", contribution.SimchaId);
cmd.Parameters.AddWithValue("@ContributorId", contribution.ContributorId);
cmd.Parameters.AddWithValue("@Amount", contribution.Amount);
connection.Open();
cmd.ExecuteNonQuery();
}
}
public IEnumerable <SimchaContributor> GetSimchaContributorsEasy(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);
}
List <SimchaContributor> result = new List <SimchaContributor>();
foreach (Contributor contributor in contributors)
{
SimchaContributor 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;
}
result.Add(sc);
}
return(result);
}
}
public IEnumerable <SimchaContributor> GetSimchaContributors(int simchaId)
{
IEnumerable <Contributor> contributors = GetContributors();
using (SqlConnection connection = new SqlConnection(_connectionString))
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "Select * from contributions where SimchaId=@simchaId";
cmd.Parameters.AddWithValue("@simchaId", simchaId);
List <Contribution> contributions = new List <Contribution>();
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
contributions.Add(new Contribution
{
SimchaId = (int)reader["SimchaId"],
ContributorId = (int)reader["ContributorId"],
Amount = (decimal)reader["Amount"]
});
}
List <SimchaContributor> SimchaContributors = new List <SimchaContributor>();
foreach (Contributor c in contributors)
{
SimchaContributor sc = new SimchaContributor();
sc.ContributorId = c.Id;
sc.Name = c.Name;
sc.AlwaysInclude = c.AlwaysInclude;
sc.Balance = GetBalance(c.Id);
Contribution contribution = contributions.FirstOrDefault(contrib => contrib.ContributorId == c.Id);
if (contribution != null)
{
sc.Amount = contribution.Amount;
}
SimchaContributors.Add(sc);
}
return(SimchaContributors);
}
}
public IEnumerable<Contribution> GetContributionsById(int contribId)
{
List<Contribution> contributions = new List<Contribution>();
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SELECT c.*, s.Name, s.Date FROM Contributions c
JOIN Simchas s ON c.SimchaId = s.Id
WHERE c.ContributorId = @contribId";
cmd.Parameters.AddWithValue("@contribId", contribId);
connection.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var contribution = new Contribution();
contribution.ContributorId = (int)reader["ContributorId"];
contribution.Amount = (decimal)reader["Amount"];
contribution.SimchaId = (int)reader["SimchaId"];
contribution.SimchaName = (string) reader["Name"];
contribution.Date = (DateTime) reader["Date"];
contributions.Add(contribution);
}
}
return contributions;
}
public IEnumerable<SimchaContributor> GetSimchaContributorsEasy(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);
}
List<SimchaContributor> result = new List<SimchaContributor>();
foreach (Contributor contributor in contributors)
{
SimchaContributor 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;
}
result.Add(sc);
}
return result;
}
}