Web Development 101: Part 5, Using Data

Thus far in this series, you’ve seen how to create an HTML page with WebMatrix, and how first you could be effective in how you style that page using Cascading Style Sheets (CSS), and then how to use the layout functionality built into WebMatrix with the ‘Razor’ syntax to allow you to focus on the content of your page and not be distracted by the plumbing.

In this Chapter we’ll look at turning the static list of movies that you have been using, and we’ll make it dynamic. In real-world terms – instead of having a list of movies hand-written in HTML, we’ll put them into a database, and we’ll have WebMatrix read the database and generate HTML for us. That way, we can change the database easily, and have the web page update automatically.

Creating a Database in WebMatrix

To get started, find the ‘Databases’ workspace in WebMatrix and open it. At the center of the window you’ll see an option to ‘Add a Database to your site’.

alt

Select this, and WebMatrix will create a new database for you called ‘Movies.sdf’. If your site has a different name, for example ‘foo’, WebMatrix will create a database based on that name (i.e. foo.sdf).

You’ll see the database in the database explorer on the left hand side of the window:

alt

Adding a Table to the database

At the top of the window you’ll see a ribbon of tools showing the different things you can do with the database, including creating tables and queries, as well as migrating to other databases. From this ribbon, select the ‘New Table’ tool. If nothing happens when you select it, make sure you have Movies.sdf selected in the database explorer.

alt

WebMatrix will create the table for you, and will open the Column editor. This allows you to create new columns within your database table. In database parlance, a Record is all the data for a particular entity. So, for example, the data for a person would be a record of their name, age, address and telephone number. Columns are the values of an individual piece of data, regardless of which record they are in. So, in the above example, Name would be a column, as would Age.

So, for our movies, we’re going to create a database that looks a little like this:

alt

First, let’s create the ID. The ID is simply the identifier for a particular record. You don’t need to have one, particularly for a simple database like this, but it’s good practice to do so, as when you build more complex databases, you’ll find them essential for tracking and querying data.

In the column editor, fill out the details as you see below:

alt

This is giving the column the Name ‘id’, and specifying it to be a number (bigint) which has to have a value (Allow Nulls is set to False). It then specifies that the field is an Identity, which means that we’re telling the database that we’re using this as an ID. What’s nice about that is that whenever we add a new record to the database, it will update the ID automatically, so we don’t have to worry about keeping track of what was added last, getting its ID and figuring out a new one.

Next we set the fact to say that this field is the Primary Key. Again, this is a database term. Keys are columns in your database that have special values, and are usually the main thing that you want to use when picking records. They’re used by the database to make data easier to find. For example, you might work in a workplace that gives you an employee number. This number makes it easier to track you, as searching for number ‘333102’ might be a lot easier than searching for employee ‘Nizhoni Benally’, particularly if there are a lot of employees in your organization. That makes your employee number a key to finding you. You might have many keys that are used in the data to find you and the Primary key should be considered the main one.

So, when filing our movies in the database, we’ll give each movie an ID, and we’ll make the ID the primary key.

Use the ‘New Column’ button in the ribbon to create 3 more columns. You’ll see 3, blank, unnamed columns within your table.

Select the first blank row, and give it the name ‘Name’, and Data type ‘ntext’. Make sure ‘Is Identity’ and ‘Is Primary Key’ are False.

alt

For the second blank row, give it the name ‘ReleaseYear’ and keep it as a ‘bigint’.

For the third blank row, give it the name ‘Genre’ and make it ‘ntext’.

Press the save button in the WebMatrix title bar to save the Table.

alt

You’ll get a dialog asking for the name of the table. Call it ‘Favorites’.

alt

You’ll see the table appear on the left hand side of the window.

alt

Adding data to the table

Double click on the table within this explorer, and the table will open, with no data in it.

alt

Place the cursor into the Name field, and type a value into it. When you’re done, press the Tab key on your keyboard to move to the next field (Release Year), give it a value, press Tab, and give the Genre a value.

alt

When you press the Tab button again, you’ll see that the id field has the number ‘1’ put into it for you. It’s a good idea not to enter anything into this column, and let the database do it for you. You see the cursor is within the field now, so press Tab again to go back to the Name field.

Repeat a few times to enter some movies. Here’s the set of 4 movies we’ve been using thus far.

alt

Creating a page that uses the data.

In the previous article you saw that your site uses a layout to create the HTML <head>, styling, body and everything else. If you’ve been following along, you’ll have a _siteLayout.cshtml and a _PageStart.cshtml set up already. If you don’t the rest of this code won’t work, and you’ll have to pop back to article 3 in order to get them working.

What’s nice about using the layouts is that you now only need to write the specific code for the specific page, and the @RenderBody() call on the template will inject it into the full page.

So, with that in mind, create a new CSTHML page and call it datamovies.cshtml.

Replace all the code in this page with the static HTML that we’ve been using thus far:

     <div id="movieslist">
       <ol>
         <li><a href="#">It's a wonderful life</a></li>
         <li><a href="#">Lord of the Rings</a></li>
         <li><a href="#">The Fourth World</a></li>
         <li><a href="#">The Lion King</a></li>
       </ol>
     </div>   

When you look closely at this, you’ll see that it’s a <div> that specifies a list <ol>.

The static list has 4 items in it. If we want a 5th item, we add a new <li></li> entry.

When pulling the data from the database, the page doesn’t know how many items are in the database, so it would have to produce ‘n’ <li></li> elements, where ‘n’ is the number of records in the database. This is perfect for a code loop, and in a moment you’ll write exactly that loop.

But before you do that, let’s first tell the page about the database. At the top of datamovies.cshtml, enter the following code:

     @{
       var db= Database.Open("Movies");
       var sqlQ = "SELECT * FROM Favorites";
       var data = db.Query(sqlQ);
     }   

You may remember the ‘@’ symbol from last article, and it was used to tell the server that the following code is Razor code that it should execute. In this case, we have a number of lines of code, so instead of putting an ‘@’ in front of each, we can use @{, and then write all our code, and then end the code block with a }.

If you’ve written in Java, C++, C, or C#, you’ll likely recognize them, and they’re called ‘braces’.

Let’s look at this code line by line:

var db= Database.Open("Movies");

‘var’ stands for Variable and this is an addressable item containing data. The code is telling the server to Open the Database called ‘Movies’, and store the reference to this in a variable that we’ll call ‘db’.

var sqlQ = "SELECT * FROM Favorites";

This line of code then creates a var, called sqlQ, and stores the SQL command ‘SELECT * FROM Favorites” in it.

When using databases, a language called ‘Structured Query Language’ (SQL) is commonly used to find the data that you want. In fact it goes beyond just querying the data, and can be used to insert new data as you’ll see in a future article, but for now, we’ll just use it to get data.

When getting data from a database, the ‘SELECT’ command is commonly used. The syntax is ‘SELECT <something> FROM <somewhere> WHERE <condition>’

The <something> can be a list of fields, or a wildcard (*) character which means ‘everything’, we haven’t set a condition, so ‘SELECT * FROM Favorites’ commands the database to get all the columns and all the records from the Favorites table.

var data = db.Query(sqlQ);

This is the workhorse of the page. Earlier you opened the database and used the variable ‘db’ to refer to it. WebMatrix was smart enough to know that what you are opening is a database, and a database object in WebMatrix has lots of functions (typically called ‘methods’) that you can call on it. One of these is the ‘Query’ method that runs a SQL command. You pass that command to it as a string, and the database executes it, returning a set of records. This set of records is then loaded into a variable called ‘data’. Now that we have our data, we can do interesting things with it.

At this point, your page should look like this:

     @{
       var db= Database.Open("Movies");
       var sqlQ = "SELECT * FROM Favorites";
       var data = db.Query(sqlQ);
     }
     <div id="movieslist">
       <ol>
         <li><a href="#">It's a wonderful life</a></li>
         <li><a href="#">Lord of the Rings</a></li>
         <li><a href="#">The Fourth World</a></li>
         <li><a href="#">The Lion King</a></li>
       </ol>
     </div>

We’ve gotten the data from the database, but we haven’t done anything with it yet. We still have the static HTML on the page. Let’s change that.

First, delete all but one of the <li> fields, so that your <ol> looks like this:

     <ol>
       <li><a href="#">It's a wonderful life</a></li>
     </ol>

Remember earlier when we said that if we have ‘n’ database fields, then we should have ‘n’ entries in the list. This is achievable using a loop, so lets write a loop in here.

Using ‘Razor’, you add your code using the @ symbol, so a loop would look like this:

     <ol>
     @foreach(var row in data)
       {
         <li><a href="#">It's a wonderful life</a></li>
       }
     </ol>     

The code is telling the server to count the number of rows in the data, and for each one of them, write out the <li> tag.

The result of this would be, of course, that ‘It’s a wonderful life’ would get written out 4 times, as I have 4 entries in my database.

alt

The server only knows what it is told to do, and the code tells it to write out that movie title for each row in the data.

So let’s get a little smarter. What we really want to do is, for each row in the data, write out the name of the movie in that row. In other words, lose the text ‘It’s a wonderful life’, and replace it with the name of the movie in the row that the loop is currently looking at.

When you created the database, you called the column that contains the name of the movie ‘Name’, and you already know that the current record is called ‘row’, so you can deduce that the value row.Name will contain the name of the movie.

So, if you change your code to this:

     <ol>
       @foreach(var row in data)
       {
         <li><a href="#">@row.Name</a></li>
       }
     </ol>

You are now telling the server that for each row in data, write out the ‘Name’ field from the row into the <li> element. Let’s see how it looks. With datamovies.cshtml file selected, run your page:

alt

You can see that we have all the movies again!

Now that the page is dynamic and loading the movies based on the data in the database, let’s add another movie to the database, and see what happens.

Simply go back to the database editor, open the Favorites table, and add a new row of data, like this:

alt

Then run your page. We have a new movie on the list without writing any HTML.

clip_image018

If you’ve written code in inline languages like this before, you might have noticed that WebMatrix is smart enough to write the content out when I place it inline in the <li>. I didn’t have to say ‘write row.Name’ or anything like that, I just said ‘@row.Name’ in the place I wanted it to go. It makes editing and maintaining the CSHTML files much easier than languages like PHP where I would be using ‘<?php echo(row.Name);>’ or something similar.

This also allows me to mix dynamic with static content seamlessly. For example if I change the code to:

     <div id="movieslist">
       <ol>
         @foreach(var row in data){
           <li><a href="#">@row.Name, @row.Genre, @row.ReleaseYear</a></li>
         }
       </ol>
     </div>

The result is a seamless integration of the static HTML (the commas) and the dynamic database-driven content (the fields) that looks like this:

clip_image020

And with that, you’ve created your first data-driven dynamic web page. Much easier done than said!

Summary

In this article you took the movies list that you’ve been creating thus far and turned it into a dynamic data driven web site. You created a SQL Server Compact database with a table that contains your favorite movies, and then saw how to write code to retrieve the movies from the database and write their details out as HTML to the browser. Combined with the layout functionality from article 3, you saw how to create a CSHTML page for this which is very lightweight and gets right down to business.

In the next article you’ll see the next step – what happens if you want to allow your users to add movies to the database, and they don’t have access to the WebMatrix source code like you do. You’ll see how to create a web interface that allows them to use their browser to type in details, and code that you write will then load those details into the database, and the site will immediately update to reflect the changes!

In the next part, you’ll learn how to create a web page that will allow your users to add data to your site.

You can discuss this article using the adjacent Facebook talkback.

For technical questions please visit our discussion forums, where we have a vibrant community of developers like you, as well as Microsoft engineers who are ready to answer your questions!