Suite of MySQL Provider Implementations for ASP.NET 2.0

• Download source - 69.9 KB

Contents

• Introduction
• Using the Code
• Points of Interest
• History

As I started to work on a new AJAX-enabled website, I looked around for a MySQL implementation of the ASP.NET 2.0 membership provider. To my amazement, I couldn't find anything. So I decided to do my own implementation. After a few days of dev work, on and off, I found that I hadn't only implemented the membership provider but also the roles provider, site map provider and personalization provider.

All the providers inherit from the generic providers from Microsoft.

[^]

Using the providers is really easy.

1. Create a new database on your MySQL server, e.g. SimpleProviders.

2. Execute the following SQL statement on the newly created database.

   SQL

   CREATE TABLE `personalization` (
     `username` varchar(255) default NULL,
     `path` varchar(255) default NULL,
     `applicationname` varchar(255) default NULL,
     `personalizationblob` blob
   );
   
   CREATE TABLE `profiles` (
     `UniqueID` int(8) NOT NULL auto_increment,
     `Username` varchar(255) NOT NULL default '',
     `ApplicationName` varchar(255) NOT NULL default '',
     `IsAnonymous` tinyint(1) default '0',
     `LastActivityDate` datetime default NULL,
     `LastUpdatedDate` datetime default NULL,
     PRIMARY KEY  (`UniqueID`),
     UNIQUE KEY `PKProfiles` (`Username`,`ApplicationName`),
     UNIQUE KEY `PKID` (`UniqueID`)
   );
   
   CREATE TABLE `roles` (
     `Rolename` varchar(255) NOT NULL default '',
     `ApplicationName` varchar(255) NOT NULL default '',
     PRIMARY KEY  (`Rolename`,`ApplicationName`)
   );
   
   CREATE TABLE `sitemap` (
     `ID` int(11) NOT NULL auto_increment,
     `ApplicationName` varchar(255) NOT NULL default '',
     `Title` varchar(255) default NULL,
     `Description` text,
     `Url` text,
     `Roles` text,
     `Parent` int(11) default NULL,
     PRIMARY KEY  (`ID`)
   );
   
   CREATE TABLE `users` (
     `PKID` varchar(255) NOT NULL default '',
     `Username` varchar(255) NOT NULL default '',
     `ApplicationName` varchar(255) NOT NULL default '',
     `Email` varchar(128) default NULL,
     `Comment` varchar(255) default NULL,
     `Password` varchar(128) NOT NULL default '',
     `FailedPasswordAttemptWindowStart` datetime default NULL,
     `PasswordQuestion` varchar(255) default NULL,
     `IsLockedOut` tinyint(1) default '0',
     `PasswordAnswer` varchar(255) default NULL,
     `FailedPasswordAnswerAttemptCount` int(8) default '0',
     `FailedPasswordAttemptCount` int(8) default '0',
     `IsApproved` tinyint(1) NOT NULL default '0',
     `FailedPasswordAnswerAttemptWindowStart` datetime default NULL,
     `LastActivityDate` datetime default NULL,
     `IsOnLine` tinyint(1) default '0',
     `CreationDate` datetime default NULL,
     `LastPasswordChangedDate` datetime default NULL,
     `LastLockedOutDate` datetime default NULL,
     `LastLoginDate` datetime default NULL,
     PRIMARY KEY  (`PKID`),
     UNIQUE KEY `PKID` (`PKID`),
     KEY `PKID_2` (`PKID`),
     KEY `usr` (`Username`)
   );
   
   CREATE TABLE `usersinroles` (
     `Username` varchar(255) NOT NULL default '',
     `Rolename` varchar(255) NOT NULL default '',
     `ApplicationName` varchar(255) NOT NULL default '',
     PRIMARY KEY  (`Username`,`Rolename`,`ApplicationName`)
   );

   There is an SQL file named DBStructure.sql included with the source code ZIP file that contains the code above.

3. Open Visual Studio and create a new Website Project.

4. Add a reference to the Simple.Providers.MySQL.dll file.

5. Make the following changes to your web.config file:

   1. Add the connection string to your newly created database to the connectionStrings section, e.g. <add connectionstring="Driver={MySQL ODBC 3.51 Driver};server={Your Server IP};port={Your Server Port No.};option=3;database={New Database Name};uid={Your username};pwd={Your password}" name="SimpleProviderConnectionString" providername="System.Data.Odbc" />.

      * Please replace the {Your Server IP}, {Your Server Port No.}, {New Database Name}, {Your username} and {Your password} entries in the connection string with your own values.

   2. Under the <system.web> section add the following:

      XML

      <siteMap defaultProvider="siteMapProvider" enabled="true">
      
          <providers>
              <clear />
              <add name="siteMapProvider" 
                 type="Simple.Providers.MySQL.MysqlSiteMapProvider" 
                 connectionStringName="SimpleProviderConnectionString" 
                 applicationName="{Your App Name}" 
                 description="MySQL site map provider" 
                 securityTrimmingEnabled="true"/>
          </providers>
      </siteMap>
      <roleManager defaultProvider="roleProvider" enabled="true" 
          cacheRolesInCookie="false" cookieName=".ASPROLES" 
          cookieTimeout="7200" cookiePath="/" cookieRequireSSL="false" 
          cookieSlidingExpiration="true" cookieProtection="All">
          <providers>
      
              <clear />
              <add name="roleProvider" 
                  type="Simple.Providers.MySQL.MysqlRoleProvider" 
                  connectionStringName="SimpleProviderConnectionString" 
                  applicationName="{Your App Name}" 
                  description="MySQL role provider"/>
          </providers>
      </roleManager>
      <membership defaultProvider="membershipProvider" 
          userIsOnlineTimeWindow="15">
          <providers>
              <clear />
      
              <add name="membershipProvider" 
                  type="Simple.Providers.MySQL.MysqlMembershipProvider" 
                  connectionStringName="SimpleProviderConnectionString" 
                  applicationName="{Your App Name}" 
                  enablePasswordRetrieval="true" 
                  enablePasswordReset="true"
                  requiresQuestionAndAnswer="true" 
                  requiresUniqueEmail="true" passwordFormat="Encrypted" 
                  minRequiredPasswordLength="6" 
                  minRequiredNonalphanumericCharacters="0"  
                  description="MySQL membership provider"/>
          </providers>
      </membership>
      <profile defaultProvider="profileProvider" 
          automaticSaveEnabled="true">
          <providers>
              <clear />
              <add name="profileProvider" 
                  type="Simple.Providers.MySQL.MysqlProfileProvider" 
                  connectionStringName="SimpleProviderConnectionString" 
                  applicationName="{Your App Name}" 
                  description="MySQL Profile Provider"/>
      
          </providers>
          <properties>
              <clear />
              <!--
                  Add any needed attributes for profiles here.
                  eg. <add name="Theme" type="System.String" 
                          defaultValue="Default"/>
              -->
          </properties>
      
      </profile>
      <webParts>
          <personalization defaultProvider="personalizationProvider">
              <providers>
                  <clear />
                  <add name="personalizationProvider" 
                      type="Simple.Providers.MySQL.
                      MysqlPersonalizationProvider" 
                      connectionStringName=
                      "{Your Connection String Name}" applicationName="
                      {Your App Name}" 
                      description="MySQL Personalization Provider/>
              </providers>
      
          </personalization>
      </webParts> 
      
      /* !!! Please replace the {Your App Name} instances with a valid 
      application name. The application name should not contain 
      any spaces or special characters. !!! */

6. Everything should be set up correctly now. Continue with the rest of your project and make sure to make use of the features provided by the above mentioned providers.

[^]

Points of Interest

I made use of the Microsoft MSDN site while developing the provider suite. For more info on...

• Membership providers, click here.
• Role providers, click here.
• Profile providers, click here.
• Personalization providers, click here.
• SiteMap providers, click here.

[^]

History

• 2007-04-25: Initial release of the article
• 2007-10-18: Update of the UpdateUser code to enable LastActivityDate functionality