An Example of Writing Your Own Blog Engine (mini-version) Part Two

This is a three part series, if you haven’t already read part one, you may want to start there (An Example of Writing Your Own Blog Engine (mini-version)). Also, please check back soon for the final part (presentation layer).

Data Access Layer

We pick up in this article after finishing setting up the database. We now want to go ahead and begin setting up the programmatic access to manipulating data within our newly created database. This can be done several different ways and there are pro’s and con’s to each. Let’s briefly, conceptually, discuss two possible ways of developing data access layers.

First, if you’ve read tutorials from Microsoft, as well as many other sources, you will generally see reference to a more GUI approach to DAL development. Primarily, I am talking about creating a DataSet and then working with .XSD’s. This gives you some obvious advantages such as formalizing the data you will be working with fairly precisely, developing in a very quick manner with wizard-style setup, ease of development is greatly increased for entry level developers, and it provides better visual demonstration of what you’re developing.

Secondly, you could create the entire data access layer in code by creating functions that interact with the database rather than allowing visual studio to develop this code for you. It is more tedious of course, but you have exacting control, complete knowledge of what is happening under the covers, and in my opinion is vastly more scaleable and flexible.

For this demonstration, we will be using the first method of developing a DataSet for our data access. I’m sure there are additional considerations to make when choosing which route to take, that will be part of your analysis and planning phase when developing your RSS engine (or any project).

I’m going to assume you know how to create DataTables, queries, etc. so that will not be covered in this article, however if you are unclear, there are many articles through Microsoft which demonstrate how to use DataSets; please refer to one of these.

BlogPostsView

Blog Posts View

This DataTable will be responsible for showing the primary details of the blog posts, including title, summary, description (which is the post itself), pubdate (date created), and comments count (a count of all comments placed for this blog post).

FillBlogPosts, GetBlogPosts

SELECT BlogID, CommentsCount, PubDate, DateUpdated, Description, Summary, Title
FROM BlogPostsView

CreateBlogPost, DeleteBlogPost

Simply use the corresponding stored procedures (dbo.spCreateBlogPost, dbo.spDeleteBlogPost)

FillBlogPostByBlogPostID, GetBlogPostByBlogID

SELECT BlogID, Title, Summary, Description, PubDate, DateUpdated, CommentsCount
FROM BlogPostsView
WHERE (BlogID = @BlogID)

FillByMostRecentBlogPost, GetMostRecentBlogPost

SELECT TOP (1) BlogID, CommentsCount, PubDate, DateUpdated, Description, Summary, Title
FROM BlogPostsView
ORDER BY PubDate DESC


Blog Comments View

This datatable represents the comments users can add to blog posts.

FillBlogComments, GetBlogComments

SELECT BlogCommentID, BlogID, Username, Email, URL, Comment, DateCreated, DateUpdated
FROM BlogComments
WHERE (BlogID = @BlogID)

GetNumberOfBlogComments

SELECT COUNT(*) FROM BlogComments WHERE BlogID = @BlogID

BlogPostList

Blog Post List

This datatable is responsible for displaying the blog posts in a list form. This is slightly different than the BlogPostView because we’re not retrieving back the actual blog post content, which is a lot heavier of a data pull. By creating a seperate datatable, we’ll remain pretty lean and efficient.

FillBlogPostList, GetBlogPostList

SELECT BlogID, Title, PubDate, CommentsCount
FROM BlogPostsView
ORDER BY PubDate DESC

An additional delete is listed in this view, because I had use for it without switching datatables – this is not really necessary though.

Sidenote: If you wanted to develop this code without using the GUI method, you could create a class that simply has your dataaccess code in there in place of this XSD-style DAL. An example of this could look like the following:

Public Function DeleteBlogPost(ByVal BlogID as Integer) as Boolean
    Dim MyConnection As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ToString())
    Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("dbo.spDeleteBlogPost", MyConnection)
    cmd.CommandType = Data.CommandType.StoredProcedure

    cmd.Parameters.AddWithValue("@BlogID ", BlogID)

    Try
        MyConnection.Open()
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        'Error handling
    Finally
        MyConnection.Close()
        cmd = Nothing
    End Try

    Return True
End Function

Business Layer

The following code is used in the business layer. This is very simple code, so I want go into too much detail explaining it; as an overview though, we are simply calling up a layer to the DAL to retrieve, create, edit, or delete data.

One slight trick is to create adapters as properties that can be then called a little bit easier later on, you can see that at the top of the code block.

Finally, you can notice there is some very basic validation being done at this level, it would be good practice to add to this for greater security/control over the data manipulation.

Imports Microsoft.VisualBasic

Imports Blog

Public Class BlogBLL
    Private _blogPostsAdapter As BlogTableAdapters.BlogPostsViewTableAdapter
    Private _blogCommentsAdapter As BlogTableAdapters.BlogCommentsTableAdapter
    Private _blogPostListAdapter As BlogTableAdapters.BlogPostListTableAdapter

    Public Property BlogPostsAdapter() As BlogTableAdapters.BlogPostsViewTableAdapter
        Get
            If _blogPostsAdapter Is Nothing Then
                _blogPostsAdapter = New BlogTableAdapters.BlogPostsViewTableAdapter
            End If

            Return _blogPostsAdapter
        End Get
        Set(ByVal value As BlogTableAdapters.BlogPostsViewTableAdapter)
            _blogPostsAdapter = value
        End Set
    End Property

    Public Property BlogCommentsAdapter() As BlogTableAdapters.BlogCommentsTableAdapter
        Get
            If _blogCommentsAdapter Is Nothing Then
                _blogCommentsAdapter = New BlogTableAdapters.BlogCommentsTableAdapter
            End If

            Return _blogCommentsAdapter
        End Get
        Set(ByVal value As BlogTableAdapters.BlogCommentsTableAdapter)
            _blogCommentsAdapter = value
        End Set
    End Property

    Public Property BlogPostListAdapter() As BlogTableAdapters.BlogPostListTableAdapter
        Get
            If _blogPostListAdapter Is Nothing Then
                _blogPostListAdapter = New BlogTableAdapters.BlogPostListTableAdapter
            End If

            Return _blogPostListAdapter
        End Get
        Set(ByVal value As BlogTableAdapters.BlogPostListTableAdapter)
            _blogPostListAdapter = value
        End Set
    End Property

    Public Function GetBlogPosts() As BlogPostsViewDataTable
        Return BlogPostsAdapter.GetBlogPosts()
    End Function

    Public Function GetBlogPosts(ByVal searchInput As String) As BlogPostsViewDataTable
        Return BlogPostsAdapter.GetBlogPostsBySearchInput(searchInput)
    End Function

    Public Function GetBlogPost(ByVal blogID As Integer) As BlogPostsViewDataTable
        Return BlogPostsAdapter.GetBlogPostsByBlogPostID(blogID)
    End Function

    Public Function GetMostRecentBlogPost() As BlogPostsViewDataTable
        Return BlogPostsAdapter.GetMostRecentBlogPost()
    End Function

    Public Function GetBlogComments(ByVal blogID As Integer) As BlogCommentsDataTable
        Return BlogCommentsAdapter.GetBlogComments(blogID)
    End Function

    Public Function CreateBlogPost(ByVal title As String, _
        ByVal summary As String, _
        ByVal description As String, _
        ByRef blogID As Integer) As Boolean

        'Check that the blog's title is not empty
        If String.IsNullOrEmpty(title) = True Then
            Throw New Exception("Title can not be empty.")
        End If

        'Check that the blog post is not empty
        If String.IsNullOrEmpty(description) = True Then
            Throw New Exception("Blog post can not be empty.")
        End If

        Return BlogPostsAdapter.CreateBlogPost(title, summary, description, blogID)
    End Function

    Public Function UpdateBlogPost(ByVal blogID As Integer, _
        ByVal title As String, _
        ByVal summary As String, _
        ByVal description As String) As Boolean

        'Check that the blog's title is not empty
        If String.IsNullOrEmpty(title) = True Then
            Throw New Exception("Title can not be empty.")
        End If

        'Check that the blog post is not empty
        If String.IsNullOrEmpty(description) = True Then
            Throw New Exception("Blog post can not be empty.")
        End If

        Return BlogPostsAdapter.UpdateBlogPost(blogID, title, description, summary)
    End Function

    Public Function DeleteBlogPost(ByVal blogID As Integer) As Boolean
        If blogID <= 0 Then
            Throw New Exception("Blog ID must be greater than 0.")
        End If

        Return BlogPostsAdapter.DeleteBlogPost(blogID)
    End Function

    Public Function CreateBlogComment(ByVal blogID As Integer, _
        ByVal comment As String, _
        Optional ByVal username As String = Nothing, _
        Optional ByVal email As String = Nothing, _
        Optional ByVal url As String = Nothing) As Boolean

        Return BlogCommentsAdapter.CreateBlogComment(blogID, username, email, url, comment)
    End Function

    Public Function GetNumberOfBlogComments(ByVal blogID As Integer)
        Return BlogCommentsAdapter.GetNumberOfBlogComments(blogID)
    End Function

    Public Function GetBlogPostList()
        Return BlogPostListAdapter.GetBlogPostList()
    End Function
End Class

Ok, that’s it for this part of the series. Please check back later when I demonstrate how we start implenting the presentation layer!

Remember, this is not only good practice for writing a simple application, but it’s also really beneficial to develop the basis of a blog engine that you can now build any custom features you can’t get with out of the box blog services or engines. So, at the end of this series, be sure to take this code and make it your own by adding to it. Feel free to write me back with additional features you’ve come up with in the comments below!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s