Click here to Skip to main content
15,867,568 members
Articles / Web Development / HTML
Tip/Trick

How to set up application using ASP.NET Identity with MySQL Provider

Rate me:
Please Sign up or sign in to vote.
4.97/5 (24 votes)
30 Jun 2014CPOL4 min read 314.2K   2.7K   31   37
To set up an ASP.NET application the implements ASP Identity with Entity Framework to use MySql as a database.

Introduction

I used Visual Studio 2013 to develop the sample code. The application framework used is .NET 4.5.

The database used is MySQL. I downloaded and installed MySQL Installer 5.6.19 which can be found here.

This installs all the necessary files including the MySQL version 1.1.4 connector. You can even select MySql from Server Explorer -> Data Connections -> Right click on Add Connection:

Image 1

Figure 1

Once you have downloaded the sample project (VS2013MySql), unzip it, open the folder and click on VS2013MySql.sln to open it in Visual Studio 2013. You will have to make changes to the connection string in Web.config to run it in your own MySql environment.

For developers familiar with Visual Studio (2012 and 2013), it is fairly easy to work using the already set-up LocalDB for development. A developer can then move easily to MS SQL Server for production.

This article is for users that want to develop their ASP.NET web application using MySQL.

Background

Sometimes, a developer may want to use a free open-sourced database to cut costs involved in using other databases e.g. MS SQL Server, Oracle DB etc. Or maybe a developer might have a client that specifically needs work done using mySql. Whichever the reason, the following will help you get set up.

Using the code

Create a new project in VS 2013. Select ASP.NET Web Application. At the top, select .NET framework 4.5.1, name your application then click next.

On the next screen, select Web Forms template (this article is based on a webforms application. However the steps for setting up MySQL for a MVC application are similar) and leave authentication to Individual User Accounts. Click "OK" to initialize the application for development.

In your Solution Explorer, right click on "References" then click on "Add Reference". The MySql installer I stated above should have installed some important .MySql files on your computer. We shall select 3 of them to add to our references:

In the Reference Manager, go to Assemblies -> Extensions and click on: MySql.Data, MySql.Data.Entity.EF6 and MySql.Web. (MySql.Data and MySql.Web are versions 6.8.3.0)

Your references should now look like this:

Image 2

Figure 2

The next step is to open the Web.config file and make the following changes. (The downloadable solution should guide you)

1. Change the connection string:

XML
<connectionStrings>
   <add name="DefaultConnection" connectionString="server=localhost;User Id=root;password=password;Persist Security Info=True;database=aspmysql"
        providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Change the server name, Id, password and databases accordingly. To easily get the connection string needed, select MySql from Server Explorer -> Data Connections -> Right click on Add Connection:

Enter the details as needed, select dtabase and test connection. Then click "OK". In server explorer, when you click on the added database, you should see the connection string under the properties window on the bottom right of your Visual Studio screen.

2. Change membership:

XML
 <membership defaultProvider="MySqlMembershipProvider">
      <providers>       
        <clear />
        <add name="MySqlMembershipProvider"
             type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web"
             connectionStringName="DefaultConnection"
             enablePasswordRetrieval="false"
             enablePasswordReset="true"
             requiresQuestionAndAnswer="false"
             requiresUniqueEmail="true"
             passwordFormat="Hashed"
             maxInvalidPasswordAttempts="5"
             minRequiredPasswordLength="6"
             minRequiredNonalphanumericCharacters="0"
             passwordAttemptWindow="10"
             applicationName="/"
             autogenerateschema="true" />
     </providers>
</membership>

3. Change profile:

<profile>
     <providers>
       <clear />
       <add type="MySql.Web.Security.MySqlProfileProvider, MySql.Web"
            name="MySqlProfileProvider" applicationName="/"
            connectionStringName="DefaultConnection"
            autogenerateschema="true" />
     </providers>
 </profile>

4. Change role manager:

<roleManager enabled="true" defaultProvider="MySqlRoleProvider">
     <providers>
       <clear />
       <add connectionStringName="DefaultConnection"
            applicationName="/" name="MySqlRoleProvider"
            type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
            autogenerateschema="true" />
     </providers>
</roleManager>

5. Change old Entity Framework to:

XML
<entityFramework>
   <providers>
     <provider invariantName="MySql.Data.MySqlClient"
               type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
   </providers>
 </entityFramework>
 <system.data>
   <DbProviderFactories>
     <remove invariant="MySql.Data.MySqlClient"></remove>
     <add name="MySQL Data Provider"
          invariant="MySql.Data.MySqlClient"
          description=".Net Framework Data Provider for MySQL"
          type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,  Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
   </DbProviderFactories>
 </system.data>

We shall now enable migrations.

Go to the menu item View -> Other Windows -> Click on "Package Manager Console". Enter "enable-migrations" in the console prompt and hit enter.

Image 3

Figure 3

A new folder, Migrations will be placed in your solution (check solution explorer) together with a new file named "Configuration.cs".

Entity Framework Code First uses Migration History to keep track of model changes and ensure consistency between the database and conceptual schemas. The Migration History table, __migrationhistory, has a primary key that is too large for MySql.

The fix:

Under the migration folder, add a new class named MySqlHistoryContext.cs and add the following code:

C#
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations.History;

namespace VS2013MySql.Migrations
{
    public class MySqlHistoryContext : HistoryContext
    {
        public MySqlHistoryContext(DbConnection connection, string defaultSchema)
            : base(connection, defaultSchema)
        {

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
            modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();
        }
    }
}

Under the same migration folder, make changes to the Configuration.cs file to look like this:

C#
namespace VS2013MySql.Migrations
{
    using System.Data.Entity.Migrations;

    internal sealed class Configuration : DbMigrationsConfiguration<VS2013MySql.Models.ApplicationDbContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;

            // register mysql code generator
            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());

            SetHistoryContextFactory("MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema));
        }

        protected override void Seed(VS2013MySql.Models.ApplicationDbContext context)
        {
           
        }
    }
}

We now need to create a custome database initializer since the MySQL provider does not support Entity Framework migrations.

Add a new class file named MySqlInitializer.cs to the project, and change its code to this:

C#
using VS2013MySql.Models;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;

namespace VS2013MySql
{

    public class MySqlInitializer : IDatabaseInitializer<ApplicationDbContext>
    {
        public void InitializeDatabase(ApplicationDbContext context)
        {
            if (!context.Database.Exists())
            {
                // if database did not exist before - create it
                context.Database.Create();
            }
            else
            {
                // query to check if MigrationHistory table is present in the database
                var migrationHistoryTableExists = ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery<int>(
                string.Format(
                  "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{0}' AND table_name = '__MigrationHistory'",
                  "aspmysql"));

                // if MigrationHistory table is not there (which is the case first time we run) - create it
                if (migrationHistoryTableExists.FirstOrDefault() == 0)
                {
                    context.Database.Delete();
                    context.Database.Create();
                }
            }
        }
    }
}

The above file on line 24 (for my project): Change "aspmysql" to the name of the MySql database you are using.

In my solution explorer, the 3 files look like this:

Image 4

Figure 4

Under the Models folder in solution explorer, change the IdentityModel.cs file code to look like this:

C#
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.Owin.Security;
using System.Web;
using System;
using VS2013MySql.Models;
using System.Data.Entity;

namespace VS2013MySql.Models
{
    // You can add User data for the user by adding more properties to your User 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", throwIfV1Schema: false)
        {
            Database.SetInitializer(new MySqlInitializer());
        }
    }

Leave the Helpers section as is.

Guess what? We are done!

To test our application, press Ctrl + F5 to build and run.

Then click the Register tab on the top-right of the page and register a new user. You should then see "Welcome @user!" on the navigation menu.

Now, if you go to MySql workbench (I use SQLyog), you should see the newly created tables:

Image 5

Figure 5

If you view the data in the "aspnetusers" table, you will see the information for your newly created user:

Image 6

Figure 6

I put up this together from bits and pieces of code available on developer forums. This tutorial helped a lot even thought they show a MySql connection to Azure. I hope it may be of help to some.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) GetNet
Kenya Kenya
http://www.kinyanjuikamau.com

Comments and Discussions

 
QuestionConvert in Vb Pin
Member 1489613122-Jul-20 3:10
Member 1489613122-Jul-20 3:10 
QuestionGreat work Kinyanjui Kamau Pin
Zeni24121-Apr-18 23:26
Zeni24121-Apr-18 23:26 
GeneralMy vote of 5 Pin
Member 134458004-Oct-17 7:27
Member 134458004-Oct-17 7:27 
Question'System.Data.Entity.Core.ProviderIncompatibleException' occurred in mscorlib.dll but was not handled in user code Pin
Member 131765886-May-17 1:44
Member 131765886-May-17 1:44 
PraiseThanx, it works on VS2015 ASP.NET MVC 4 with MySQL 5.6.20 Pin
Kurt Muellner13-Apr-17 9:36
Kurt Muellner13-Apr-17 9:36 
QuestionEF6 connect to mysql on linux server? Pin
sridhargiri6-Apr-17 2:36
professionalsridhargiri6-Apr-17 2:36 
AnswerRe: EF6 connect to mysql on linux server? Pin
kariokabrs9-Apr-17 10:22
kariokabrs9-Apr-17 10:22 
PraiseYou save me! Thank you. Pin
MadCasper9-Feb-17 16:03
MadCasper9-Feb-17 16:03 
Questionadditional configuration Pin
tcraven19-Jan-17 6:07
tcraven19-Jan-17 6:07 
SuggestionIntegrating with MySQL on a Linux based system is big pain in the a** Pin
david2912-Oct-15 0:32
david2912-Oct-15 0:32 
QuestionDatabase Schema is Incorrect Pin
Ray Brack17-Sep-15 23:44
Ray Brack17-Sep-15 23:44 
AnswerRe: Database Schema is Incorrect Pin
david2912-Oct-15 0:36
david2912-Oct-15 0:36 
AnswerRe: Database Schema is Incorrect Pin
jvgonza13-Jul-18 7:24
jvgonza13-Jul-18 7:24 
GeneralThanks Pin
Ferro Mario22-Jul-15 4:07
Ferro Mario22-Jul-15 4:07 
GeneralMy vote of 5 Pin
Zephire11-Jul-15 3:58
Zephire11-Jul-15 3:58 
I've been looking in many places to find a step by step guide to assist me in adding ASP.NET Identity onto my MySql database.

This is by far the most complete point of resources around. Thanks Kinyanjui for the effort put into this!
QuestionTry and fail Pin
Member 1154109422-Mar-15 6:31
Member 1154109422-Mar-15 6:31 
QuestionMore tables created Pin
Member 868550122-Feb-15 5:20
Member 868550122-Feb-15 5:20 
GeneralMy vote of 2 Pin
trailmax18-Feb-15 11:59
trailmax18-Feb-15 11:59 
SuggestionAddition to IdentityDbContext Pin
Casper Hougaard Jensen13-Feb-15 3:54
Casper Hougaard Jensen13-Feb-15 3:54 
GeneralRe: Addition to IdentityDbContext Pin
Kinyanjui Kamau18-Feb-15 19:18
professionalKinyanjui Kamau18-Feb-15 19:18 
QuestionWhy migration is necessary Pin
eugene al18-Jan-15 9:35
eugene al18-Jan-15 9:35 
AnswerRe: Why migration is necessary Pin
Kinyanjui Kamau22-Jan-15 10:32
professionalKinyanjui Kamau22-Jan-15 10:32 
BugMySql.Data.Entity Required! Pin
Member 111018182-Jan-15 10:30
Member 111018182-Jan-15 10:30 
GeneralRe: MySql.Data.Entity Required! Pin
Kinyanjui Kamau2-Jan-15 21:41
professionalKinyanjui Kamau2-Jan-15 21:41 
QuestionProblem when Porting to MVC5 Pin
Zaidor2-Jan-15 6:01
Zaidor2-Jan-15 6:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.