Click here to Skip to main content
15,917,652 members
Articles / Web Development / IIS
Article

Suite of MySQL Provider Implementations for ASP.NET 2.0

Rate me:
Please Sign up or sign in to vote.
4.13/5 (35 votes)
22 Oct 2007CPOL2 min read 282.5K   738   98   115
An Article on Implementing MySQL Providers for ASP.NET 2.0 Membership, Roles, Site Map and Personalization

Contents

Introduction

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 Code

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...

[^]

History

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

License

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


Written By
Web Developer
South Africa South Africa
Jacques Snyman is an Analyst Developer from Midrand, South Africa. He has developed numerous entreprise applications using C#, VB.Net, Java and various other languages.

His hobbies include programming, cricket, blogging and a fair bit of reading (mostly on developent).

Comments and Discussions

 
GeneralMy vote of 5 Pin
rdfelix22-Feb-13 8:52
rdfelix22-Feb-13 8:52 
GeneralReset Password Error Pin
Sojan8014-Jul-09 14:35
Sojan8014-Jul-09 14:35 
GeneralRe: Reset Password Error Pin
J Snyman14-Jul-09 19:17
J Snyman14-Jul-09 19:17 
GeneralProblem running on IIS 5.1 Pin
anderea26-Feb-09 22:53
anderea26-Feb-09 22:53 
GeneralMySQL Membership, Role, Personalization and Profile providers for ASP.NET Pin
AlexRiley14-Feb-09 8:34
AlexRiley14-Feb-09 8:34 
GeneralOnly passwordFormat = "Clear" is accepted. Pin
Michael Bakker15-Nov-08 11:07
Michael Bakker15-Nov-08 11:07 
I get the same error as 'fpajaro' stated in an earlier message.
Using passwordFormat = "Clear" works just fine
This error occurs befóre an actual connection is made to the database, so the error lies not in there.
Targeting framework 2.0, 3.0 or 3.5 makes no difference.
It has to be in the membership provider implementation.
Any help or suggestions on this are very welcome.

Otherwise nice coding and thanks for sharing it...Rose | [Rose] ...

Michael Bakker, the Netherlands.

GeneralRe: Only passwordFormat = "Clear" is accepted. Pin
J Snyman16-Nov-08 18:20
J Snyman16-Nov-08 18:20 
GeneralBecause in Asp.Net website he does not recognize the Web.config of the MySqlSiteMapProvider ApplicationName Pin
LuizItatiba11-Nov-08 14:02
LuizItatiba11-Nov-08 14:02 
GeneralRe: Because in Asp.Net website he does not recognize the Web.config of the MySqlSiteMapProvider ApplicationName Pin
J Snyman12-Nov-08 21:44
J Snyman12-Nov-08 21:44 
AnswerRe: Because in Asp.Net website he does not recognize the Web.config of the MySqlSiteMapProvider ApplicationName Pin
LuizItatiba13-Nov-08 4:38
LuizItatiba13-Nov-08 4:38 
QuestionHow CurrentNodes and accented with spaces in SiteMapPath? For example São Paulo-SP. Pin
LuizItatiba22-Oct-08 9:23
LuizItatiba22-Oct-08 9:23 
AnswerRe: How CurrentNodes and accented with spaces in SiteMapPath? For example São Paulo-SP. Pin
J Snyman22-Oct-08 9:30
J Snyman22-Oct-08 9:30 
QuestionHow to correctly set the standard MemberShipProvider Provider of the SqlServer2005 to work together with the MySqlSiteMapProvider? Pin
LuizItatiba8-Oct-08 6:04
LuizItatiba8-Oct-08 6:04 
Generalspeed problems Pin
peteresorensen8-Aug-08 2:44
peteresorensen8-Aug-08 2:44 
GeneralRe: speed problems Pin
J Snyman8-Aug-08 2:51
J Snyman8-Aug-08 2:51 
GeneralMySQL/Net Connector Pin
peteresorensen8-Aug-08 3:07
peteresorensen8-Aug-08 3:07 
GeneralRe: MySQL/Net Connector Pin
J Snyman10-Aug-08 21:40
J Snyman10-Aug-08 21:40 
GeneralRe: MySQL/Net Connector Pin
peteresorensen10-Aug-08 22:36
peteresorensen10-Aug-08 22:36 
GeneralMembershipProvider passwordFormat Encrypted/Hashed Error Pin
fpajaro11-Jun-08 9:03
fpajaro11-Jun-08 9:03 
QuestionSo near, and yet.... Pin
Ross Holland25-May-08 11:11
Ross Holland25-May-08 11:11 
AnswerRe: So near, and yet.... Pin
J Snyman25-May-08 19:06
J Snyman25-May-08 19:06 
GeneralRe: So near, and yet.... Pin
Ross Holland25-May-08 21:18
Ross Holland25-May-08 21:18 
GeneralRe: So near, and yet.... Pin
J Snyman25-May-08 21:26
J Snyman25-May-08 21:26 
Generalhelp me :(( Pin
Dasty18-Apr-08 0:57
Dasty18-Apr-08 0:57 
GeneralRe: help me :(( Pin
Christian Wikander14-May-08 22:24
Christian Wikander14-May-08 22:24 

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.