How To: Guide to build an MVC 3 application using Entity Framework and Connector/Net

Overview

Entity Framework provides three ways to define the model of your entities. Database First which starts with a legacy database. Model First where a model in an XML designer is defined. Or by simply defining the classes that will let EF create a database for you.

In this very basic application we'll use the last approach described and we'll do some of the first steps when developing applications using MVC and MySQL databases.  

Requirements:

- MySQL Connector/Net 6.6.2 or later

- A running MySQL Server instance of any 5.5.x version

- Visual Studio 2010 or 2012

- A basic understanding of MVC, C# and Entity Framework.

Step 1: Create a New ASP.NET MVC 3 Application

Start by creating a new ASP.NET MVC 3 Web Application Project within Visual Studio 2012. 

Choose File >New Project and use the “ASP.NET MVC 3 Web Application” as shown bellow.

Select the template: Web Application. Razor engine is what we’ll use so just click OK.

Below is the structure of the application just right after you created it. We have the Account, Home and Shared folders for the corresponding Controllers and Views of the application.

The next step is creating the model which is the core of the application. We’ll create this model layer using Entity Framework 5 (the result would be the same using 4.3.1) which is the latest version at the moment of this tutorial.

Add the Entity Framework binaries to your project using the Package Manager Console in Visual Studio 2012. Select Tools >Library Package Manager >Package Manager Console. Type Install-Package EntityFramework.

Step 2: Create the Model

The application we’ll do is a basic Appointments Manager application to organize and create some Appointments. Each Appointment will have a location, a service, a representative and a customer. Later we’ll do a View to check the existing appointments for a specific date.

Select Models folder and from the context menu select Add > New class.

We need an Appointment class that will become the Appointment table. One for Location, Staff and Customer entities. In each one of these entities is necessary to use data validations as well as type related properties that we'll add using Data Annotations. If you haven’t used them or checked this subject we recommend you to dig a little bit into it so you can understand what we use here.

Model definition.

public abstract class Person

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

public int Id { get; set; }

[Required]

[MaxLength(255)]

public string Name { get; set; }

[MaxLength(255)]

public string Email { get; set; }

}

The Person abstract class is used to define some common properties between our entities: Customer and Staff.

public class Customer : Person

{

public string TelephoneNumber { get; set; }

}

public class Staff : Person

{

public string Services { get; set; }

public virtual ICollection<Appointment> Appointments { get; set; }

}

The definition of the Location class is the following:

public class Location

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

public int Id { get; set; }

[Required]

[MaxLength(255)]

public string Name { get; set; }

public string Address { get; set; }

public virtual ICollection<Appointment> Appointments { get; set; }

}

Class for the Appointment entity.

public class Appointment

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

public int Id { get; set; }

public string Description { get; set; }

[DisplayName("Location")]

[Required]

public int locationId { get; set; }

[DisplayName("Representative")]

[Required]

public int StaffId { get; set; }

[DisplayName("Customer")]

[Required]

public int CustomerId {get; set; }

[DisplayName("Date")]

[DataType(DataType.DateTime, ErrorMessage = "Date non valid.")]

[DisplayFormat(DataFormatString = "{0:MM/dd/yy}", ApplyFormatInEditMode = true)]

[Required]

public DateTime StartDate { get; set; }

[DisplayName("Time")]

[DataType(DataType.Time, ErrorMessage = "Time non valid.")]

[DisplayFormat(DataFormatString = "{0:t}")]

[Required]

public TimeSpan StartTime { get; set; }

[Required]

public int Duration { get; set; }

public virtual Staff StaffPerson { get; set; }

public virtual Location Location { get; set; }

public virtual Customer Customer { get; set; }

[DisplayName("Date")]

[DataType(DataType.DateTime, ErrorMessage = "Date non valid.")]

[DisplayFormat(DataFormatString = "{0:MM/dd/yy}", ApplyFormatInEditMode = true)]

public virtual DateTime? DateFilter { get; set; }

}

We’ll add an additional validation for the Start Time column by defining our own Validation Adapter (please check the DataAnnotationsModelValidatorProvider.RegisterAdapter() method's documentation ) to do the Client side validations.

public class DataTypeAttributeAdapter : DataAnnotationsModelValidator<DataTypeAttribute>

{

public DataTypeAttributeAdapter(ModelMetadata metadata, ControllerContext context, DataTypeAttribute attribute)

: base(metadata, context, attribute) { }

public override IEnumerable<ModelClientValidationRule> GetClientValidationRules()

{

Object[] validations = new Object[2];

if (Attribute.DataType == DataType.DateTime)

{

validations[0] = new ValidationDateRule(Attribute.FormatErrorMessage(Metadata.GetDisplayName()));

}

if (Attribute.DataType == DataType.Time)

{

validations[1] = new ValidationDateRule(Attribute.FormatErrorMessage(Metadata.GetDisplayName()));

}

return base.GetClientValidationRules();

}

}

public class ValidationDateRule : ModelClientValidationRule

{

public ValidationDateRule(string errorMessage)

{

ErrorMessage = errorMessage;

ValidationType = "datetime";

}

}

public class ValidationTimeRule : ModelClientValidationRule

{

public ValidationTimeRule(string errorMessage)

{

ErrorMessage = errorMessage;

ValidationType = "time";

}

}


This class has two methods that can be overridden to add some functionality. In this example we used the GetClientValidationRules one, defining a new ValidationDateRule. The validation will be used by the jquery.validate.unobtrusive library. Notice that the ValidationType of the ValidationDateRule method should be in lower case as is defined in the jquery.validate library.

Go to the global.asax file and add the following lines to the Application_Start() method to register the date validator.

DataAnnotationsModelValidatorProvider.RegisterAdapter(typeof(DataTypeAttribute), typeof(AppointmentsManager.Models.DataTypeAttributeAdapter));

After the model is completed let’s define the DbContext class and finally the Configuration for the Database generation.

Step 3: DbContext Definition

public class AppointmentsManagerContext : DbContext

{

public DbSet<Staff> Staff { get; set; }

public DbSet<Customer> Customers { get; set; }

public DbSet<Location> Locations { get; set; }

public DbSet<Appointment> Appointments { get; set; }

}

Step 4: Configuration

Add the references in the project to the main Connector/Net library. On the Solution Explorer window right-click on project's References >Add Reference, look for the MySql.Data.MySqlClient library under the Assemblies category and add it.

Go to the Web.Config file at the root of your Project in order to set as Data Provider the MySQL Connector/Net and set the connection string.

The name of you connection string should match the name of your DbContext class:

<connectionStrings>

<add name="AppointmentsManagerContext" connectionString="server=localhost; user id = root; database= apptmanager; port=3306;" providerName="MySql.Data.MySqlClient" />

</connectionStrings>

Add the following code to add the MySql.Data.MySqlClient as the Data Provider in the application.

<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.6.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

</DbProviderFactories>

</system.data>

Both sections should be inside the <configuration> tag.

Run the project (F5) so the database gets generated.

Database structure

Here’s the whole structure of our database. The _MigrationHistory table keeps tracks of changes to the model entities after the database has been generated.

Step 5: UI Elements

We need a Controller for the Appointment, Customer, Location and the Staff. Let’s start with the controllers for the Locations then Staff and Customer.

Right-click on the Controllers folder and Add>Controller

We’ll use the Scaffolding options also to generate our views for read and write actions. After you select your DataContext class click on Add to generate it. Do the same for the Location and Customer.

Also add a controller for the Appointments entity.

Step 6: Customizing UI

We’ll do some basic customization to the Application starting by creating the Appointment view to add a new record. Let’s change the text boxes for the StaffId, LocationId, CustomerId to combo boxes filled with all the data from the corresponding tables.

Go to the AppointmentController.cs file and navigate to the Create method for the Get action.

Add the following code at the Create method on the Get and Post actions

//

// GET: /Appointment/Create

public ActionResult Create()

{

ViewBag.StaffList = StaffList();

ViewBag.LocationList = LocationsList();

ViewBag.CustomerList = CustomerList();

return View();

}

//

// POST: /Appointment/Create

[HttpPost]

public ActionResult Create(Appointment appointment)

{

try

{

if (ModelState.IsValid)

{

db.Appointments.Add(appointment);

db.SaveChanges();

return RedirectToAction("Index");

}

}

catch (DataException)

{

//Log the error (add a variable name after DataException)

ModelState.AddModelError("", "Unable to save changes.");

}

ViewBag.StaffList = StaffList();

ViewBag.LocationList = LocationsList();

ViewBag.CustomerList = CustomerList();

return View(appointment);

}

Methods for the lists:

private SelectList StaffList()

{

var queryStaff = db.Staff.Select(c => new { c.Id, c.Name });

return new SelectList(queryStaff.AsEnumerable(), "Id", "Name", 0);

}

private SelectList LocationsList()

{

var queryLocations = db.Locations.Select(c => new { c.Id, c.Name });

return new SelectList(queryLocations.AsEnumerable(), "Id", "Name", 0);

}

private SelectList CustomerList()

{

var queryCustomers = db.Customers.Select(c => new { c.Id, c.Name });

return new SelectList(queryCustomers.AsEnumerable(), "Id", "Name", 0);

}

Then we have to add the DropDownList elements in our view. Open the Create view for the Appointment and add for each one of the fields (Staff, Location, Customer) the following:

@Html.DropDownList("locationId", (SelectList) ViewBag.LocationList , "--Select One--")

@Html.DropDownList("StaffId", (SelectList) ViewBag.StaffList, "--Select One--")

@Html.DropDownList("CustomerId", (SelectList) ViewBag.CustomerList, "--Select One--")

Here is how the Create Appointment will look after the changes:

In order to add a DateTime Picker for the start time field. You can use the jQueryUI Library that includes the DateTimePicker control. If you do this you need to add an Editor Template with the following code:

@inherits System.Web.Mvc.WebViewPage<System.DateTime?>

@Html.TextBox("", (Model.HasValue ? Model.Value.ToString("MM/dd/yyyy") : DateTime.Today.ToShortDateString()), new { @class = "datefield" })

Change the code for the Start Time field to use the EditorFor method.

<div class="editor-field">

@Html.EditorFor(model => model.StartTime)

@Html.ValidationMessageFor(model => model.StartTime)

</div>

The last part is to get the View for the Appointments for a specific date. Also let���s customize the Location, Representative and Customer columns to see the names instead of the ids.

Go to Views >Appointment >Index.cshtml and edit the DisplayFor methods with the corresponding:

@foreach (var item in Model) {

<tr>

<td>

@Html.DisplayFor(modelItem => item.Description)

</td>

<td>

@Html.DisplayFor(modelItem => item.Location.Name)

</td>

<td>

@Html.DisplayFor(modelItem => item.StaffPerson.Name)

</td>

<td>

@Html.DisplayFor(modelItem => item.Customer.Name)

</td>

<td>

@Html.DisplayFor(modelItem => item.StartDate)

</td>

<td>

@Html.DisplayFor(modelItem => item.StartTime)

</td>

<td>

@Html.DisplayFor(modelItem => item.Duration)

</td>

<td>

@Html.ActionLink("Edit", "Edit", new { id=item.Id }) |

@Html.ActionLink("Details", "Details", new { id=item.Id }) |

@Html.ActionLink("Delete", "Delete", new { id=item.Id })

</td>

</tr>

}

Add an html form to perfom the filter by date in the Appointments list:

<h2>Index</h2>

<p>

@Html.ActionLink("Create New", "Create")

</p>

<div>

<table>

<tr>

<td>

Filter Date

</td>

<td>

</td>

<td>

@using (Html.BeginForm("Index", "Appointment", FormMethod.Get))

{

@Html.EditorFor(model => model.FirstOrDefault().DateFilter);

<input type="submit" value="Go" />

}

</td>

</tr>

</table>

</div>

Change the code in the AppointmentController.cs at the Index method

//

// GET: /Appointment/

// for an specific date

public ViewResult Index(DateTime? DateFilter)

{

if (DateFilter != null)

{

var d1 = DateFilter.Value;

var d2 = d1.AddDays(1);

return View(db.Appointments.Where(a => a.StartDate >= d1 && a.StartDate < d2).ToList());

}

else

return View(db.Appointments.ToList());

}

Here’s the result after the changes on the Index view of the Appointment Controller.

Applying the filter:

Conclusion 

We built a very basic application but it was enough to see how to configure and start building applications with Connector/Net, Entity Framework and MVC in a quick way.

Please let us know if you have any questions or comments, also please check our forums here where we keep answering questions in general for the community. 

Hope you found this information useful.

Happy MySQL/.Net Coding!  

References:

http://www.codeproject.com/Articles/207797/Learn-MVC-Model-view-controller-Step-by-Step-in-7


Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today