public bool Create(Course course, out int id)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
var success = true;
id = -1;
var sql = "INSERT INTO Courses (Name, Abbreviation, BannerCode) Values (@Name, @Abbreviation, @BannerCode);";
success &= cn.Execute(sql, course) > 0;
var newId = cn.Query<ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").SingleOrDefault();
var convId = id = Convert.ToInt32(newId);
if (id > 0)
{
if (course.CourseYears.HasContent())
{
var courseYears = course.CourseYears.Select(n =>
{
n.CourseID = convId;
return n;
}).ToList();
sql = "INSERT INTO CourseYears (Name, Abbreviation, BannerCode, YearStart, CourseID, Year) Values (@Name, @Abbreviation, @BannerCode, @YearStart, @CourseID, @Year);";
success = cn.Execute(sql, courseYears/*course.CourseYears*/) > 0; //TODO: check this out! should I be passing course.Courseyears in or courseYears list which I attached the newly inserted courseyear ID to each course year.
// id = newId;
}
}
transaction.Complete();
return success | newId > 0;
}
}
}
public bool Delete(Course course)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
var success = true;
var sql = "DELETE FROM `Courses` WHERE `Id`=@id;";
success &= cn.Execute(sql, new { id = course.ID }) > 0;
sql = "DELETE FROM `CourseYears` WHERE `CourseID`=@CourseID;";
success &= cn.Execute(sql, new { CourseID = course.ID }) > 0;
transaction.Complete();
return success;
}
}
}
public bool Update(Course course)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
bool success = true;
var sql = "SELECT `ID`, `Name`, `Abbreviation`, `BannerCode` FROM `Courses` WHERE `id`=@id;";
var oldCourse = cn.Query<Course>(sql, new { id = course.ID }).SingleOrDefault();
if (oldCourse != null)
{
sql = "SELECT `ID`, `Name`, `Abbreviation`, `BannerCode`, `YearStart`, `CourseID`, `Year` FROM `CourseYears` WHERE `CourseID` = @id;";
oldCourse.CourseYears = cn.Query<CourseYear>(sql, new { id = course.ID });
}
sql = "UPDATE `Courses` SET `Name` = @Name, `Abbreviation` = @Abbreviation, `BannerCode` = @BannerCode WHERE `id`=@id;";
success &= cn.Execute(sql, new { Name = course.Name, Abbreviation = course.Abbreviation, BannerCode = course.BannerCode, id = course.ID }) > 0;
if (course.CourseYears.IsEmpty() && !oldCourse.CourseYears.IsEmpty())
{
sql = "DELETE FROM `CourseYears` WHERE `CourseID`=@id;";
success &= cn.Execute(sql, new { id = course.ID }) > 0;
}
else
{
if (oldCourse.CourseYears.IsEmpty() && !course.CourseYears.IsEmpty())
{
sql =
"INSERT INTO `CourseYears` (`Name`, `Abbreviation`, `BannerCode`, `YearStart`, `CourseID`, `Year`) VALUES (@Name, @Abbreviation, @BannerCode, @YearStart, @CourseID, @Year);";
var toCreateAll = course.CourseYears.Select(n =>
{
n.CourseID = course.ID;
return n;
}).ToList();
success &= cn.Execute(sql, toCreateAll) > 0;
}
else if (!oldCourse.CourseYears.IsEmpty() && !course.CourseYears.IsEmpty())
{
//todo: check for null?
var toDelete =
oldCourse.CourseYears.Where(n => !course.CourseYears.Any(m => n.ID == m.ID && m.ID > 0));
var toUpdate = course.CourseYears.Where(n => oldCourse.CourseYears.Any(m => n.ID == m.ID));
var toCreate = course.CourseYears.Where(n => n.ID == 0);
var toCreateFinal = toCreate.Select(n =>
{
n.CourseID = course.ID;
return n;
}).ToList();
if (!toDelete.IsEmpty())
{
sql = "DELETE FROM `CourseYears` WHERE `id`=@id;";
success &= cn.Execute(sql, toDelete) > 0;
}
if (!toUpdate.IsEmpty())
{
sql =
"UPDATE `CourseYears` SET `Name` = @Name, `Abbreviation` = @Abbreviation, `BannerCode` = @BannerCode, `YearStart` = @YearStart, `Year` = @Year WHERE `id`=@id;";
success &= cn.Execute(sql, toUpdate) > 0;
}
if (!toCreate.IsEmpty())
{
sql =
"INSERT INTO `CourseYears` (`Name`, `Abbreviation`, `BannerCode`, `YearStart`, `CourseID`, `Year`) VALUES (@Name, @Abbreviation, @BannerCode, @YearStart, @CourseID, @Year );";
success &= cn.Execute(sql, toCreateFinal) > 0;
// _connection.Execute()
}
}
}
if (!success)
transaction.Dispose();
else
transaction.Complete();
return success;
}
}
}
public bool Put(Course course)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
var success = true;
//TODO: We might need to put to ID aswel, so a delete and then insert inc ID? This would be needed for rolling back a deleted course
var sql =
"UPDATE `Courses` SET `Name` = @Name, `Abbreviation` = @Abbreviation, `BannerCode` = @BannerCode WHERE `id`=@id;";
success &= cn.Execute(sql, new { Name = course.Name, Abbreviation = course.Abbreviation, BannerCode = course.BannerCode, id = course.ID }) > 0;
sql = "DELETE FROM `CourseYears` WHERE `CourseID`=@id;";
cn.Execute(sql, new {id = course.ID}); //doesn't work checking rows affected here incase there were no rows in the old item, if there were none, the bool would flip success to false
if (course.CourseYears.HasContent())
{
sql =
"INSERT INTO CourseYears (ID, Name, Abbreviation, BannerCode, YearStart, Year, CourseID) Values (@ID, @Name, @Abbreviation, @BannerCode, @YearStart, @Year, @CourseID);";
success &= cn.Execute(sql, course.CourseYears) > 0;
// id = newId;
}
if (!success)
transaction.Dispose();
else
transaction.Complete();
return success;
}
}
}
