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