ASP.NET:Setup a MVC5 website with MySQL, Entity Framework 6 Code-first and VS2013

The new features available in EF6 allow any developer to build a simple db-powered website with very few lines of code. There is many tutorials explaining how to does that with SQL Express available on the web, but those who want (or is force d) To use MySQL would most likely find a quite smaller amount of literature. I wrote this simple guide for those who asked me to summarize all the required steps to build a mysql-powered MV5 website Using Visual Studio and Entity Framework 6. For the first steps I ' m gonna quote the old good Getting started with Entity Framework 6 Code first using MVC5 available O N Www.asp.net website, which also holds the credits for the images I used.

Let's start with a list of everything we ' re gonna need (and their free download links):

• Visual Studio, downloadable here (Free 90-days Trial Edition).
• The. NET Framework 4.5 or higher, downloadable here.
• Entity Framework 6 or higher, available on NuGet platform.
• MySQL Server 5.6 or higher, downloadable from the official site, or a free or paid access to a remotely-hosted MySQL data Base service of your choice.
• MySQL connector.net 6.9.4 or higher, downloadable from the official site or using NuGet.

Step 1. Creating a new Web application

Let's start Visual Studio and create a new C # project just like that:

In the following screens we ' re gonna choose the MVC template, then we ' re-click to the Change Authentication ... button where W E can choose if we want to enable authentication mechanism or not. The answer here depends on the features we need to has the website we ' re building:for This example we don't need any Authentication system, so we can choose No Authentication.

The next steps is kinda obvious, we just has to click a couple OKs to end the Web application creation phase.

Step 2. Installing the Entity Framework

From the Tools menu, select Library Package Manager and then Package Manager Console. Insert the following command:

> Install-package entityframework

NuGet would automatically download and install the most recent version of the Entity Framework (currently 6.1.1). As soon as the tasks are completed we can start creating our entities following the Code-first approach.

Step 3. Creating the Entity Classes

It's worth to mention that's an Entity isn ' t anything more than a class designed to hold all the relevant fields AB Out a single element (i.e row) of our Database. That's why before we start creating entities We definitely need to has a decent idea on what we need and, MOS T importantly, which kind of relationship our elements is gonna have. For the example let's just flush out an evergreen classic:the student-enrollment-course Archive. Here's what we ' re gonna has:

• A list of Students
• Signing up to Zero-or-more enrollments
• Related to zero-or more Courses

In order to build such a model we need to open the/models/folder (creating it if it doesn ' t exists) and add the Followin G Three classes:

usingSystem;usingSystem.Collections.Generic;namespacemyapplication.models{ Public classStudent { Public intID {Get;Set; }  Public stringLastName {Get;Set; }  PublicDateTime EnrollmentDate {Get;Set; }  Public Virtualicollection<enrollment> Enrollments {Get;Set; } }}

usingSystem;namespacemyapplication.models{ Public classEnrollment { Public intEnrollmentid {Get;Set; }  Public intCourseID {Get;Set; }  Public intStudentID {Get;Set; } [Crayon-567743939cb3f039488039 inline="true" ]         Public VirtualCourse Course {Get;Set; }  Public VirtualStudent Student {Get;Set; } }}    

usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations.Schema;namespacemyapplication.models{ Public classCourse {[databasegenerated (databasegeneratedoption.none)] Public intCourseID {Get;Set; }  Public stringTitle {Get;Set; }  Public intCredits {Get;Set; }  Public Virtualicollection<enrollment> Enrollments {Get;Set; } }}

Step 4. Setting up the Database Context

The main class who handles and keeps track of the various Entity Framework, operations is known as Database Context. It can be created by adding a new class, deriving it from System.Data.Entity.DbContext and inserting some properties for a ll the entities we just created, who'll populate the Data Model. Let's move to The/dal/folder (for Data-access-layer), creating it if it doesn ' t exist already, and add the following Classes

usingMyapplication.models;usingSystem.Data.Entity;usingSystem.Data.Entity.ModelConfiguration.Conventions;namespacemyapplication.dal{ Public classMydbcontext:dbcontext { PublicMydbcontext ():Base("mydbcontextconnectionstring") {Database.setinitializer<MyDbContext> (NewMydbinitializer ()); }                 PublicDbset<student> Students {Get;Set; }  Publicdbset<enrollment> Enrollments {Get;Set; }  PublicDbset<course> Courses {Get;Set; } protected Override voidonmodelcreating (Dbmodelbuilder modelBuilder) {modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); }    }}

 Public classMydbinitializer:createdatabaseifnotexists<mydbcontext>{    protected Override voidSeed (Mydbcontext context) {//Create 3 students to seed the databaseContext. Students.add (NewStudent {ID =1, FirstName ="Mark", LastName ="Richards", enrollmentdate =DateTime.Now}); Context. Students.add (NewStudent {ID =2, FirstName ="Paula", LastName ="Allen", enrollmentdate =DateTime.Now}); Context. Students.add (NewStudent {ID =3, FirstName ="Tom", LastName ="Hoover", enrollmentdate =DateTime.Now}); Base.    Seed (context); }}

We can see, the MyDbContext.cs class contains a DbSet for each Entity we previously created. That's because in the Entity Framework pattern each DbSet was corresponding to a Database Table and while the entities was the Table records (or rows). We can also see this in the constructor initializer we ' re instantiating a object of type Mydbinitializer, which is Defin Ed in the following class:we need this object to initialize the database the first time we launch the application and ALS O To insert some sample record in the newly created Student table. We can derive the Mydbinitializer from a number of initialization base class made available by the framework, such as:

2. Createdatabaseifnotexists: this is the default Initialized:as the name suggests, it creates the database if it D OESN ' t exist already. It's worth to say so if the DB is already present and it's Model differs from the one specified by the entities Itializer would throw an exception. This behaviour makes it a optimal choice for the production environments   (where you don ' t want the DB to Be altered by the EF6 automatic tasks) and not-so-great for the development ones (where the  Database Model &n bsp;frequently changes).
4. Dropcreatedatabaseifmodelchanges: this initializer Creates a new database, deleting the previous one (if any), Ev Erytime The datamodel is changed since the last initialization. It's worth to mention this a Datamodel "change" everytime one or More entity class are added, removed or modified  in any. This is a ideal behavior for development and testing environments, where we usually want to reset/update the DB ever Y time we need to change the the the-want to store data:for These exact same reasons it should never is used in any produc tion environment in order to avoid data-losses  (a small change in One entity class was all it would take to E Rase all data in the DB).
6. Dropcreatedatabasealways:as The name suggests, this initializer deletes and re-creates the DB on every initialization, I. E. Every time the Web application would start. This might is good for those development (or testing) environments designed to work with the same, pre-defined amount of D ATA every time:needless to say, it's far from ideal in any production environment.

Before going on, notice this mydbcontextconnectionstring literal referenced by the constructor initializer of our Mydbcont Ext.cs Class:this is the connection string we'll add to our web. config during the next step.

Step 5. Connecting to MySQL

To connect our web application to our MySQL database we need the MySQL connector.net, which can is downloaded from the off Icial site or using NuGet. All we need to do are to add their libraries to our project and add a valid connection string to our web. config file, just Li Ke that:

<connectionStrings>    <add name="mydbcontextconnectionstring" providerName ="MySql.Data.MySqlClient" connectionstring="server=localhost; Userid=username; Password=password;database=mydatabase; Charset=utf8; Persist Security info=true"/></connectionstrings>

Step 6. Running the application and (auto) creating the Database

If all the previous steps has been properly completed the Web application would automatically create the database as soon As an object of type Mydbcontext would be instantiated. For example:

Private New Mydbcontext ();

You can initialize the object inside a Controller with a singleton class (or in the Global.asax) as a static property or in Other parts of the Web application depending on developer needs and/or the Choosen design pattern and/or Ioc/uo W strategies might want to adopt. In the upcoming posts I'll talk more about these techniques, as well as explain other useful EF concepts, capabilities &am P Tools such as Data migrations and more.

http://www.ryadel.com/en/asp-net-setup-mvc5-website-mysql-entity-framework-6-code-first-vs2013/

ASP.NET:Setup a MVC5 website with MySQL, Entity Framework 6 Code-first and VS2013