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
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
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
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!
Nicholas Barger's Blog 