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