MySQL Membership, Role, Sitemap, Personalization Provider for ASP.Net 2.0

• Download ccmMyProvClass.zip - 165.0 KB

Introduction

This is the implementation of Membership,Role,Sitemap,Personalization provider of ASP.Net using MySQL as backend database. This class uses the native net MySQL connector version 5.1.2

Background

This code is based on the work of J Snyman, he uses odbc to connect to MySQL, I modified it to use the native net MySQL connector version 5.1.2

Using the code

INSTRUCTION:

This is actually a C# class project, you only need to reference the compiled dll output from this project in your Web project, you can use this even if your language in your web app is vb. The provided compiled dll can be used right out of the box, but if you prefer you can compile your own.

Using the providers is really easy.

1. Create a new database on your MySQL server eg. SimpleProviders
2. Execute the following SQL statement on the newly created database

<code lang=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`)
);

</code>


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
5. Make the following changes to your web.config file

1. Add the connection string to your newly created database to the connectionStrings section
eg. <add connectionString="server=localhost;database=simpleproviders;user id=<put user>;pwd=<put password>" name="SimpleProviderconnectionstring" providerName="MySql.Data.MySqlClient"/>

* Please replace the {Your username} and {Your password} entries in the connection string with your own values.


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

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

Blocks of code should be set as style "Formatted" like this:

//

// Any source code blocks look like this

//

Remember to set the Language of your code snippet using the Language dropdown.

Use the "var" button to to wrap Variable or class names in <code> tags like this.

Points of Interest

Did you learn anything interesting/fun/annoying while writing the code? Did you do anything particularly clever or wild or zany?

History

9/7/2007 - Using mysql net connector 5.1.2 instead of ODBC.