Sunday, July 13, 2008

ASP.NET Provider Model Support in MySQL

After a long googling, I found that ASP.NET Membership Provider seems to be a good architectural choice for my project at the faculty. Microsoft prepared the providers for SQL Server out of the box. But I need my data store to be MySQL server. So something is needed to be done.

I first downloaded and installed MySQL Connector/Net 5.2.2. The installer also configured the machine.config file on my system too.

After that, I configured my project's web.config file to override the default connection string.

<connectionStrings> <remove name="LocalMySqlServer"/> <add
name="LocalMySqlServer"
connectionString="Datasource=localhost;Database=mydb;uid=root;pwd=root;"
providerName="MySql.Data.MySqlClient"/> </connectionStrings>

Then, I loaded the ASP.NET Web Site Administration Tool from Visual Studio 2008 and click on the Provider Configuration / Select a different provider for each feature (advanced) links to config MySQL providers with my project.

After the providers are configured, I went to the Security page but it provide me with error message.
Unable to initialize provider. Missing or incorrect schema.
I found out later that I need to create the database before MySQL connector can generated the schema. This is easily done by using MySQL's create database mydb command. Everything works fine in the end. All tables are generated.
mysql> show tables
-> ;
+-------------------------+
| Tables_in_mydb |
+-------------------------+
| my_aspnet_applications |
| my_aspnet_membership |
| my_aspnet_profiles |
| my_aspnet_roles |
| my_aspnet_schemaversion |
| my_aspnet_users |
| my_aspnet_usersinroles |
+-------------------------+
7 rows in set (0.00 sec)

Hope this helps people who are struggling with this problem :)

Other helpful blog on the topic:

1 comment:

Wiennat said...

I implemented my own provider using Active Record. :p