public IEnumerable <Contributor> GetContributors()
{
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Contributors ";
connection.Open();
var contributors = new List <Contributor>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var contributor = new Contributor
{
Id = (int)reader["Id"],
FirstName = (string)reader["FirstName"],
LastName = (string)reader["LastName"],
CellNumber = (string)reader["CellNumber"],
AlwaysInclude = (bool)reader["AlwaysInclude"],
Date = (DateTime)reader["Date"]
};
SetContributorBalance(contributor);
contributors.Add(contributor);
}
return(contributors);
}
}
public void AddContributor(Contributor contributor)
{
using (var sqlConnection = new SqlConnection(_connectionString))
using (var cmd = sqlConnection.CreateCommand())
{
sqlConnection.Open();
cmd.CommandText = "INSERT INTO Contributors " +
"VALUES (@firstName, @lastName, @cellNumber, @alwaysInclude, @date)";
cmd.Parameters.AddWithValue("@firstName", contributor.FirstName);
cmd.Parameters.AddWithValue("@lastName", contributor.LastName);
cmd.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
cmd.Parameters.AddWithValue("@date", contributor.Date);
cmd.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
cmd.ExecuteNonQuery();
}
}
public void UpdateContributor(Contributor contributor)
{
using (var sqlConnection = new SqlConnection(_connectionString))
using (var cmd = sqlConnection.CreateCommand())
{
sqlConnection.Open();
cmd.CommandText = "UPDATE Contributors " +
"SET FirstName = @firstName, LastName = @lastName, CellNumber = @cellNumber, AlwaysInclude = @alwaysInclude, Date = @date " +
"WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", contributor.Id);
cmd.Parameters.AddWithValue("@firstName", contributor.FirstName);
cmd.Parameters.AddWithValue("@lastName", contributor.LastName);
cmd.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
cmd.Parameters.AddWithValue("@date", contributor.Date);
cmd.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
cmd.ExecuteNonQuery();
}
}
public void SetContributorBalance(Contributor contributor)
{
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandText = "SELECT IsNull(SUM(DepositAmount),0) " +
"FROM Deposits " +
"WHERE ContributorId = @contributorId";
cmd.Parameters.AddWithValue("@contributorId", contributor.Id);
decimal depositTotal = (decimal)cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.CommandText = "SELECT IsNull(SUM(ContributionAmount),0) " +
"FROM Contributions " +
"WHERE ContributorId = @contributorId";
cmd.Parameters.AddWithValue("@contributorId", contributor.Id);
decimal contributionsTotal = (decimal)cmd.ExecuteScalar();
var x = depositTotal - contributionsTotal;
contributor.Balance = depositTotal - contributionsTotal;
}
}
public Contributor GetContributorById(int contributorId)
{
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Contributors " +
"WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", contributorId);
connection.Open();
var contributor = new Contributor();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
contributor.Id = (int)reader["Id"];
contributor.FirstName = (string)reader["FirstName"];
contributor.LastName = (string)reader["LastName"];
contributor.CellNumber = (string)reader["CellNumber"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Date = (DateTime)reader["Date"];
}
SetContributorBalance(contributor);
return(contributor);
}
}
