SimpleMembership and SimpleRole Providers for MySQL in ASP.NET MVC 4 Application Tutorial

dotConnect for MySQL implements SimpleMembership functionality as custom MySqlExtendedMembershipProvider and MySqlExtendedRoleProvider classes. This tutorial describes how to use the new ASP.NET MVC membership provider functionality in ASP.NET MVC 4 application (using the Internet Application template).

To complete this tutorial you need ASP.NET MVC 4 installed. It is preinstalled with Visual Studio 2012 and later. For Visual Studio 2010 you can download it from here.

In order to create an ASP.NET MVC 4 application using our MySqlExtendedMembershipProvider, perform the following steps:

  1. Create an ASP.NET MVC 4 Web Application in Visual Studio. To do it, select New -> Project from the File menu, click Web under Installed Templates, and select ASP.NET MVC 4 Web Application in the list of available templates.

    Selecting a project template

    Then select the Internet Application template in the opened New ASP.NET MVC4 Project dialog box.

    Selecting an ASP.NET MVC 4 web application template
  2. Add necessary references to our assemblies to the project. You need to add references to the Devart.Data, Devart.Data.MySql, Devart.Data.MySql.Web assemblies. Note that you should add the Devart.Data.MySql.Web assembly by selecting it as a file (not from the extension list) from the NET4 subdirectory of the dotConnect for MySQL installation directory (by default, from Program Files\Devart\dotConnect\MySQL\NET4), not the one from the Extensions list. The Devart.Data.MySql.Web assembly that is in Extensions list is for .NET Framework 2.0, and we need the one for .NET Framework 4. The required assembly has the revision number 4 in its version.

  3. In the Web.config file replace the DefaultConnection connection string with the actual connection string to the app database. Replace the providerName value with "Devart.Data.MySql". For example, your connection string may look like the following:

    <connectionStrings>
    	<add name="DefaultConnection" connectionString="User Id=root;Host=localhost;Database=Test" 
    	  providerName="Devart.Data.MySql" />
      </connectionStrings>
  4. In the Web.config file remove the following tag:

    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  5. Add the following lines to the Web.config file:

          <membership defaultProvider="MySqlExtendedMembershipProvider">
            <providers>
              <remove name="MySqlExtendedMembershipProvider"/>
              <add name="MySqlExtendedMembershipProvider" type="Devart.Data.MySql.Web.Providers.MySqlExtendedMembershipProvider, 
                Devart.Data.MySql.Web, Version=8.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
                description="dotConnect for MySQL extended membership provider" connectionStringName="DefaultConnection" />
            </providers>
          </membership>
          <roleManager enabled="true" defaultProvider="MySqlExtendedRoleProvider">
            <providers>
              <remove name="MySqlExtendedRoleProvider"/>
              <add name="MySqlExtendedRoleProvider" type="Devart.Data.MySql.Web.Providers.MySqlExtendedRoleProvider, 
                  Devart.Data.MySql.Web, Version=8.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
                  description="dotConnect for MySQL extended role provider" connectionStringName="DefaultConnection" />
            </providers>
          </roleManager>

    Note: Replace 8.1.26.4 in the code with your actual version.

  6. In the InitializeSimpleMembershipAttribute.cs or InitializeSimpleMembershipAttribute.vb file (that is located in the Filters project folder), replace the namespace "WebMatrix.WebData" with the "Devart.Common.Web".

  7. Now you can run the application and check whether it works by registering a user.

    Registering a test user

    As you can see, the user is registered and logged in.

    Test user is registered

 

dotConnect for MySQL ASP.NET Membership Database

dotConnect for MySQL uses the following ASP.NET membership database for its SimpleMembership provider:

ASP.NET ExtendedMemberShip database schema

By default, the application automatically verifies if the tables exist by the specified connection string. The userprofile table is automatically created by Entity Framework runtime if it is not already present in the database. Other tables are created by the WebSecurity.InitializeDatabaseConnection method, defined in the Devart.Common.Web namespace. If these tables are already created, no actions are performed.

However you can create these tables manually. Execute the DDL script %Install_Directory%\Devart\dotConnect\MySQL\NET4\InstallExtendedWebTables.sql (for example C:\Program Files\Devart\dotConnect\MySQL\NET4\InstallExtendedWebTables.sql) against the database server to create the database tables to store the user and role information.

If you are sure that the necessary tables are always present in the specified database and want to disable this automatic verification, you may re-register our provider in your Web.config file with the following settings:

  <membership defaultProvider="MySqlExtendedMembershipProvider">
    <providers>
      <remove name="MySqlExtendedMembershipProvider"/>
      <add name="MySqlExtendedMembershipProvider" type="Devart.Data.MySql.Web.Providers.MySqlExtendedMembershipProvider, 
        Devart.Data.MySql.Web, Version=8.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
        description="dotConnect for MySQL extended membership provider" connectionStringName="DefaultConnection" 
        useridcolumn="UserId" usernamecolumn="UserName" usertablename="UserProfile" />
    </providers>
  </membership>
  <roleManager enabled="true" defaultProvider="MySqlExtendedRoleProvider">
    <providers>
      <remove name="MySqlExtendedRoleProvider"/>
      <add name="MySqlExtendedRoleProvider" type="Devart.Data.MySql.Web.Providers.MySqlExtendedRoleProvider, 
        Devart.Data.MySql.Web, Version=8.1.26.4, Culture=neutral, PublicKeyToken=09af7300eec23701" 
        description="dotConnect for MySQL extended role provider" connectionStringName="DefaultConnection" 
        useridcolumn="UserId" usernamecolumn="UserName" usertablename="UserProfile" />
    </providers>
  </roleManager>

Note: Replace 8.1.26.4 in the code with your actual version.

Note: In case dotConnect for MySQL is installed on the target computer with the ASP.NET providers option, you may use shortened registration of our provider in the Web.config file as necessary records are added to machine.config. Add the following membership and roleManager elements to the system.web tag of your Web.config file:

<configuration>
  ...
  <system.web>
    ...
    <membership defaultProvider="MySqlExtendedMembershipProvider"/>
    <roleManager enabled="true" defaultProvider="MySqlExtendedRoleProvider"/>
  </system.web>
  ...
</configuration>
Back to list