public void AddSimcha(Simcha simcha)
{
var cManager = new ContributorManager(_connectionString);
using (var sqlConnection = new SqlConnection(_connectionString))
using (var cmd = sqlConnection.CreateCommand())
{
sqlConnection.Open();
cmd.CommandText = "INSERT INTO Simchos " +
"VALUES (@simchaName, @date) " +
"SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@simchaName", simcha.SimchaName);
cmd.Parameters.AddWithValue("@date", simcha.Date);
simcha.Id = (int)(decimal)cmd.ExecuteScalar();
}
var contributors = cManager.GetContributors();
foreach (Contributor contributor in contributors)
{
if (contributor.AlwaysInclude)
{
AddContribution(new Contribution
{
SimchaId = simcha.Id,
ContributorId = contributor.Id,
Amount = 5
});
}
}
}
private void SetSimchaTotal(Simcha simcha)
{
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT ISNull(SUM(ContributionAmount), 0) as Total, " +
"Count(*) as ContributorCount " +
"FROM Contributions " +
"WHERE SimchaId = @simchaId";
cmd.Parameters.AddWithValue("@simchaId", simcha.Id);
connection.Open();
var reader = cmd.ExecuteReader();
reader.Read();
simcha.Total = (decimal)reader["Total"];
simcha.ContributorCount = (int)reader["ContributorCount"];
}
}
public IEnumerable <Simcha> GetSimchos()
{
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Simchos ";
connection.Open();
var simchos = new List <Simcha>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var simcha = new Simcha
{
Id = (int)reader["Id"],
Date = (DateTime)reader["Date"],
SimchaName = (string)reader["SimchaName"]
};
SetSimchaTotal(simcha);
simchos.Add(simcha);
}
return(simchos);
}
}
public Simcha GetSimchaById(int simchaId)
{
using (var connection = new SqlConnection(_connectionString))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Simchos " +
"WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", simchaId);
connection.Open();
var reader = cmd.ExecuteReader();
if (!reader.Read())
{
return(null);
}
var simcha = new Simcha
{
Id = (int)reader["Id"],
Date = (DateTime)reader["Date"],
SimchaName = (string)reader["SimchaName"]
};
SetSimchaTotal(simcha);
return(simcha);
}
}
