MySQL

To create a connection to MySQL from MVC project

Prerequisites

• Entity Framework 6 or higher (MySQL Enforces 6.0.0 for Test Project)
• MySql.Data.Entities 6.8.3.0

Step by Step

1. Create a new MVC Project with Unit Tests
2. In Package Manager Console

   Install-Package EntityFramework -Version 6.0.0

3. In Package Manager (not Console), Add Entity Framework to Test project as well
4. In Package Manager under Online Tab Search MySQL
5. Install MySql.Data.Entities to both test and real projects (I used v 6.8.3.0)
6. Add Connection String to Web.Config and it look like below
7. Create some ordinary models
8. Set up the Database Context
   1. Create a DAL folder
   2. Craete a Context File (MyContext.cs) see context sample below
   3. Create a Initializer File (MyInit.cs) see Initializer sample below
9. To see it's working invoke database instance from the controller see Controller sample below

Web.Config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
      <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </configSections>
    <connectionStrings>
      <add name="MyDbContextConnectionString" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;UserId=root;Password=C1pralex;database=myDatabase;CharSet=utf8;Persist Security Info=True"/>
  </connectionStrings>
  <appSettings>
  </appSettings>
  <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 invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <system.data>
      <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <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.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
</configuration>

Model Sample

using System;
using System.Linq;
using System.Web;

namespace MySQLUsage.Models
{
    public class MyModel
    {
        public int ID { get; set; }
        public string LastName { get; set; }
        public DateTime EnrollmentDate { get; set; }
    }
}

Context Sample

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Web;
using MySQLUsage.Models;

namespace MySQLUsage.DAL
{
    [DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
    public class MyContext : DbContext
    {
         public MyContext()
            : base("MyDbContextConnectionString")
        {
            Database.SetInitializer<MyContext>(new MyInit());
        }

        public DbSet<MyModel> Tests { get; set; }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }
    }
}

Initializer Sample

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using MySQLUsage.Models;

namespace MySQLUsage.DAL
{
    public class MyInit : DropCreateDatabaseAlways<MyContext>
    {
       protected override void Seed(MyContext context)
        {
            // create 3 students to seed the database
            context.Tests.Add(new MyModel { ID = 1,  LastName = "Richards", EnrollmentDate = DateTime.Now });
            context.Tests.Add(new MyModel { ID = 2, LastName = "Allen", EnrollmentDate = DateTime.Now });
            base.Seed(context);
        }
    }
}

Controller Sample

 public class HomeController : Controller
    {
        private MyContext db = new MyContext();
        public ActionResult Index()
        {
            var lastn = db.Tests.FirstOrDefault().LastName;
            return View();
        }