自定义ASP.NET Identity（一）- 自定义ASP.NET Identity存储提供程序

ASP.NET Identity是一个可扩展的系统，是你可以创建自己的存储提供程序并且可以将它集成到你的应用中，而不需要重新开发应用。这个主题将说明如何创建一个自定义的ASP.NET Identity存储提供程序。它将涵盖创建你自己的存储提供程序的重要概念，但是本章不会一步一步的实现自定义存储提供程序。

介绍

默认情况下ASP.NET Identity系统在SQL Server数据库中存储用户信息,并且使用EntityFramework 代码优先(Code First)创建数据库。对于多数应用，这种做法的效果很好。然而，你可能更愿意使用不同类型的持久化机制，例如云存储或者你已经有了一个与默认实现不同结构的数据库。在任何一种情况下，你都可以为你的存储机制写一个自定义的提供程序，并且将该提供程序集成到你的应用中。

ASP.NET Identity已经默认被包含到了许多Visual Studio 2013的模板中。你可以通过Microsoft AspNet Identity EntityFramework NuGet 包更新ASP.NET Identity。

这个主题包括以下章节：

理解架构
理解存储的数据
创建数据访问层
自定义用户类
自定义用户存储
自动以角色类
自定义角色存储
重新配置应用使用的新的存储提供程序
其它自定义存储提供程序实现

理解架构

ASP.NET Identity由一些管理器类和存储类组成。管理器类是高级别类，应用开发人员使用它类执行操作，例如在ASP.NET Identity系统中创建用户。存储类是指定实体的低级别类，如用户和角色。在持久化机制中存储具有耦合性，但是，管理器与存储是解耦的，这意味着你可以替换持久机制而不会破坏应用的整体结构。

下图显示了Web应用程序如何与管理器之间进行相互作用，存储与数据访问层如何进行交互。

创建一个ASP.NET Identity的自定义存储提供程序，你必须创建数据源，数据访问层，和与数据访问层交互的存储类。你可以继续使用相同的管理器APIs来执行数据操作来管理用户，但是现在数据被存储到了不同的存储系统。

你不需要自定义管理器类，因为当创意一个UserManager或者RoleManager的新的实例的时候，用户类的类型是通过用户存储类的实例以参数的形式提供的。这种方式可以是你将自定义的类集成到现有的程序结构中。在本章节中你将看到怎样使用自定义存储类实例UserManager和RoleManager对象使用新的存储提供程序重新配置应用。

理解存储的数据

要实现自定义存储提供程序,你不许理解ASP.NET Identity中使用的数据类型.并且确定哪些特性同你的应用是有关的。

数据	描述
Users	Web站点的注册用户。包括用户编号和用户名称。如果用户使用凭据登录到你的站点，可能包含密码（而不是像Facebook那样使用外部站点的认证凭据），还有安全戳，表明用户凭据是否发生改变。也可能包含电子邮箱地址，电话号码，双因素身份验证是否启用，当前登录失败的次数和账号是否被所定等。
User Claims	一组表明用户身份的描述，可以比使用角色更好的表述用户的身份。
User Logins	用户登录时使用的外部身份认证提供程序的信息。
Roles	你的站点中的身份认证组。包含角色编号和角色名称（如：“Admin”或者“Employee”)

创建数据访问层

本主题架设你已经熟悉持久化机制,并且了解如何使用和创建实体。本主题不讨论怎样创建资源库或者数据访问类。相反，本文会提供一些在使用ASP.NET Identity时需要做的一些设计决策的建议。

在你设计自定义存储提供程序库时有很多自由。你只需要创建一些在你的应用中使用的功能库。例如，在你的应用中没有使用角色，你就不需要闯将规则存储或者用户规则。你要使用的结构可能同ASP.NET Identity提供的默认实现有很大的不同，你可能在数据访问层，你的库结构提供工作逻辑。

ASP.NET Identity 2.0的MySQL数据库实现，请参看如下MySQLIdentity.sql

CREATE TABLE `roles` (
  `Id` varchar(128) NOT NULL,
  `Name` varchar(256) NOT NULL,
  PRIMARY KEY (`Id`)
);
CREATE TABLE `users` (
  `Id` varchar(128) NOT NULL,
  `Email` varchar(256) DEFAULT NULL,
  `EmailConfirmed` tinyint(1) NOT NULL,
  `PasswordHash` longtext,
  `SecurityStamp` longtext,
  `PhoneNumber` longtext,
  `PhoneNumberConfirmed` tinyint(1) NOT NULL,
  `TwoFactorEnabled` tinyint(1) NOT NULL,
  `LockoutEndDateUtc` datetime DEFAULT NULL,
  `LockoutEnabled` tinyint(1) NOT NULL,
  `AccessFailedCount` int(11) NOT NULL,
  `UserName` varchar(256) NOT NULL,
  PRIMARY KEY (`Id`)
);
CREATE TABLE `userclaims` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserId` varchar(128) NOT NULL,
  `ClaimType` longtext,
  `ClaimValue` longtext,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`),
  KEY `UserId` (`UserId`),
  CONSTRAINT `ApplicationUser_Claims` FOREIGN KEY (`UserId`) REFERENCES `users` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE TABLE `userlogins` (
  `LoginProvider` varchar(128) NOT NULL,
  `ProviderKey` varchar(128) NOT NULL,
  `UserId` varchar(128) NOT NULL,
  PRIMARY KEY (`LoginProvider`,`ProviderKey`,`UserId`),
  KEY `ApplicationUser_Logins` (`UserId`),
  CONSTRAINT `ApplicationUser_Logins` FOREIGN KEY (`UserId`) REFERENCES `users` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE TABLE `userroles` (
  `UserId` varchar(128) NOT NULL,
  `RoleId` varchar(128) NOT NULL,
  PRIMARY KEY (`UserId`,`RoleId`),
  KEY `IdentityRole_Users` (`RoleId`),
  CONSTRAINT `ApplicationUser_Roles` FOREIGN KEY (`UserId`) REFERENCES `users` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `IdentityRole_Users` FOREIGN KEY (`RoleId`) REFERENCES `roles` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ;

在数据访问层,你同工保存ASP.NET Identity信息到你的数据源的逻辑.数据访问层的自定义存储提供程序可以包含下列类来存储用户和角色信息。

类	描述	示例
Context	封装可以连接到你的持久化机制和执行查询的信息。这个类是数据访问层的中心。其它数据类需要这个类的实例来执行操作。你也可以在这个类的实例中初始化你的存储类。	MySQLDatabase
User Storage	存储和检索用户信息（如用户名和哈希密码）。	UserTable (MySQL)
Role Storage	存储和检索角色信息（如角色名）。	RoleTable (MySQL)
UserClaims Storage	存储和检索用户Claim信息（如Claim类型和值）。	UserClaimsTable (MySQL)
UserLogins Storage	存储和检索用户登录信息（如外部身份认证提供程序）。	UserLoginsTable (MySQL)
UserRole Storage	存储和检索与用户关联的角色。	UserRoleTable (MySQL)

**重申一遍，你只需要实现你打算在你的应用中使用的类。**

在数据访问类中，你提供的代码为您的特定的持久化机制进行数据操作。例如，在MySQL实现中，UserTable类包含一个方法，向Users数据库表插入一条新的记录。变量 _database是一个MySQLDatabase类的实例。

public int Insert(TUser user)
{
    string commandText = @"Insert into Users (UserName, Id, PasswordHash, SecurityStamp,Email,EmailConfirmed,PhoneNumber,PhoneNumberConfirmed, AccessFailedCount,LockoutEnabled,LockoutEndDateUtc,TwoFactorEnabled)
        values (@name, @id, @pwdHash, @SecStamp,@email,@emailconfirmed,@phonenumber,@phonenumberconfirmed,@accesscount,@lockoutenabled,@lockoutenddate,@twofactorenabled)";
    Dictionary<string, object> parameters = new Dictionary<string, object>();
    parameters.Add("@name", user.UserName);
    parameters.Add("@id", user.Id);
    parameters.Add("@pwdHash", user.PasswordHash);
    parameters.Add("@SecStamp", user.SecurityStamp);
    parameters.Add("@email", user.Email);
    parameters.Add("@emailconfirmed", user.EmailConfirmed);
    parameters.Add("@phonenumber", user.PhoneNumber);
    parameters.Add("@phonenumberconfirmed", user.PhoneNumberConfirmed);
    parameters.Add("@accesscount", user.AccessFailedCount);
    parameters.Add("@lockoutenabled", user.LockoutEnabled);
    parameters.Add("@lockoutenddate", user.LockoutEndDateUtc);
    parameters.Add("@twofactorenabled", user.TwoFactorEnabled);
    return _database.Execute(commandText, parameters);
}

创建数据访问类之后，你必须创建存储类，供数据访问层中的指定方法调用。

自定义用户类

当你实现自己的存储提供程序时，你必须创建一个与Microsoft.ASP.NET.Identity.EntityFramework命名空间中IdentityUser类等效的用户类。

下面类图中展示了你必须创建的IdentityUser类及其实现的接口。

IUser<TKey>接口定义了UserManager在执行请求操作中需要调用的属性。这个接口包含2个属性 - Id和UserName。 IUser<TKey> 允许你通过泛型参数TKey指定用户的主键类型，Id属性的类型将匹配到TKey参数。

Identity framework还提供了一个IUser接口（没有泛型参数），当你想使用一个字符串作为主键类型时可以使用它。

你为站点中的用户创建的IdentityUser类实现了IUser并且包含一些附加属性或者构造器。下面的例子展示了一个IdentityUser类使用整型作为主键。Id字段被设置到与泛型参数相匹配的int类型。

public class IdentityUser : IUser<int>
{
    public IdentityUser() { ... }
    public IdentityUser(string userName) { ... }
    public int Id { get; set; }
    public string UserName { get; set; }
    // can also define optional properties such as:
    //    PasswordHash
    //    SecurityStamp
    //    Claims
    //    Logins
    //    Roles
}

完整的实现请查看IdentityUser (MySQL)。

自定义用户存储

你可以创建一个UserStore类提供所有有关用户数据操作的方法。这个类与Microsoft.ASP.NET.Identity.EntityFramework命名空间中的UserStore<TUser>类是等效的。在你的用户存储类中，你需要实现IUserStore<TUser, TKey>和任何其他可选接口。你选择哪一个可选接口来实现取决于你的应用中希望提供哪些功能。

下面的图片显示了UserStore类，你必须创建并且实现相关的接口。

Visual Studio中默认的工程模板中包含的代码假设许多可选接口已经在用户存储中被实现。如果你使用默认模板自定义用户存储，你必须在你的用户存储中实现可选接口或者改变模板代码不在调用你没有实现的接口方法。

下面的例子展示了一个简单的用户存储类。TUser泛型参数提供了你的用户类的类型，通常是你定义的IdentityUser类。Tkey泛型参数提供了你的用户主键类型。

public class UserStore : IUserStore<IdentityUser, int>
{
    public UserStore() { ... }
    public UserStore(ExampleStorage database) { ... }
    public Task CreateAsync(IdentityUser user) { ... }
    public Task DeleteAsync(IdentityUser user) { ... }
    public Task<IdentityUser> FindByIdAsync(int userId) { ... }
    public Task<IdentityUser> FindByNameAsync(string userName) { ... }
    public Task UpdateAsync(IdentityUser user) { ... }
    public void Dispose() { ... }
}

在这个例子中，构造器指定了一个被命名为database的ExampleDatabase类型的参数，展示如何传递数据访问类。例如，在MySQL的实现中，这个构造器指定了一个MySQLDatabase类型的参数。

在你的UserStore类内部，你可以执行数据访问类中创建的操作。例如，在MySQL的实现中，UserStore类有一个CreateAsync方法使用UserTable实例插入一条新纪录。userTable对象的Insert方法是已经在前面章节的代码中展示过了。

public Task CreateAsync(IdentityUser user)
{
    if (user == null) {
        throw new ArgumentNullException("user");
    }
    userTable.Insert(user);
    return Task.FromResult<object>(null);
}

自定义用户存储的接口实现

下面的图片展示了每一个接口功能定义的更多细节。所有的可选接口都继承自IUserStore

IUserStore
IUserStore<TUser, TKey> 是唯一一个在你自己的用户存储中必须要实现的接口。它定义了创建，更新，删除和检索用户的方法。
IUserClaimStore
IUserClaimStore<TUser, TKey> 如果你的用户存储启用了用户Claims，你就必须实现这个接口定义的方法。它包含添加，移除和检索用户Claims的方法。
IUserLoginStore
IUserLoginStore<TUser, TKey> 如果你的用户存储启用了外部身份验证提供程序，你就必须实现这个接口定义的方法。它包含添加，移除和检索用户登录和基于登录信息的检索一个用户的方法。
IUserRoleStore
IUserRoleStore<TKey, TUser> 如果在你的用户存储中映射了用户和角色的关系，你就必须实现这个接口定义的方法。它包含了添加，移除和检索一个用户规则的方法和检测一个用户是否关联到一个角色的方法。
IUserPasswordStore
IUserPasswordStore<TUser, TKey> 如果在你的用户存储中使用了哈希密码，你就必须实现这个接口定义的方法。它包含获取和设置哈希密码的方法和判断用户是否设置了一个密码。
IUserSecurityStampStore
IUserSecurityStampStore<TUser, TKey> 如果你的用户存储中使用了安全戳来判断用户账号信息是否改变，你就必须实现这个接口定义的方法。当用户改变密码，添加或者移出登录是这个安全戳将会被更新。它包含安全戳的获取和设置方法。
IUserTwoFactorStore
IUserTwoFactorStore<TUser, TKey> 双因素认证必须实现这个接口定义的方法。它包含获取或设置一个用的是否启用双因素认证的方法。
IUserPhoneNumberStore
IUserPhoneNumberStore<TUser, TKey> 存储用户电话号码必须实现这个接口定义的方法。它包含获取和设置电话号码和电话号码是否被确认的方法。
IUserEmailStore
IUserEmailStore<TUser, TKey> 存储用户电子邮箱地址必须实现这个接口定义的方法。它包含获取和设置电子邮箱地址和邮箱地址是否被确认。
IUserLockoutStore
The IUserLockoutStore<TUser, TKey> 存储账号锁定信息必须实现这个接口定义的方法。它包含获取当前尝试访问失败的次数，获取和设置账号是否可以锁定，获取和设置锁定结束日期，尝试失败增量次数和重置尝试失败次数的方法。
IQueryableUserStore
The IQueryableUserStore<TUser, TKey> 提供一个可查询的用户存储必须实现这个接口定义的方法。它包含一个属性，拥有可查询的用户。

你实现你的应用需要的接口；如，IUserClaimStore,IUserLoginStore,IUserRoleStore,IUserPasswordStore和IUserSecurityStampStore接口显示如下。

public class UserStore : IUserStore<IdentityUser, int>,
                         IUserClaimStore<IdentityUser, int>,
                         IUserLoginStore<IdentityUser, int>,
                         IUserRoleStore<IdentityUser, int>,
                         IUserPasswordStore<IdentityUser, int>,
                         IUserSecurityStampStore<IdentityUser, int>
{
    // interface implementations not shown
}

完整的实现请查看 UserStore (MySQL)。

IdentityUserClaim, IdentityUserLogin 和 IdentityUserRole

Microsoft.AspNet.Identity.EntityFramework命名空间包含IdentityUserClaim, IdentityUserLogin和 IdentityUserRole接口的实现类。如果你正在使用这些功能，你可能希望创建这些类自己的版本并且为你的应用定义属性。然而，有时候执行一些基本操作（如天剑或者移出用户的Claim）的更有效的不是加载这些实体到内存中。想法，后台存储类能直接在数据源上执行这些操作。例如，UserStore.GetClaimsAsync()方法可以调用userClaimTable.FindByUserId(user.Id)方法在表上直接执行一个查询并且返回一个Claims的列表。

public Task<IList<Claim>> GetClaimsAsync(IdentityUser user)
{
    ClaimsIdentity identity = userClaimsTable.FindByUserId(user.Id);
    return Task.FromResult<IList<Claim>>(identity.Claims.ToList());
}

自定义角色类

当你实现自己的存储提供程序的时候，必须创建一个与Microsoft.ASP.NET.Identity.EntityFramework命名空间中IdentityRole类等效的角色类。

下面的类图展示了你必须创建的IdentityRole 类和其所要实现的接口。

IRole<TKey> 接口定义RoleManager执行请求操作时尝试调用的属性。这个接口包含两个属性 - Id和Name。IRole<TKey> 接口通过反向参数 TKey 确定接口的主键类型。属性Id的类型与TKey参数的类型相匹配。

Identity framework还提供了IRole接口（没有返现参数），当你希望使用字符串作为主键类型时可以使用这个接口。

下面的例子展示了一个IdentityRole类使用整型作为主键。Id字段被设置为与泛型参数相匹配的值。

public class IdentityRole : IRole<int>
{
    public IdentityRole() { ... }
    public IdentityRole(string roleName) { ... }
    public int Id { get; set; }
    public string Name { get; set; }
}

完整了实现请参看IdentityRole (MySQL).

自定义角色存储

你还需要创建RoleStore类提供角色数据操作的方法.这个类是与Microsoft.ASP.NET.Identity.EntityFramework命名空间中的RoleStore<TRole>类等效的。在你的RoleStore类中，你实现IRoleStore<TRole, TKey>和可选的IQueryableRoleStore<TRole, TKey>接口。

下面的例子展示了一个角色存储类。TRole泛型参数确定了你的角色类的类型，通常情况下是你定义的IdentityRole类。Tkey泛型参数确定了你的角色类的主键类型。

public class RoleStore : IRoleStore<IdentityRole, int>
{
    public RoleStore() { ... }
    public RoleStore(ExampleStorage database) { ... }
    public Task CreateAsync(IdentityRole role) { ... }
    public Task DeleteAsync(IdentityRole role) { ... }
    public Task<IdentityRole> FindByIdAsync(int roleId) { ... }
    public Task<IdentityRole> FindByNameAsync(string roleName) { ... }
    public Task UpdateAsync(IdentityRole role) { ... }
    public void Dispose() { ... }
}

IRoleStore<TRole>
IRoleStore 定义了你的角色存储类需要实现的方法。它包含了创建，更新，删除和检索角色的方法。

RoleStore<TRole>
自定义RoleStore，创建一个实现IRoleStore接口的类。如果你想在你的系统中使用角色你只需要实现这个类。构造器指定了一个被命名为database的ExampleDatabase类型的参数，展示如何传递数据访问类。例如，在MySQL 的实现中, 构造器指定了一个MySQLDatabase类型的参数。

完整了实现请参看RoleStore (MySQL).

使用新的存储提供程序重新配置应用

你已经实现了你自己的新的存储提供程序。现在，你必须配置你的应用来使用这个存储提供程序。如果默认的存储提供程序已经被包含在你的工程中，你必须先移出这个默认的提供程序并且用你自己的提供程序替换它。

在MVC工程中替换默认存储提供程序

1、在Manage NuGet Packages窗口中卸载Microsoft ASP.NET Identity EntityFramework包。你可以在已安装包中通过查找Identity.EntityFramework找到这个包

你将被询问是否卸载。如果在你的应用中的其它部分不需要它，你可以卸载它。

2、在Models文件夹的IdentityModels.cs文件中删除或者注释ApplicationUser和ApplicationDbContext类。在MVC应用中，你可以删除整个IdentityModels.cs文件。在Web Forms应用中，删除这两个类但是要确认保留帮助类，帮助类的位置也在IdentityModels.cs文件中。

3、如果你的存储提供程序在一个独立的工程中，你需要将添加它的引用到你的Web应用中。

4、用你自己的存储提供程序的命名空间替换所有引用到 using Microsoft.AspNet.Identity.EntityFramework;的命名空间。

5、打开Startup.Auth.cs文件，使用适当的上下文实例修改ConfigureAuth方法。

public void ConfigureAuth(IAppBuilder app)
{
    app.CreatePerOwinContext(ExampleStorageContext.Create);
    app.CreatePerOwinContext(ApplicationUserManager.Create);
    ...

6、在App_Start文件夹中IdentityConfig.cs打开文件。在ApplicationUserManager类中，修改Create方法返回使用你的用户存储初始化的用户管理器。

public static ApplicationUserManager Create(IdentityFactoryOptions<ApplicationUserManager> options, IOwinContext context) 
{
    var manager =

new ApplicationUserManager(new UserStore(context.Get<ExampleStorageContext>

()));
    ...
}

7、使用IdentityUser替换所有ApplicationUser的引用。

8、默认工程中用户类包含了一些在IUser接口中没有定义的成员；例如电子邮箱，哈希密码和GenerateUserIdentityAsync。如果你的用户类中没有这些成员，你必须实现它们，或者修改使用这些成员的代码。

9、如果你创建了任何RoleManager的实例，你需要修改代码来使用你的新的RoleStore类。

var roleManager = new RoleManager<IdentityRole>(new RoleStore(context.Get<ExampleStorageContext>()));

10、默认工程的用户类被设计为使用字符串作为用户的主键。如果你的用户类使用了不同的主键（如整型），你必须修改工程中的用户主键。可以参看在ASP.NET Identity中修改用户主键

11、如果需要，可以添加连接字符串到Web.config文件。

相关代码

MySQLDatabase

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Threading;
namespace AspNet.Identity.MySQL
{
     /// <summary>
    /// Class that encapsulates a MySQL database connections 
     /// and CRUD operations
     /// </summary>
    public class MySQLDatabase : IDisposable
    {
        private MySqlConnection _connection;
        /// Default constructor which uses the "DefaultConnection" connectionString
        /// </summary>
    public MySQLDatabase()
            : this("DefaultConnection")
        {
        }
        /// <summary>
    /// Constructor which takes the connection string name
        /// </summary>
    /// <param name="connectionStringName"></param>
    public MySQLDatabase(string connectionStringName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            _connection = new MySqlConnection(connectionString);
        }
        /// <summary>
    /// Executes a non-query MySQL statement
        /// </summary>
    /// <param name="commandText">The MySQL query to execute</param>
    /// <param name="parameters">Optional parameters to pass to the query</param>
    /// <returns>The count of records affected by the MySQL statement</returns>
    public int Execute(string commandText, Dictionary<string, object> parameters)
        {
            int result = 0;
            if (String.IsNullOrEmpty(commandText))
            {
                throw new ArgumentException("Command text cannot be null or empty.");
            }
            try
            {
                EnsureConnectionOpen();
                var command = CreateCommand(commandText, parameters);
                result = command.ExecuteNonQuery();
            }
            finally
            {
                _connection.Close();
            }
            return result;
        }
        /// <summary>
    /// Executes a MySQL query that returns a single scalar value as the result.
        /// </summary>
    /// <param name="commandText">The MySQL query to execute</param>
    /// <param name="parameters">Optional parameters to pass to the query</param>
    /// <returns></returns>
    public object QueryValue(string commandText, Dictionary<string, object> parameters)
        {
            object result = null;
            if (String.IsNullOrEmpty(commandText))
            {
                throw new ArgumentException("Command text cannot be null or empty.");
            }
            try
            {
                EnsureConnectionOpen();
                var command = CreateCommand(commandText, parameters);
                result = command.ExecuteScalar();
            }
            finally
            {
                EnsureConnectionClosed();
            }
            return result;
        }
        /// <summary>
    /// Executes a SQL query that returns a list of rows as the result.
        /// </summary>
    /// <param name="commandText">The MySQL query to execute</param>
    /// <param name="parameters">Parameters to pass to the MySQL query</param>
    /// <returns>A list of a Dictionary of Key, values pairs representing the 
        /// ColumnName and corresponding value</returns>
    public List<Dictionary<string, string>> Query(string commandText, Dictionary<string, object> parameters)
        {
            List<Dictionary<string, string>> rows = null;
            if (String.IsNullOrEmpty(commandText))
            {
                throw new ArgumentException("Command text cannot be null or empty.");
            }
            try
            {
                EnsureConnectionOpen();
                var command = CreateCommand(commandText, parameters);
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    rows = new List<Dictionary<string, string>>();
                    while (reader.Read())
                    {
                        var row = new Dictionary<string, string>();
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var columnName = reader.GetName(i);
                            var columnValue = reader.IsDBNull(i) ? null : reader.GetString(i);
                            row.Add(columnName, columnValue);
                        }
                        rows.Add(row);
                    }
                }
            }
            finally
            {
                EnsureConnectionClosed();
            }
            return rows;
        }
        /// <summary>
    /// Opens a connection if not open
        /// </summary>
    private void EnsureConnectionOpen()
        {
            var retries = 3;
            if (_connection.State == ConnectionState.Open)
            {
                return;
            }
            else
            {
                while (retries >= 0 && _connection.State != ConnectionState.Open)
                {
                    _connection.Open();
                    retries--;
                    Thread.Sleep(30);
                }
            }
        }
        /// <summary>
    /// Closes a connection if open
        /// </summary>
    public void EnsureConnectionClosed()
        {
            if (_connection.State == ConnectionState.Open)
            {
                _connection.Close();
            }
        }
        /// <summary>
    /// Creates a MySQLCommand with the given parameters
        /// </summary>
    /// <param name="commandText">The MySQL query to execute</param>
    /// <param name="parameters">Parameters to pass to the MySQL query</param>
    /// <returns></returns>
    private MySqlCommand CreateCommand(string commandText, Dictionary<string, object> parameters)
        {
            MySqlCommand command = _connection.CreateCommand();
            command.CommandText = commandText;
            AddParameters(command, parameters);
            return command;
        }
        /// <summary>
    /// Adds the parameters to a MySQL command
        /// </summary>
    /// <param name="commandText">The MySQL query to execute</param>
    /// <param name="parameters">Parameters to pass to the MySQL query</param>
    private static void AddParameters(MySqlCommand command, Dictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                return;
            }
            foreach (var param in parameters)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = param.Key;
                parameter.Value = param.Value ?? DBNull.Value;
                command.Parameters.Add(parameter);
            }
        }
        /// <summary>
    /// Helper method to return query a string value 
        /// </summary>
    /// <param name="commandText">The MySQL query to execute</param>
    /// <param name="parameters">Parameters to pass to the MySQL query</param>
    /// <returns>The string value resulting from the query</returns>
    public string GetStrValue(string commandText, Dictionary<string, object> parameters)
        {
            string value = QueryValue(commandText, parameters) as string;
            return value;
        }
        public void Dispose()
        {
            if (_connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }
    }
}

UserTable (MySQL)

using System;
using System.Collections.Generic;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that represents the Users table in the MySQL Database
    /// </summary>
    public class UserTable<TUser>
    where TUser :IdentityUser
    {
        private MySQLDatabase _database;
        /// <summary>
    /// Constructor that takes a MySQLDatabase instance 
        /// </summary>
    /// <param name="database"></param>
    public UserTable(MySQLDatabase database)
        {
            _database = database;
        }
        /// <summary>
    /// Returns the user's name given a user id
        /// </summary>
    /// <param name="userId"></param>
    /// <returns></returns>
    public string GetUserName(string userId)
        {
            string commandText = "Select Name from Users where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@id", userId } };
            return _database.GetStrValue(commandText, parameters);
        }
        /// <summary>
    /// Returns a User ID given a user name
        /// </summary>
    /// <param name="userName">The user's name</param>
    /// <returns></returns>
    public string GetUserId(string userName)
        {
            string commandText = "Select Id from Users where UserName = @name";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@name", userName } };
            return _database.GetStrValue(commandText, parameters);
        }
        /// <summary>
    /// Returns an TUser given the user's id
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public TUser GetUserById(string userId)
        {
            TUser user = null;
            string commandText = "Select * from Users where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@id", userId } };
            var rows = _database.Query(commandText, parameters);
            if (rows != null && rows.Count == 1)
            {
                var row = rows[0];
                user = (TUser)Activator.CreateInstance(typeof(TUser));
                user.Id = row["Id"];
                user.UserName = row["UserName"];
                user.PasswordHash = string.IsNullOrEmpty(row["PasswordHash"]) ? null : row["PasswordHash"];
                user.SecurityStamp = string.IsNullOrEmpty(row["SecurityStamp"]) ? null : row["SecurityStamp"];
                user.Email = string.IsNullOrEmpty(row["Email"]) ? null : row["Email"];
                user.EmailConfirmed = row["EmailConfirmed"] == "1" ? true:false;
                user.PhoneNumber = string.IsNullOrEmpty(row["PhoneNumber"]) ? null : row["PhoneNumber"];
                user.PhoneNumberConfirmed = row["PhoneNumberConfirmed"] == "1" ? true : false;
                user.LockoutEnabled = row["LockoutEnabled"] == "1" ? true : false;
                user.LockoutEndDateUtc = string.IsNullOrEmpty(row["LockoutEndDateUtc"]) ? DateTime.Now : DateTime.Parse(row["LockoutEndDateUtc"]);
                user.AccessFailedCount = string.IsNullOrEmpty(row["AccessFailedCount"]) ? 0 : int.Parse(row["AccessFailedCount"]);
            }
            return user;
        }
        /// <summary>
    /// Returns a list of TUser instances given a user name
        /// </summary>
    /// <param name="userName">User's name</param>
    /// <returns></returns>
    public List<TUser> GetUserByName(string userName)
        {
            List<TUser> users = new List<TUser>();
            string commandText = "Select * from Users where UserName = @name";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@name", userName } };
            var rows = _database.Query(commandText, parameters);
            foreach(var row in rows)
            {
                TUser user = (TUser)Activator.CreateInstance(typeof(TUser));
                user.Id = row["Id"];
                user.UserName = row["UserName"];
                user.PasswordHash = string.IsNullOrEmpty(row["PasswordHash"]) ? null : row["PasswordHash"];
                user.SecurityStamp = string.IsNullOrEmpty(row["SecurityStamp"]) ? null : row["SecurityStamp"];
                user.Email = string.IsNullOrEmpty(row["Email"]) ? null : row["Email"];
                user.EmailConfirmed = row["EmailConfirmed"] == "1" ? true : false;
                user.PhoneNumber = string.IsNullOrEmpty(row["PhoneNumber"]) ? null : row["PhoneNumber"];
                user.PhoneNumberConfirmed = row["PhoneNumberConfirmed"] == "1" ? true : false;
                user.LockoutEnabled = row["LockoutEnabled"] == "1" ? true : false;
                user.TwoFactorEnabled = row["TwoFactorEnabled"] == "1" ? true : false;
                user.LockoutEndDateUtc = string.IsNullOrEmpty(row["LockoutEndDateUtc"]) ? DateTime.Now : DateTime.Parse(row["LockoutEndDateUtc"]);
                user.AccessFailedCount = string.IsNullOrEmpty(row["AccessFailedCount"]) ? 0 : int.Parse(row["AccessFailedCount"]);
                users.Add(user);
            }
            return users;
        }
        public List<TUser> GetUserByEmail(string email)
        {
            return null;
        }
        /// <summary>
    /// Return the user's password hash
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public string GetPasswordHash(string userId)
        {
            string commandText = "Select PasswordHash from Users where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@id", userId);
            var passHash = _database.GetStrValue(commandText, parameters);
            if(string.IsNullOrEmpty(passHash))
            {
                return null;
            }
            return passHash;
        }
        /// <summary>
    /// Sets the user's password hash
        /// </summary>
    /// <param name="userId"></param>
    /// <param name="passwordHash"></param>
    /// <returns></returns>
    public int SetPasswordHash(string userId, string passwordHash)
        {
            string commandText = "Update Users set PasswordHash = @pwdHash where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@pwdHash", passwordHash);
            parameters.Add("@id", userId);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Returns the user's security stamp
        /// </summary>
    /// <param name="userId"></param>
    /// <returns></returns>
    public string GetSecurityStamp(string userId)
        {
            string commandText = "Select SecurityStamp from Users where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@id", userId } };
            var result = _database.GetStrValue(commandText, parameters);
            return result;
        }
        /// <summary>
    /// Inserts a new user in the Users table
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public int Insert(TUser user)
        {
            string commandText = @"Insert into Users (UserName, Id, PasswordHash, SecurityStamp,Email,EmailConfirmed,PhoneNumber,PhoneNumberConfirmed, AccessFailedCount,LockoutEnabled,LockoutEndDateUtc,TwoFactorEnabled)
                values (@name, @id, @pwdHash, @SecStamp,@email,@emailconfirmed,@phonenumber,@phonenumberconfirmed,@accesscount,@lockoutenabled,@lockoutenddate,@twofactorenabled)";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@name", user.UserName);
            parameters.Add("@id", user.Id);
            parameters.Add("@pwdHash", user.PasswordHash);
            parameters.Add("@SecStamp", user.SecurityStamp);
            parameters.Add("@email", user.Email);
            parameters.Add("@emailconfirmed", user.EmailConfirmed);
            parameters.Add("@phonenumber", user.PhoneNumber);
            parameters.Add("@phonenumberconfirmed", user.PhoneNumberConfirmed);
            parameters.Add("@accesscount", user.AccessFailedCount);
            parameters.Add("@lockoutenabled", user.LockoutEnabled);
            parameters.Add("@lockoutenddate", user.LockoutEndDateUtc);
            parameters.Add("@twofactorenabled", user.TwoFactorEnabled);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Deletes a user from the Users table
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    private int Delete(string userId)
        {
            string commandText = "Delete from Users where Id = @userId";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@userId", userId);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Deletes a user from the Users table
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public int Delete(TUser user)
        {
            return Delete(user.Id);
        }
        /// <summary>
    /// Updates a user in the Users table
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public int Update(TUser user)
        {
            string commandText = @"Update Users set UserName = @userName, PasswordHash = @pswHash, SecurityStamp = @secStamp, 
                Email=@email, EmailConfirmed=@emailconfirmed, PhoneNumber=@phonenumber, PhoneNumberConfirmed=@phonenumberconfirmed,
                AccessFailedCount=@accesscount, LockoutEnabled=@lockoutenabled, LockoutEndDateUtc=@lockoutenddate, TwoFactorEnabled=@twofactorenabled  
                WHERE Id = @userId";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@userName", user.UserName);
            parameters.Add("@pswHash", user.PasswordHash);
            parameters.Add("@secStamp", user.SecurityStamp);
            parameters.Add("@userId", user.Id);
            parameters.Add("@email", user.Email);
            parameters.Add("@emailconfirmed", user.EmailConfirmed);
            parameters.Add("@phonenumber", user.PhoneNumber);
            parameters.Add("@phonenumberconfirmed", user.PhoneNumberConfirmed);
            parameters.Add("@accesscount", user.AccessFailedCount);
            parameters.Add("@lockoutenabled", user.LockoutEnabled);
            parameters.Add("@lockoutenddate", user.LockoutEndDateUtc);
            parameters.Add("@twofactorenabled", user.TwoFactorEnabled);
            return _database.Execute(commandText, parameters);
        }
    }
}

RoleTable (MySQL)

using System;
using System.Collections.Generic;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that represents the Role table in the MySQL Database
    /// </summary>
    public class RoleTable 
    {
        private MySQLDatabase _database;
        /// <summary>
    /// Constructor that takes a MySQLDatabase instance 
        /// </summary>
    /// <param name="database"></param>
    public RoleTable(MySQLDatabase database)
        {
            _database = database;
        }
        /// <summary>
    /// Deltes a role from the Roles table
        /// </summary>
    /// <param name="roleId">The role Id</param>
    /// <returns></returns>
    public int Delete(string roleId)
        {
            string commandText = "Delete from Roles where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@id", roleId);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Inserts a new Role in the Roles table
        /// </summary>
    /// <param name="roleName">The role's name</param>
    /// <returns></returns>
    public int Insert(IdentityRole role)
        {
            string commandText = "Insert into Roles (Id, Name) values (@id, @name)";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@name", role.Name);
            parameters.Add("@id", role.Id);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Returns a role name given the roleId
        /// </summary>
    /// <param name="roleId">The role Id</param>
    /// <returns>Role name</returns>
    public string GetRoleName(string roleId)
        {
            string commandText = "Select Name from Roles where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@id", roleId);
            return _database.GetStrValue(commandText, parameters);
        }
        /// <summary>
    /// Returns the role Id given a role name
        /// </summary>
    /// <param name="roleName">Role's name</param>
    /// <returns>Role's Id</returns>
    public string GetRoleId(string roleName)
        {
            string roleId = null;
            string commandText = "Select Id from Roles where Name = @name";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@name", roleName } };
            var result = _database.QueryValue(commandText, parameters);
            if (result != null)
            {
                return Convert.ToString(result);
            }
            return roleId;
        }
        /// <summary>
    /// Gets the IdentityRole given the role Id
        /// </summary>
    /// <param name="roleId"></param>
    /// <returns></returns>
    public IdentityRole GetRoleById(string roleId)
        {
            var roleName = GetRoleName(roleId);
            IdentityRole role = null;
            if(roleName != null)
            {
                role = new IdentityRole(roleName, roleId);
            }
            return role;
        }
        /// <summary>
    /// Gets the IdentityRole given the role name
        /// </summary>
    /// <param name="roleName"></param>
    /// <returns></returns>
    public IdentityRole GetRoleByName(string roleName)
        {
            var roleId = GetRoleId(roleName);
            IdentityRole role = null;
            if (roleId != null)
            {
                role = new IdentityRole(roleName, roleId);
            }
            return role;
        }
        public int Update(IdentityRole role)
        {
            string commandText = "Update Roles set Name = @name where Id = @id";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@id", role.Id);
            return _database.Execute(commandText, parameters);
        }
    }
}

UserClaimsTable

using System.Collections.Generic;
using System.Security.Claims;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that represents the UserClaims table in the MySQL Database
    /// </summary>
    public class UserClaimsTable
    {
        private MySQLDatabase _database;
        /// <summary>
    /// Constructor that takes a MySQLDatabase instance 
        /// </summary>
    /// <param name="database"></param>
    public UserClaimsTable(MySQLDatabase database)
        {
            _database = database;
        }
        /// <summary>
    /// Returns a ClaimsIdentity instance given a userId
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public ClaimsIdentity FindByUserId(string userId)
        {
            ClaimsIdentity claims = new ClaimsIdentity();
            string commandText = "Select * from UserClaims where UserId = @userId";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@UserId", userId } };
            var rows = _database.Query(commandText, parameters);
            foreach (var row in rows)
            {
                Claim claim = new Claim(row["ClaimType"], row["ClaimValue"]);
                claims.AddClaim(claim);
            }
            return claims;
        }
        /// <summary>
    /// Deletes all claims from a user given a userId
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public int Delete(string userId)
        {
            string commandText = "Delete from UserClaims where UserId = @userId";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("userId", userId);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Inserts a new claim in UserClaims table
        /// </summary>
    /// <param name="userClaim">User's claim to be added</param>
    /// <param name="userId">User's id</param>
    /// <returns></returns>
    public int Insert(Claim userClaim, string userId)
        {
            string commandText = "Insert into UserClaims (ClaimValue, ClaimType, UserId) values (@value, @type, @userId)";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("value", userClaim.Value);
            parameters.Add("type", userClaim.Type);
            parameters.Add("userId", userId);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Deletes a claim from a user 
        /// </summary>
    /// <param name="user">The user to have a claim deleted</param>
    /// <param name="claim">A claim to be deleted from user</param>
    /// <returns></returns>
    public int Delete(IdentityUser user, Claim claim)
        {
            string commandText = "Delete from UserClaims where UserId = @userId and @ClaimValue = @value and ClaimType = @type";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("userId", user.Id);
            parameters.Add("value", claim.Value);
            parameters.Add("type", claim.Type);
            return _database.Execute(commandText, parameters);
        }
    }
}

UserLoginsTable

using Microsoft.AspNet.Identity;
using System.Collections.Generic;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that represents the UserLogins table in the MySQL Database
    /// </summary>
    public class UserLoginsTable
    {
        private MySQLDatabase _database;
        /// <summary>
    /// Constructor that takes a MySQLDatabase instance 
        /// </summary>
    /// <param name="database"></param>
    public UserLoginsTable(MySQLDatabase database)
        {
            _database = database;
        }
        /// <summary>
    /// Deletes a login from a user in the UserLogins table
        /// </summary>
    /// <param name="user">User to have login deleted</param>
    /// <param name="login">Login to be deleted from user</param>
    /// <returns></returns>
    public int Delete(IdentityUser user, UserLoginInfo login)
        {
            string commandText = "Delete from UserLogins where UserId = @userId and LoginProvider = @loginProvider and ProviderKey = @providerKey";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("UserId", user.Id);
            parameters.Add("loginProvider", login.LoginProvider);
            parameters.Add("providerKey", login.ProviderKey);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Deletes all Logins from a user in the UserLogins table
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public int Delete(string userId)
        {
            string commandText = "Delete from UserLogins where UserId = @userId";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("UserId", userId);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Inserts a new login in the UserLogins table
        /// </summary>
    /// <param name="user">User to have new login added</param>
    /// <param name="login">Login to be added</param>
    /// <returns></returns>
    public int Insert(IdentityUser user, UserLoginInfo login)
        {
            string commandText = "Insert into UserLogins (LoginProvider, ProviderKey, UserId) values (@loginProvider, @providerKey, @userId)";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("loginProvider", login.LoginProvider);
            parameters.Add("providerKey", login.ProviderKey);
            parameters.Add("userId", user.Id);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Return a userId given a user's login
        /// </summary>
    /// <param name="userLogin">The user's login info</param>
    /// <returns></returns>
    public string FindUserIdByLogin(UserLoginInfo userLogin)
        {
            string commandText = "Select UserId from UserLogins where LoginProvider = @loginProvider and ProviderKey = @providerKey";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("loginProvider", userLogin.LoginProvider);
            parameters.Add("providerKey", userLogin.ProviderKey);
            return _database.GetStrValue(commandText, parameters);
        }
        /// <summary>
    /// Returns a list of user's logins
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public List<UserLoginInfo> FindByUserId(string userId)
        {
            List<UserLoginInfo> logins = new List<UserLoginInfo>();
            string commandText = "Select * from UserLogins where UserId = @userId";
            Dictionary<string, object> parameters = new Dictionary<string, object>() { { "@userId", userId } };
            var rows = _database.Query(commandText, parameters);
            foreach (var row in rows)
            {
                var login = new UserLoginInfo(row["LoginProvider"], row["ProviderKey"]);
                logins.Add(login);
            }
            return logins;
        }
    }
}

UserRoleTable

using System.Collections.Generic;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that represents the UserRoles table in the MySQL Database
    /// </summary>
    public class UserRolesTable
    {
        private MySQLDatabase _database;
        /// <summary>
    /// Constructor that takes a MySQLDatabase instance 
        /// </summary>
    /// <param name="database"></param>
    public UserRolesTable(MySQLDatabase database)
        {
            _database = database;
        }
        /// <summary>
    /// Returns a list of user's roles
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public List<string> FindByUserId(string userId)
        {
            List<string> roles = new List<string>();
            string commandText = "Select Roles.Name from UserRoles, Roles where UserRoles.UserId = @userId and UserRoles.RoleId = Roles.Id";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@userId", userId);
            var rows = _database.Query(commandText, parameters);
            foreach(var row in rows)
            {
                roles.Add(row["Name"]);
            }
            return roles;
        }
        /// <summary>
    /// Deletes all roles from a user in the UserRoles table
        /// </summary>
    /// <param name="userId">The user's id</param>
    /// <returns></returns>
    public int Delete(string userId)
        {
            string commandText = "Delete from UserRoles where UserId = @userId";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("UserId", userId);
            return _database.Execute(commandText, parameters);
        }
        /// <summary>
    /// Inserts a new role for a user in the UserRoles table
        /// </summary>
    /// <param name="user">The User</param>
    /// <param name="roleId">The Role's id</param>
    /// <returns></returns>
    public int Insert(IdentityUser user, string roleId)
        {
            string commandText = "Insert into UserRoles (UserId, RoleId) values (@userId, @roleId)";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("userId", user.Id);
            parameters.Add("roleId", roleId);
            return _database.Execute(commandText, parameters);
        }
    }
}

IdentityUser

using Microsoft.AspNet.Identity;
using System;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that implements the ASP.NET Identity
    /// IUser interface 
    /// </summary>
    public class IdentityUser : IUser
    {
        /// <summary>
    /// Default constructor 
        /// </summary>
    public IdentityUser()
        {
            Id = Guid.NewGuid().ToString();
        }
        /// <summary>
    /// Constructor that takes user name as argument
        /// </summary>
    /// <param name="userName"></param>
    public IdentityUser(string userName)
            : this()
        {
            UserName = userName;
        }
        /// <summary>
    /// User ID
        /// </summary>
    public string Id { get; set; }
        /// <summary>
    /// User's name
        /// </summary>
    public string UserName { get; set; }
        /// <summary>
    ///     Email
        /// </summary>
    public virtual string Email { get; set; }
        /// <summary>
    ///     True if the email is confirmed, default is false
        /// </summary>
    public virtual bool EmailConfirmed { get; set; }
        /// <summary>
    ///     The salted/hashed form of the user password
        /// </summary>
    public virtual string PasswordHash { get; set; }
        /// <summary>
    ///     A random value that should change whenever a users credentials have changed (password changed, login removed)
        /// </summary>
    public virtual string SecurityStamp { get; set; }
        /// <summary>
    ///     PhoneNumber for the user
        /// </summary>
    public virtual string PhoneNumber { get; set; }
        /// <summary>
    ///     True if the phone number is confirmed, default is false
        /// </summary>
    public virtual bool PhoneNumberConfirmed { get; set; }
        /// <summary>
    ///     Is two factor enabled for the user
        /// </summary>
    public virtual bool TwoFactorEnabled { get; set; }
        /// <summary>
    ///     DateTime in UTC when lockout ends, any time in the past is considered not locked out.
        /// </summary>
    public virtual DateTime? LockoutEndDateUtc { get; set; }
        /// <summary>
    ///     Is lockout enabled for this user
        /// </summary>
    public virtual bool LockoutEnabled { get; set; }
        /// <summary>
    ///     Used to record failures for the purposes of lockout
        /// </summary>
    public virtual int AccessFailedCount { get; set; }
    }
}

UserStore

using Microsoft.AspNet.Identity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Claims;
using System.Threading.Tasks;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that implements the key ASP.NET Identity user store iterfaces
    /// </summary>
    public class UserStore<TUser> : IUserLoginStore<TUser>,
        IUserClaimStore<TUser>,
        IUserRoleStore<TUser>,
        IUserPasswordStore<TUser>,
        IUserSecurityStampStore<TUser>,
        IQueryableUserStore<TUser>,
        IUserEmailStore<TUser>,
        IUserPhoneNumberStore<TUser>,
        IUserTwoFactorStore<TUser, string>,
        IUserLockoutStore<TUser, string>,
        IUserStore<TUser>
    where TUser : IdentityUser
    {
        private UserTable<TUser> userTable;
        private RoleTable roleTable;
        private UserRolesTable userRolesTable;
        private UserClaimsTable userClaimsTable;
        private UserLoginsTable userLoginsTable;
        public MySQLDatabase Database { get; private set; }
        public IQueryable<TUser> Users
        {
            get
            {
                throw new NotImplementedException();
            }
        }
        /// <summary>
    /// Default constructor that initializes a new MySQLDatabase
        /// instance using the Default Connection string
        /// </summary>
    public UserStore()
        {
            new UserStore<TUser>(new MySQLDatabase());
        }
        /// <summary>
    /// Constructor that takes a MySQLDatabase as argument 
        /// </summary>
    /// <param name="database"></param>
    public UserStore(MySQLDatabase database)
        {
            Database = database;
            userTable = new UserTable<TUser>(database);
            roleTable = new RoleTable(database);
            userRolesTable = new UserRolesTable(database);
            userClaimsTable = new UserClaimsTable(database);
            userLoginsTable = new UserLoginsTable(database);
        }
        /// <summary>
    /// Insert a new TUser in the UserTable
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task CreateAsync(TUser user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            userTable.Insert(user);
            return Task.FromResult<object>(null);
        }
        /// <summary>
    /// Returns an TUser instance based on a userId query 
        /// </summary>
    /// <param name="userId">The user's Id</param>
    /// <returns></returns>
    public Task<TUser> FindByIdAsync(string userId)
        {
            if (string.IsNullOrEmpty(userId))
            {
                throw new ArgumentException("Null or empty argument: userId");
            }
            TUser result = userTable.GetUserById(userId) as TUser;
            if (result != null)
            {
                return Task.FromResult<TUser>(result);
            }
            return Task.FromResult<TUser>(null);
        }
        /// <summary>
    /// Returns an TUser instance based on a userName query 
        /// </summary>
    /// <param name="userName">The user's name</param>
    /// <returns></returns>
    public Task<TUser> FindByNameAsync(string userName)
        {
            if (string.IsNullOrEmpty(userName))
            {
                throw new ArgumentException("Null or empty argument: userName");
            }
            List<TUser> result = userTable.GetUserByName(userName) as List<TUser>;
            // Should I throw if > 1 user?
    if (result != null && result.Count == 1)
            {
                return Task.FromResult<TUser>(result[0]);
            }
            return Task.FromResult<TUser>(null);
        }
        /// <summary>
    /// Updates the UsersTable with the TUser instance values
        /// </summary>
    /// <param name="user">TUser to be updated</param>
    /// <returns></returns>
    public Task UpdateAsync(TUser user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            userTable.Update(user);
            return Task.FromResult<object>(null);
        }
        public void Dispose()
        {
            if (Database != null)
            {
                Database.Dispose();
                Database = null;
            }
        }
        /// <summary>
    /// Inserts a claim to the UserClaimsTable for the given user
        /// </summary>
    /// <param name="user">User to have claim added</param>
    /// <param name="claim">Claim to be added</param>
    /// <returns></returns>
    public Task AddClaimAsync(TUser user, Claim claim)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (claim == null)
            {
                throw new ArgumentNullException("user");
            }
            userClaimsTable.Insert(claim, user.Id);
            return Task.FromResult<object>(null);
        }
        /// <summary>
    /// Returns all claims for a given user
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<IList<Claim>> GetClaimsAsync(TUser user)
        {
            ClaimsIdentity identity = userClaimsTable.FindByUserId(user.Id);
            return Task.FromResult<IList<Claim>>(identity.Claims.ToList());
        }
        /// <summary>
    /// Removes a claim froma user
        /// </summary>
    /// <param name="user">User to have claim removed</param>
    /// <param name="claim">Claim to be removed</param>
    /// <returns></returns>
    public Task RemoveClaimAsync(TUser user, Claim claim)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (claim == null)
            {
                throw new ArgumentNullException("claim");
            }
            userClaimsTable.Delete(user, claim);
            return Task.FromResult<object>(null);
        }
        /// <summary>
    /// Inserts a Login in the UserLoginsTable for a given User
        /// </summary>
    /// <param name="user">User to have login added</param>
    /// <param name="login">Login to be added</param>
    /// <returns></returns>
    public Task AddLoginAsync(TUser user, UserLoginInfo login)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (login == null)
            {
                throw new ArgumentNullException("login");
            }
            userLoginsTable.Insert(user, login);
            return Task.FromResult<object>(null);
        }
        /// <summary>
    /// Returns an TUser based on the Login info
        /// </summary>
    /// <param name="login"></param>
    /// <returns></returns>
    public Task<TUser> FindAsync(UserLoginInfo login)
        {
            if (login == null)
            {
                throw new ArgumentNullException("login");
            }
            var userId = userLoginsTable.FindUserIdByLogin(login);
            if (userId != null)
            {
                TUser user = userTable.GetUserById(userId) as TUser;
                if (user != null)
                {
                    return Task.FromResult<TUser>(user);
                }
            }
            return Task.FromResult<TUser>(null);
        }
        /// <summary>
    /// Returns list of UserLoginInfo for a given TUser
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<IList<UserLoginInfo>> GetLoginsAsync(TUser user)
        {
            List<UserLoginInfo> userLogins = new List<UserLoginInfo>();
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            List<UserLoginInfo> logins = userLoginsTable.FindByUserId(user.Id);
            if (logins != null)
            {
                return Task.FromResult<IList<UserLoginInfo>>(logins);
            }
            return Task.FromResult<IList<UserLoginInfo>>(null);
        }
        /// <summary>
    /// Deletes a login from UserLoginsTable for a given TUser
        /// </summary>
    /// <param name="user">User to have login removed</param>
    /// <param name="login">Login to be removed</param>
    /// <returns></returns>
    public Task RemoveLoginAsync(TUser user, UserLoginInfo login)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (login == null)
            {
                throw new ArgumentNullException("login");
            }
            userLoginsTable.Delete(user, login);
            return Task.FromResult<Object>(null);
        }
        /// <summary>
    /// Inserts a entry in the UserRoles table
        /// </summary>
    /// <param name="user">User to have role added</param>
    /// <param name="roleName">Name of the role to be added to user</param>
    /// <returns></returns>
    public Task AddToRoleAsync(TUser user, string roleName)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (string.IsNullOrEmpty(roleName))
            {
                throw new ArgumentException("Argument cannot be null or empty: roleName.");
            }
            string roleId = roleTable.GetRoleId(roleName);
            if (!string.IsNullOrEmpty(roleId))
            {
                userRolesTable.Insert(user, roleId);
            }
            return Task.FromResult<object>(null);
        }
        /// <summary>
    /// Returns the roles for a given TUser
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<IList<string>> GetRolesAsync(TUser user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            List<string> roles = userRolesTable.FindByUserId(user.Id);
            {
                if (roles != null)
                {
                    return Task.FromResult<IList<string>>(roles);
                }
            }
            return Task.FromResult<IList<string>>(null);
        }
        /// <summary>
    /// Verifies if a user is in a role
        /// </summary>
    /// <param name="user"></param>
    /// <param name="role"></param>
    /// <returns></returns>
    public Task<bool> IsInRoleAsync(TUser user, string role)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if (string.IsNullOrEmpty(role))
            {
                throw new ArgumentNullException("role");
            }
            List<string> roles = userRolesTable.FindByUserId(user.Id);
            {
                if (roles != null && roles.Contains(role))
                {
                    return Task.FromResult<bool>(true);
                }
            }
            return Task.FromResult<bool>(false);
        }
        /// <summary>
    /// Removes a user from a role
        /// </summary>
    /// <param name="user"></param>
    /// <param name="role"></param>
    /// <returns></returns>
    public Task RemoveFromRoleAsync(TUser user, string role)
        {
            throw new NotImplementedException();
        }
        /// <summary>
    /// Deletes a user
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task DeleteAsync(TUser user)
        {
            if (user != null)
            {
                userTable.Delete(user);
            }
            return Task.FromResult<Object>(null);
        }
        /// <summary>
    /// Returns the PasswordHash for a given TUser
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<string> GetPasswordHashAsync(TUser user)
        {
            string passwordHash = userTable.GetPasswordHash(user.Id);
            return Task.FromResult<string>(passwordHash);
        }
        /// <summary>
    /// Verifies if user has password
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<bool> HasPasswordAsync(TUser user)
        {
            var hasPassword = !string.IsNullOrEmpty(userTable.GetPasswordHash(user.Id));
            return Task.FromResult<bool>(Boolean.Parse(hasPassword.ToString()));
        }
        /// <summary>
    /// Sets the password hash for a given TUser
        /// </summary>
    /// <param name="user"></param>
    /// <param name="passwordHash"></param>
    /// <returns></returns>
    public Task SetPasswordHashAsync(TUser user, string passwordHash)
        {
            user.PasswordHash = passwordHash;
            return Task.FromResult<Object>(null);
        }
        /// <summary>
    ///  Set security stamp
        /// </summary>
    /// <param name="user"></param>
    /// <param name="stamp"></param>
    /// <returns></returns>
    public Task SetSecurityStampAsync(TUser user, string stamp)
        {
            user.SecurityStamp = stamp;
            return Task.FromResult(0);
        }
        /// <summary>
    /// Get security stamp
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<string> GetSecurityStampAsync(TUser user)
        {
            return Task.FromResult(user.SecurityStamp);
        }
        /// <summary>
    /// Set email on user
        /// </summary>
    /// <param name="user"></param>
    /// <param name="email"></param>
    /// <returns></returns>
    public Task SetEmailAsync(TUser user, string email)
        {
            user.Email = email;
            userTable.Update(user);
            return Task.FromResult(0);
        }
        /// <summary>
    /// Get email from user
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<string> GetEmailAsync(TUser user)
        {
            return Task.FromResult(user.Email);
        }
        /// <summary>
    /// Get if user email is confirmed
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<bool> GetEmailConfirmedAsync(TUser user)
        {
            return Task.FromResult(user.EmailConfirmed);
        }
        /// <summary>
    /// Set when user email is confirmed
        /// </summary>
    /// <param name="user"></param>
    /// <param name="confirmed"></param>
    /// <returns></returns>
    public Task SetEmailConfirmedAsync(TUser user, bool confirmed)
        {
            user.EmailConfirmed = confirmed;
            userTable.Update(user);
            return Task.FromResult(0);
        }
        /// <summary>
    /// Get user by email
        /// </summary>
    /// <param name="email"></param>
    /// <returns></returns>
    public Task<TUser> FindByEmailAsync(string email)
        {
            if (String.IsNullOrEmpty(email))
            {
                throw new ArgumentNullException("email");
            }
            TUser result = userTable.GetUserByEmail(email) as TUser;
            if (result != null)
            {
                return Task.FromResult<TUser>(result);
            }
            return Task.FromResult<TUser>(null);
        }
        /// <summary>
    /// Set user phone number
        /// </summary>
    /// <param name="user"></param>
    /// <param name="phoneNumber"></param>
    /// <returns></returns>
    public Task SetPhoneNumberAsync(TUser user, string phoneNumber)
        {
            user.PhoneNumber = phoneNumber;
            userTable.Update(user);
            return Task.FromResult(0);
        }
        /// <summary>
    /// Get user phone number
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<string> GetPhoneNumberAsync(TUser user)
        {
            return Task.FromResult(user.PhoneNumber);
        }
        /// <summary>
    /// Get if user phone number is confirmed
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<bool> GetPhoneNumberConfirmedAsync(TUser user)
        {
            return Task.FromResult(user.PhoneNumberConfirmed);
        }
        /// <summary>
    /// Set phone number if confirmed
        /// </summary>
    /// <param name="user"></param>
    /// <param name="confirmed"></param>
    /// <returns></returns>
    public Task SetPhoneNumberConfirmedAsync(TUser user, bool confirmed)
        {
            user.PhoneNumberConfirmed = confirmed;
            userTable.Update(user);
            return Task.FromResult(0);
        }
        /// <summary>
    /// Set two factor authentication is enabled on the user
        /// </summary>
    /// <param name="user"></param>
    /// <param name="enabled"></param>
    /// <returns></returns>
    public Task SetTwoFactorEnabledAsync(TUser user, bool enabled)
        {
            user.TwoFactorEnabled = enabled;
            userTable.Update(user);
            return Task.FromResult(0);
        }
        /// <summary>
    /// Get if two factor authentication is enabled on the user
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<bool> GetTwoFactorEnabledAsync(TUser user)
        {
            return Task.FromResult(user.TwoFactorEnabled);
        }
        /// <summary>
    /// Get user lock out end date
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<DateTimeOffset> GetLockoutEndDateAsync(TUser user)
        {
            return
                Task.FromResult(user.LockoutEndDateUtc.HasValue
                    ? new DateTimeOffset(DateTime.SpecifyKind(user.LockoutEndDateUtc.Value, DateTimeKind.Utc))
                    : new DateTimeOffset());
        }
        /// <summary>
    /// Set user lockout end date
        /// </summary>
    /// <param name="user"></param>
    /// <param name="lockoutEnd"></param>
    /// <returns></returns>
    public Task SetLockoutEndDateAsync(TUser user, DateTimeOffset lockoutEnd)
        {
            user.LockoutEndDateUtc = lockoutEnd.UtcDateTime;
            userTable.Update(user);
            return Task.FromResult(0);
        }
        /// <summary>
    /// Increment failed access count
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<int> IncrementAccessFailedCountAsync(TUser user)
        {
            user.AccessFailedCount++;
            userTable.Update(user);
            return Task.FromResult(user.AccessFailedCount);
        }
        /// <summary>
    /// Reset failed access count
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task ResetAccessFailedCountAsync(TUser user)
        {
            user.AccessFailedCount = 0;
            userTable.Update(user);
            return Task.FromResult(0);
        }
        /// <summary>
    /// Get failed access count
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<int> GetAccessFailedCountAsync(TUser user)
        {
            return Task.FromResult(user.AccessFailedCount);
        }
        /// <summary>
    /// Get if lockout is enabled for the user
        /// </summary>
    /// <param name="user"></param>
    /// <returns></returns>
    public Task<bool> GetLockoutEnabledAsync(TUser user)
        {
            return Task.FromResult(user.LockoutEnabled);
        }
        /// <summary>
    /// Set lockout enabled for user
        /// </summary>
    /// <param name="user"></param>
    /// <param name="enabled"></param>
    /// <returns></returns>
    public Task SetLockoutEnabledAsync(TUser user, bool enabled)
        {
            user.LockoutEnabled = enabled;
            userTable.Update(user);
            return Task.FromResult(0);
        }
    }
}

IdentityRole

using Microsoft.AspNet.Identity;
using System;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that implements the ASP.NET Identity
    /// IRole interface 
    /// </summary>
    public class IdentityRole : IRole
    {
        /// <summary>
    /// Default constructor for Role 
        /// </summary>
    public IdentityRole()
        {
            Id = Guid.NewGuid().ToString();
        }
        /// <summary>
    /// Constructor that takes names as argument 
        /// </summary>
    /// <param name="name"></param>
    public IdentityRole(string name) : this()
        {
            Name = name;
        }
        public IdentityRole(string name, string id)
        {
            Name = name;
            Id = id;
        }
        /// <summary>
    /// Role ID
        /// </summary>
    public string Id { get; set; }
        /// <summary>
    /// Role name
        /// </summary>
    public string Name { get; set; }
    }
}

RoleStore

using Microsoft.AspNet.Identity;
using System;
using System.Linq;
using System.Threading.Tasks;
namespace AspNet.Identity.MySQL
{
    /// <summary>
    /// Class that implements the key ASP.NET Identity role store iterfaces
    /// </summary>
    public class RoleStore<TRole> : IQueryableRoleStore<TRole>
    where TRole : IdentityRole
    {
        private RoleTable roleTable;
        public MySQLDatabase Database { get; private set; }
        public IQueryable<TRole> Roles
        {
            get
            {
                throw new NotImplementedException();
            }
        }
        /// <summary>
    /// Default constructor that initializes a new MySQLDatabase
        /// instance using the Default Connection string
        /// </summary>
    public RoleStore()
        {
            new RoleStore<TRole>(new MySQLDatabase());
        }
        /// <summary>
    /// Constructor that takes a MySQLDatabase as argument 
        /// </summary>
    /// <param name="database"></param>
    public RoleStore(MySQLDatabase database)
        {
            Database = database;
            roleTable = new RoleTable(database);
        }
        public Task CreateAsync(TRole role)
        {
            if (role == null)
            {
                throw new ArgumentNullException("role");
            }
            roleTable.Insert(role);
            return Task.FromResult<object>(null);
        }
        public Task DeleteAsync(TRole role)
        {
            if (role == null)
            {
                throw new ArgumentNullException("user");
            }
            roleTable.Delete(role.Id);
            return Task.FromResult<Object>(null);
        }
        public Task<TRole> FindByIdAsync(string roleId)
        {
            TRole result = roleTable.GetRoleById(roleId) as TRole;
            return Task.FromResult<TRole>(result);
        }
        public Task<TRole> FindByNameAsync(string roleName)
        {
            TRole result = roleTable.GetRoleByName(roleName) as TRole;
            return Task.FromResult<TRole>(result);
        }
        public Task UpdateAsync(TRole role)
        {
            if (role == null)
            {
                throw new ArgumentNullException("user");
            }
            roleTable.Update(role);
            return Task.FromResult<Object>(null);
        }
        public void Dispose()
        {
            if (Database != null)
            {
                Database.Dispose();
                Database = null;
            }
        }
    }
}

posted @ 2015-06-03 14:07 冷杉阅读(2281) 评论(0) 编辑收藏举报

刷新页面返回顶部

路漫漫其修遠兮吾將上下爾求索

路漫漫其修远兮吾将上下而求索

自定义ASP.NET Identity（一）- 自定义ASP.NET Identity存储提供程序

介绍

理解架构

理解存储的数据

创建数据访问层

在数据访问类中，你提供的代码为您的特定的持久化机制进行数据操作。例如，在MySQL实现中，UserTable类包含一个方法，向Users数据库表插入一条新的记录。变量 _database是一个MySQLDatabase类的实例。

创建数据访问类之后，你必须创建存储类，供数据访问层中的指定方法调用。

自定义用户类

自定义用户存储

自定义用户存储的接口实现

IdentityUserClaim, IdentityUserLogin 和 IdentityUserRole

自定义角色类

自定义角色存储

使用新的存储提供程序重新配置应用

在MVC工程中替换默认存储提供程序

相关代码

MySQLDatabase

UserTable (MySQL)

RoleTable (MySQL)

UserClaimsTable

UserLoginsTable

UserRoleTable

IdentityUser

UserStore

IdentityRole

RoleStore

公告

路漫漫其修遠兮 吾將上下爾求索

路漫漫其修远兮吾将上下而求索

自定义ASP.NET Identity（一）- 自定义ASP.NET Identity存储提供程序

介绍

理解架构

理解存储的数据

创建数据访问层

在数据访问类中，你提供的代码为您的特定的持久化机制进行数据操作。例如，在MySQL实现中，UserTable类包含一个方法，向Users数据库表插入一条新的记录。变量 _database是一个MySQLDatabase类的实例。

创建数据访问类之后，你必须创建存储类，供数据访问层中的指定方法调用。

自定义用户类

自定义用户存储

自定义用户存储的接口实现

IdentityUserClaim, IdentityUserLogin 和 IdentityUserRole

自定义角色类

自定义角色存储

使用新的存储提供程序重新配置应用

在MVC工程中替换默认存储提供程序

相关代码

MySQLDatabase

UserTable (MySQL)

RoleTable (MySQL)

UserClaimsTable

UserLoginsTable

UserRoleTable

IdentityUser

UserStore

IdentityRole

RoleStore

公告

路漫漫其修遠兮吾將上下爾求索