connecting an mvc application to a database jim warren, [email protected] compsci 280 s2 2014...

Connecting an MVC application to a databaseJim Warren, [email protected]

COMPSCI 280 S2 2014Enterprise Software Development

Today’s learning objectives To be able to program the key components of a

C#/.NET MVC application for Establishing a database connection Defining a data model for its tables Querying the database Presenting the user a view of the results

To gain familiarity with use of the MVC pattern and its application to a Web application in practice

COMPSCI 2802

The Model-View-Controller approach

Last lecture we introduced the MVC pattern and instantiated a default Internet Application template for MVC in C#/.NET

Today we want to learn more about the MVC components and other supporting files of the application so we can integrate the application with our own database

The Model will define the database connection andmap C# classes to the database tables

The View will be tailored to present informationfrom a table

The Controller will execute databasequeries and return data as parameter to the View

COMPSCI 2803

See http://www.asp.net/mvc/tutorials/older-versions/overview/asp-net-mvc-overview

Getting MySQL and .NET to play together

Principles are the same regardless of the database solution Get the infrastructure (DBMS and its tools) Set up a database and get its server running Establish interoperability of the database and the .NET

environment .NET is proprietary to Microsoft, so they’d prefer you

ran Microsoft SQL Server (‘Express’ version is included Visual Studio) rather than a competitor such as the free MySQL

Add to your program a ‘connection’ This specifies the running DBMS service, which

database (set of tables, views etc.) you want and your ‘login’ as an authorised user on that database

COMPSCI 2804

Specifics Get MySQL running

I did this by installing XAMPP to get MYSQL, Apache, XAMPP Control Panel and PHPMyAdmin

Then just start Apache* and MySQL in XAMPP Control Panel

Used PHPMyAdmin to build an employee database with an employee table, and also to add a user “jim”

Establish interoperability of the technologies Installed MySQL Connector Net 6.7.5 Did Project/Add Reference/Browse in VS to add the

MySql.Web.dll and MySql.Data.dll from ConnectNet's Assemblies\v4.5 subdirectory

* Apache only needed for PHPMyAdmin - .NET will use Internet Information Server (IIS) Express to serve the applications Web pages

COMPSCI 2805

Add a connection Go into Web.config (from Solution Explorer)

It’s an XML file with project metadata There’s already a connection defined

It’s used for the user accounts that are built into this project by default

<connectionStrings>

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0 ... 62020.mdf" providerName="System.Data.SqlClient" />

We’ll put in another ‘add’<add name="MySqlConnection"

connectionString="Server=localhost;Database=employee;Uid=jim;Pwd=password" providerName="MySql.Data.MySqlClient" />

COMPSCI 2806

The connection name will come up in the Model

Part of that DLL we got from MySQL Connector Net

The MySQL user we defined (and its password! – note you can

encrypt part of configuration for security:

http://msdn.microsoft.com/en-us/library/ms254494(v=vs.1

10).aspx

Our Model: Employee.cs

Handout 01COMPSCI 2807

...using System.Data.Entity;using System.ComponentModel.DataAnnotations;using System.ComponentModel.DataAnnotations.Schema;

namespace MvcLetsConnect.Models{public class EmployeesContext : DbContext { public EmployeesContext() : base("MySqlConnection") { }

public DbSet<Employee> Employees { get; set; } }

[Table("employee")] public class Employee { [Key] public int IDnum { get; set; }

public string Surname { get; set; }public string GivenNames { get; set; }public DateTime DateOfBirth { get; set; }

}

First ‘using’ directive is added for referencing the DbContext object; next two are for the ‘key’ and ‘table’ decorators

Use that connection we added to the Web.config file

Our context’s DbSet ‘Employees’ will be a collection of objects of type Employee

The table in MySQL is named

‘employee’Define properties of this class with names matched to table fields in MySQL ([Key] identifies IDnum as the primary key as set in MySQL)Entity Framework can create the table entity definitions

graphically/automatically for SQL server, but still in beta for MySQL

OurController

The HomeController.cs file includes the handler for the index (home) page

Creating an instance of the Employees context initiates the database connection Putting it in a ‘using’ statement is tidy – invokes the connection’s dispose

method, which closes the connection when we’re done serving up the page We can then invoke methods on the Employees collection such as

to .Count() them or assign the whole collection as an enumerable list to be pointed to by an implicitly typed (‘var’) element that we pass to the View

COMPSCI 2808

public ActionResult Index() { using (EmployeesContext db = new EmployeesContext()) { int j = db.Employees.Count();

ViewBag.j = String.Format("We have {0} records.",j); var data = db.Employees.ToList(); return View(data);

}

C# moment: the .Format method of the String class is handy for laying out output

OurView

Using the Index.cshtml file we display the datafor the user on the home page

COMPSCI 2809

... </hgroup>

@model IEnumerable<MvcLetsConnect.Models.Employee>@{ ViewBag.Title = "People"; WebGrid grid = new WebGrid(Model);}<h2>People</h2> @ViewBag.j @grid.GetHtml(columns: new [] { grid.Column("Surname","Last Name"), grid.Column("GivenNames","Given Names"), grid.Column("DateOfBirth","Date of Birth")})

The @model keyword in Razor says that the data passed to the View will be interpreted as an enumerable list of Employee objects as defined in the Model section of the project

Using C#-like syntax in a Razor code block we instantiate an instance of the WebGrid helper on the Model data

Razor functions put the text of element ‘j’ of the ViewBag and the instance of a WebGrid into the body of the HTML. Each WebGrid Column is instantiated on an exact property name of the Employee class from the model

The result


The result: a closer look


In Chrome, if you right-click and select ‘Inspect Element’ it’ll open a window showing the HTML (bottom left) and the CSS styles that have

been applied (bottom right)

A closer look (contd.) The WebGrid and

ViewBag aren’t there

Just as the C# codehas been convertedto CIL for the CLR,the View’s Razorsyntax has been converted to HTML for the browser to consume

HTML moment: The <table> tag defines the start of a table, with a <tr> to start each row and a <td> (‘table data’) to start each cell Note that Chrome’s Inspect Element navigation has opened the

elements directly leading to the cell we inspected but, for instance, has the table header definition (‘thead’) closed


A closer look (contd. again)

If we inspect the ‘Given Names’ column header, we see that the WebGrid helper did even more for us It built a hyperlink (HTML ‘a’ – for ‘anchor’ – tag) which sends back to the Controller a

request for the page in a different sort order (and the Controller automatically knows how to handle this, too!)

Note how the Employee class property name (‘GivenNames’) is used in the URL If we had had more rows, the WebGrid would’ve also built us links for paging the data


Making your mark with style We can add a style parameter to the constructor for a

grid.Column

In the Site.css file (under Content in the Solution Explorer) we can create a corresponding style definition to apply to any element of class surname And the VS intelli-sense greatly assists writing the CSS code!

We can also change any HTML tag’s styling for the whole site by editing in Site.css E.g. to make all the rows of any table amber (not depicted in next

slide)


@grid.GetHtml(columns: new [] { grid.Column("Surname","Last Name",style:"surname"),

.surname { font-style: italic; width: 150px;}

tr { background-color: #ffe030;}

Styling result


The class attribute of each body cell in the Last Name column has been

given the value we specified

Chrome has picked up to apply the style to the content (note how the

Inspect Element tooltip [left] shows the cell as ‘td.surname’)

n.b. You’ll probably need to hit Refresh on your browser to get the style change to show up

Another query syntax: LINQ Language Integrated Query (LINQ)

Can modify our HomeController.cs to read:

Very similar to SQL, but actually part of .NET (i.e. integrated with the C# language) Differs in leading with ‘from’ and ending with ‘select’ (a

better order for the intelli-sense!) Note that the class of the objects returned by the query

(each row, the members pointed to by emp) has to align to our @model in the View definition


using (EmployeesContext db = new EmployeesContext()) {

var emp = from e in db.Employees where e.DateOfBirth.Year < 1975 select e;

return View(emp.ToList()); }

Where we’re up to We’ve now been introduced to the MVC approach in

C#/.NET And we’ve seen how to connect it to a MySQL database

Now… Work the first labsheet (if you haven’t already) Look at Assignment 2 (if you haven’t already) Make a start – use your break time wisely and don’t be

afraid to read ahead into weeks 7 and 8

In weeks 7 and 8 we’ll extend what we can do with MVC in C#/.NET and get a firmer understanding of what we’ve used so far


connecting an mvc application to a database jim warren, [email protected] compsci 280 s2 2014...

Documents