Switching from SQL Server to MySQL in an .NET MVC app

During the course of building a .NET MVC app I switched from using SQL Server to a MySQL. The hosting provider I use for my personal web projects, Godaddy, allows up to ten MySQL databases to be created, compared to just three SQL Server databases under the basic Windows hosting plan. I started in my MVC app with a SQL Server localDB instance. I used a SQL script generated in Visual Studio and modified the syntax to conform to MySQL standards. I then ran the script in the phpAdmin of my newly created MySQL database on Godaddy. There wasn’t much data so I was able to get away with just using a modified SQL script. I’m not going to get into how I modified the SQL script to fit the MySQL format since that alone could be an entire blog post. The MySQL database was created and now populated with data. The next part was to change the database connection in the MVC application.

Here were the steps:
1. Download and install MySQL Workbench including the MySQL for Visual Studio, MySQL .NET Connector, and MySQL Server.
2. Open the .NET MVC application in Visual Studio. I’m using the 2015 Community version with NET 4.5, and .NET MVC 5. Right-click on ‘References’ and select ‘add Reference’. Navigate to the Connector.NET folder: C:\Program Files(x86)\MySQL\Connector.NET 6.9\Assemblies\v4.5\ and add the MySql.Data.Entity.EF6.dll and MySql.Data.dll. Your file path might differ.
3. Open the main web.config, change the attribute of the provider element (highlighted in orange) in the the entityFramework section to MySQL Entity Framework 6:
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"></provider>
</providers>
</entityFramework>

4. Below the entityFramework section add a MySQL database factory provider element inside a system.data section:
<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.9.8.0"/>
</DbProviderFactories>
</system.data>

5. Add a new database connection in the connectionStrings section to connect to the MySQL database:
<connectionStrings>
<add name="DefaultConnection" connectionString="server=[ server address ];user id=[ username ];password=[ password ];database=[ database name ]" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

If using an older version of Visual Studio, the version of Entity Framework will probably be different. The stated EF version in web.config must match the version being used in the application otherwise an error will be thrown during build. If you referenced the database connection string in an controller make sure to change it to the new name.

Matthew Dailey

Web developer, photographer, and Photoshop user.