Create user inside a transaction

Question

When creating a user like so:

using var trans = Db.BeginTransaction();
AuthRepository.CreateUserAuth(newUser, request.Password);
AuthRepository.AssignRoles(created, new List { request.role });
//.. do other stuff
throw new Exception("other code may throw this");
trans.Commit();

The Auth repo has it's own connection so it's not part of the transaction. This means, if my code bails out, I end up with an unwanted user.

Is there any way to use a transaction with AuthRepository or is only way to manually write to user and role tables? I couldn't find an example of creating the password hash in docs when manually saving, is there any example?

user_85785 · Answer

You can't use the existing Auth Repository APIs within a transaction because each uses its own DB connection.

If you want you can take the implementation of those OrmLite Auth Respository APIs in OrmLiteAuthRepository.cs and move them into your method so they're all using the same DB Connection + Transaction.

Also note that to use Transactions in OrmLite you should use OpenTransaction() instead, e.g:

using (var dbTrans = db.OpenTransaction())
{
}

user_657477 · Answer

Adding class I used here in case it helps anyone as needed a little refactoring:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using OutReachPete.ServiceModel.User;
using ServiceStack;
using ServiceStack.Auth;
using ServiceStack.OrmLite;

namespace OutReachPete.ServiceInterface.User
{
    public class UserHelper
    {
        public static bool ForceCaseInsensitiveUserNameSearch { get; set; } = true;
        public static bool UseDistinctRoleTables { get; set; } = true;

        public static UserAuthCustom CreateUserAuth(UserAuthCustom newUser, string password, IDbConnection db)
        {
            newUser.ValidateNewUser(password);

            AssertNoExistingUser(db, newUser);

            newUser.PopulatePasswordHashes(password);
            newUser.CreatedDate = DateTime.UtcNow;
            newUser.ModifiedDate = newUser.CreatedDate;

            db.Save(newUser);

            newUser = db.SingleById(newUser.Id);

            return newUser;
        }

        public static void AssertNoExistingUser(IDbConnection db, IUserAuth newUser,
            IUserAuth exceptForExistingUser = null)
        {
            if (newUser.UserName != null)
            {
                var existingUser = GetUserAuthByUserName(db, newUser.UserName);
                if (existingUser != null
                    && (exceptForExistingUser == null || existingUser.Id != exceptForExistingUser.Id))
                    throw new ArgumentException(string.Format(ErrorMessages.UserAlreadyExistsTemplate1,
                        newUser.UserName.SafeInput()));
            }

            if (newUser.Email != null)
            {
                var existingUser = GetUserAuthByUserName(db, newUser.Email);
                if (existingUser != null
                    && (exceptForExistingUser == null || existingUser.Id != exceptForExistingUser.Id))
                    throw new ArgumentException(string.Format(ErrorMessages.EmailAlreadyExistsTemplate1,
                        newUser.Email.SafeInput()));
            }
        }

        public static UserAuthCustom GetUserAuthByUserName(IDbConnection db, string userNameOrEmail)
        {
            var isEmail = userNameOrEmail.Contains("@");
            var lowerUserName = userNameOrEmail.ToLower();

            UserAuthCustom userAuth = null;

            // Usernames/Emails are saved in Lower Case so we can do an exact search using lowerUserName
            if (HostContext.GetPlugin()?.SaveUserNamesInLowerCase == true)
            {
                return isEmail
                    ? db.Select(q => q.Email == lowerUserName).FirstOrDefault()
                    : db.Select(q => q.UserName == lowerUserName).FirstOrDefault();
            }

            // Try an exact search using index first
            userAuth = isEmail
                ? db.Select(q => q.Email == userNameOrEmail).FirstOrDefault()
                : db.Select(q => q.UserName == userNameOrEmail).FirstOrDefault();

            if (userAuth != null)
                return userAuth;

            // Fallback to a non-index search if no exact match is found
            if (ForceCaseInsensitiveUserNameSearch)
            {
                userAuth = isEmail
                    ? db.Select(q => q.Email.ToLower() == lowerUserName).FirstOrDefault()
                    : db.Select(q => q.UserName.ToLower() == lowerUserName).FirstOrDefault();
            }

            return userAuth;
        }

        public static void AssignRoles(IAuthRepository userAuthRepo, IUserAuth userAuth, IDbConnection db,
            ICollection roles = null, ICollection permissions = null)
        {
            if (userAuthRepo is IManageRoles managesRoles)
            {
                AssignRoles(userAuth.Id.ToString(), db, roles, permissions);
            }
            else
            {
                AssignRolesInternal(userAuth, roles, permissions);

                SaveUserAuth(userAuth, db);
            }
        }

        public static IUserAuth GetUserAuth(string userAuthId, IDbConnection db)
        {
            if (string.IsNullOrEmpty(userAuthId))
                throw new ArgumentNullException(nameof(userAuthId));

            return db.SingleById(int.Parse(userAuthId));
        }

        public static void AssignRoles(string userAuthId, IDbConnection db, ICollection roles = null, ICollection permissions = null)
        {
            var userAuth = GetUserAuth(userAuthId, db);
            if (!UseDistinctRoleTables)
            {
                if (!roles.IsEmpty())
                {
                    foreach (var missingRole in roles.Where(x => userAuth.Roles == null || !userAuth.Roles.Contains(x)))
                    {
                        if (userAuth.Roles == null)
                            userAuth.Roles = new List();

                        userAuth.Roles.Add(missingRole);
                    }
                }

                if (!permissions.IsEmpty())
                {
                    foreach (var missingPermission in permissions.Where(x => userAuth.Permissions == null || !userAuth.Permissions.Contains(x)))
                    {
                        if (userAuth.Permissions == null)
                            userAuth.Permissions = new List();

                        userAuth.Permissions.Add(missingPermission);
                    }
                }

                SaveUserAuth(userAuth, db);
            }
            else
            {

                var now = DateTime.UtcNow;
                var userRoles = db.Select(q => q.UserAuthId == userAuth.Id);

                if (!roles.IsEmpty())
                {
                    var roleSet = userRoles.Where(x => x.Role != null).Select(x => x.Role).ToHashSet();
                    foreach (var role in roles)
                    {
                        if (!roleSet.Contains(role))
                        {
                            db.Insert(new UserAuthRole
                            {
                                UserAuthId = userAuth.Id,
                                Role = role,
                                CreatedDate = now,
                                ModifiedDate = now,
                            });
                        }
                    }
                }

                if (!permissions.IsEmpty())
                {
                    var permissionSet = userRoles.Where(x => x.Permission != null).Select(x => x.Permission).ToHashSet();
                    foreach (var permission in permissions)
                    {
                        if (!permissionSet.Contains(permission))
                        {
                            db.Insert(new UserAuthRole
                            {
                                UserAuthId = userAuth.Id,
                                Permission = permission,
                                CreatedDate = now,
                                ModifiedDate = now,
                            });
                        }
                    }
                }

            }
        }

        private static void AssignRolesInternal(IUserAuth userAuth, ICollection roles, ICollection permissions)
        {
            if (!roles.IsEmpty())
            {
                foreach (var missingRole in roles.Where(x => userAuth.Roles == null || !userAuth.Roles.Contains(x)))
                {
                    if (userAuth.Roles == null)
                        userAuth.Roles = new List();

                    userAuth.Roles.Add(missingRole);
                }
            }

            if (!permissions.IsEmpty())
            {
                foreach (var missingPermission in permissions.Where(x =>
                    userAuth.Permissions == null || !userAuth.Permissions.Contains(x)))
                {
                    if (userAuth.Permissions == null)
                        userAuth.Permissions = new List();

                    userAuth.Permissions.Add(missingPermission);
                }
            }
        }

        public static void SaveUserAuth(IUserAuth userAuth, IDbConnection db)
        {
            if (userAuth == null)
                throw new ArgumentNullException(nameof(userAuth));

            userAuth.ModifiedDate = DateTime.UtcNow;
            if (userAuth.CreatedDate == default(DateTime))
                userAuth.CreatedDate = userAuth.ModifiedDate;
            
            db.Save((UserAuthCustom)userAuth);
            
        }

    }
}

Then just call it like:

var created = UserHelper.CreateUserAuth(newUser, request.Password, Db);

UserHelper.AssignRoles(AuthRepository, created, Db, new List { request.CreateUserType.ToString() });

It will only use the connection passed. Just change UserAuthCustom to whatever you user class is.

Create user inside a transaction

2 Answers