Build Your Own Blog: Part I

The Foundation — Database Schema, Data Access Layer, and User Controls

CoverStory

LANGUAGES: VB.NET

ASP.NET VERSIONS: 2.0

 

Build Your Own Blog: Part I

The Foundation Database Schema, Data Access Layer, and User Controls

 

By Bipin Joshi

 

Blogging has become a very popular trend on the Web. Indeed, many people are using blogs to express their own thoughts, work, and personality. Although there are many Web sites that offer the opportunity to start your own blog, don t you think your blog should reflect your own identity and personal flavor? If you re a developer, always desperate to get your hands dirty with code, chances are you ll want to build your own blogging application instead of using some readymade version. To that end, this three-part series explains how to build your own blog using ASP.NET 2.0 and SQL Server Express Edition. In this first part we ll build the foundation of the application in terms of database schema, the data access layer, and Web user controls.

 

Functional Requirements

Before we start with any coding it s necessary to understand the functional requirements of any blogging application. For our application we ll include the following functional requirements:

  • The application should allow you to add and edit blog posts. Only the administrator (i.e., you) should be able to add, modify, or delete posts.
  • The posts should be displayed to the user in descending order of date posted.
  • Selecting a particular post should take the user to another page where they can view the post, as well as all the comments posted by other users.
  • The user should be able to post his comments.
  • The user should be able to view old posts by specifying a date range.
  • Statistics, such as total number of posts and total number of comments, should be displayed.
  • The application should generate RSS feeds of the latest additions for syndication purposes.
  • The administrator should be able to manage blog categories.
  • The administrator should be able to manage links to external and internal Web pages.

 

ASP.NET Feature Consumption

We ll use ASP.NET 2.0 and SQL Server Express Edition to develop our application. Specifically, we ll use the following features of ASP.NET 2.0:

  • Web forms and server controls
  • Data-bound controls such as GridView, DetailsView, and DataList
  • Data source controls especially the SQL Data Source control
  • Web user controls
  • Master pages
  • Forms authentication and login controls

 

Creating the Web Application

To begin, create a new ASP.NET Web site using Visual Studio .NET or Visual Web Developer. Figure 1 shows the new New Web Site dialog box of Visual Studio .NET.

 


Figure 1: Creating a new Web site in VS.NET.

 

Database Schema

All our tables and data reside in a SQL Server Express Edition database called BlogDb. SQL Server Express Edition gets installed along with Visual Studio .NET, or you can install it separately. You can also use SQL Server 2000 or SQL Server 2005. Figure 2 lists the tables we need in the database.

 

Table

Columns

Description

Posts

Id (int, identity)

Title (varchar(255))

Message (text)

PostDate (datetime)

CategoryID (int)

Contains all the blog posts.

PostCategories

Id (int, identity)

Name (varchar(255))

Contains the post categories.

Comments

Id (int, identity)

PostId (int)

Title (varchar(255))

Message (text)

PostedBy (varchar(255))

Url (varchar(255))

PostDate (datetime)

Stores all the comments submitted by the visitors.

Links

Id (int, identity)

Title (varchar(255))

Url (varchar(255))

Sequence (int)

Stores links to internal and external Web pages. You can use the Sequence column to decide the sequence in which they should be rendered on the user interface.

Figure 2: Tables required for the BlogDb database.

 

To add a SQL Server database to the Web site, right-click on the App_Data folder of your Web site in the Solution Explorer and select New Item. Select SQL Database from the Add New Item dialog box and specify the name as BlogDb (see Figure 3).

 


Figure 3: Adding a new SQL Server database in VS.NET.

 

Once you add the database, you can create the various tables listed in Figure 2 using Server Explorer. Figure 4 shows Server Explorer with our table definitions.

 


Figure 4: Tables used by the blog application.

 

Handling Data Access

We ll use the SQL Data Source control to perform our database operations. Data source controls act as a bridge between your database and the user interface, and they help reduce the amount of code you must write for data access. The SQL Data Source control allows you to develop two-tier applications. Although most of the data access is taken care via the SQL Data Source control, at times we need to execute queries on our own. For that we must create a class named SqlHelper, which encapsulates common tasks such as opening a connection, closing a connection, and executing the queries.

 

To create the SqlHelper class, right-click your App_Code folder and select Add New Item. Then select Class from the Add New Item dialog box and name it SqlHelper. Figure 5 lists the main methods of the SqlHelper class.

 

Method

Description

Public Shared Function

ExecuteNonQuery(ByVal query As String) As Integer

Allows you to execute action queries without any parameters.

Public Shared Function

ExecuteNonQuery(ByVal query As String, ByVal params As SqlParameter) As Integer

Allows you to execute action queries with parameters.

Public Shared Function

ExecuteReader(ByVal sql As String) As SqlDataReader

Allows you to execute SELECT queries without any parameters and get returned results in a SqlDataReader.

Public Shared Function

ExecuteReader(ByVal sql As String, ByVal params As SqlParameter) As SqlDataReader

Allows you to execute SELECT queries with parameters and get returned results in a SqlDataReader.

Public Shared Function

ExecuteScalar(ByVal sql As String) As Object

Allows you to execute queries without any parameters that return a single value.

Public Shared Function

ExecuteScalar(ByVal sql As String, ByVal params As SqlParameter) As Object

Allows you to execute queries with parameters that return a single value.

Public Shared Function

ExecuteDataSet(ByVal sql As String) As DataSet

Allows you to execute SELECT queries without any parameters and get returned results in a DataSet.

Public Shared Function

ExecuteDataSet(ByVal sql As String, ByVal params As SqlParameter) As DataSet

Allows you to execute SELECT queries with parameters and get returned results in a DataSet.

Figure 5: Methods of the SqlHelper class.

 

All the SqlHelper methods are shared (static in C#) so you can call them without creating an instance of SqlHelper. Listing One shows the complete source code of the SqlHelper class.

 

We store the database connection string in the web.config file; Figure 6 shows the relevant markup.

 

 

   

    "Data Source=.\SQLEXPRESS;AttachDbFilename=

    |DataDirectory|\BlogDb.mdf;Integrated Security=

    True;User Instance=True" providerName=

    "System.Data.SqlClient"/>

 

Figure 6: Database connection string in the web.config file.

 

We use the section to store the connection string. Note the connection string format SQL Server 2005 Express Edition allows you to dynamically attach database files. The .mdf file to attach is specified via the AttachDbFilename attribute. Because we use the default authentication mode of SQL Server 2005 Express, we set the Integrated Security attribute to True.

 

In the shared constructor of the SqlHelper class, we pick up the above-mentioned connection string. In each of the public methods, we essentially create a new instance of the SqlConnection and SqlCommand object. We then configure the command object by setting its properties, such as CommandText and Connection. If our query has any parameters, we also populate the Parameters collection of the command object using a private method (FillParameters). We then call the ExecuteNonQuery, ExecuteScalar, or ExecuteReader methods of the command object.

 

Web Forms and User Controls

We ll use ASP.NET Web forms and user controls to build our user interface. All in all, we ll develop eight Web forms, three user controls, and one master page (see Figure 7). Note that throughout our application we use the code-behind style of coding.

 

Item

Web Form/User Control/Master Page

Description

Default.aspx

Web form

Acts as the default page of the Web site. It displays blog posts in a GridView.

ShowPost.aspx

Web form

When a user selects a specific post from Default.aspx they are taken to ShowPost.aspx, where the selected post is displayed along with the user feedback. Users can also submit their own feedback from this page.

Archives.aspx

Web form

Allows users to see blog posts for a specific day, week, or month. Once a user selects a date range, all the posts within the selected range are displayed. Selecting a specific post takes the user to the ShowPost.aspx page.

Login.aspx

Web form

This page is primarily for the site administrator. The administrator can log in and add new posts or edit existing posts.

RssFeed.aspx

Web form

This page generates RSS markup for syndication purposes. Any RSS reader can grab and consume this feed.

CategoryManager.aspx

Web form

This page is used by the administrator to add/edit/delete blog categories.

LinkManager.aspx

Web form

This page is used by the administrator to add/edit/delete links.

PostManager.aspx

Web form

This page is used by the administrator to add/edit/delete blog posts.

Categories.ascx

User control

Displays available blog categories; this is used in the master page.

Links.ascx

User control

Displays links and is used in the master page.

Statsl.ascx

User control

Displays blog statistics such as total number of posts and total number of comments; this is used in the master page.

MasterPage.master

Master page

Provides a consistent layout to all the other Web forms.

Figure 7: List of Web forms, user controls, and a master page.

 

Creating a User Control for Displaying Categories

The Categories user control displays all the available blog categories in a GridView. To create this user control, add to your Web site a new Web user control named Categories.ascx. Drag and drop a GridView and SQL Data Source control on it. From the smart tag of SQL Data Source control choose Configure DataSource to start the configure data source wizard. Choosing the connection string stored in the web.config configures the SQL Data Source control to select all the records from the PostCategories table. Figure 8 shows the relevant step from the configure data source wizard.

 


Figure 8: Configuring the SQL Data Source control.

 

After you complete the configuration of the SQL Data Source control, set the DataSourceID property of the GridView to the ID of the SQL Data Source control. You can do this either via the property window or via a smart tag of GridView. Next, choose Edit Fields from the smart tag of GridView to open the Fields dialog box (see Figure 9). Then add one HyperLinkField and set its properties as shown in Figure 10.

 


Figure 9: Managing GridView fields.

 

Property

Value

HeaderText

Categories

DataTextField

Name

DataNavigateUrlFields

Id

DataNavigateUrlFormatString

~/default.aspx?categoryid={0}

Figure 10: Configuring a HyperLinkField of GridView.

 

Note how the DataNavigateUrlFormatString property is set. This way GridView will render hyperlinks to Default.aspx with a category ID substituted in the query string. Figure 11 shows the complete markup of the Categories user control.

 

<%@ Control Language="VB" AutoEventWireup="false"

CodeFile="Categories.ascx.vb" Inherits="Categories" %>

AutoGenerateColumns="False" CellPadding="4"

DataKeyNames="Id" DataSourceID="SqlDataSource1"

ForeColor="#333333" GridLines="None" Width="100%">

ForeColor="White" />

DataTextField="Name" HeaderText="Categories" >

ForeColor="#333333" />

HorizontalAlign="Center" />

ForeColor="White" />

ConnectionString="<%$ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT * FROM [PostCategories]">

Figure 11: Markup of Categories.ascx.

 

Creating a User Control for Displaying Links

Developing a user control for displaying links is very similar to the Categories user control we just developed the only difference is in the property values of the HyperLinkField.

 

Add a new Web user control to your Web site and name it Links.ascx. Drag and drop a GridView and SQL Data Source control on it. From the smart tag of SQL Data Source control choose Configure DataSource to start the configure data source wizard. Choosing the connection string stored in the web.config, configure the SQL Data Source control to select all the records from the Links table.

 

When you complete the configuration of the SQL Data Source control, set the DataSourceID property of the GridView to the ID of the SQL Data Source control. Add a HyperLinkField to the GridView and set its properties as shown in Figure 12. Figure 13 shows the complete markup of Links.ascx.

 

Property

Value

HeaderText

Links

DataTextField

Title

DataNavigateUrlFields

URL

DataNavigateUrlFormatString

{0}

Figure 12: GridView HyperLinkField properties.

 

<%@ Control Language="VB" AutoEventWireup="false"

CodeFile="Links.ascx.vb" Inherits="Links" %>

AutoGenerateColumns="False" CellPadding="4"

DataKeyNames="Id" DataSourceID="SqlDataSource1"

ForeColor="#333333" GridLines="None"

Width="100%">

ForeColor="White" />

DataNavigateUrlFormatString="{0}"

DataTextField="Title" HeaderText="Links">

ForeColor="#333333" />

HorizontalAlign="Center" />

ForeColor="White" />

ConnectionString="<%$ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT * FROM [Links] ORDER BY [Sequence]">

Figure 13: Markup up of Links.ascx.

 

Creating a User Control for Displaying Blog Statistics

We need to develop a user control that displays statistics of the blog, such as total number of posts and total number of comments. Add one more user control to your Web site called Stats.ascx (see Figure 14), then add an HTML table to it with three rows and two columns.

 

<%@ Control Language="VB" AutoEventWireup="false"

CodeFile="Stats.ascx.vb" Inherits="Statsl" %>

   

       

   

   

       

       

   

   

       

       

   

           

  Font-Bold="True" Text="Statistics">

  

           

  Text="Posts :" Font-Bold="True">

           

            Font-Bold="True">

  

           

  Text="Comments :" Font-Bold="True">

  

           

  Font-Bold="True">

  

Figure 14: Markup of Stats.ascx.

 

The labels Label3 and Label4 display the total number of posts and total number of comments, respectively. To obtain these statistics we need to execute queries against our database. Go to the Page_Load event handler of the user control and enter the code shown in Figure 15.

 

Protected Sub Page_Load(ByVal sender As Object,

ByVal e As System.EventArgs) Handles Me.Load

       If Not IsPostBack Then

           Label3.Text = SqlHelper.ExecuteScalar

  ("select count(*) from posts")

           Label4.Text = SqlHelper.ExecuteScalar

  ("select count(*) from comments")

       End If

End Sub

Figure 15: The Page_Load event handler of Stats.ascx.

 

Here we use the SqlHelper class and, using its ExecuteScalar method, fire two queries: one for retrieving the total number of posts and the other for retrieving the total number of comments. The return value of the ExecuteScalar method is displayed in the corresponding labels.

 

Conclusion

We initiated this three-part Build Your Own Blog series by detailing functional requirements, database schema, and user interface elements. We developed a data access component that encapsulates the frequently required operations. We then developed user controls that will be used later on our pages. We also got a glimpse of the GridView control and how it eases our job for us while developing the Categories user control. In Part II we ll develop the master page required by our application, administrative pages, and a page to display blog posts.

 

The sample code for this series is available for download.

 

Bipin Joshi is the founder and owner of BinaryIntellect Consulting (http://www.binaryintellect.com), where he conducts professional training programs on .NET technologies. He is the author of Developer s Guide to ASP.NET 2.0 (http://www.binaryintellect.com/books) and co-author of three WROX books on .NET 1.x. He writes regularly for http://www.DotNetBips.com, a community Web site he founded in the early days of .NET. He is a Microsoft MVP, MCAD, MCT, and member of ASPInsiders. He jots down his thoughts about .NET, life, and Yoga at http://www.bipinjoshi.com. He also conducts workshops on Yoga and Meditation, where he helps IT professionals develop a positive personality. You can contact him at mailto:[email protected].

 

Begin Listing One SqlHelper class source code

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.Text

Namespace DAL

Public Class SqlHelper

Private Shared strConn As String

Shared Sub New()

strConn = ConfigurationManager.ConnectionStrings

("ConnectionString").ConnectionString

End Sub

Public Shared Function ExecuteNonQuery

(ByVal query As String) As Integer

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(query, cnn)

cnn.Open()

Dim retval As Integer = cmd.ExecuteNonQuery()

cnn.Close()

Return retval

End Function

Public Shared Function ExecuteNonQuery

(ByVal query As String, ByVal params() As SqlParameter)

As Integer

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(query, cnn)

FillParameters(cmd, params)

cnn.Open()

Dim retval As Integer = cmd.ExecuteNonQuery()

cnn.Close()

Return retval

End Function

Public Shared Function ExecuteReader

(ByVal sql As String) As SqlDataReader

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(sql, cnn)

Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

End Function

Public Shared Function ExecuteReader(ByVal sql As String,

ByVal params() As SqlParameter) As SqlDataReader

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(sql, cnn)

FillParameters(cmd, params)

Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

End Function

Public Shared Function ExecuteScalar(ByVal sql As String)

 As Object

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(sql, cnn)

cnn.Open()

Dim retval As Object = cmd.ExecuteScalar()

cnn.Close()

Return retval

End Function

Public Shared Function ExecuteScalar(ByVal sql As String,

ByVal params() As SqlParameter) As Object

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(sql, cnn)

FillParameters(cmd, params)

cnn.Open()

Dim retval As Object = cmd.ExecuteScalar()

cnn.Close()

Return retval

End Function

Public Shared Function ExecuteDataSet(ByVal sql As String)

 As DataSet

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(sql, cnn)

Dim da As SqlDataAdapter = New SqlDataAdapter

da.SelectCommand = cmd

Dim ds As DataSet = New DataSet

da.Fill(ds)

Return ds

End Function

Public Shared Function ExecuteDataSet(ByVal sql As String,

 ByVal params() As SqlParameter) As DataSet

Dim cnn As SqlConnection = New SqlConnection(strConn)

Dim cmd As SqlCommand = New SqlCommand(sql, cnn)

FillParameters(cmd, params)

Dim da As SqlDataAdapter = New SqlDataAdapter

da.SelectCommand = cmd

Dim ds As DataSet = New DataSet

da.Fill(ds)

Return ds

End Function

Private Shared Sub FillParameters(ByVal cmd As SqlCommand,

 ByVal parameters() As SqlParameter)

Dim i As Integer

For i = 0 To parameters.Length - 1

cmd.Parameters.Add(parameters(i))

Next

End Sub

End Class

End Namespace

End Listing One

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish