Using Dapper.CX with Dependency Injection

This topic describes a couple ways to use Dapper.CX with dependency injection in ASP.NET Core. You can also use Dapper.CX as a set of IDbConnection extension methods.

In all use cases below, install NuGet package Dapper.CX.SqlServer.AspNetCore in your application project first.

The Simplest Case

During Startup you add the DapperCX service with a connection string and a delegate for converting SELECT SCOPE_IDENTITY() to your entity identity type. This example uses int Id types and a connection string retrieved from configuration:

public void ConfigureServices(IServiceCollection services)
{
    var connectionString = Configuration.GetConnectionString("Default");
    services.AddDapperCX(connectionString, (value) => Convert.ToInt32(value));
}

Now, in your pages or controllers, you'd inject it like this:

public class SampleController : Controller
{
    private readonly DapperCX<int> _data;

    public SampleController(DapperCX<int> data)
    {
        _data = data;
    }
}

In your Razor pages or Blazor components, you'd inject it like this:

@inject DapperCX<int> Data

Now you have access to all DapperCX methods but without the User access.

Integrating User Profile data with ISession

Most applications will have authentication and need to track crud operations by user. You can add your user profile model to DapperCX so that rows are updated with user and timestamps as needed. You can also validate tenant isolation and apply application-level permission logic. To use this, your user profile model class must implement IUserBase. (This is part of the AO.Models package, and is installed as a Dapper.CX dependency.)

When using ISession in your application, here's how to use it with Dapper.CX:

• implement IGetUser and add your implementation to your service collection like this. In this example GetUser is the implementation class, shown next.

services.AddScoped<IGetUser<User>>((sp) =>
{
	var http = sp.GetRequiredService<IHttpContextAccessor>();
	return new GetUser(http.HttpContext.Session, connectionString);
});

Here's a sample implementation of GetUser -- this is what I'm using at the time of this writing with a private repo.

public class GetUser : IGetUser<User>
{
	private readonly ISession _session;
	private readonly string _connectionString;

	private const string profile = "UserProfile";

	public GetUser(ISession session, string connectionString)
	{
		_session = session;
		_connectionString = connectionString;
	}

	public User Get(string userName)
	{
		string json;

		if (_session.TryGetValue(profile, out byte[] data))
		{
			json = Encoding.UTF8.GetString(data);                
		}
		else
		{
			User user;
			using (var cn = new SqlConnection(_connectionString))
			{
				user = cn.GetWhere<User>(new { emailAddress = userName });
				//todo: create user if not found
			}

			json = JsonSerializer.Serialize(user);
			_session.Set(profile, Encoding.UTF8.GetBytes(json));
		}

		return JsonSerializer.Deserialize<User>(json);
	}
}

• use this overload when adding DapperCX to your service collection, like this. In this example User is the model class type with user profile data.

services.AddDapperCX<int, User>(connectionString, (value) => Convert.ToInt32(value));

In this approach, you get efficient user profile access throughout your application wherever you inject the DapperCX service, and all your crud operations are tracked by user.

Integration with Claims

Most of the work setting up Dapper.CX for use with dependency injection involves creating components that allow ASP.NET Identity to integrate with your user profile model class in a scalable way. Just about any application you create will require authentication, and you'll have a model class in your application that represents users. What you want to avoid, however, is querying the user profile model from your database with every page view. A more scalable solution is to query user properties once during login, and to capture the profile data as a set of Claims. In turn, Dapper.CX converts these Claims into your user profile model so you have strong-typed access to user profile data at all times. The procedure outlined below describes how to enable your user profile model properties to get this working. Examples below come from the SampleApp in this repo:

1. Add a model class to your project that implements IUserBase. Example: UserProfile.

2. Add NuGet package Dapper.CX.SqlServer.AspNetCore to your project.

3. Add a service to your project that derives from abstract class DbUserClaimsConverter<TUser>. This used to convert your user profile model class to a set of claims that map onto it. Example: UserProfileClaimsConverter

4. Add a service to your project that derives from DbUserClaimsFactory<TUser>. Example: UserProfileClaimsFactory. This is used when adding identity during your application startup. This uses the DbUserclaimsFactory<TUser> class you created in step 3, and allows Identity to generate your desired claims when users log in. See example:

services
    .AddDefaultIdentity<IdentityUser>(options => options.SignIn.RequireConfirmedAccount = true)
    .AddEntityFrameworkStores<ApplicationDbContext>()
    .AddClaimsPrincipalFactory<UserProfileClaimsFactory>();

5. Use the AddDapperCX extension method in your application startup, like this example:

services.AddDapperCX(
    connectionString, 
    (id) => Convert.ToInt32(id), 
    () => new UserProfileClaimsConverter(connectionString));

The AddDapperCX extension method accepts a database connection string, a delegate that returns your identity column type of choice (-- currently int and long are supported), and lastly a factory that instantiates your DbUserClaimsConverter<TUser> that you created in step 3 above.

Now, you can inject the Dapper.CX SqlServerCrudService<TIdentity, TUser> into your pages or components as per this example.

public class BasePageModel : PageModel
{
    public BasePageModel(DapperCX<int, UserProfile> data)
    {
        Data = data;
    }

    public DapperCX<int, UserProfile> Data { get; }
}

Here, I use a read-only property Data as the accessor for all CRUD operations. The DapperCX class derives from SqlCrudService. CRUD method reference is here.

Using ISession and IGetUser<TUser>

Extension Methods

There are several extension methods defined here:

• Task<RedirectResult> SaveAndRedirectAsync (this DapperCX<TIdentity, TUser> crudService, TModel model, Func<TModel, Exception, RedirectResult> redirect, [ ChangeTracker changeTracker ], [ Action beforeSave ], [ Func<TModel, Task> onSuccess ], [ Func<TModel, Exception, Task> onException ])
• Task<RedirectResult> DeleteAndRedirectAsync (this DapperCX<TIdentity, TUser> crudService, TIdentity id, Func<TModel, Exception, RedirectResult> redirect, [ Func<TModel, Task> onSuccess ], [ Func<TModel, Exception, Task> onException ])
• Task<RedirectResult> DeleteAndRedirectAsync (this DapperCX<TIdentity, TUser> crudService, TIdentity id, string redirect)
• Task<IEnumerable<TResult>> QueryAsync (this DapperCX<TIdentity, TUser> crudService, Query query)
• Task<TResult> QuerySingleAsync (this DapperCX<TIdentity, TUser> crudService, Query query)
• Task<TResult> QuerySingleOrDefaultAsync (this DapperCX<TIdentity, TUser> crudService, Query query)
• Task<SelectList> QuerySelectListAsync (this DapperCX<TIdentity, TUser> crudService, Query query, [ object selectedValue ])
• Task<SelectList> QuerySelectListAsync (this DapperCX<TIdentity, TUser> crudService, SelectListQuery query, [ object selectedValue ])

More Examples

• Items page backend, Razor