How to Create ASP.NET Identity Tables in an Already Created Database Using Code First

How to create ASP.net identity tables in an already created database using code first?

So after a bit of reading a fiddling i got the answer. All I had to do was finally run the register method from ASP.NET Identity and all the tables were created.

How to create ASP.Net Identity tables inside existing database?

Will there the user tables be added to the existing sql server, or is
this user database a completely separate database?

You do not need two databases - you can create Identity tables inside your existing database.

ASP.Net Identity uses Entity Framework Code First. Therefore, before running your application first time, you want to update Connection String same as existing database which is normally inside ApplicationDbContext.

enter image description here

If you already have two separate databases and want to merge them, you want to use tools such as RedGate - SQL Compare and Data Compare.

Merging two database is totally out of original question; please kindly create a separate question if you have one.

How to integrate ASP.NET Identity tables in an existing database

I think that depends preety much on what you want to achieve.

Is it a single asp.Net application that you are developing or a set of different apps that would use the same database?

Here there is an answer I pretty much agree with. Separate schema and one database for one app/database. Two databases kind of beat the point of separation but three databases might be a better solution if the authorization rules remain the same.

As for the DB first approach there is a pretty good article (and code) here.

Entity Framework does not create identity tables on existing database

Found the answer,

Step 1. Use the following SQL Script to create the tables on the external sql server:

/****** Object:  Table [dbo].[__MigrationHistory]    Script Date: 5/15/2014 3:57:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[__MigrationHistory](
[MigrationId] [nvarchar](150) NOT NULL,
[ContextKey] [nvarchar](300) NOT NULL,
[Model] [varbinary](max) NOT NULL,
[ProductVersion] [nvarchar](32) NOT NULL,
CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED
(
[MigrationId] ASC,
[ContextKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


/****** Object: Table [dbo].[AspNetRoles] Script Date: 5/15/2014 4:04:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetRoles](
[Id] [nvarchar](128) NOT NULL,
[Name] [nvarchar](256) NOT NULL,
CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



/****** Object: Table [dbo].[AspNetUsers] Script Date: 5/15/2014 4:06:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUsers](
[Id] [nvarchar](128) NOT NULL,
[Hometown] [nvarchar](max) NULL,
[Email] [nvarchar](256) NULL,
[EmailConfirmed] [bit] NOT NULL,
[PasswordHash] [nvarchar](max) NULL,
[SecurityStamp] [nvarchar](max) NULL,
[PhoneNumber] [nvarchar](max) NULL,
[PhoneNumberConfirmed] [bit] NOT NULL,
[TwoFactorEnabled] [bit] NOT NULL,
[LockoutEndDateUtc] [datetime] NULL,
[LockoutEnabled] [bit] NOT NULL,
[AccessFailedCount] [int] NOT NULL,
[UserName] [nvarchar](256) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



/****** Object: Table [dbo].[AspNetUserClaims] Script Date: 5/15/2014 4:05:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserClaims](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [nvarchar](128) NOT NULL,
[ClaimType] [nvarchar](max) NULL,
[ClaimValue] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[AspNetUserClaims] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]
GO



/****** Object: Table [dbo].[AspNetUserLogins] Script Date: 5/15/2014 4:05:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserLogins](
[LoginProvider] [nvarchar](128) NOT NULL,
[ProviderKey] [nvarchar](128) NOT NULL,
[UserId] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED
(
[LoginProvider] ASC,
[ProviderKey] ASC,
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AspNetUserLogins] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
GO



/****** Object: Table [dbo].[AspNetUserRoles] Script Date: 5/15/2014 4:05:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUserRoles](
[UserId] [nvarchar](128) NOT NULL,
[RoleId] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
GO

ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
GO

Step 2: Change the web.config connection of the identity model to match the following (do not re-use or change the edmx connection)

<add name="IdentityDbContext" connectionString="Data Source=MYSERVER;Initial Catalog=MYDATABASE;Integrated Security=True;" providerName="System.Data.SqlClient" />

Step 3: Set the connection in IdentityModel.cs

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
: base("IdentityDbContext", throwIfV1Schema: false)
{
}

public static ApplicationDbContext Create()
{
return new ApplicationDbContext();
}
}

Add table to Code-First ASP.NET app with Identity

It is the easiest thing to do with Entity Framework. You can learn a lot by checking the Info section of EntityFramework tag here in StackOverflow. You can read the books or visit the websites listed there.

To answer your question, just create the CreditCard entity like this:

public class CreditCard
{
public int Id { get; set; }

[StringLength(128)]
public string UserName { get; set; }

[MinLength(16), MaxLength(16)]
[Column(TypeName = "CHAR")]
public string CardNumber { get; set; }

[Column(TypeName = "DATE")]
public DateTime ExpirationDate { get; set; }

[MinLength(3), MaxLength(3)]
[Column(TypeName = "CHAR")]
public string CVV { get; set; }
}

And your DbContext just add a new property called CreditCards which will be of type DbSet<CreditCard>.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{

public DbSet<CreditCard> CreditCards { get; set; }

public ApplicationDbContext()
: base("DefaultConnection", throwIfV1Schema: false)
{
}

public static ApplicationDbContext Create()
{
return new ApplicationDbContext();
}
}

If you recreate or update the database you'll have that table.

Using Asp.Net Identity DataBase first approach

A possible solution which works for me, basically I am able to integrate Asp.Net Identity User Profiles with an existing Database.

Getting the Asp.Identity Tables:

  • Create an MVC Project with Authentication Individual User Account
  • Open the DB listed under the DefaultConnection in Web.config. It will be called (aspnet-[timestamp] or something like that.)
  • Script the database tables using SQL Server Management Studio (attach database for mdc).

Alternatively use something like http://identity.codeplex.com/

Integrating with your existing db:

  • Insert the scripted tables into existing database in SQL Server Management Studio.
  • Customize and add relationships to ApplicationUser (if necessary).
  • Create new Web Project > MVC > DB First Project > Import DB with EF ... .
  • In IdentityModels.cs change the ApplicationDbContext :base("DefaltConnection") to use your project's DbContext.

Now you have the Asp.Identity Tables in your db with ER model in your application.

Asp.Identity Profile Adding new properties:

  • Enable Entity Framework Code First Database Migrations, just in VS go under Tools ‘Package Manager Console’,
  • Execute the command “Enable-Migrations”; Once we enabled the database migrations, we can go ahead and add new properties for our UserProfile

  • To Add new properties modify IdentityModels.cs file, example:


public class ApplicationUser : IdentityUser
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string EmailID { get; set; }
}

Add New Migration

  • Once we added the properties, bring the Package Manager Console and execute the following command.

    Add-Migration “YouMigrationName”

This command will generate a database script file, now execute following command to run this script file against the database.

Update-Database

Now, all the new properties will turn into table fields in the same database table.

I hope it can help others, if you have a better idea please let me know.



Related Topics



Leave a reply



Submit