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.
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).
SELECT BlogID, CommentsCount, PubDate, DateUpdated, Description, Summary, Title FROM BlogPostsView
Simply use the corresponding stored procedures (dbo.spCreateBlogPost, dbo.spDeleteBlogPost)
SELECT BlogID, Title, Summary, Description, PubDate, DateUpdated, CommentsCount FROM BlogPostsView WHERE (BlogID = @BlogID)
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.
SELECT BlogCommentID, BlogID, Username, Email, URL, Comment, DateCreated, DateUpdated FROM BlogComments WHERE (BlogID = @BlogID)
SELECT COUNT(*) FROM BlogComments WHERE BlogID = @BlogID
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.
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
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!