Suite of MySQL Provider Implementations for ASP.NET 2.0

J Snyman

4.13/5 (35 votes)

Apr 26, 2007

CPOL

2 min read

291583

738

An Article on Implementing MySQL Providers for ASP.NET 2.0 Membership, Roles, Site Map and Personalization

Download source - 69.9 KB

Introduction
Using the Code
Points of Interest
History

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.