DAL User Roles & Management

If you wish to allow for a user management system then we recommend you use start with the default AspNet database packaged as part of a Visual Studio template (covered in an earlier tutorial).

This tutorial will look at some of the  functions you may like to create that work with both USERS and ROLES as these may be easily incorporated into user access privileges (covered elsewhere in our tutorial series).

We don't describe an ideal methodology for user management, but something that will suffice for small-scale projects. There are a number of methods you may like to configure in your data access layer.

Begin by creating a new TableAdapter in your data set (data access layer).

'Right-click' in blank space, hover over 'Add' and select 'TableAdatper...'.

Setup your TableAdapter, selecting your data connection and opting to 'Use SQL Statements'.

You may then either write your SQL straight into the text area or use the Query Builder.

We recommend for this to use the Query Builder at first. Select The AspNetRoles, AspNetUserRoles and AspNetUsers tables.

There is a 'many to many' relation between Users and Roles, meaning a user may have a number of roles and a role may be allocated to a number of users. UserRoles represent the link between the two by storing the primary key of each and using these as a composite primary key (no two records will have the same UserID and RoleID combination.

These tables will be linked in SQL through the use of INNER JOIN statements, with the ON command denoting the fields to link with.

Select all the fields in ROLES, and select only the fields in USERS we envisage wanting to retrieve or edit using our DAL. 

We do not need either of the fields in USERROLES as the equivalent fields are taken from the other tables.

As we have two 'Id' fields, lets give them both an appropriate alias (UserID and RoleID for example). This can be seen below.

As there are three tables, we have two linking INNER JOIN statements. Change them both to RIGHT OUTER JOIN (more details about JOIN types furher below).

You can use the Execute Query button to test it returns the relevant users and to see all columns returned.

Complete the wizard by naming the functions (e.g. FillUsers and GetUsers) and confirming the wizard results.

Your SQL statement and the wizard dialogue boxes should reflect the following.

SELECT  AspNetUsers.Id AS UserID, 
        AspNetUsers.Email,
        AspNetUsers.PhoneNumber,
        AspNetUsers.TwoFactorEnabled,
        AspNetUsers.LockoutEndDateUtc,
        AspNetUsers.LockoutEnabled,
        AspNetUsers.AccessFailedCount, 
        AspNetUsers.UserName,

        AspNetRoles.Id AS RoleID,
        AspNetRoles.Name

FROM    AspNetRoles
        RIGHT OUTER JOIN AspNetUserRoles 
            ON AspNetRoles.Id = AspNetUserRoles.RoleId 
        RIGHT OUTER JOIN AspNetUsers 
            ON AspNetUserRoles.UserId = AspNetUsers.Id

Name the TableAdapter in the dataset appropriately, such as 'UsersAndRoles' and 'UsersAndRolesTableAdapter'.

Joins

There are four common types of JOIN statements though variations can also be produced. 

Consider the following example of two tables with a one to many relation between USERS and ROLES. All joins produce records that exist in both tables, but some append other data too. 

Our JOIN types are:

  • INNER JOIN - Returns only those records that appear in both the USERS AND ROLES table
  • FULL OUTER JOIN - Returns all records from either and both tables much like a logical OR statement. 
  • LEFT OUTER JOIN - Returns all records that appear in the first table listed (left side of the join statement)and all linking fields from the 'right' table. In the diagram below this would be all records from USERS whether or not they have ROLES allocated. Users without a role will appear in the results with their RoleID fields holding NULL values.
  • RIGHT OUTER JOIN - Returns all records that appear in the second table listed (right side of the join statement) and all linking fields from the 'left' table. In the diagram below this would be all records from ROLES whether or not they have USERS who use them. Roles that are not allocated to anyone will appear in the results with NULL values against the USERS-related fields. All USERS allocated a RoleID will also be shown.

Don't confuse LEFT or RIGHT joins as only returning values from one table as they include records with shared fields (unless conditions are put on place to avoid this). In Boolean Algebra the LEFT OUTER JOIN below could be explained as 'USER OR (USER AND ROLE)'

A worked example:

We will assume the following data for the tables USERS and ROLES.

Note that two users have no RoleID allocated. One role (RoleID: 1, Guest) is not assigned to any users.

The INNER JOIN will produce all users that have an allocated role.

The FULL OUTER JOIN will produce a list of all USERS, with NULL values for any ROLE-related field for that record. Any additional ROLES will also be included with NULL values for any USER-related fields (seen here in the last row).

The LEFT OUTER JOIN returns all USERS, irrespective of whether they have a role allocated. It is likely that this is the most beneficial option for this scenario.

Note: With the AspNet database we use the RIGHT OUTER JOIN to replicate this method as the AspNetUsers appears after (right of) the SQL command.

The RIGHT OUTER JOIN displays all USERS with an allocated RoleID, and all other ROLES that may not be allocated (shown in the first record below that has NULL values for the USER-related fields). For this scenario, this may be the least useful.

You may like to use parameterised queries too, so add another query to your TableAdapter:

Working through the wizard you can either enter your SQL statement (you could start by copying in the SQL from the previous query) or use the Query Builder.

We will then add WHERE conditions with parameters, shown below, to allow for filtering. 

WHERE   (AspNetUsers.Email LIKE '%' + @email + '%'             OR AspNetUsers.Email IS NULL) 
    AND (AspNetUsers.PhoneNumber LIKE '%' + @phoneNumber + '%' OR AspNetUsers.PhoneNumber IS NULL) 
    AND (AspNetUsers.UserName LIKE '%' + @username + '%'       OR AspNetUsers.UserName IS NULL)
    AND (AspNetRoles.Id LIKE '%' + @roleID + '%'               OR AspNetRoles.Id IS NULL)
    AND (AspNetRoles.Name LIKE '%' + @roleName + '%'           OR AspNetRoles.Name IS NULL)

Alternatively use the Filter column of the Query Builder as shown below.

Call the methods 'FillUsersByParameters' and 'GetUsersByParameters' respectively.

We will likely also need to retrieve the different roles available. Create a new query using the SQL below and name the methods 'FillRoles' and 'GetRoles' respectively.

SELECT  Id AS RoleID,
        Name

FROM    AspNetRoles

An issue with our 'GetUsersByParameters' method is that it can produce multiple rows for a single user who has a number of roles assigned. It is possible to merge all the RoleIDs for a single user into a single cell, and this is described below. 

Start with the 'GetUsersByParameters' SQL statement, and delete reference to the fields from Roles (Id AS RoleID and Name). Keep the preceding comma for what follows.

We will replace these fields with a single column called Roles using a nested SELECT statement that will select all role IDs where the UserID matches and output them as XML text.

Simply concatenating (putting together) each data entry would not look quite right, so we separate them with two characters: a comma and a space: ', '.

The STUFF command then removes the first comma-space combo using the following syntax:

STUFF (string, start, length, replacement)

  • string - The original text to delete characters from.
  • start - The position of the first character to delete.
  • length - The number of characters to delete, from the starting position.
  • replacement - A string of text to insert in-place of the deleted text (we insert nothing using open and close inverted-commas.)

All of this will still results in multiple rows for each user that has more than one role, but each row will contain the same data in their 'Roles' fields. We can now add the DISTINCT command after the original SELECT command to ensure repeated rows are ignored.

We named this new method, 'GetUsersRolesByParameters'.

SELECT DISTINCT
    AspNetUsers.Id AS UserID, 
    AspNetUsers.Email,
    AspNetUsers.PhoneNumber,
    AspNetUsers.TwoFactorEnabled,
    AspNetUsers.LockoutEndDateUtc,
    AspNetUsers.LockoutEnabled,
    AspNetUsers.AccessFailedCount, 
    AspNetUsers.UserName,
 
    STUFF ((
        SELECT ', ' + AspNetRoles.Name AS [text()]
        FROM   AspNetRoles INNER JOIN AspNetUserRoles ON AspNetUserRoles.RoleId = AspNetRoles.Id
        WHERE  (AspNetUserRoles.UserId = AspNetUsers.Id) 
        FOR XML PATH('')), 1, 2, '') 
    AS Roles

FROM
    AspNetRoles
    RIGHT OUTER JOIN AspNetUserRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId 
    RIGHT OUTER JOIN AspNetUsers     ON AspNetUserRoles.UserId = AspNetUsers.Id

WHERE   (AspNetUsers.Email LIKE '%' + @email + '%'             OR AspNetUsers.Email IS NULL) 
    AND (AspNetUsers.PhoneNumber LIKE '%' + @phoneNumber + '%' OR AspNetUsers.PhoneNumber IS NULL) 
    AND (AspNetUsers.UserName LIKE '%' + @username + '%'       OR AspNetUsers.UserName IS NULL)
    AND (AspNetRoles.Id LIKE '%' + @roleID + '%'               OR AspNetRoles.Id IS NULL)
    AND (AspNetRoles.Name LIKE '%' + @roleName + '%'           OR AspNetRoles.Name IS NULL)

Whilst the role IDs are not necessarily numeric, they may also not be readily identifiable to someone reviewing the roles of their users. You may like to instead use role names (AspNetRoles.Name). This involves the nested SELECT statement to use an INNER JOIN and presented below for comparison.

We used this version of the method in our later tutorial about creating a user management panel under the method name: 'GetUsersRolesByParameters'.

STUFF ((
        SELECT ', ' + AspNetRoles.Name AS [text()]
        FROM   AspNetRoles INNER JOIN AspNetUserRoles ON AspNetUserRoles.RoleId = AspNetRoles.Id
        WHERE  (AspNetUserRoles.UserId = AspNetUsers.Id) 
        FOR XML PATH('')), 1, 2, '') 
    AS Roles

We will of course also want to select individual user records by the user ID. This query detailed below was saved under the method name, GetUserByID.

SELECT	Id AS UserID, 
        Email, 
        PhoneNumber, 
        TwoFactorEnabled, 
        LockoutEndDateUtc, 
        LockoutEnabled, 
        AccessFailedCount, 
        UserName

FROM 	AspNetUsers

WHERE	(Id = @userID)

We may want to edit only a select number of fields here, as playing around too much with this data can be problematic.

For now we will allow password changes to be completed by the in-built web form and will only therefore allow the user to edit the users email, phone number, or username.

UPDATE  AspNetUsers

SET	Email = @email, 
        PhoneNumber = @phoneNumber, 
        UserName = @username

WHERE	(Id = @id)

We will note be creating an INSERT method as we can instead make use of the packaged 'Register.aspx' web form - potentially making it restricted to administrators only to control who can become a user of the system.

Well done, you can now move on to creating the User Management Panels.