Site hosting news, tutorials, tips, How Tos and more

Creating ASP.NET Membership Tables in a MySQL Database

howto

Did you know you could use a MySQL database to store your ASP.NET Membership Table information rather than Microsoft SQL Server? I’ve written this simple guide to help you get started.

First, create a MySQL database through the Winhost Control Panel. Click on the Sites tab.

WHCPSitesTabClick on the Manage link and then click on the MySQL button.

WHCPMySQLButtonClick the Add button and enter the values for Database Name, Database User, and Quota. Click the Create button to finish creating the database. Now click on the Manage link and record the connection information (Database Name, Database Server, Database User, and Database Password) on a piece of paper or a text editor such as Notepad.

Start Visual Studio and create a New Project by going to File -> New –> Project… (Ctrl+Shift+N).

NewProjectAdd the necessary .NET assemblies to your project using NuGet. Go to TOOLS -> Library Package Manager -> Manage NuGet Packages for a Solution… Type mysql in the Search field and hit Enter. Install both the MySql.Data and MySql.Web assemblies. A check mark will appear to the right when the assemblies have been installed.

NuGetAnother way to verify that the assemblies have been installed correctly is that they will appear as References in the Solution Explorer window.

ReferencesWhen you compile your project, a copy of these assemblies will be added to your local /bin folder which you will need to upload to your site account.

MySQLdllsThe next step is to modify the Web.config file with this markup:

<?xml version="1.0"?>

<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->

<configuration>
  <connectionStrings>
    <add name="MySqlMembershipConnection"
         connectionString="Data Source=my02.winhost.com;user id=membership;password=password;database=mysql_48503_membership;"
         providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Logon"
             timeout="30"
             name=".ASPXFORM$"
             path="/"
             requireSSL="false"
             slidingExpiration="true"
             defaultUrl="Default.aspx"
             enableCrossAppRedirects="false" />
    </authentication>
    <membership defaultProvider="MySqlMembershipProvider">
      <providers>
        <clear/>
        <add name="MySqlMembershipProvider"
             type="MySql.Web.Security.MySQLMembershipProvider, mysql.web"
             connectionStringName="MySqlMembershipConnection"
             enablePasswordRetrieval="false"
             enablePasswordReset="true"
             requiresQuestionAndAnswer="false"
             requiresUniqueEmail="true"
             passwordFormat="Hashed"
             maxInvalidPasswordAttempts="5"
             minRequiredPasswordLength="6"
             minRequiredNonalphanumericCharacters="0"
             passwordAttemptWindow="10"
             applicationName="/"
             autogenerateschema="true"/>
       </providers>
    </membership>
    <roleManager enabled="true" defaultProvider="MySqlRoleProvider">
      <providers>
        <clear />
        <add connectionStringName="MySqlMembershipConnection"
             applicationName="/"
             name="MySqlRoleProvider"
             type="MySql.Web.Security.MySQLRoleProvider, mysql.web"
             autogenerateschema="true"/>
      </providers>
    </roleManager>
    <profile>
      <providers>
        <clear/>
        <add type="MySql.Web.Security.MySqlProfileProvider, mysql.web"
             name="MySqlProfileProvider"
             applicationName="/"
             connectionStringName="MySqlMembershipConnection"
             autogenerateschema="true"/>
      </providers>
    </profile>
  </system.web>
</configuration>

Replacing the connection string section with the Winhost Connection String to your MySQL database. Save the file. The key to creating the tables is this attribute in the XML markup: autogenerateschema=”true” Now, select BUILD -> Build Solution (Ctrl+Shift+B) to compile your application, and then select PROJECT -> ASP.NET Configuration. This will launch the ASP.NET Web Site Administration Tool.

ASPNETConfigOnce the tool has been launched, the tables will be created, and you can check by logging into your MySQL database using an administration tool like MySQL WorkBench.

MembershipTablesNow, let’s build a very simple ASP.NET application that will allow you to create users. Go back to your project in Visual Studio, right click on your project name in Solution Explorer and select Add -> New Item… (Ctrl+Shift+A). Select Web Form and name the file. Switch to Design Mode. In the Toolbox Window, expand Login of the tree view, and drag and drop the CreateUserWizard control on to the page. Build your application.

CreateUserDeploy your files to the Winhost server using FTP or Web Deploy and then access your site using a browser either with your domain name or Secondary URL. Fill out the create user form and click on the Create User button.

LoginFormIf you go back and access your database using MySQL WorkBench and query the my_aspnet_users table, you can see that the user was created.

UserCreatedYou can also just use the ASP.NET Web Site Administration Tool to create the users. Of course, none of this information is necessarily new, so I want to thank Nathan Bridgewater’s blog and Oracle for helping me put together this tutorial for our customers at Winhost.


No responses yet

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.