public bool Create(Application.Application application, out int id)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
var success = true;
id = -1;
var sql = "INSERT INTO Applications (Name) Values (@Name);";
success &= cn.Execute(sql, application) > 0;
var newId = cn.Query<ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").SingleOrDefault();
var convId = id = Convert.ToInt32(newId);
if (id > 0)
{
var applicationRoles = application.Roles.Select(n =>
{
n.ApplicationID = convId;
return n;
}).ToList();
sql = "INSERT INTO ApplicationRoles (ApplicationID, Name) Values (@ApplicationID, @Name);";
success &= cn.Execute(sql, applicationRoles) > 0;
if (!application.CourseAccess.IsEmpty())
{
var courseAccess =
application.CourseAccess.Select(
n =>
{
n.ApplicationID = convId;
return n;
});
sql = "INSERT INTO ApplicationCourseYears (ApplicationID, CourseYearID) Values (@ApplicationID, @CourseYearID);";
success &= cn.Execute(sql, courseAccess) > 0;
}
}
// if (!success | newId > 0)
// transaction.Dispose();
// else
transaction.Complete();
return success | newId > 0;
}
}
}
public bool Put(Application.Application application)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
var success = true;
var sql = "UPDATE `Applications` SET `Name`=@Name WHERE `ID`=@ID;";
success &= cn.Execute(sql, new { Name = application.Name, ID = application.ID }) > 0;
sql = "DELETE FROM `ApplicationRoles` WHERE `ApplicationID`=@id;";
/*success &=*/ cn.Execute(sql, new { id = application.ID });// > 0;
if (!application.Roles.IsEmpty())
{
sql =
"INSERT INTO `ApplicationRoles` (`ID`, `ApplicationID`, `Name`) Values (@ID, @ApplicationID, @Name);";
success &= cn.Execute(sql, application.Roles) > 0;
}
sql = "DELETE FROM `ApplicationCourseYears` WHERE `ApplicationID`=@id;";
/*success &=*/ cn.Execute(sql, new { id = application.ID });// > 0;
if (!application.CourseAccess.IsEmpty())
{
sql = "INSERT INTO `ApplicationCourseYears` (`ID`, `ApplicationID`, `CourseYearID`) Values (@ID, @ApplicationID, @CourseYearID);";
success &= cn.Execute(sql, application.CourseAccess) > 0;
}
if (!success)
transaction.Dispose();
else
transaction.Complete();
return success;
}
}
}
public bool Update(Application.Application application)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
bool success = true;
#region fetch old application obj with same ID
var sql = "SELECT `ID`, `Name` FROM `Applications` WHERE `ID` = @id;";
var oldApplication =
cn.Query<Application.Application>(sql, new {id = application.ID}).SingleOrDefault();
if (oldApplication != null)
{
sql =
"SELECT `ID`, `ApplicationID`, `CourseYearID` FROM `ApplicationCourseYears` WHERE `ApplicationID` = @id;";
oldApplication.CourseAccess = cn.Query<CourseYears>(sql, new {id = application.ID});
sql =
"SELECT `ID`, `ApplicationID`, `Name` FROM `ApplicationRoles` WHERE `ApplicationID` = @id;";
oldApplication.Roles = cn.Query<ApplicationRole>(sql, new {id = application.ID});
}
#endregion
sql = "UPDATE `Applications` SET `Name`=@Name WHERE `ID`=@ID;";
success &= cn.Execute(sql, new {Name = application.Name, ID = application.ID}) > 0;
if (application.Roles.IsEmpty() && !oldApplication.Roles.IsEmpty())
{
sql = "DELETE FROM `ApplicationRoles` WHERE `ApplicationID`=@ApplicationID;";
success &= cn.Execute(sql, new {ApplicationID = application.ID}) > 0;
}
else
{
if (!application.Roles.IsEmpty() && oldApplication.Roles.IsEmpty())
{
var applicationRoles = application.Roles.Select(n =>
{
n.ApplicationID = application.ID;
return n;
}).ToList();
sql = "INSERT INTO `ApplicationRoles` (ApplicationID, Name) Values (@ApplicationID, @Name);";
success = cn.Execute(sql, applicationRoles) > 0;
}
else if (!application.Roles.IsEmpty() && !oldApplication.Roles.IsEmpty())
{
var toDelete =
oldApplication.Roles.Where(n => !application.Roles.Any(m => n.ID == m.ID && m.ID > 0));
var toUpdate = application.Roles.Where(n => oldApplication.Roles.Any(m => n.ID == m.ID));
var toCreate = application.Roles.Where(n => n.ID == 0);
var toCreateFinal = toCreate.Select(n =>
{
n.ApplicationID = application.ID;
return n;
}).ToList();
if (!toDelete.IsEmpty())
{
sql = "DELETE FROM `ApplicationRoles` WHERE `id`=@id;";
success &= cn.Execute(sql, toDelete) > 0;
}
if (!toUpdate.IsEmpty())
{
sql =
"UPDATE `ApplicationRoles` SET `Name` = @Name, `ApplicationID` = @ApplicationID WHERE `id`=@id;";
success &= cn.Execute(sql, toUpdate) > 0;
}
if (!toCreate.IsEmpty())
{
sql =
"INSERT INTO `ApplicationRoles` (`ID`, `ApplicationID`, `Name`) Values (@ID, @ApplicationID, @Name);";
success &= cn.Execute(sql, toCreateFinal) > 0;
// _connection.Execute()
}
}
}
if (application.CourseAccess.IsEmpty() && !oldApplication.CourseAccess.IsEmpty())
{
sql = "DELETE FROM `ApplicationCourseYears` WHERE `ApplicationID`=@ApplicationID;";
success &= cn.Execute(sql, new { ApplicationID = application.ID }) > 0;
}
else
{
if (!application.CourseAccess.IsEmpty() && oldApplication.CourseAccess.IsEmpty())
{
var applicationCourseYears = application.CourseAccess.Select(n =>
{
n.ApplicationID = application.ID;
return n;
}).ToList();
sql = "INSERT INTO `ApplicationCourseYears` (ApplicationID, CourseYearID) Values (@ApplicationID, @CourseYearID);";
success = cn.Execute(sql, applicationCourseYears) > 0;
}
else if (!application.CourseAccess.IsEmpty() && !oldApplication.CourseAccess.IsEmpty())
{
//TODO: we reattach the ID to existing items. This seemed more difficult to do on the angularside.
foreach (var oldItem in oldApplication.CourseAccess)
{
foreach (var newItem in application.CourseAccess)
{
if (oldItem.CourseYearID == newItem.CourseYearID)
newItem.ID = oldItem.ID;
}
}
var toDelete = oldApplication.CourseAccess.Where(n => !application.CourseAccess.Any(m => n.ID == m.ID && m.ID > 0));
var toUpdate = application.CourseAccess.Where(n => oldApplication.CourseAccess.Any(m => n.ID == m.ID));
var toCreate = application.CourseAccess.Where(n => n.ID == 0);
var toCreateFinal = toCreate.Select(n =>
{
n.ApplicationID = application.ID;
return n;
}).ToList();
if (!toDelete.IsEmpty())
{
sql = "DELETE FROM `ApplicationCourseYears` WHERE `id`=@id;";
success &= cn.Execute(sql, toDelete) > 0;
}
if (!toUpdate.IsEmpty())
{
sql =
"UPDATE `ApplicationCourseYears` SET `CourseYearID` = @CourseYearID, `ApplicationID` = @ApplicationID WHERE `id`=@id;";
success &= cn.Execute(sql, toUpdate) > 0;
}
if (!toCreate.IsEmpty())
{
sql =
"INSERT INTO `ApplicationCourseYears` (`ID`, `ApplicationID`, `CourseYearID`) Values (@ID, @ApplicationID, @CourseYearID);";
success &= cn.Execute(sql, toCreateFinal) > 0;
// _connection.Execute()
}
}
}
if (!success)
transaction.Dispose();
else
transaction.Complete();
return success;
}
}
}
public bool Delete(Application.Application application)
{
using (var cn = Connection)
{
using (var transaction = new TransactionScope())
{
cn.Open();
var success = true;
var sql = "DELETE FROM `Applications` WHERE `Id`=@id;";
success &= cn.Execute(sql, new { id = application.ID }) > 0;
if (!application.CourseAccess.IsEmpty())
{
sql = "DELETE FROM `ApplicationCourseYears` WHERE `ApplicationID`=@ApplicationID;";
success &= cn.Execute(sql, new {ApplicationID = application.ID}) > 0;
}
if (!application.Roles.IsEmpty())
{
sql = "DELETE FROM `ApplicationRoles` WHERE `ApplicationID`=@ApplicationID;";
success &= cn.Execute(sql, new {ApplicationID = application.ID}) > 0;
}
if (!success)
transaction.Dispose();
else
transaction.Complete();
return success;
}
}
}
