DAL class structure for storing and organizing SQL using Dapper?

Question

I'm creating a web application with Asp.Net Core MVC, and I've replaced Entity Framework with a custom data store. I've decided to use Dapper, but have never used it prior to this project. So I'm learning what I need to as I go. One of the examples I referenced to help replace EF with my own custom storage provider used a backing class for each table to organize and store routine queries for that particular table.

For example:

CREATE TABLE [dbo].[CustomUser](
    [Id] [uniqueidentifier] NOT NULL,
    [Email] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [UserName] [nvarchar](256) NOT NULL,
)
Go

Would have a backing class for dapper queries like so:

 public class DapperUsersTable
    {
        private readonly SqlConnection _connection;
        public DapperUsersTable(SqlConnection connection)
        {
            _connection = connection;
        }


        public async Task<IdentityResult> CreateAsync(ApplicationUser user)
        {
            string sql = "INSERT INTO dbo.CustomUser " +
                "VALUES (@id, @Email, @EmailConfirmed, @PasswordHash, @UserName)";

            int rows = await _connection.ExecuteAsync(sql, user);

            if(rows > 0)
            {
                return IdentityResult.Success;
            }
            return IdentityResult.Failed(new IdentityError { Description = $"Could not insert user {user.Email}." });
        }


        public async Task<IdentityResult> DeleteAsync(ApplicationUser user)
        {
            string sql = "DELETE FROM dbo.CustomUser WHERE Id = @Id";
            int rows = await _connection.ExecuteAsync(sql, new { user.Id });

            if(rows > 0)
            {
                return IdentityResult.Success;
            }
            return IdentityResult.Failed(new IdentityError { Description = $"Could not delete user {user.Email}." });



          //..more routine methods
        }

Normally, I'm used to using one DAL class for a smaller project and maybe a separate DAL for Identity queries. Or if I used areas (like in a ASp.NET MVC application), I would have a DAL for each area to help keep things more organized.

Right now I'm just working on queries for the Identity actions (Creating and managing a user, managing passwords, and 2FAuthentication), so I figured I'd try that approach too and created two separate backing classes. So far, for the most part it's mostly been standard CRUD actions for the user table and the tokens table for 2fA stuff.

Currently I'm working on modifying the email confirmation, and added some more tables to track and limit how many confirmation email requests by one user are sent out (before being confirmed to prevent any abuse). So I will need to use joins on those different tables as well as with the CustomUser table to verify the info. So for that I created another backing class DapperEmailTables, to handle queries that will interact with different tables needed to support email functionality.

Since I'm in the beginning of the design and wanted to get this squared away early on, I'm wondering if this is a good approach, or should I be creating more generalized classes to store the SQL that dapper will use.

Answer 1

Normally I would say your "Backing class" is a "Repository" and should follow that pattern. For which i would normally recommend one per database

UserDataRepository
{
    GetUserById(id)
    GetAllRoles()
    GetAddress(userid) //all "user" data goes in the user database not just User objects
    ...etc
}

However, here you are replacing a repository used by the Core Identity framework. You already have the various IUserXStore interfaces to implement, these are your Backing class/Repository classes and you dont need an additional layer just to wrap the dapper/sql commands.