public Simcha GetSimcha(int id)
{
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT *, (
SELECT ISNull(SUM(AmountContributing), 0)
FROM SimchaContributors
WHERE SimchaId = s.Id
) as 'Total', (
SELECT COUNT(*)
FROM SimchaContributors
WHERE SimchaId = s.Id
) as 'ContributorAmount' FROM Simchas s
WHERE Id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
var reader = cmd.ExecuteReader();
if (!reader.Read())
{
return(null);
}
var simcha = new Simcha();
simcha.Id = (int)reader["Id"];
simcha.Date = (DateTime)reader["SimchaDate"];
simcha.SimchaName = (string)reader["SimchaName"];
simcha.Contributors = (int)reader["ContributorAmount"];
simcha.Total = (decimal)reader["Total"];
return(simcha);
}
}
public IEnumerable <Simcha> GetSimchas()
{
List <Simcha> simchas = new List <Simcha>();
using (SqlConnection conn = new SqlConnection(_connectionString))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"SELECT s.Id, s.SimchaName, s.SimchaDate, COUNT(sc.ContributorId) AS ContributorCount, SUM(sc.AmountContributing) AS Total FROM Simchas s
LEFT JOIN SimchaContributors sc
ON s.Id = sc.SimchaId
GROUP BY s.Id, s.SimchaName, s.SimchaDate";
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Simcha simcha = new Simcha
{
Id = (int)reader["Id"],
SimchaName = (string)reader["SimchaName"],
Date = (DateTime)reader["SimchaDate"]
};
if (reader["ContributorCount"] != DBNull.Value && reader["Total"] != DBNull.Value)
{
simcha.Contributors = (int)reader["ContributorCount"];
simcha.Total = (decimal)reader["Total"];
}
simchas.Add(simcha);
}
}
return(simchas);
}
public void AddSimcha(Simcha s)
{
using (var context = new SimchaContext(_connectionString))
{
context.Simchas.Add(s);
context.SaveChanges();
}
}
public void AddSimcha(Simcha simcha)
{
using (var connection = new SqlConnection(_connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO Simchas VALUES (@name, @date)";
command.Parameters.AddWithValue("@name", simcha.Name);
command.Parameters.AddWithValue("@date", simcha.Date);
connection.Open();
command.ExecuteNonQuery();
}
}
public void AddSimcha(Simcha simcha)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO Simchas (SimchaName, SimchaDate) VALUES (@simchaName, @simchaDate)";
cmd.Parameters.AddWithValue("@simchaName", simcha.SimchaName);
cmd.Parameters.AddWithValue("@simchaDate", simcha.Date);
conn.Open();
cmd.ExecuteNonQuery();
};
}
public Simcha GetById(int SimchaId)
{
using (var connection = new SqlConnection(_connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = "Select * FROM Simchas WHERE Id = @Id";
command.Parameters.AddWithValue("@Id", SimchaId);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (!reader.Read())
{
return(null);
}
Simcha simcha = new Simcha
{
Id = (int)reader["Id"],
Date = (DateTime)reader["Date"],
Name = (string)reader["Name"]
};
return(simcha);
}
}
public IEnumerable <Simcha> GetAllSimchas()
{
using (var connection = new SqlConnection(_connectionString))
using (var command = connection.CreateCommand())
{
command.CommandText = "Select * FROM Simchas ORDER BY Id DESC";
connection.Open();
List <Simcha> result = new List <Simcha>();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Simcha simcha = new Simcha
{
Id = (int)reader["Id"],
Date = (DateTime)reader["Date"],
Name = (string)reader["Name"]
};
result.Add(simcha);
}
return(result);
}
}