public bool Create(User user, out int id)
{
using (var connection = Connection)
{
using (var transactionScope = new TransactionScope())
{
connection.Open();
var sql = "INSERT INTO `users` (`username`, `forename`, `surname`, `email`, `enabled`, `archived`) VALUES (@username, @forename, @surname, @email, @enabled, @archived);";
var cn = connection.Execute(sql, new
{
username = user.Username,
forename = user.Forename,
surname = user.Surname,
email = user.Email,
enabled = user.Enabled,
archived = user.Archived
}) > 0;
var insertedID = connection.Query<ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").SingleOrDefault();
id = Convert.ToInt32(insertedID);
var insertObj = user.Roles.Select(n => new { UserId = insertedID, RoleId = n.ID });
sql = "INSERT INTO `UserHasRoles` (`UserID`, `RoleId`) VALUES (@UserId, @RoleId);";
cn = connection.Execute(sql, insertObj) > 0;
transactionScope.Complete();
return cn & insertedID > 0;
}
}
}
public UserSearchDocument(User user)
{
User = user;
DatabaseID = user.ID;
Title = string.Format("{0} {1}", User.Forename, User.Surname);
Description = String.Empty;
Type = "User";
UniqueID = string.Format("{0}_{1}", Type, user.ID);
}
public bool Delete(User user)
{
user.Enabled = false;
user.Surname = string.Empty;
user.Forename = string.Empty;
user.Archived = false;
using (var cn = Connection)
{
const string sql = "UPDATE `users` SET `username` = @username, `forename` = @forename, `surname`= @surname, `email` = @email, `enabled` = @enabled, `archived` = @archived WHERE `id` = @id";
var sqlParams = new
{
username = user.Username,
forename = string.Empty,
surname = string.Empty,
email = user.Email,
enabled = false,
archived = true,
id = user.ID
};
return cn.Execute(sql, sqlParams) > 0;
}
}
public bool Update(User user)
{
using (var cn = Connection)
{
using (var transactionScope = new TransactionScope())
{
cn.Open();
var sql = "UPDATE `users` SET `username` = @username, `forename` = @forename, `surname`= @surname, `email` = @email, `enabled` = @enabled, `archived` = @archived WHERE `id` = @id";
var success = cn.Execute(sql, new
{
username = user.Username,
forename = user.Forename,
surname = user.Surname,
email = user.Email,
enabled = user.Enabled,
archived = user.Archived,
id = user.ID
}) > 0;
//todo: how to only alter roles which have been updated?
sql = "SELECT `RoleID` FROM `UserHasRoles` WHERE `UserID` = @UserId";
var oldRoles = cn.Query<int>(sql, new { UserId = user.ID });
var enumerable = oldRoles as int[] ?? oldRoles.ToArray();
// if (!enumerable.HasContent())
// {
// sql = "INSERT INTO `UserHasRoles` (`UserID`, `RoleId`) VALUES (@UserId, @RoleId);";
// var insertObj = _user.Roles.Select(n => new { UserId = _user.ID, RoleId = n.ID });
// success = _connection.Execute(sql, insertObj) > 0;
// }
// else
// {var toDelete = enumerable.Where(n => !_user.Roles.Any(m => m.ID == n));
var toDelete = enumerable.Where(n => user.Roles.All(m => m.ID != n));
var toUpdate = user.Roles.Where(n => !enumerable.Any(m => m == n.ID));
var delete = toDelete as int[] ?? toDelete.ToArray();
if (delete.HasContent())
{
sql = "DELETE FROM `UserHasRoles` WHERE `RoleId` IN @roleIds AND `UserId` = @UserId;";
success = cn.Execute(sql, new { roleIds = delete, UserId = user.ID }) > 0;
}
var update = toUpdate as Role[] ?? toUpdate.ToArray();
if (update.HasContent())
{
sql = "INSERT INTO `UserHasRoles` (`UserID`, `RoleId`) VALUES (@UserId, @RoleId);";
var insertObj = update.Select(n => new { UserId = user.ID, RoleId = n.ID });
success = cn.Execute(sql, insertObj) > 0;
}
// }
transactionScope.Complete();
return success;
}
}
}