Using MySQL Providers with ASP.NET

I'm figuring out how to use MySQL with Visual Studio and ASP.NET. The first thing to install (besides MySQL Server) is the MySQL Connector for .NET, currently at version 6.0.4.
After installing this connector, you've all the .NET providers and entity framework stuff to go. One thing I should mention is that the .NET Connectors will install MySql providers which you can use in your website, MySql won't popup in the Express editions of Visual Studio. So you can't connect to a MySQL database with your server explorer with these versions.

So installing the MySQL connector on your server will modify the server's machine.config and add the provider config in it. Not installing the connector on your server means you need to configure the providers in the system.web section of the web.config of the site (and bin deploying the MySql dll's of course):

<membership defaultProvider="MySQLMembershipProvider">
<providers>
<remove name="MySQLMembershipProvider" />
<add connectionStringName="MySqlServer" enablePasswordRetrieval="false"
enablePasswordReset="true" requiresQuestionAndAnswer="true"
applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear"
maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7"
minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10"
passwordStrengthRegularExpression="" name="MySQLMembershipProvider"
type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</providers>
</membership>
<profile>
<providers>
<remove name="MySQLProfileProvider"/>
<add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="MySqlServer" applicationName="/" />
</providers>
</profile>
<roleManager defaultProvider="MySQLRoleProvider">
<providers>
<remove name="MySQLRoleProvider" />
<add connectionStringName="MySqlServer" applicationName="/" name="MySQLRoleProvider"
type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</providers>
</roleManager>


These new providers need a connection string, so add to your connectionString section:

<connectionStrings>
<add name="MySqlServer" providerName="MySql.Data.MySqlClient" connectionString="server=mysql1;user id=webuser;password=xxxxxx;persist security info=True;database=website" />
</connectionStrings>

Next you need the schema. The docs will say they will be created automatically, but I didn't saw a single table created. By looking at the connector source code, I distilled the following script to create the schema in your database:

CREATE TABLE `my_aspnet_applications` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) DEFAULT NULL,
`description` varchar(256) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

CREATE TABLE `my_aspnet_membership` (
`userId` int(11) NOT NULL DEFAULT '0',
`Email` varchar(128) DEFAULT NULL,
`Comment` varchar(255) DEFAULT NULL,
`Password` varchar(128) NOT NULL,
`PasswordKey` char(32) DEFAULT NULL,
`PasswordFormat` tinyint(4) DEFAULT NULL,
`PasswordQuestion` varchar(255) DEFAULT NULL,
`PasswordAnswer` varchar(255) DEFAULT NULL,
`IsApproved` tinyint(1) DEFAULT NULL,
`LastActivityDate` datetime DEFAULT NULL,
`LastLoginDate` datetime DEFAULT NULL,
`LastPasswordChangedDate` datetime DEFAULT NULL,
`CreationDate` datetime DEFAULT NULL,
`IsLockedOut` tinyint(1) DEFAULT NULL,
`LastLockedOutDate` datetime DEFAULT NULL,
`FailedPasswordAttemptCount` int(10) unsigned DEFAULT NULL,
`FailedPasswordAttemptWindowStart` datetime DEFAULT NULL,
`FailedPasswordAnswerAttemptCount` int(10) unsigned DEFAULT NULL,
`FailedPasswordAnswerAttemptWindowStart` datetime DEFAULT NULL,
PRIMARY KEY (`userId`)
) COMMENT='2';

CREATE TABLE `my_aspnet_profiles` (
`userId` int(11) NOT NULL,
`valueindex` longtext,
`stringdata` longtext,
`binarydata` longblob,
`lastUpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`userId`)
) ;


CREATE TABLE `my_aspnet_roles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`applicationId` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ROW_FORMAT=DYNAMIC;

CREATE TABLE `my_aspnet_schemaversion` (
`version` int(11) DEFAULT NULL
) ;

CREATE TABLE `my_aspnet_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`applicationId` int(11) NOT NULL,
`name` varchar(256) NOT NULL,
`isAnonymous` tinyint(1) NOT NULL DEFAULT '1',
`lastActivityDate` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

CREATE TABLE `my_aspnet_usersinroles` (
`userId` int(11) NOT NULL DEFAULT '0',
`roleId` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`userId`,`roleId`)
) ROW_FORMAT=DYNAMIC;

INSERT my_aspnet_SchemaVersion (version) VALUES (4);


Finally, you can use the ASP.NET Configuration website to configure users. To get only the providers working, it took a whole blog post. The .NET connector for MySQL is still a little bit rough on the edges, but after figuring the above things out, it will work!

Combining XML documents into one big one with XInclude

I needed to figure out how to include transparently some xml documents into another xml last week. We store our site navigation in one XML, but due to a decentralized management of this file, I need to cut this navigation into different file parts.

For this, the W3C XInlude standard was developed. Unfortunately, there is still no support for this in the .NET Framework. The solution is the use the Mvp.Xml library from codeplex, which includes XInclude support. A strong point of XInclude is the fallback possibilities of this standard, so when the included file isn't present, the output is predictable.

With XInclude, a XML fragment can be included from the original document:

<topic name="External Topic" path="/external-topic" id="18442">
<xi:include href="../../external-topic/navigation.xml"
xmlns:xi="http://www.w3.org/2003/XInclude"
xpointer="xpointer(/navigation/*)">
<xi:fallback>
<subject name="Fallback Subject" pad="/external-topic/FB"
id="19672" />
</xi:fallback>
</xi:include>
</topic>


Where the included XML fragment will be:

<?xml version="1.0"?>
<navigation name="External Topic" path="/external-topic"
id="18442" xmlns:xi="http://www.w3.org/2001/XInclude">
<subject name="Poll" path="/external-topic/poll" id="19670" />
<subject name="Text" path="/external-topic/text" id="19671" />
<subject name="Links" path="/external-topic/links id="19672" />
</navigation>

While XInclude describes the format of the XML document, we still need a XInclude aware XmlReader. There is one within the Mvp.Xml package. The old XML reading code:

mXmlDoc = new XmlDocument();
mXmlDoc.Load(mXmlFile.FullName);

should be changed to:

mXmlDoc = new XmlDocument();
using (XmlReader reader = new XmlIncludingReader(mXmlFile.FullName))
{
mXmlDoc.Load(reader);
}

The resulting mXmlDoc document representations are equal in both situations. Only the XInclude result includes extra xml:base attributes.

The resulting XML is:

<code style="xml">
<topic name="External Topic" path="/external-topic" id="18442">
<subject name="Poll" path="/external-topic/poll" id="19670" xml:base="../../external-topic/navigation.xml" />
<subject name="Text" path="/external-topic/text" id="19671" xml:base="../../external-topic/navigation.xml" />
<subject name="Links" path="/external-topic/links id="19672" xml:base="../../external-topic/navigation.xml" />
</topic>

These xml:base attributes can be used to add extra CacheDependencies for the storage of the resulting XML document to the application cache, so the cache will be invalidated as one of the included XML documents change.