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