X

MySQL and HeatWave

  • October 8, 2012

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


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.