Pretty basic so far.  Let’s now convert this page to use a database, and make the movie listing dynamic instead of having it just be a static list.

Create a Database

We’ll start by clicking the “Databases” tab within the left-hand navigation bar of WebMatrix.  This will bring up a simple database editor:

SQL Server Compact Edition ships with WebMatrix – and so is always available to use within projects.  Because it can be embedded within an application, it can also be easily copied and used in a remote hosting environment (no extra deployment or setup steps required – just publish up the database file with FTP or Web Deploy and you are good to go).

Note: In addition to supporting SQL CE, the WebMatrix database tools below also work against SQL Express, SQL Server, as well as with MySQL. 

We can create a new SQL CE database by clicking the “Add a Database to your site” link (either in the center of the screen or by using the “New Database” icon at the top in the ribbon).  This will add a “FirstSite.sdf” database file under an \App_Data directory within our application directory. 

We can then click the "New Table" icon within the Ribbon to create a new table to store our movie data.  We can use the "New Column" button in the Ribbon to add three columns to the table – Id, Name and Year.

Note: for the first beta you have to use the property grid editor at the bottom of the screen to configure the columns – a richer database editing experience will show up in the next beta. 

We’ll make Id the primary key by setting the "Is Primary Key" property to true:

We'll then hit "save" and name the table "Movies". Once we do this it will show up under our Tables node on the left hand side. 

Let’s then click the "Data" icon on the ribbon to edit the data in the table we just created, and add a few rows of movie data to it:

And now we have a database, with a table, with some movie data we can use in it.

Using our Database within an ASP.NET Web Page

ASP.NET Web Pages can use any .NET API or VB/C# language feature.  This means you can use the full power of .NET within any Web site or application built with it.  WebMatrix also includes some additional .NET libraries and helpers that you can optionally take advantage of for common tasks.

One of these helpers is a simple database API that allows you to write SQL code against a database.  Let’s use it within our page to query our new Movies table and retrieve and display all of the movies within it.  To-do this we’ll go back to the Files tab in WebMatrix, and add the below code to our Index.cshtml file:

As you can see – the page is conceptually pretty simple (and doesn’t require understanding any deep object-oriented concepts).  We have two lines of code at the top of the file.

The first line of code opens the database.  Database.Open() first looks to see if there is a connection-string named “FirstSite” in a web.config file – and if so will connect and use that as the database (note: right now we do not have any web.config file at all).  Alternatively, it looks in the \App_Data folder for a SQL Express database file named “FirstSite.mdf” or a SQL Compact database file name “FirstSite.sdf”.  If it finds either it will open it.  The second line of code performs a query against the database and retrieves all of the Movies within it.  Database.Query() returns back an dynamic list – where each dynamic object in the list is shaped based on the SQL query performed.

We then have a foreach loop within our <ul> statement, which simply iterates over the movies collection, and outputs each name as a <li> element.  Because movies is a collection of dynamic objects, we can write @movies.Name instead of having to write movies[“Name”].

When we re-run the page (or just hit refresh on it in the browser) and do a “view source” on the HTML returned to the client, we’ll see the following:

The list of movies above is now coming out of our database and is dynamic.

Adding a Simple Filter Clause

One last step we can do to make our application a little more dynamic is to add simple support to filter the list of movies based on a querystring parameter that is passed in. 

We can do this by updating our Index.cshtml file to have a little extra code:

Above we added a line of code to retrieve a “year” querystring parameter from the Request object.  We are taking advantage of a new “AsInt()” extension helper method that comes with WebMatrix.  This helper returns either the value as an integer, or if it is null returns zero.  We then modified our SELECT query to take a WHERE parameter as an argument.  The syntax we are using ensures that we cannot be hit with a SQL injection attack.

Lastly, we added an if statement inside our <h1> which will append a (post 1975) message to the <h1> if a year filter is specified.  And now when we run the page again we will see all movies by default:

And we can optionally pass a “year” querystring parameter to show only those movies after that date: