Each page will have a number of attributes that we want to manage. We'll want a name, an ID, and we'll need to determine where a page is in relation to other pages. To do this, we'll use the parent/child relationship and identify a parent page for every page. This way, if a page has no parent, it will be in the root of our page menu. On the other hand, if it has a parent, it will fall under the parent page on the menu.

To create the Pages table, open SQL Server Management Studio and expand Tables under the SimpleCMS database. Leave the Content table alone for now, we won't use it in this chapter. However, we don't have a reason to get rid of it yet, especially if we already have data we want to store in it. Right-click in the Tables pane and choose New Table. Create a Pages table with three columns, PageID, PageName, and PageParentID, set to data types of int, nvarchar(50), and int respectively. Do not allow nulls in any field. In the Properties pane, set the name to Pages and the Identity Column to PageID. Your table should look something like this when you save it:

We need to create the Panes table in the same manner. Right-click on Tables in the Object Explorer pane and choose New Table. Create a Panes table, with columns of PaneID, PaneName, and PanePageID, set to the data types of int, nvarchar(50), and int respectively. Allow nulls for the PaneName and PanePageID fields. You guessed it, PaneID will be the Identity Column. The table should look like this:

In this table, we left the PageName and PageID as null because we don't actually need them when we create a pane. We will assign them individually. In the case of Pages, we don't want a page without a name, or the identification of its parent page, as we wouldn't be able to add it to a menu. In later chapters, we'll expand these tables to accommodate other data, but we just need these fields for now.

Articles are what our content will be displayed with, along with the permission to edit that content. This table is essentially the heart of the application, with other tables controlling the display and positioning of the articles and the structure of the web site. To begin this table, we need four columns—ArticleID, ArticleName, Article, and ArticlePaneID. We need the ArticlePaneID column to define which pane this particular article will appear in. We will eventually want to describe this article with many other pieces of data such as an author, date, and expiration date, but the available columns would suffice for now. Create the Articles table with the following settings:

Name this table Articles and use the ArticleID as the Identity Column. When you finish, it should look something like this:

INSERT Into Articles
(ArticleName, Article, ArticlePaneID)
VALUES
('Article 1','<p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Donec sodales luctus nisl. Duis fringilla felis 
convallis magna. Nullam urna. Nulla ante. In eget tortor. Sed est nibh, consectetuer ut, aliquet nec, euismod sit amet, odio. 
Nam nisl. Morbi sapien dolor, consectetuer sit amet, condimentum non, lobortis vitae, lorem. Sed consequat, erat vitae 
feugiat rutrum, ligula ipsum pharetra dui, in dapibus nisi massa ut sem. Integer at nunc quis lectus facilisis molestie. 
Vestibulum fermentum.</p>',1);
GO

We'll start by creating a typed dataset using the Visual Web Developer wizard, which will also walk us through creating a table adapter to access the dataset. Open Visual Web Developer and open the SimpleCMS web site. In Solution Explorer, right-click the root of the site and choose Add New Item. Add a DataSet, using the default settings and name. When you are prompted, choose to save the dataset in the App_Code folder. After the dataset saves, you will be presented with the DataSet Designer.

Right-click in the designer pane and choose to Add a new TableAdapter. The TableAdapter wizard will open. Choose your existing SimpleCMS connection string and click Next, then choose SQL Statements and again click Next. You'll be presented with the SQL query window. We'll create this table adapter for use with the Articles table, and we'll start with a query that selects all the tables. Enter this in your query window:

SELECT ArticleID, ArticlePaneID, Article, ArticleName
FROM Articles

This selects all columns in our Articles table. We could simply use the code:

SELECT * FROM Articles

But this is a lazy programming method. In a query, we may not need every column and, even if we do, specifying the columns individually will let someone reading the code immediately know what we want.

Click the Advanced Options button, choose Generate Insert, Update and Delete Statements, and click OK. At this point, you may select Next until the TableAdapter is created, or simply choose Finish and the TableAdapter will be created, looking something like this:

At this point, you have created the DataSet attached to the Articles table, along with a corresponding ArticlesTableAdapter that includes a Fill method and a GetData() method. These can be used in an ASP.NET form with simple programming and no data coding. Let's create a simple test page for the data adapter we have created.

Right-click the root of your site in Solution Explorer and choose Add New Item. Add a new web form, and name it Test.aspx. We'll just delete it after we test, so we don't need anything fancy. In the Test.aspx form, we'll use a GridView control to display the data from our table. You can either drag one from the toolbox to the page or add the following lines in the default <div> created with the new form:

<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

This is all we'll do with the Test.aspx page itself. Open the code behind file, Test.aspx.vb, and add the following Page_Load subroutine:

Protected Sub Page_Load(ByVal sender As Object,
ByVal e As System.EventArgs) Handles Me.Load
Dim articlesAdapter As New DataSet1TableAdapters.ArticlesTableAdapter
GridView1.DataSource = articlesAdapter.GetData()
GridView1.DataBind()
End Sub

As you add this code, you'll notice that Visual Web Developer's IntelliSense will pick up DataSet1 and the methods we created for it. This is because of the strongly typed dataset, which allows IntelliSense to work with it. The Test.aspx form will then look like the following:

Okay, it's ugly. But this isn't the presentation layer, so we don't care. All this page does is show that a few simple lines in the Page_Load event allow us to work with the data access layer we created.

The table adapter we created for our dataset has a Fill method and a GetData() method, both of which return the entire table. We will almost never want every article in the Articles table returned. We would want only the specific article we wanted to display. And we would normally want to retrieve our article by name, or maybe using the unique identifier, ArticleID. To make these retrievals possible, we simply set these methods up in our table adapter.

We know from the last chapter that we want a WHERE clause in our SQL query that specifies the data we want returned. The only thing we won't know until the user requests a specific article is the actual WHERE clause, so what we need to use is a parameterized query. This is simply a WHERE clause that uses a parameter in place of the specific WHERE details. Our current query that returns the entire table looks something like this:

SELECT ArticleID, ArticlePaneID, Article, ArticleName
FROM Articles

What we really want is something more like this:

SELECT ArticleID, ArticlePaneID, Article, ArticleName
FROM Articles
WHERE ArticleName = "Article 1"

But we don't know that we want Article 1 every time, so we have to substitute a parameter for the article name and then provide that parameter, when we call for the table adapter to do its work.

To configure the table adapter to handle the WHERE clause, open DataSet1.xsd from the App_Code folder in Visual Web Developer. Right-click on the ArticlesTableAdapter and choose Add Query. Choose Use SQL Statements and click Next. Now, choose SELECT, which returns rows, and finally click Next, bringing up the query window. The original query should be in the query window, which you can modify using the Query Builder. Otherwise, simply type in your new query. Modify the query by adding the WHERE clause at the end, so the query now looks like:

SELECT ArticleID, ArticlePaneID, Article, ArticleName
FROM Articles
WHERE (ArticleName = @ArticleName)

Click Next and rename the Fill and GetData methods to FillByArticleName and GetDataByArticleName. Finish the wizard and you'll see two new methods added to the ArticlesTableAdapter—FillByArticleName and GetDataByArticleName(@ArticleTitle).

Once these methods have been added to the ArticlesTableAdapter, you can test them by modifying the code-behind file for Test.aspx—Test.aspx.vb. Change the Gridview1.DataSource line to read:

GridView1.DataSource = articlesAdapter.GetDataByArticleName _
("Article 1")

Now, when you view Test.aspx in your browser, you should see only a single record, something like shown in the following screenshot:

Add a second set of methods to the table adapter using the same process, this time to select by ArticleID, using this WHERE clause:

WHERE ArticleID = @ArticleID

As long as we pass the parameter required by the method to the table adapter, we no longer need to write SQL queries for this data. If you wish, you can modify the Test.aspx.vb file to display by ArticleID. Just remember that ArticleID is an integer, so the value doesn't need to be in quotes.

Now we can retrieve a record based on the ArticleName or ArticleID field. However, what if we want to add a new record—should we change an existing one or delete one altogether? For those functions, we'll create custom methods for our table adapter. In the designer screen, right-click on the TableAdapter and choose Add | Query. Choose Use SQL Statements and click Next, then choose Insert Query and Next. This will bring you to the query screen. Notice that the query is already created for you in a very basic manner. However, we need to modify it because when we insert a new article, we want to retrieve the automatically created ArticleID, so we can use it in our code. To do this, we will use the SELECT SCOPE_IDENTITY() function that returns the value of the last auto-generated Identity column in the scope of the query. This is actually a second query to the database, so we end the first query with a semicolon and then add this query to the code. Your query should look something like this:

INSERT INTO [Articles]
([ArticlePaneID], [Article], [ArticleName])
VALUES
(@ArticlePaneID, @Article, @ArticleName);
SELECT SCOPE_IDENTITY()

Name this function InsertArticle and save it. Insert methods normally return the number of rows affected, but we want to return the new ArticleID instead. To accomplish this, we have to change the ExecuteMode on our query. Click on the InsertArticle method we just created in the ArticlesTableAdapter and change the ExecuteMode to Scalar in the properties pane, as shown below:

To test this function, open the Test.aspx file and edit the Page_Load subroutine in the code behind to look like the following:

Protected Sub Page_Load(ByVal sender As Object, ByVal e _ As System.EventArgs) Handles Me.Load
Dim ArticlesAdapter As New _
DataSet1TableAdapters.ArticlesTableAdapter()
Dim NewArticleID As Integer = (ArticlesAdapter.InsertArticle( _
1, "This is a new article.", "New Article"))
GridView1.DataSource = ArticlesAdapter.GetData()
GridView1.DataBind()
End Sub

This test page will insert a new article on page load and then display the grid with all the articles. The newest article should be on the bottom, with an ArticleName of New Article and text reading: "This is a new article".

We also will need update and delete methods in our ArticlesTableAdapter. To create these, click on ArticlesTableAdapter to select it, and look at the Properties Pane for the DeleteCommand and UpdateCommand properties. Click the drop-down next to the UpdateCommand and select (New). This will create a new UpdateCommand listed as (UpdateCommand). In the CommandText property for the UpdateCommand, select the ellipses (...) to open the Query Builder. Here, we need to enter the query that will be executed when we update an article. Enter the following query:

UPDATE Articles
SET ArticleName = @ArticleName, Article = @Article,
ArticlePaneID = @ArticlePaneID
WHERE (ArticleID = @Original_ArticleID)

This query simply updates the ArticleName, Article, and ArticlePaneID fields with the parameters passed to the query, when the ArticleID is the same as the ArticleID passed to the query. Save this query and then modify the DeleteCommand in the same way, but with this query:

DELETE FROM Articles
WHERE (ArticleID = @Orginal_ArticleID)

This query simply deletes the article which has the ArticleID passed to the query. Once you have finished these changes, right-click on your web site root in the Solution Explorer pane and choose Build Web Site. The site must build with no errors before we can create the business logic layer.

Our business logic layer will use classes, each of which handles a specific table adapter. In these classes, we'll handle basic business rules and validation, and pass requests between the presentation layer and the data access layer. In some ways, the BLL behaves as a proxy between the layers, especially for those functions where all we will do is pass information without working with the information within our BLL. For our BLL class, we will have methods for SELECT, INSERT, UPDATE, and DELETE operations on our DAL. We'll also have two separate SELECT methods—one to select by ArticleName and the other by ArticleID.

In Visual Web Developer 2008, right-click on the new BLL folder you created under the App_Data folder and choose New Item. Select the Class template and name it ArticlesBLL.vb. We will give this class five methods:

The first three functions merely pass a query on to the data access layer, passing either an ArticleID or ArticleName to retrieve a specific article, or nothing at all to retrieve all articles. The code that does this in our class looks similar to this code for the first function in our list, retrieving a list of all articles:

<System.ComponentModel.DataObjectMethodAttribute( _
System.ComponentModel.DataObjectMethodType.Select, True)> _
Public Function GetArticles() As DataSet1.ArticlesDataTable
Return Adapter.GetData()
End Function

We are simply passing the request to return all records to the GetData() method in our ArticlesTableAdapter and returning the data as a typed dataset. We can add validation logic or other business rules into this function, but for now we'll leave it as is. For example, in future, we might want to retrieve only those articles that haven't been archived, in which case we could modify the business rule to handle this.

The AddArticle and UpdateArticle functions are somewhat different from the GetArticle functions, as both AddArticle and UpdateArticle need to pass new data to the database access layer. We do this by creating a DataTable with the data to be added or updated, and passing it as a new DataRow to the data access layer. For example, the AddArticle function code looks like this:

<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddArticle(ByVal ArticleName As String, ByVal _
Article As String, ByVal ArticlePaneID As Nullable(Of Integer)) _

As Boolean
Dim Articles As New DataSet1.ArticlesDataTable()
Dim ArticleRow As DataSet1.ArticlesRow = Articles.NewArticlesRow()
If ArticleName Is Nothing Then ArticleRow.SetArticleNameNull() _
Else ArticleRow.ArticleName = ArticleName
If Article Is Nothing Then ArticleRow.SetArticleNull() _
Else ArticleRow.Article = Article
If Not ArticlePaneID.HasValue Then _
ArticleRow.SetArticlePaneIDNull() Else _
ArticleRow.ArticlePaneID = ArticlePaneID.Value
Articles.AddArticlesRow(ArticleRow)
Dim rowsAffected As Integer = Adapter.Update(Articles)
Return rowsAffected = 1
ArticlesBLL class, BLLmethodsEnd Function

We first create the ArticlesDataTable, and then the NewArticlesRow to add the new row to the database. We populate this row with the ArticleName, Article, and ArticlePaneID by setting each to the data we pass into the function from the presentation layer. In this process, as these columns in our database are null, we must also check to see if the data passed in is null and, if it is, we should set that data as null in our data row too.

Finally, we add the new row to the database and test the addition to ensure that only a single row was affected by the function. If no rows are affected, or more than one row is affected, we return False from our function, which we can process to present an error message to the user.

The last type of function we use is the DeleteArticle function which, as its name suggests, simply deletes the article matching the ArticleID we pass in. The code for this is simple.

<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Delete, True)> _
Public Function DeleteArticle(ByVal ArticleID As Integer) _
As Boolean
Dim rowsAffected As Integer = Adapter.Delete(ArticleID)
Return rowsAffected = 1
End Function

We simply delete the article matching the ArticleID and return a True/False, depending again on whether a single row was affected.

Dim ArticlesLogic As New ArticlesBLL()
GridView1.DataSource = ArticlesLogic.GetArticles()
GridView1.DataBind()

We'll start our presentation layer for the application by building a Master Page, which will hold our content and provide some basic site-wide formatting. To begin, add a new file to the SimpleCMS application, choose the Master Page template and name the file SimpleCMS.master. You will notice that the default code added to the SimpleCMS.master file includes a new control—the ContentPlaceHolder control. This is a special control that takes all code and text from a related Content control in a content page and displays it as is in the Master Page. By default, the new Master Page has two ContentPlaceHolder controls—one named Head and the other named ContentPlaceHolder1. There cannot be two ContentPlaceHolder controls on a Master Page with the same name, as this is what is used to determine which content page is loaded.

<table border="0" cellpadding="2" cellspacing="0">
<tr>
<td>
<img src="Images/SimpleCMSLogo.jpg" alt="SimpleCMS" />
</td>
</tr>
<tr>
<td>
<asp:ContentPlaceHolder id="ContentPlaceHolder1"
runat="server">
</asp:ContentPlaceHolder>
</td>
</tr>
<tr>
<td>
<p><strong>Website design Copyright 2009
by SimpleCMS</strong></p>
</td>
</tr>
</table>

Creating the Default.aspx home page

Our Master Page isn't much use to us without a standard web page that implements this new Master Page. Master Pages are never requested on their own. They are combined with an ASP.NET page at compile time and served as part of the request for the ASP.NET page. This ASP.NET page is called the content page, as it contains the content displayed in the ContentPlaceHolders on the Master Page. This is easily demonstrated by creating a quick content page.

Add a new file to the SimpleCMS application, this time choosing the Web Form template. Name it Default.aspx and make sure the Select Master Page option is checked. Click on Add and select the SimpleCMS.master—the Master Page we just created. This will create the Default.aspx content page. If you look at the default code created, you'll see something like this:

<%@ Page Language="VB" MasterPageFile="~/SimpleCMS.master"
AutoEventWireup="false" CodeFile="Default.aspx.vb"
Inherits="_Default" title="Untitled Page" %>
<asp:Content ID="Head" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
</asp:Content>

You'll see first off that the @Page directive sets the MasterPageFile to the Master Page we selected. You'll also see that a title method has been added to the directive as well. Beyond that, the page is really just content controls with ContentPlaceHolderIDs that match those found in the SimpleCMS.master Master Page. Before we test it, let's add some static content to the Content1 Content control. Change this section of code to read:

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>----] Content for ContentPlaceHolder1 goes here [----</h2>
</asp:Content>

This addition of a single line of content will provide some content for our Master Page to display in the matching ContentPlaceHolder control. If you view the Default.aspx page in a browser, it should look something like this:

Notice that the logo and the copyright statement that show for the Default.aspx page are not part of the Default.aspx page code; they exist only in the SimpleCMS.master Master Page.

Adding dynamic content

A Content Management System is pretty useless if you need to create a new page for every piece of content you might want to serve. Plus, we didn't go through the first two thirds of this chapter building application layers just to abandon them now. We need to add the dynamic content functions that we created to our content page, in order to retrieve the content from the database. To do this, we'll use some of the code we already created earlier in the chapter.

Edit the Content1 content control in Default.aspx to the following code:

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</asp:Content>

The GridView control is the same one we have been using while testing the rest of our code in this chapter. Add this Page_Load subroutine in the Default.aspx.vb code behind file:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim ArticlesLogic As New ArticlesBLL()
GridView1.DataSource = ArticlesLogic.GetDataByArticleID(1)
GridView1.DataBind()
End Sub

This calls our GetDataByArticleID function in our business logic layer and passes it the ArticleID of 1. The GridView is then bound to the results of this function, and displays the record matching the ArticleID parameter we passed. The end result is similar to past test pages, except that it now uses our Master Page because this is the Default.aspx page we created. If you choose to view Default.aspx in your browser, it should look something like this:

Okay, it's not pretty. We really don't want the entire row displayed, and we don't want it in a grid format either. The secret is to pull a trick from Chapter 2 and use a FormView control with an ItemTemplate that displays only what we want. To do this, first edit the Default.aspx.vb code behind, in order to change the GridView to a FormView using the following code:

FormView1.DataSource = ArticlesLogic.GetDataByArticleID(1)
FormView1.DataBind()

That's right, we're really changing only the GridView1 to FormView1—the rest is already handled by our BLL and DAL. Now, change the Default.aspx code to remove the GridView control and replace it with the FormView control, using this code:

<asp:FormView ID="FormView1" runat="server">
<ItemTemplate>
<h2><asp:Label ID="Label1" runat="server"
Text='<%# Bind("ArticleName") %>'></asp:Label></h2>
<asp:Label ID="Label2" runat="server"
Text='<%# Bind("Article") %>'></asp:Label>
<hr />
</ItemTemplate>
</asp:FormView>

This code uses a FormView control with an ItemTemplate to display two Label controls. The first is the ArticleName field retrieved through our DAL by the BLL, and bound to the FormView in the code-behind file. The second is the Article field. We also format the first label, the ArticleName, with a <h2> HTML tag, and we add a horizontal line with the <hr /> tag before we close the FormView control. When you view the Default.aspx file in a browser, it now looks more like a web page, with the logo, an article title, an article, and a copyright line at the bottom of the page. Something like shown in the next screenshot:

Finally, something that isn't truly lame. We have a lot more to add to our application, but by now you should be starting to understand the background concepts of our SimpleCMS application.