Connecting an ASP.NET MVC Application to MySQL

In this tutorial you are going to learn how to connect an ASP.NET MVC application to MySQL. We will also be creating a CRUD application that utilizes MySQL as data source and Entity Framework as object mapper. So let’s get started!

  1. Download and Install MySQL Installer from this link:
    • After installation, open MySQL Installer then download the following softwares:
      1. MySQL Server (v5.5 or latest)
      2. MySQL WorkBench
      3. MySQL For Visual Studio
      4. MySQL Connect/NET
    • After installation of above 4 softwares, make sure to verify by checking Programs & Features under Control Panel. Refer to the image bellow.
  2. Create a project. Go to Visual Studio -> File -> New -> Project.
    • From the pop-up, make sure the following are selected. Click the “Ok” button when you’re done.
    • In the next screen, make sure to select MVC. Click “Ok” button when you’re done.
  3. After the project is successfully created, we now add reference to 3 DLL files from the MySQL Connector directory:
    • Go to the Solution Explorer then right click on Reference then click Add Reference. Refer to the screenshot bellow:
    • A window will pop-up. The the left side pain, click on “Browse” then locate for the 3 DLL files as seen from the screenshot bellow. I have included the path to where you can find them. When you’re done finding the files, make sure to check all 3 files before clicking the “Ok” button. Refer to the screenshot bellow:
    • Alright now we’re done with the basic setup. Let us now proceed with building the CRUD Application!
  4. Install Entity Framework for the project using Nuget Package manager. Follow the screenshot bellow:
    • On the Nuget Package Manager window, click on “Browse” then search for “entity framework”. Select EntityFramework from the results then on the right side pain check the box of your application then click on the “Install” button. Refer to the screenshot bellow:
  5. Open MySQL Workbench then create a new schema, name it anything you want. Then inside the schema create a new table called “employees” with the following columns:
  6. Go back to Visual Studio then from the Solutions Explorer, right click on the Model -> Add -> Class. Refer to the image bellow:
    • A new window will pop-up. Select Data from the left pain. From the center select ADO.NET Entity Data Model. Enter name “EmployeeModel“. Then click “Ok” button. Refer to the screenshot bellow:
    • On the next window select EF Designer from Database then click “Next” button. Refer to screenshot bellow:
    • On the next screen, click on New Connection button, then on the pop-up click on the “Change” button then select “MySQL Database” option from the list then click “Ok” button.  Refer to the screenshot bellow:
        • Note that the MySQL Database option will not be available if you have not installed the MySQL Connect/NET software.
        • If you are sure that you have installed MySQL Connect/NET and you are still not able to see the MySQL Database option, try installing an older version of MySQL Connect/Net which you can get from here:
    • Enter the credentials for your MySQL host and select the database to connect to. Make sure to also click “Test Connection” button to see if passes before clicking the “Ok” button  Refer to screenshot bellow:
    • In the next screen, select the checkbox “Yes, include sensitive data in the connection string” then leave everything else as is. Refer to the screenshot bellow:
      • If you click next button and you’re getting the following error: “Your project references the latest version of Entity Framework; however, an Entity Framework database provider compatible with this version could not be found for your data connection. if you have already installed a have already installed a comptible provider, ensure you have rebuilt your project before performing this action. Otherwise, exit this wizard, install a compatible provider, and rebuid your project before performing this action.”
      • Follow the fix here:
    • In the next screen, check the employee table to include to the application.  Click “Finish” button when you’re done.  Refer to the screenshot bellow:
    • If everything went successfully, you will see a screen similar to bellow:
  7. Ok now we’re on the final step. We are going to build the CRUD application that will interact with our MySQL database. To make things simpler, we’re going to make use of a “scaffold”.  Note: Rebuild the project before proceeding.
    • From the “Solution Explorer” right click on the “Controller” folder then select “Add -> “New Scaffold Item… Refer to the screenshot bellow:
    • In the middle, select “MVC 5 Controller with views, using Entity Framework”, then click “Add” button. Refer to the screenshot bellow:
    • In the next window, you’ll be prompted to Add a Controller. For the Model Class, select the model we created as well as the Data context class that was generated earlier. Make sure to uncheck “Use a layout page”. For the controller name it must follow the formatting: [NAME_OF_CONTROLLER]Controller with no spaces. Refer to the screenshot bellow:
  8. We’re done!, now Run the application and visit http://localhost:__PORT_HERE__/Employee

Were you able to get it working? let me know on the comments section bellow.