In the last post, we saw how to set up an MVC4 web site in Visual Studio 2012, and how to add a simple controller and view to it.
Most web sites that do anything more than display a bit of fixed information have a database behind them. For example, a blog is backed by a database that stores the posts, a shop contains information on products available in a database and so on.
In this post, we’ll expand our ComicShop site so it contains a database that stores information about comics in stock. There are many databases available, but for now we’ll take the easiest route and use SQL Server Compact Edition, which is included with VS2012. This database has the advantage that it doesn’t require a separate server running in the background; it stores its data in a local file and accesses this file whenever it needs to update the data.
Most of the web sites and books I looked at contained instructions for installing a SQL Server CE database and connecting it with an MVC4 project, but unfortunately, if you start from an Empty project in VS2012, these methods didn’t work: the first attempt to access the database threw an exception complaining that it was unable to attach the database file.
Eventually I tracked down the problem, so in this post I’ll show how to connect a simple database to an MVC4 project.
We’ll begin by creating the database, and then add a controller and a view that allows us to add data to it.
Starting with our ComicShop project from the last post, in Solution Explorer, right-click on App_Data and select Add –> SQL Server Compact 4.0 Local Database. Name this database ComicBooks. You should see a database appear under the App_Data folder. Double-click on this file to open the Server Explorer. Open the ComicBooks node and right-click on Tables, then select Create Table. In the dialog that appears, name the table Books.
Now create some columns in this table, as follows. First, we create a primary key for the table, which we’ll call Id. Enter Id as the column name, set its Data Type to int, then set Primary Key to Yes and Unique to Yes. (The Allow Nulls value should automatically become No during this process.) In the box at the bottom of the dialog, set its Identity to True. This will make Id an auto-incrementing field, so every time you add a record to the table, Id will automatically increase by 1.
Now add a Title column, setting its Data Type to nvarchar, then a Volume column, setting its Data Type to int. Then Issue, also an int. Finally, add a Publisher column, setting its data type to nvarchar. Click on OK to close the dialog.
Just to verify that the table has been created properly, open the Tables node in Server Explorer, right-click on the Books table and select Show Table Data. You should see a window showing one row with the columns you just defined, each containing a NULL entry.
Now that we have our database defined, we need a way of putting data into it. One way is to edit the data table in the Show Data Table window directly, but that obviously isn’t much use if you want to edit the data via the web site. So we need to add a controller and view that allows access to the database via a web page.
First, we need a class to store the data fields for a single comic book. This class will mirror the structure of the database. Since it stores data, it belongs in the Models folder, so right-click on Models in Solution Explorer and select Add–>Class. Call the class Book, and enter some properties to match those in the database:
namespace ComicShop.Models
{
public class Book
{
public int Id { get; set; }
public string Title { get; set; }
public int Volume { get; set; }
public int Issue { get; set; }
public string Publisher { get; set; }
}
}
Next, we need a way of connecting the C# code to the database. The easiest way (well, after we know what to do…it took a lot of searching to find the right way of doing it!) is to use a DbContext. Add another class called ComicContext to the Models folder, with code as follows (I’ll explain this code in more detail later):
using System.Data.Entity;
namespace ComicShop.Models
{
public class ComicContext : DbContext
{
public ComicContext(string conn)
: base(conn)
{
}
public DbSet<Book> ComicBooks { get; set; }
}
}
You will notice that VS complains that System.Data.Entity doesn’t exist. You might think that the usual solution of adding this namespace in your References list will fix the problem, but even though System.Data.Entity does exist in the list of assemblies you can add to References, adding it doesn’t help much: VS still complains that it can’t find DbContext (even though the online documentation says that it is part of System.Data.Entity).
The solution turns out to require the addition of a NuGet package. To do this, right-click on References in Solution Explorer and select Manage NuGet Packages (if this option doesn’t exist, you’ll need to install NuGet from nuget.org). Select Online on the left, and enter EntityFramework in the search box in the upper right. After the search engine finds it, check that it’s version 5.0.0 of EntityFramework, then click on Install. At this point, everything should compile properly. (For the curious, EntityFramework is a huge topic in its own right, so we won’t go into that here.)
We also need to install another NuGet package to allow us to use SQL Server CE, so while you’ve got NuGet open, do a search for EntityFramework.SqlServerCompact. This should return only a single result, so install that and then close the NuGet dialog.
We’ve now got the model classes set up so we need to add the controller and view to allow data to be input. Open HomeController.cs (which we created in the last post). Remember that each method in this controller corresponds to a separate URL that the user can visit in your web site. The Index() method gives the home page which we saw last time.
We want to add a page for entering data for a comic book, so we’ll write an Add() method. The code is much the same as for Index():
public ActionResult Add()
{
return View();
}
Right-click in this method and select Add View, accepting the default name of Add. We’ll keep things simple by knocking up a basic HTML form; it’s not pretty but we can worry about that later. Right now we just want to get the site working. The code is
@{
ViewBag.Title = "Add comic";
}
<h2>Add new comic</h2>
<form method="post">
<fieldset>
<legend>Add comic form</legend>
Title: <input type="text" name="Title" maxlength="100" />
<br />
Volume: <input type="number" name="Volume" />
<br />
Issue: <input type="number" name="Issue" />
<br />
Publisher: <input type="text" name="Publisher" />
<br /><br />
<input type="submit" value="Submit Comic" />
</fieldset>
</form>
This produces four input boxes for entering the four bits of data required for a comic book, and then displays a submit button at the bottom, which returns the entered data back to the web server.
Note that we’ve named each input element so that its name matches the corresponding field in the Book class (and hence the column in the database); this is important for the connection to work properly.
The last link in the chain is the handler for the data submitted by the form. To enable communication with the database, we need to let the web server know where to find the database file. This requires adding a bit to the web.config file for the project. Open Web.config in Solution Explorer (Note that there are two web.config files, one at the top level and one inside the Views folder. It’s the top-level one we want.) At the end of the file, just above the closing </configuration> tag, insert the following:
<connectionStrings>
<add name="ComicContextDb" connectionString="Data Source=|DataDirectory|\ComicBooks.sdf" providerName="System.Data.SqlServerCe.4.0"/>
</connectionStrings>
(This assumes you’ve followed the instructions above and created your database file under App_Data with the name ComicBooks.) This defines a connection to the database file called ComicContextDb, and specifies that it is a SQL Server CE database.
Now we can write the handler for the data sent back by the form. To do this, we add a bit more code to the HomeController class:
private ComicContext database = new ComicContext("ComicContextDb");
[HttpPost]
public ActionResult Add(Book book)
{
database.ComicBooks.Add(book);
database.SaveChanges();
return Content("New comic added.");
}
We create an instance of our ComicContext model class and pass it the name of the connection that we defined in web.config above. If you look at the code for ComicContext above, you’ll see that this string is passed back to the base class (DbContext), which takes care of the interaction with the database, so this base class will use the connection string to establish a link with the database.
Another important point is that the data type (in this case Book) specified in the DbSet object in ComicContext is the singular of the name of the table (Books) in the database. This is actually required in order for the link to work properly. In fact, if we had specified a different class name in DbSet, a table with that name with an ‘s’ added at the end would be created within the database automatically. In general, if you change the underlying model code that links with the database after the database has been created, you’ll need to invoke a ‘migration’ process to update the database so it is in sync with the new code. We won’t go into that here, but just be aware that making changes isn’t entirely straightforward.
Returning to our second Add() method above, note that it is prefixed by an [HttpPost] tag. This indicates that the method is a handler for data returned from the Add page via the HTTP post protocol. The returned data is used to create a Book object (by matching up the control names with the Book’s field names, as mentioned above) which is then passed into the Add() method as its parameter.
The first line of Add() adds the new Book object to the ComicBooks DbSet of the database object, and the next line saves the changes to the database itself. If you’ve done any database programming before, it’s at this point that you would normally write SQL statements for inserting a new record into the database, but the DbContext and DbSet classes do all this for you.
Finally, we return a Content object that prints a message saying “New comic added.”. Obviously in a proper web site we’d want something a bit fancier than that, but at this stage that’s all we need to see that it works.
If you now start the site running and then enter the URL localhost:36195/Home/Add, (your port number might be different), you should see the form for entering some data for a new comic book. Fill in some data and press the submit button, and if all goes well, you should see the “New comic added.” message.
We haven’t provided any way of seeing the contents of the database on the web site yet, but to verify that the comic you just entered did in fact make it into the database, you can use the Show Table Data command to look at the Books table.
