How to connect mysql database in mvc asp net

This article shows how to use only the Entity Framework and the CData ADO.NET provider to access MySQL from an ASP.NET MVC application.

This article shows how to use wizards in Visual Studio to drop the CData ADO.NET Provider for MySQL into a simple MVC (model, view, controller) project.

Create the Entity Framework Model

Follow the steps below to save connection properties and map tables to entities in the data model.

1. Create a new MVC project in Visual Studio. In this example, the project name is MvcMySQLApp.

2. If you are using Entity Framework 6, you will need to take the preliminary step of registering the MySQL Entity Framework provider for your project. See the "LINQ and Entity Framework" chapter in the help documentation for a guide.

   Note that MVC 3 scaffolding and MVC 4 scaffolding do not support Entity Framework 6. You can use your scaffolding with Entity Framework 6 by upgrading to the latest version of MVC.
3. To add the .edmx file from the designer, right-click your Models folder and click Add New Item. Select ADO.NET Entity Data Model, name the model, and click Add. In this example, the name of the model is MySQLModel.
4. In the Entity Data Model wizard, select the option 'EF Designer from database'. The Entity Data Model wizard is displayed.
5. Click New Connection. Select CData MySQL Data Source in the dialog that is displayed.

6. Specify the required connection string properties.

   The Server and Port properties must be set to a MySQL server. If IntegratedSecurity is set to false, then User and Password must be set to valid user credentials. Optionally, Database can be set to connect to a specific database. If not set, tables from all databases will be returned.

   A typical connection string is below:

   User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;
   

7. Name the connection and select whether to include sensitive information, such as connection credentials, in the connection string. For simplicity, this example saves sensitive information in Web.config. The connection settings are saved as MySQLEntities.

   
8. Select the tables and views you need. In this example, Orders is imported. Also, the option to pluralize object names is deselected in this example. Click Finish to create the .edmx file.
   
9. Build your project to complete this step.

Scaffold the Controller and Views

After creating the model and building the project, you can use ASP.NET Scaffolding wizards to create the controller and the views.

1. In Solution Explorer, right-click the controllers folder and click Add -> Controller. Select MVC 5 Controller with views, using Entity Framework.
2. In the Add Controller dialog that is then displayed, select the following options:
   • Model class: Select a table you imported; for example, Orders.
   • Data context class: Select your context class.
3. Leave the default values for the other fields.
   

You can now access the list of Orders records at http://MySite/Orders. Next to each record are links to edit, delete, and see more information. You can also create new Orders records. With every state change the site picks up any data changes.

So last week one of my colleges came to me and asked for how to use code first migrations with mysql databases. I haven’t really worked with mysql together with .net so I said I will look into it and answer later. So, on my quest to figure out how to do it, this is what I found.

First, let’s go ahead and create a ASP.NET MVC 5 Wep Application on Visual Studio. I’m sure all of you know how to do it, but just in case I’m gonna explain it anyway. (I’m using Visual Studio 2017)

Go to File — > New — > Project.

Select ASP.NET Web Application (.NET Framework). I will name it as MySQLCodeFirst, you may use your desired name. And note that I’m on .NET Framework 4.6.1

In the next prompt select MVC from the list of templates, and if you want to make the Identity framework to take care about user accounts go ahead and add it too by clicking on “Change Authentication” button and selecting “Individual User Accounts”

Once you are done, click OK and let the VS to create the solution.

Now we need a nuget package for MySQL called MySql.Data.Entity.EF6 but before that you need to have MySQL connector for .NET to be installed on your computer. You can download it from this web site. https://dev.mysql.com/downloads/connector/net/8.0.html

Now right click on the project from Solution Explorer, go to Manage Nuget Packages. Search for “mysql.data.entity”.

Install MySql.Data.Entity package by oracle. Once it is done it should appear in the providers under entity framework in Web.config file.

<entityFramework><defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"><parameters><parameter value="mssqllocaldb" /></parameters></defaultConnectionFactory><providers><provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /><provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.10.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider></providers></entityFramework>

Open up phpMyAdmin and setup a new empty database.

After that add the connection string to the Web.config. It should be added to the <configuration><configuration/> tag. Make sure that the <connectionStrings> element is the second element(child) under <configuration>. First child should be <configSections> which will be there by default.

<configuration>
     <configSections>          <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->          <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="DefaultConnection" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;userid=root;password=;database=testdatabase;persistsecurityinfo=True" />    </connectionStrings>
    .
    .
    .
    .
</configuration>

My full Web.config file can be found here.

Now you need to have your model classes to migrate them to the database. So, let’s go ahead and create one model class. Right click on Models -> Add -> Class

I have added a class named User. Then I will add following properties to user. You may do as you desire.

using System;using System.Collections.Generic;using System.Linq;using System.Web;namespace MySQLCodeFirst.Models{    public class User    {        public int Id { get; set; }        public string FirstName { get; set; }        public string LastName { get; set; }    }}

Now we need to create our Database Context class. Again right click on Models -> Add -> Class. I will name my Database Context class as DataContext. It should be a child class of DbContext super class. To refer DbContext you need to have System.Data.Entity namespace.

using System;using System.Collections.Generic;using System.Data.Entity;using System.Linq;using System.Web;namespace MySQLCodeFirst.Models{    public class DataContext : DbContext    {    }}

Copy following constructor to the DataContext class.

public DataContext()    : base("DefaultConnection") //This 'DefaultConnection' should be equal to the connection string name on Web.config.{    this.Configuration.ValidateOnSaveEnabled = false;}

You need to add DbConfigurationType decorator to the class, and to do that, you need to add MySql.Data.Entity namespace.

using MySql.Data.Entity;namespace MySQLCodeFirst.Models{    [DbConfigurationType(typeof(MySqlEFConfiguration))]
    public class DataContext : DbContext    {
    .
    .
    .
    .
    }

And inside the DataContext class, add a DbSet property to include your model class. Every other model class you create afterwards should have a DbSet property in DataContext Class.

public DbSet<User> Users { get; set; }

If you went with No Authentication when selecting the template, as in here,

your full DataContext class should look like this.

If you changed the Authentication to Individual User Accounts, then your DbContext will be ApplicationDbContext and it should reside in IdentityModels.cs file. Add [DbConfigurationType(typeof(MySqlEFConfiguration))] to ApplicationDbContext and OnModelCreating method as follows.

I have added an extra method OnModelCreating and some rules inside to make MySql complaint with entity framework rules. Here we are converting nvarchar(MSSQL Server) to varchar(MySql). If you did not do this, you might see an exception while creating the migration as,

Specified key was too long; max key length is 767 bytes

See this stackoverflow question for more details.

Now go to Tools -> NuGet Package Manager -> Package Manager Console. This will open up a console window. Type,

enable-migrations

You will notice there is a new folder named Migrations in the project.

Now execute the command,

add-migration UserTable

Here, you can specify the name of the migration after <space>. I have named it as “UserTable”. If you did everything correctly, new migration should open up.

Now you can run the command,

update-database

to persist the changes to the database. After that is done, go to the database and check if everything is there, including the __migrationhistory table.

Done!, now go ahead and create your model classes, relationships and run migrations.

Happy coding! :)

Can we connect asp net with MySQL?

How do I connect MySQL to dotnet?

How does Visual Studio 2019 MVC connect to database?

Can I use Entity Framework with MySQL?