Using ADO.NET Implementation of ASP.NET Identity 1 for PostgreSQL
In This Topic
ASP.NET Identity is a new system of user authentication and authorization, that continues the evolution of ASP.NET membership system, and is used in the Visual Studio 2013 or Visual Studio 2013 Update 1 project templates for ASP.NET MVC, Web Forms, Web API and SPA. You can read more on ASP.NET Identity in Microsoft documentation.
dotConnect for PostgreSQL enables you to employ an implementation of ASP.NET Identity for PostgreSQL database using either ADO.NET or Entity Framework functionality in your web applications. This allows you to use such ASP.NET Identity benefits as unit-testable user authentication system, social login support, OWIN integration, etc. This tutorial demonstrates creating an ADO.NET implementation of ASP.NET Identity.
To complete this tutorial you need Visual Studio 2013 or Visual Studio 2013 Update 1 installed. For Visual Studio 2013 Update 2 or higher see Using ADO.NET Implementation of ASP.NET Identity 2 for PostgreSQL.
In order to create an ASP.NET MVC 5 application using dotConnect for PostgreSQL for storing identity information, perform the following steps:
-
In the Add New Project dialog box expand Visual C# on the left, then Web, and then select ASP.NET Web Application. Name your project, for example, "AspNet_Identity_Application" and then click OK.
-
In the New ASP.NET Project dialog box, select the MVC template with the default options (that includes Individual User Accounts as authentication method) and click OK.
-
In Solution Explorer, right-click your project and select Manage NuGet Packages from the shortcut menu. In the search text box dialog, type "Identity.EntityFramework". Select this package in the list of results and click Uninstall. You will be prompted to uninstall the dependency package EntityFramework. Click Yes since we will no longer this package on this application.
-
Add references to the necessary dotConnect for PostgreSQL assemblies:
- Devart.Data.dll
- Devart.Data.PostgreSql.dll
- Devart.Data.PostgreSql.Web.Identity.dll
For ASP.NET Identity 1 you need to add the reference to the Devart.Data.PostgreSql.Web.Identity.dll, having the revision number 0, and installed to the Program Files\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 1 folder
When creating an ASP.NET application, using ASP.NET Identity, you can use the existing ASP.NET Identity functionality as is or customize this functionality. For example, you may add storing some custom information about the user. In our tutorial we will demonstrate both options.
Using Existing ASP.NET Identity Functionality
First, you need to create a schema that will store information about users. To do it, execute the Install_identity_tables.sql script, which goes with dotConnect for PostgreSQL. By default, this script is in the Program Files\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 1 folder. You can also copy this script from the Identity Database Script chapter of this topic. After this perform the following steps:
- In Visual Studio remove the IdentityModels.cs (or IdentityModels.vb for Visual Basic) file that is in the Models folder, from the project.
-
In the Controllers project folder open the AccountController.cs (or AccountController.vb for Visual Basic) file.
Replace
using Microsoft.AspNet.Identity.EntityFramework;
Imports Microsoft.AspNet.Identity.EntityFramework
with:
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
using ApplicationUser = Devart.Common.Web.Identity.IdentityUser;
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
Imports ApplicationUser = Devart.Common.Web.Identity.IdentityUser
Replace the constructor code:
public AccountController()
: this(new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext())))
{
}
Public Sub New()
Me.New(New UserManager(Of ApplicationUser)(New UserStore(Of ApplicationUser)(New ApplicationDbContext())))
End Sub
with
public AccountController()
: this(new UserManager<ApplicationUser>(new PgSqlUserStore()))
{
}
Public Sub New()
Me.New(New UserManager(Of ApplicationUser)(New PgSqlUserStore()))
End Sub
In the web.config file of your project, replace the default connection string with your PostgreSQL one.
<add name="DefaultConnection" connectionString="host=server;database=test;user id=postgres;" providerName="Devart.Data.PostgreSql" />
Extending ASP.NET Identity Functionality
If you want to extend the out-of-the-box ASP.NET Identity functionality, you may inherit from the standard ASP.NET Identity classes. In our tutorial we will create a new user class that stores additional information about users - their emails.
To implement email storing, please perform the following steps:
- Open the IdentityModels.cs (or IdentityModels.vb for Visual Basic) file from the Models project folder.
Replace the imported namespace
using Microsoft.AspNet.Identity.EntityFramework;
Imports Microsoft.AspNet.Identity.EntityFramework
with:
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
- Remove the code of the ApplicationDbContext class.
Add the Email property to the ApplicationUser class that is inherited from the IdentityUser class.
Code before:
// You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
public class ApplicationUser : IdentityUser
{
}
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
: base("DefaultConnection")
{
}
}
' You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
Public Class ApplicationUser
Inherits IdentityUser
End Class
Public Class ApplicationDbContext
Inherits IdentityDbContext(Of ApplicationUser)
Public Sub New()
MyBase.New("DefaultConnection")
End Sub
End Class
Code after:
public class ApplicationUser : IdentityUser {
public ApplicationUser() {
}
public string Email { get; set; }
}
Public Class ApplicationUser
Inherits IdentityUser
Public Sub New()
End Sub
Public Property Email() As String
Get
Return m_Email
End Get
Set
m_Email = Value
End Set
End Property
Private m_Email As String
End Class
After this we need to create a database that will store the user information. Modify the Install_identity_tables.sql script, which goes with dotConnect for PostgreSQL. By default, this script is in the Program Files\Devart\dotConnect\PostgreSQL\Web\ASP.NET Identity 1 folder. You can also copy this script from the Identity Database Script chapter of this topic. . Add a column for storing emails to the AspNetUsers table.
Before adding the column:
CREATE TABLE "AspNetUsers" (
"Id" varchar(128) NOT NULL,
"UserName" varchar(256) NOT NULL,
"PasswordHash" varchar(256) NULL,
"SecurityStamp" varchar(256) NULL,
PRIMARY KEY ("Id")
);
After adding the column:
CREATE TABLE "AspNetUsers" (
"Id" varchar(128) NOT NULL,
"UserName" varchar(256) NOT NULL,
"PasswordHash" varchar(256) NULL,
"SecurityStamp" varchar(256) NULL,
"Email" varchar(256) NULL,
PRIMARY KEY ("Id")
);
Execute this script.
After this we need to create a UserTable class descendant that will read/write the ApplicationUser objects from/to the database. It can be implemented in two ways. The first way is using SQL to read and update the database.
public class MyUserTable : UserTable<ApplicationUser> {
public MyUserTable(DbDataSource dataSource)
: base(dataSource) {
}
public override int Insert(ApplicationUser user) {
return DataSource.ExecuteNonQuery("INSERT INTO MY_IDENTITY_SCHEMA.\"AspNetUsers\" (\"Id\", \"UserName\", \"PasswordHash\", \"SecurityStamp\", \"Email\") VALUES (:p0, :p1, :p2, :p3, :p4)",
new object[] { user.Id, user.UserName, user.PasswordHash, user.SecurityStamp, user.Email });
}
public override int Update(ApplicationUser user) {
return DataSource.ExecuteNonQuery("UPDATE MY_IDENTITY_SCHEMA.\"AspNetUsers\" SET \"UserName\" = :p0, \"PasswordHash\" = :p1, \"SecurityStamp\" = :p2, \"Email\" = :p3 WHERE \"Id\" = :p4",
new object[] { user.UserName, user.PasswordHash, user.SecurityStamp, user.Email, user.Id });
}
protected override ApplicationUser Materialize(DataRecord record) {
ApplicationUser user = base.Materialize(record);
if (user != null)
user.Email = (string)record["Email"];
return user;
}
}
Public Class MyUserTable
Inherits UserTable(Of ApplicationUser)
Public Sub New(dataSource As DbDataSource)
MyBase.New(dataSource)
End Sub
Public Overrides Function Insert(user As ApplicationUser) As Integer
Return DataSource.ExecuteNonQuery("INSERT INTO MY_IDENTITY_SCHEMA.""AspNetUsers"" (""Id"", ""UserName"", ""PasswordHash"", ""SecurityStamp"", ""Email"") VALUES (:p0, :p1, :p2, :p3, :p4)", New Object() {user.Id, user.UserName, user.PasswordHash, user.SecurityStamp, user.Email})
End Function
Public Overrides Function Update(user As ApplicationUser) As Integer
Return DataSource.ExecuteNonQuery("UPDATE MY_IDENTITY_SCHEMA.""AspNetUsers"" SET ""UserName"" = :p0, ""PasswordHash"" = :p1, ""SecurityStamp"" = :p2, ""Email"" = :p3 WHERE ""Id"" = :p4", New Object() {user.UserName, user.PasswordHash, user.SecurityStamp, user.Email, user.Id})
End Function
Protected Overrides Function Materialize(record As DataRecord) As ApplicationUser
Dim user As ApplicationUser = MyBase.Materialize(record)
If user IsNot Nothing Then
user.Email = DirectCast(record("Email"), String)
End If
Return user
End Function
End Class
Alternatively we can create a class, which provides the same functionality, but uses the functionality of the UserTable class, inherited from AspNetTable, instead of performing SQL statements.
public class MyUserTable : UserTable<ApplicationUser> {
public MyUserTable(DbDataSource dataSource)
: base(dataSource) {
}
protected override List<string> GetInsertColumns() {
var columns = base.GetInsertColumns();
columns.Add("Email");
return columns;
}
public override int Insert(ApplicationUser user) {
return Insert(user.Id, user.UserName, user.PasswordHash, user.SecurityStamp, user.Email);
}
public override int Update(ApplicationUser user) {
return Update(user.UserName, user.PasswordHash, user.SecurityStamp, user.Email, // SET
user.Id // WHERE
);
}
protected override ApplicationUser Materialize(DataRecord record) {
ApplicationUser user = base.Materialize(record);
if (user != null)
user.Email = (string)record["Email"];
return user;
}
}
Public Class MyUserTable
Inherits UserTable(Of ApplicationUser)
Public Sub New(dataSource As DbDataSource)
MyBase.New(dataSource)
End Sub
Protected Overrides Function GetInsertColumns() As List(Of String)
Dim columns = MyBase.GetInsertColumns()
columns.Add("Email")
Return columns
End Function
Public Overrides Function Insert(user As ApplicationUser) As Integer
Return Insert(user.Id, user.UserName, user.PasswordHash, user.SecurityStamp, user.Email)
End Function
Public Overrides Function Update(user As ApplicationUser) As Integer
Return Update(user.UserName, user.PasswordHash, user.SecurityStamp, user.Email, user.Id)
End Function
Protected Overrides Function Materialize(record As DataRecord) As ApplicationUser
Dim user As ApplicationUser = MyBase.Materialize(record)
If user IsNot Nothing Then
user.Email = DirectCast(record("Email"), String)
End If
Return user
End Function
End Class
In this example we have overridden the GetInsertColumns() method to add the "Email" column to the list of the columns, used for generating the INSERT command. Since we haven't overridden the GetUpdateColumns() method, the same "Email" column will also be present in the column list used for generating the UPDATE command. We pass the parameter values for the commands, generated automatically, in the Insert and Update methods.
After you have created the MyUserTable class in one or another way, create a TableFactory descendant that returns our custom UserTable implementation, that reads and saves ApplicationUser objects from/to database.
public class MyTableFactory : TableFactory<ApplicationUser, IdentityRole>, ITableFactory<ApplicationUser, IdentityRole> {
public MyTableFactory(DbDataSource dataSource)
: base(dataSource) {
}
public override UserTable<ApplicationUser> CreateUserTable() {
return new MyUserTable(DataSource);
}
}
Public Class MyTableFactory
Inherits TableFactory(Of ApplicationUser, IdentityRole)
Implements ITableFactory(Of ApplicationUser, IdentityRole)
Public Sub New(dataSource As DbDataSource)
MyBase.New(dataSource)
End Sub
Public Overrides Function CreateUserTable() As UserTable(Of ApplicationUser)
Return New MyUserTable(DataSource)
End Function
End Class
-
Create a PgSqlDataSource descendant, that creates our custom TableFactory.
public class MyDataSource : PgSqlDataSource {
public MyDataSource()
: base() {
}
public override ITableFactory<MyIdentityUser, IdentityRole> CreateTableFactory<MyIdentityUser, IdentityRole>() {
return (ITableFactory<MyIdentityUser, IdentityRole>)new MyTableFactory(this);
}
}
Public Class MyDataSource
Inherits PgSqlDataSource
Public Sub New()
MyBase.New()
End Sub
Public Overrides Function CreateTableFactory(Of MyIdentityUser, IdentityRole)() As ITableFactory(Of MyIdentityUser, IdentityRole)
Return DirectCast(New MyTableFactory(Me), ITableFactory(Of MyIdentityUser, IdentityRole))
End Function
End Class
We also can modify the MyDataSource class constructor in order to specify connection string name instead of DefaultConnection in the config file or a custom name of the schema where the identity tables reside.
The MyDataSource class constructor for it can be the following:
public MyDataSource()
: base("name=MyConnection", "MY_IDENTITY_SCHEMA") { }
Public Sub New()
MyBase.New("name=MyConnection", "MY_IDENTITY_SCHEMA")
End Sub
In the web.config file of your project, replace the default connection string with your PostgreSQL one.
<add name="DefaultConnection" connectionString="host=server;database=test;user id=postgres;" providerName="Devart.Data.PostgreSql" />
Or, if you are going to use another connection string name, add the new connection string.
-
Create a PgSqlUserStore class descendant, that will be used in our application. We will pass it in the AccountController to the UserManager being created.
public class MyUserStore : PgSqlUserStore<ApplicationUser, IdentityRole> {
public MyUserStore()
: base(new MyDataSource()) {
}
}
Public Class MyUserStore
Inherits PgSqlUserStore(Of ApplicationUser, IdentityRole)
Public Sub New()
MyBase.New(New MyDataSource())
End Sub
End Class
-
In the Controllers project folder open the AccountController.cs (or AccountController.vb for Visual Basic) file.
Replace the imported namespace
using Microsoft.AspNet.Identity.EntityFramework;
Imports Microsoft.AspNet.Identity.EntityFramework
with:
using Devart.Common.Web.Identity;
using Devart.Data.PostgreSql.Web.Identity;
Imports Devart.Common.Web.Identity
Imports Devart.Data.PostgreSql.Web.Identity
Replace the constructor code
public AccountController()
: this(new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext())))
{
}
Public Sub New()
Me.New(New UserManager(Of ApplicationUser)(New UserStore(Of ApplicationUser)(New ApplicationDbContext())))
End Sub
with:
public AccountController()
: this(new UserManager<ApplicationUser>(new MyUserStore())) {
}
Public Sub New()
Me.New(New UserManager(Of ApplicationUser)(New MyUserStore()))
End Sub
-
After this we need to update our application and add code for specifying user's email. Open the Models\AccountViewModels.cs (or Models\AccountViewModels.vb for Visual Basic) file and add the email field to the RegisterViewModel class.
Code before:
public class RegisterViewModel
{
[Required]
[Display(Name = "User name")]
public string UserName { get; set; }
[Required]
[StringLength(100, ErrorMessage = "The {0} must be at least {2} characters long.", MinimumLength = 6)]
[DataType(DataType.Password)]
[Display(Name = "Password")]
public string Password { get; set; }
[DataType(DataType.Password)]
[Display(Name = "Confirm password")]
[Compare("Password", ErrorMessage = "The password and confirmation password do not match.")]
public string ConfirmPassword { get; set; }
}
Public Class RegisterViewModel
<Required> _
<Display(Name := "User name")> _
Public Property UserName() As String
Get
Return m_UserName
End Get
Set
m_UserName = Value
End Set
End Property
Private m_UserName As String
<Required> _
<StringLength(100, ErrorMessage := "The {0} must be at least {2} characters long.", MinimumLength := 6)> _
<DataType(DataType.Password)> _
<Display(Name := "Password")> _
Public Property Password() As String
Get
Return m_Password
End Get
Set
m_Password = Value
End Set
End Property
Private m_Password As String
<DataType(DataType.Password)> _
<Display(Name := "Confirm password")> _
<Compare("Password", ErrorMessage := "The password and confirmation password do not match.")> _
Public Property ConfirmPassword() As String
Get
Return m_ConfirmPassword
End Get
Set
m_ConfirmPassword = Value
End Set
End Property
Private m_ConfirmPassword As String
End Class
Code after:
public class RegisterViewModel
{
[Required]
[Display(Name = "User name")]
public string UserName { get; set; }
[Required]
[StringLength(100, ErrorMessage = "The {0} must be at least {2} characters long.", MinimumLength = 6)]
[DataType(DataType.Password)]
[Display(Name = "Password")]
public string Password { get; set; }
[DataType(DataType.Password)]
[Display(Name = "Confirm password")]
[Compare("Password", ErrorMessage = "The password and confirmation password do not match.")]
public string ConfirmPassword { get; set; }
[DataType(DataType.EmailAddress)]
[Display(Name = "E-mail")]
public string Email { get; set; }
}
Public Class RegisterViewModel
<Required> _
<Display(Name := "User name")> _
Public Property UserName() As String
Get
Return m_UserName
End Get
Set
m_UserName = Value
End Set
End Property
Private m_UserName As String
<Required> _
<StringLength(100, ErrorMessage := "The {0} must be at least {2} characters long.", MinimumLength := 6)> _
<DataType(DataType.Password)> _
<Display(Name := "Password")> _
Public Property Password() As String
Get
Return m_Password
End Get
Set
m_Password = Value
End Set
End Property
Private m_Password As String
<DataType(DataType.Password)> _
<Display(Name := "Confirm password")> _
<Compare("Password", ErrorMessage := "The password and confirmation password do not match.")> _
Public Property ConfirmPassword() As String
Get
Return m_ConfirmPassword
End Get
Set
m_ConfirmPassword = Value
End Set
End Property
Private m_ConfirmPassword As String
<DataType(DataType.EmailAddress)> _
<Display(Name := "E-mail")> _
Public Property Email() As String
Get
Return m_Email
End Get
Set
m_Email = Value
End Set
End Property
Private m_Email As String
End Class
-
Open the Views\Account\Register.cshtml file and edit the Register method. Add a field for email.
<div class="form-group">
@Html.LabelFor(m => m.Email, new { @class = "col-md-2 control-label" })
<div class="col-md-10">
@Html.TextBoxFor(m => m.Email, new { @class = "form-control" })
</div>
</div>
-
Open the Controllers\AccountController.cs (or Models\AccountController.vb for Visual Basic) file and add code for passing email to the Register method of the AccountController class.
Code before:
public async Task<ActionResult> Register(RegisterViewModel model)
{
if (ModelState.IsValid)
{
var user = new ApplicationUser() { UserName = model.UserName };
var result = await UserManager.CreateAsync(user, model.Password);
if (result.Succeeded)
{
await SignInAsync(user, isPersistent: false);
return RedirectToAction("Index", "Home");
}
else
{
AddErrors(result);
}
}
// If we got this far, something failed, redisplay form
return View(model);
}
Public Function Register(model As RegisterViewModel) As Task(Of ActionResult)
If ModelState.IsValid Then
Dim user = New ApplicationUser() With { _
Key .UserName = model.UserName _
}
Dim result = Await UserManager.CreateAsync(user, model.Password)
If result.Succeeded Then
Await SignInAsync(user, isPersistent := False)
Return RedirectToAction("Index", "Home")
Else
AddErrors(result)
End If
End If
' If we got this far, something failed, redisplay form
Return View(model)
End Function
Code after:
public async Task<ActionResult> Register(RegisterViewModel model)
{
if (ModelState.IsValid)
{
var user = new ApplicationUser() { UserName = model.UserName, Email = model.Email };
var result = await UserManager.CreateAsync(user, model.Password);
if (result.Succeeded)
{
await SignInAsync(user, isPersistent: false);
return RedirectToAction("Index", "Home");
}
else
{
AddErrors(result);
}
}
// If we got this far, something failed, redisplay form
return View(model);
}
Public Function Register(model As RegisterViewModel) As Task(Of ActionResult)
If ModelState.IsValid Then
Dim user = New ApplicationUser() With { _
Key .UserName = model.UserName, _
Key .Email = model.Email _
}
Dim result = Await UserManager.CreateAsync(user, model.Password)
If result.Succeeded Then
Await SignInAsync(user, isPersistent := False)
Return RedirectToAction("Index", "Home")
Else
AddErrors(result)
End If
End If
' If we got this far, something failed, redisplay form
Return View(model)
End Function
Checking Application
Identity Database Script
CREATE TABLE "AspNetRoles" (
"Id" varchar(128) NOT NULL,
"Name" varchar(256) NOT NULL,
PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetUsers" (
"Id" varchar(128) NOT NULL,
"UserName" varchar(256) NOT NULL,
"PasswordHash" varchar(256) NULL,
"SecurityStamp" varchar(256) NULL,
PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetUserClaims" (
"Id" serial NOT NULL,
"ClaimType" varchar(256) NULL,
"ClaimValue" varchar(256) NULL,
"UserId" varchar(128) NOT NULL,
PRIMARY KEY ("Id")
);
CREATE TABLE "AspNetUserLogins" (
"UserId" varchar(128) NOT NULL,
"LoginProvider" varchar(128) NOT NULL,
"ProviderKey" varchar(128) NOT NULL,
PRIMARY KEY ("UserId", "LoginProvider", "ProviderKey")
);
CREATE TABLE "AspNetUserRoles" (
"UserId" varchar(128) NOT NULL,
"RoleId" varchar(128) NOT NULL,
PRIMARY KEY ("UserId", "RoleId")
);
CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");
CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");
CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");
CREATE INDEX "IX_AspNetUserRoles_UserId" ON "AspNetUserRoles" ("UserId");
ALTER TABLE "AspNetUserClaims"
ADD CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_User_Id" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id")
ON DELETE CASCADE;
ALTER TABLE "AspNetUserLogins"
ADD CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id")
ON DELETE CASCADE;
ALTER TABLE "AspNetUserRoles"
ADD CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id")
ON DELETE CASCADE;
ALTER TABLE "AspNetUserRoles"
ADD CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id")
ON DELETE CASCADE;
See Also
Using Entity Framework Implementation of ASP.NET Identity 1 for PostgreSQL
| Using Entity Framework Implementation of ASP.NET Identity 2 for PostgreSQL
| Using ADO.NET Implementation of ASP.NET Identity 2 for PostgreSQL
| Using Entity Framework Core Implementation of ASP.NET Core Identity for PostgreSQL