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.
- Create a new MVC project in Visual Studio. In this example, the project name is MvcMySQLApp.
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.- 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.
- In the Entity Data Model wizard, select the option 'EF Designer from database'. The Entity Data Model wizard is displayed.
- Click New Connection. Select CData MySQL Data Source in the dialog that is displayed.
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;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.
- 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.
- 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.
- In Solution Explorer, right-click the controllers folder and click Add -> Controller. Select MVC 5 Controller with views, using Entity Framework.
- 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.
- Leave the default values for the other fields.
You can now access the list of Orders records at //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.
Introduction
We know how to use Code First Migration in SQL Server. But in most cases, a customer will think we can use it for the open source database. So that’s the reason we pick the “MySQL” database, and we can follow the same steps we follow in the “SQL” database. In this article, we are going to explain Code First Migration in ASP.NET MVC 5 with Entity FrameWork and MySQL.
Prerequisites
- MySQL Installer
- MySQL Workbench
- Visual Studio [ We are using Visual Studio 2017 Community Edition ].
Create a Web Application using MVC 5
Click File -> New -> Project -> Visual C# -> Web -> ASP.Net Web Application [ .NET Framework ].
Click on “OK” then click on “MVC”.
Install Entity Framework & MySQL Entity
Go to Visual Studio “Tools -> NuGet Package Manager -> Manage Nuget Packages for Solution” or right-click your web application and click “Manage NuGet Packages”.
EntityFramework
Search EntityFramework in the “Browse” Section.
MySql.Data.Entity
Search MySql.Data.Entity in the “Browse” Section.
Once we installed EntityFramework and MySql Entity in our application it will generate a SQL and MySQL Provider inside the EntityFramework Section in Web.Config.
Model Class
We just created a sample model class for demo purposes.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebAppWithMySql.Models
{
public class
Student
{
public int
Id { get;
set; }
public string
Name { get;
set; }
public string
Password { get;
set; }
}
}
Creation of DBContext
Create a dbcontext class in our application. The following dbcontext will point out our connection string in WebConfig.
using MySql.Data.Entity;
using System.Data.Entity;
using WebAppWithMySql.Models;
namespace WebAppWithMySql
{
[DbConfigurationType[typeof[MySqlEFConfiguration]]]
public class
WebAppContext : DbContext
{
public DbSet Products
{
get;
set;
}
public WebAppContext[]
//Reference the name of your connection string [ WebAppCon ]
:
base["WebAppCon"] { }
}
}
Connection String
We added the same connection string name that we added in the dbcontext class. The following connection string represents “MySql” Db.
Migration Steps
Go to Visual Studio "Tools -> NuGet Package Manager -> Package Manager Console". Then execute the following command.
- Enable-Migrations – [ We need to enable the migration, only then can we do the EF Code First Migration ].
- Add-Migration IntialDb [migration name] – [ Add a migration name and run the command ].
- Update-Database -Verbose — if it is successful then we can see this message [Running Seed method].
Once Migration is done, we can see that the respective files are auto-generated under the “Migrations” folder.
Output
Reference
- Code First Migration - ASP.NET Core MVC With EntityFrameWork Core
Summary
In this article, we explained Code First Migration in ASP.NET MVC 5 with Entity Framework and MySQL. Hope this article is useful for all Azure beginners.
See Also
It's recommended to read more articles related to ASP.NET Core & Azure App Service.
- ASP.NET CORE 1.0: Getting Started
- ASP.NET Core 1.0: Project Layout
- ASP.NET Core 1.0: Middleware And Static files [Part 1]
- Middleware And Staticfiles In ASP.NET Core 1.0 - Part Two
- ASP.NET Core 1.0 Configuration: Aurelia Single Page Applications
- ASP.NET Core 1.0: Create An Aurelia Single Page Application
- Create Rest API Or Web API With ASP.NET Core 1.0
- ASP.NET Core 1.0: Adding A Configuration Source File
- Code First Migration - ASP.NET Core MVC With EntityFrameWork Core
- Building ASP.NET Core MVC Application Using EF Core and ASP.NET Core 1.0
- Send Email Using ASP.NET CORE 1.1 With MailKit In Visual Studio 2017
- ASP.NET Core And MVC Core: Session State
- Startup Page In ASP.NET Core
- Sending SMS Using ASP.NET Core With Twilio SMS API
- Create And Deploy An ASP.NET Core Web App In Azure
- Chat Bot with Azure Bot Service
- Channel Configuration - Azure Bot Service To Slack Application