Serenity: Assign default Roles for New User

[stixoffire]

I am attempting to assign a set of default roles for a new user - this is almost working except when I try to update the UserRoles Table , I receive the following error:

Cannot insert duplicate key row in object 'dbo.UserRoles' with unique index 'UQ_UserRoles_UserId_RoleId'. The duplicate key value is (4, 3).
The statement has been terminated.'

I don't have any users with Id of 4 in the UserRoles table, so why am I getting the duplicate key error ?

So how do I add a role to a user programatically?

my code

Web.Config

<AppSettings>
    <add key="DefaultRole" value="group1,group2"/>
<AppSettings>

AuthorizationService.cs ValidateFirstTimeUser

          // Changed code from 'return ValidateFirstTimeUser(ref username, password);'
            bool IsValidUser = ValidateFirstTimeUser(ref username, password);

            // Added for first time user auto assign roles.
            if (IsValidUser)
            {
                Int32? MyId = FirstTimeLogIn.GetUserID(username);

                if (MyId != null)
                {
                    //Assign an Existing Default Role to the User
                    FirstTimeLogIn.UpdateUserRoles(MyId.Value);
                }
            }

            return IsValidUser;

FirstTimeLogIn.cs

```
public static class FirstTimeLogIn
{
public static void UpdateUserRoles(int userID)
{
List rows = new List();

        List<string> defaultRoles = new List<string>();

        if (!ConfigurationManager.AppSettings["DefaultRole"].IsTrimmedEmpty())
        {
            string[] sep = new string[] { ','.ToString() };
            string[] roles = ConfigurationManager.AppSettings["DefaultRole"].ToString().Split(sep, StringSplitOptions.RemoveEmptyEntries);

            defaultRoles.AddRange(roles);
        }

        using (var connection = SqlConnections.NewFor<RoleRow>())
        {
            var fld = RoleRow.Fields;
            rows = connection.List<RoleRow>(q => q
                .Select(fld.RoleId, fld.RoleName));
        }

        for (int i = 0; i < rows.Count; i++)
        {
            string roleName = rows[i].RoleName.ToLower();                
            if (defaultRoles.Contains(roleName))
            {
                if (rows[i].RoleId.HasValue)
                {
                    int roleID = rows[i].RoleId.Value; 
                    UpdateUserRole(roleID, userID);
                }  
            }
        }
    }

    public static Int32? GetUserID(string userName)
    {
        using (var connection = SqlConnections.NewFor<UserRow>())
        {
            Int32? myId = null;
            var fld = UserRow.Fields;
            var row = connection.First<UserRow>(q => q
                .Select(fld.UserId).Where(fld.Username == userName));
            if (row != null & row.UserId.HasValue)
            {
                myId = row.UserId.Value;
            }
            return myId;
        }
    }

    private static void UpdateUserRole(int roleID, int userID)
    {
        using (var connection = SqlConnections.NewFor<UserRoleRow>())
        using (var uow = new UnitOfWork(connection))
        {
            var fld = UserRoleRow.Fields;
            string tbl = fld.TableName;                
            new SqlUpdate(fld.TableName)
                .Set(fld.UserId, userID)
                .Set(fld.RoleId, roleID)
                .Execute(connection, ExpectedRows.One);
            uow.Commit();
        }
    }

}

```

[stixoffire]

After figuring out how to update using the Repository call it works ..

Authorization.cs

            // Changed code from 'return ValidateFirstTimeUser(ref username, password);'
            bool IsValidUser = ValidateFirstTimeUser(ref username, password);

            // Added for first time user auto assign roles.
            if (IsValidUser)            {

                user = Dependency.Resolve<IUserRetrieveService>().ByUsername(username) as UserDefinition;   

                if (user != null)
                {
                    //Assign an Existing Default Role to the User
                    FirstTimeLogIn.AssignNewUserDefaultRoles(user.UserId);
                }
            }
            return IsValidUser;
            // End Additional Code ..

    /// FirstTimeLogin.cs
    /// <summary>
    /// Set Group Roles for First Time LogIn User
    /// Application Settings File provides a comma separated roles field
    /// </summary>
    public static class FirstTimeLogIn
    {

        /// <summary>
        /// Update the User Roles to Preset Roles.
        /// </summary>
        /// <param name="userID"></param>
        public static void AssignNewUserDefaultRoles(int userID)
        {
            List<RoleRow> rows = new List<RoleRow>();

            List<string> defaultRoles = new List<string>();

            // Get the DefaultRoles assigned in the web config
            if (!ConfigurationManager.AppSettings["DefaultRole"].IsTrimmedEmpty())
            {
                string[] sep = new string[] { ','.ToString() };
                string[] roles = ConfigurationManager.AppSettings["DefaultRole"].ToString().Split(sep, StringSplitOptions.RemoveEmptyEntries);

                defaultRoles.AddRange(roles);
            }

            // Get the Role Ids that match the role names in the Database.
            using (var connection = SqlConnections.NewFor<RoleRow>())
            {
                var fld = RoleRow.Fields;
                rows = connection.List<RoleRow>(q => q
                    .Select(fld.RoleId, fld.RoleName));
            }

            UserRoleUpdateRequest UserRoleRequest = new UserRoleUpdateRequest();

            UserRoleRequest.UserID = userID;

            List<Int32> rolesList = new List<int>();

            // Add the default roles to the list 
            for (int i = 0; i < rows.Count; i++)
            {
                string roleName = rows[i].RoleName.ToLower();

                if (defaultRoles.Contains(roleName))
                {
                    if (rows[i].RoleId.HasValue)
                    {
                        int roleID = rows[i].RoleId.Value;

                        rolesList.Add(roleID);
                    }
                }
            }

            // Set the RolesList in the UserRoleRequest = to our list of roles (ById Field).
            UserRoleRequest.Roles = rolesList;

            using (var connection = SqlConnections.NewFor<UserRoleRow>())
            using (var uow = new UnitOfWork(connection))
            {
                if (UserRoleRequest.Roles.Count > 0)
                {
                Repositories.UserRoleRepository MyUserRoleRepository = new Repositories.UserRoleRepository();

                MyUserRoleRepository.Update(uow, UserRoleRequest);

                }

                uow.Commit();
            }
        }