Click here to Skip to main content
12,299,086 members (49,073 online)
Click here to Skip to main content
Add your own
alternative version

Stats

186.8K views
691 downloads
98 bookmarked
Posted

Suite of MySQL Provider Implementations for ASP.NET 2.0

, 22 Oct 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

    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:

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

Share

About the Author

J Snyman
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).

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
rdfelix22-Feb-13 8:52
memberrdfelix22-Feb-13 8:52 
GeneralReset Password Error Pin
Sojan8014-Jul-09 14:35
memberSojan8014-Jul-09 14:35 
GeneralRe: Reset Password Error Pin
J Snyman14-Jul-09 19:17
memberJ Snyman14-Jul-09 19:17 
GeneralProblem running on IIS 5.1 Pin
anderea26-Feb-09 22:53
memberanderea26-Feb-09 22:53 
GeneralMySQL Membership, Role, Personalization and Profile providers for ASP.NET Pin
AlexRiley14-Feb-09 8:34
memberAlexRiley14-Feb-09 8:34 
GeneralOnly passwordFormat = "Clear" is accepted. Pin
Michael Dolittle15-Nov-08 11:07
memberMichael Dolittle15-Nov-08 11:07 
GeneralRe: Only passwordFormat = "Clear" is accepted. Pin
J Snyman16-Nov-08 18:20
memberJ 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
memberLuizItatiba11-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
memberJ 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
memberLuizItatiba13-Nov-08 4:38 
QuestionHow CurrentNodes and accented with spaces in SiteMapPath? For example São Paulo-SP. Pin
LuizItatiba22-Oct-08 9:23
memberLuizItatiba22-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
memberJ 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
memberLuizItatiba8-Oct-08 6:04 
GeneralMembershipProvider passwordFormat Encrypted/Hashed Error Pin
fpajaro11-Jun-08 9:03
memberfpajaro11-Jun-08 9:03 
QuestionSo near, and yet.... Pin
Ross Holland25-May-08 11:11
memberRoss Holland25-May-08 11:11 
AnswerRe: So near, and yet.... Pin
J Snyman25-May-08 19:06
memberJ Snyman25-May-08 19:06 
GeneralRe: So near, and yet.... Pin
Ross Holland25-May-08 21:18
memberRoss Holland25-May-08 21:18 
GeneralRe: So near, and yet.... Pin
J Snyman25-May-08 21:26
memberJ Snyman25-May-08 21:26 
Generalhelp me :(( Pin
Dasty18-Apr-08 0:57
memberDasty18-Apr-08 0:57 
GeneralRe: help me :(( Pin
Christian Wikander14-May-08 22:24
memberChristian Wikander14-May-08 22:24 
QuestionHow to Order Alphabetically by the children MySqlSiteMapProvider us Pin
LuizItatiba1-Apr-08 16:23
memberLuizItatiba1-Apr-08 16:23 
QuestionAs Customizar Menus of MySQLSITEMAPPROVIDER, based on profiles or Roles? Pin
LuizItatiba1-Apr-08 9:21
memberLuizItatiba1-Apr-08 9:21 
QuestionMySQLSITE MAP PROVIDER WITH MYSQL CONNECTOR 5.0 solved Pin
LuizItatiba31-Mar-08 16:50
memberLuizItatiba31-Mar-08 16:50 
QuestionWEBCONFIG to MySqlConnector 5.0? Pin
LuizItatiba31-Mar-08 15:04
memberLuizItatiba31-Mar-08 15:04 
Question3 Questions on Article Pin
LuizItatiba26-Mar-08 10:54
memberLuizItatiba26-Mar-08 10:54 
GeneralRe: 3 Questions on Article Pin
J Snyman26-Mar-08 19:14
memberJ Snyman26-Mar-08 19:14 
GeneralRe: 3 Questions on Article Pin
LuizItatiba27-Mar-08 8:50
memberLuizItatiba27-Mar-08 8:50 
GeneralRe: 3 Questions on Article Pin
J Snyman27-Mar-08 18:58
memberJ Snyman27-Mar-08 18:58 
GeneralRe: 3 Questions on Article Pin
LuizItatiba27-Mar-08 19:07
memberLuizItatiba27-Mar-08 19:07 
GeneralRe: 3 Questions on Article Pin
J Snyman27-Mar-08 19:11
memberJ Snyman27-Mar-08 19:11 
GeneralRe: 3 Questions on Article Pin
LuizItatiba27-Mar-08 19:15
memberLuizItatiba27-Mar-08 19:15 
GeneralRe: 3 Questions on Article Pin
J Snyman27-Mar-08 19:18
memberJ Snyman27-Mar-08 19:18 
GeneralRe: 3 Questions on Article Pin
LuizItatiba27-Mar-08 19:22
memberLuizItatiba27-Mar-08 19:22 
GeneralRe: 3 Questions on Article Pin
J Snyman27-Mar-08 19:24
memberJ Snyman27-Mar-08 19:24 
GeneralRe: 3 Questions on Article Pin
LuizItatiba27-Mar-08 19:29
memberLuizItatiba27-Mar-08 19:29 
QuestionHow to Implement the driver MYSQL5.0 CONNECTOR? Pin
LuizItatiba25-Mar-08 15:59
memberLuizItatiba25-Mar-08 15:59 
GeneralDo I have to call Initialize() Pin
John Simmons / outlaw programmer20-Jan-08 3:33
mvpJohn Simmons / outlaw programmer20-Jan-08 3:33 
GeneralRe: Do I have to call Initialize() Pin
J Snyman20-Jan-08 18:41
memberJ Snyman20-Jan-08 18:41 
GeneralMySQL Connector/NET version Pin
voloda27-Jan-08 11:33
membervoloda27-Jan-08 11:33 
Generalstrange error in MysqlProfileProvider Pin
peteresorensen18-Nov-07 7:55
memberpeteresorensen18-Nov-07 7:55 
GeneralMySQL Connector/NET provides this Pin
SMikko31-Oct-07 19:01
memberSMikko31-Oct-07 19:01 
GeneralRe: MySQL Connector/NET provides this Pin
peteresorensen18-Nov-07 7:48
memberpeteresorensen18-Nov-07 7:48 
GeneralSiteMap provider throwing an error Pin
kwilder21-Aug-07 13:38
memberkwilder21-Aug-07 13:38 
GeneralRe: SiteMap provider throwing an error Pin
J Snyman21-Aug-07 20:11
memberJ Snyman21-Aug-07 20:11 
GeneralUI Interface for this available Pin
kwilder20-Aug-07 12:31
memberkwilder20-Aug-07 12:31 
GeneralRe: UI Interface for this available Pin
J Snyman21-Aug-07 2:45
memberJ Snyman21-Aug-07 2:45 
GeneralRe: UI Interface for this available Pin
kwilder21-Aug-07 6:19
memberkwilder21-Aug-07 6:19 
QuestionMySQL Cluster problems Pin
Dan Atkinson20-Aug-07 1:04
memberDan Atkinson20-Aug-07 1:04 
AnswerRe: MySQL Cluster problems Pin
J Snyman20-Aug-07 2:04
memberJ Snyman20-Aug-07 2:04 
GeneralRe: MySQL Cluster problems Pin
Dan Atkinson20-Aug-07 4:21
memberDan Atkinson20-Aug-07 4:21 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160525.2 | Last Updated 22 Oct 2007
Article Copyright 2007 by J Snyman
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid