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:
