Suite of MySQL Provider Implementations for ASP.NET 2.0
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.
-
Create a new database on your MySQL server, e.g.
SimpleProviders
. -
Execute the following SQL statement on the newly created database.
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.
-
Open Visual Studio and create a new Website Project.
-
Add a reference to the Simple.Providers.MySQL.dll file.
-
Make the following changes to your web.config file:
-
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. -
Under the
<system.web>
section add the following:<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. !!! */
-
-
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 enableLastActivityDate
functionality