Connecting an MVC application to a database Jim Warren, jim@cs.auckland.ac.nz

1. COMPSCI 280 S2 2014Enterprise Software Development Connecting an MVC application to a database Jim Warren, jim@cs.auckland.ac.nz

2. 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 280

3. 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 See http://www.asp.net/mvc/tutorials/older-versions/overview/asp-net-mvc-overview COMPSCI 280

4. 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 280

5. 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 280

6. 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" /> The connection name will come up in the Model 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.110).aspx Part of that DLL we got from MySQL Connector Net COMPSCI 280

7. Our Model: Employee.cs First ‘using’ directive is added for referencing the DbContextobject; next two are for the ‘key’ and ‘table’ decorators ... 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 intIDnum { get; set; } public string Surname { get; set; } public string GivenNames { get; set; } public DateTimeDateOfBirth { get; set; } } 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 COMPSCI 280

8. OurController C# moment: the .Format method of the String class is handy for laying out output • 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 a generic (‘var’) element that we pass to the View public ActionResult Index() { using (EmployeesContextdb = new EmployeesContext()) { int j = db.Employees.Count(); ViewBag.j = String.Format("We have {0} records.",j); var data = db.Employees.ToList(); return View(data); } COMPSCI 280

9. OurView • Using the Index.cshtml file we display the datafor the user on the home page 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 ... </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") }) 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 COMPSCI 280

10. The result COMPSCI 280

11. 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) COMPSCI 280

12. A closer look (contd.) • The WebGrid andViewBag 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 COMPSCI 280

13. 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 COMPSCI 280

14. 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","LastName",style:"surname"), .surname { font-style: italic; width: 150px; } tr { background-color: #ffe030; } COMPSCI 280

15. Styling result Chrome has picked up to apply the style to the content (note how the Inspect Element tooltip [left] shows the cell as ‘td.surname’) The class attribute of each body cell in the Last Name column has been given the value we specified n.b. You’ll probably need to hit Refresh on your browser to get the style change to show up COMPSCI 280

16. 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 (EmployeesContextdb = new EmployeesContext()) { varemp = from e in db.Employees where e.DateOfBirth.Year < 1975 select e; return View(emp.ToList()); } COMPSCI 280

17. 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 COMPSCI 280