Introduction
I used Visual Studio 2013 to develop the sample code. The application framework used is .NET 4.5.
The database used is MySQL. I downloaded and installed MySQL Installer 5.6.19 which can be found here.
This installs all the necessary files including the MySQL version 1.1.4 connector. You can even select MySql from Server Explorer -> Data Connections -> Right click on Add Connection:
Figure 1
Once you have downloaded the sample project (VS2013MySql), unzip it, open the folder and click on VS2013MySql.sln to open it in Visual Studio 2013. You will have to make changes to the connection string in Web.config to run it in your own MySql environment.
For developers familiar with Visual Studio (2012 and 2013), it is fairly easy to work using the already set-up LocalDB for development. A developer can then move easily to MS SQL Server for production.
This article is for users that want to develop their ASP.NET web application using MySQL.
Background
Sometimes, a developer may want to use a free open-sourced database to cut costs involved in using other databases e.g. MS SQL Server, Oracle DB etc. Or maybe a developer might have a client that specifically needs work done using mySql. Whichever the reason, the following will help you get set up.
Using the code
Create a new project in VS 2013. Select ASP.NET Web Application. At the top, select .NET framework 4.5.1, name your application then click next.
On the next screen, select Web Forms template (this article is based on a webforms application. However the steps for setting up MySQL for a MVC application are similar) and leave authentication to Individual User Accounts. Click "OK" to initialize the application for development.
In your Solution Explorer, right click on "References" then click on "Add Reference". The MySql installer I stated above should have installed some important .MySql files on your computer. We shall select 3 of them to add to our references:
In the Reference Manager, go to Assemblies -> Extensions and click on: MySql.Data, MySql.Data.Entity.EF6 and MySql.Web. (MySql.Data and MySql.Web are versions 6.8.3.0)
Your references should now look like this:
Figure 2
The next step is to open the Web.config file and make the following changes. (The downloadable solution should guide you)
1. Change the connection string:
<connectionStrings>
<add name="DefaultConnection" connectionString="server=localhost;User Id=root;password=password;Persist Security Info=True;database=aspmysql"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
Change the server name, Id, password and databases accordingly. To easily get the connection string needed, select MySql from Server Explorer -> Data Connections -> Right click on Add Connection:
Enter the details as needed, select dtabase and test connection. Then click "OK". In server explorer, when you click on the added database, you should see the connection string under the properties window on the bottom right of your Visual Studio screen.
2. Change membership:
<membership defaultProvider="MySqlMembershipProvider">
<providers>
<clear />
<add name="MySqlMembershipProvider"
type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web"
connectionStringName="DefaultConnection"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="false"
requiresUniqueEmail="true"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0"
passwordAttemptWindow="10"
applicationName="/"
autogenerateschema="true" />
</providers>
</membership>
3. Change profile:
<profile>
<providers>
<clear />
<add type="MySql.Web.Security.MySqlProfileProvider, MySql.Web"
name="MySqlProfileProvider" applicationName="/"
connectionStringName="DefaultConnection"
autogenerateschema="true" />
</providers>
</profile>
4. Change role manager:
<roleManager enabled="true" defaultProvider="MySqlRoleProvider">
<providers>
<clear />
<add connectionStringName="DefaultConnection"
applicationName="/" name="MySqlRoleProvider"
type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
autogenerateschema="true" />
</providers>
</roleManager>
5. Change old Entity Framework to:
<entityFramework>
<providers>
<provider invariantName="MySql.Data.MySqlClient"
type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient"></remove>
<add name="MySQL Data Provider"
invariant="MySql.Data.MySqlClient"
description=".Net Framework Data Provider for MySQL"
type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
We shall now enable migrations.
Go to the menu item View -> Other Windows -> Click on "Package Manager Console". Enter "enable-migrations" in the console prompt and hit enter.
Figure 3
A new folder, Migrations will be placed in your solution (check solution explorer) together with a new file named "Configuration.cs".
Entity Framework Code First uses Migration History to keep track of model changes and ensure consistency between the database and conceptual schemas. The Migration History table, __migrationhistory, has a primary key that is too large for MySql.
The fix:
Under the migration folder, add a new class named MySqlHistoryContext.cs and add the following code:
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations.History;
namespace VS2013MySql.Migrations
{
public class MySqlHistoryContext : HistoryContext
{
public MySqlHistoryContext(DbConnection connection, string defaultSchema)
: base(connection, defaultSchema)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();
}
}
}
Under the same migration folder, make changes to the Configuration.cs file to look like this:
namespace VS2013MySql.Migrations
{
using System.Data.Entity.Migrations;
internal sealed class Configuration : DbMigrationsConfiguration<VS2013MySql.Models.ApplicationDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
SetHistoryContextFactory("MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema));
}
protected override void Seed(VS2013MySql.Models.ApplicationDbContext context)
{
}
}
}
We now need to create a custome database initializer since the MySQL provider does not support Entity Framework migrations.
Add a new class file named MySqlInitializer.cs to the project, and change its code to this:
using VS2013MySql.Models;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
namespace VS2013MySql
{
public class MySqlInitializer : IDatabaseInitializer<ApplicationDbContext>
{
public void InitializeDatabase(ApplicationDbContext context)
{
if (!context.Database.Exists())
{
context.Database.Create();
}
else
{
var migrationHistoryTableExists = ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery<int>(
string.Format(
"SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '{0}' AND table_name = '__MigrationHistory'",
"aspmysql"));
if (migrationHistoryTableExists.FirstOrDefault() == 0)
{
context.Database.Delete();
context.Database.Create();
}
}
}
}
}
The above file on line 24 (for my project): Change "aspmysql" to the name of the MySql database you are using.
In my solution explorer, the 3 files look like this:
Figure 4
Under the Models folder in solution explorer, change the IdentityModel.cs file code to look like this:
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.Owin.Security;
using System.Web;
using System;
using VS2013MySql.Models;
using System.Data.Entity;
namespace VS2013MySql.Models
{
public class ApplicationUser : IdentityUser
{
}
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
: base("DefaultConnection", throwIfV1Schema: false)
{
Database.SetInitializer(new MySqlInitializer());
}
}
Leave the Helpers section as is.
Guess what? We are done!
To test our application, press Ctrl + F5 to build and run.
Then click the Register tab on the top-right of the page and register a new user. You should then see "Welcome @user!" on the navigation menu.
Now, if you go to MySql workbench (I use SQLyog), you should see the newly created tables:
Figure 5
If you view the data in the "aspnetusers" table, you will see the information for your newly created user:
Figure 6
I put up this together from bits and pieces of code available on developer forums. This tutorial helped a lot even thought they show a MySql connection to Azure. I hope it may be of help to some.
