public IEnumerable <Contributor> GetContributors()
{
var contributors = new List <Contributor>();
using (var connection = new SqlConnection(connectionString))
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandText = @"SELECT *,
(
(SELECT ISNULL(SUM(d.Amount), 0)
FROM Deposits d WHERE d.ContributorId = c.Id)
-
(SELECT ISNULL(SUM(co.Amount), 0)
FROM Contributions co WHERE co.ContributorId = c.Id)
)
as 'Balance' FROM Contributors c";
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var contributor = new Contributor();
contributor.Id = (int)reader["Id"];
contributor.FirstName = (string)reader["FirstName"];
contributor.LastName = (string)reader["LastName"];
contributor.CellNumber = (string)reader["CellNumber"];
contributor.Date = (DateTime)reader["Date"];
contributor.AlwaysInclude = (bool)reader["AlwaysInclude"];
contributor.Balance = (decimal)reader["Balance"];
contributors.Add(contributor);
}
}
return(contributors);
}
public void AddContributor(Contributor contributor)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = @"INSERT INTO Contributors(FirstName, LastName, CellNumber, Date, AlwaysInclude)
VALUES(@firstName,@lastName,@cellNumber,@date,@alwaysInclude) SELECT SCOPE_IDENTITY()";
connection.Open();
command.Parameters.AddWithValue("@firstName", contributor.FirstName);
command.Parameters.AddWithValue("@lastName", contributor.LastName);
command.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
command.Parameters.AddWithValue("@date", contributor.Date);
command.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
contributor.Id = (int)(decimal)command.ExecuteScalar();
}
}
public void UpdateContributor(Contributor contributor)
{
using (var connection = new SqlConnection(connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = @"UPDATE Contributors SET FirstName = @firstName, LastName = @lastName,
CellNumber = @cellNumber, Date = @date, AlwaysInclude = @alwaysInclude WHERE Id = @id";
connection.Open();
command.Parameters.AddWithValue("@firstName", contributor.FirstName);
command.Parameters.AddWithValue("@lastName", contributor.LastName);
command.Parameters.AddWithValue("@cellNumber", contributor.CellNumber);
command.Parameters.AddWithValue("@date", contributor.Date);
command.Parameters.AddWithValue("@alwaysInclude", contributor.AlwaysInclude);
command.Parameters.AddWithValue("@id", contributor.Id);
command.ExecuteNonQuery();
}
}