Inserting the Blogs
And above we've inserted the first blog with the userid we generated with the first display. Please note that I've replaced the HTML breaks (<BR>) with just regular newlines. This is what we'd expect from an input element from a web page, which will eventually be how these blogs are input. However, I've left the anchor (<a href=>) alone since we're not up to dealing with that. I'll skip a screenshot of inserting the second blog entry since doing so should be fairly simple. The only potential stumbling block is that the second blog has some single quotes. To properly insert them, you need to escape the single quote with another single quote. For instance, I've
should instead be I''ve
(that's two single quotes together).
Now let's head back to Visual Studio and code the routines to grab the blog entries. If you looked at the code from the previous page, I've now taken my page layout and broken it out across several methods. The method that deals with retrieving the blogs is called showBlogs()
. Following is a collapsed example.
The Code Broken Up
Within the showBlogs()
method we're going to query the database, then take the results and format them into our previously created format. First, let's take a look at the showBlogs()
private string showBlogs()
string content = null;
string select = "SELECT top 30 blogID, blogTitle, blogText, displayName, displayDate, " +
"CASE " +
"WHEN showEmail = 1 THEN ' (<a href='mailto: " + email + '">' + email + '</a>)' " +
"ELSE '' " +
"END AS 'email', " +
"(SELECT COUNT(*) FROM comments WHERE blogID=a.blogID and visible=1) as Comments " +
"FROM blogs a " +
"JOIN users b on " +
"a.userID=b.userID " +
"ORDER BY displayDate Desc";
SqlDataReader dr = query(select);
content += @"<div class='blogEntry'>
<div class='title'>" + dr["blogTitle"] + @"</div>
<div class='commands'><a href=''>Edit</a> / <a href=''>Delete</a></div>
<div class='author'>By " + dr["displayName"] + dr["email"] + @"</div>
<div class='date'>" + Convert.ToDateTime(dr["displayDate"]).ToString("dd MMM yyyy") + @"</div>
<div class='text'>" + Convert.ToString(dr["blogText"]).Replace("n", "<br>") + @"</div>
<div class='comments'><a href=''>Comments (" + dr["Comments"] + @") </a></div>
So the first thing I do is initialize a string called content which I populate with the blog text, then return at the end of the method. That's just like what we've done previously. Then I write out my select statement, which I do with a bunch of string concatenations. I'm selecting the top 30 blog entries, then I join in the users table to get displayName and showEmail. I then use the showEmail to do a SQL case (same idea as a C# case) to see if I should return an empty string ('') or an anchor mailto with the email address. Finally, I do a subquery against the comments table to bring back a count of how many comments there are for this blog.
With the select statement defined, I then run it against a method called query(). We'll look at that shortly. It returns a SqlDataReader object... very similar to the OleDbDataReader object we've seen in previous tutorials, but specific to SQL server. We then loop through the SqlDataReader object just like we did with OleDbDataReader, though instead of accessing the data reader item offset via offset number (ie, dr.GetValue(1)) we do it via field name (dr["blogTitle"]). This makes the code much more readable.
Lastly, we dispose of the data reader object and return the blog entries.
As noted we made use of another method called query()
. This was a way for us to move our database query code out of this method. Since we'll be querying the database in other places, it makes sense to put the code in a central place so we don't have to repeat it. Here's the code for that.
private SqlDataReader query(string select)
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["blogDatabase"].ConnectionString);
SqlCommand cm = new SqlCommand(select, conn);
Here is the query()
method. Though it's using commands specific to SQL server instead of the more generic OleDB commands we've used in previous tutorials, the syntax is virtually the same. Please note that this requires adding a namespace to the top of the code page. As usual, just type it out and with your cursor at the end of the word (SqlConnection for instance), a red underline appears. Mouse over that and Visual Studio will let you add the namespace automatically. You may have noticed I'm pulling a connection string from the ConnectionStrings section of my web.config. Don't forget to set up the connection string in your web.config! I then return the data reader object that cm.ExecuteReader() creates. Also notice there's no error handling. Because the method is made to return a SqlDataReader object, I need to return one from every code path. If I did try / catch in here, the catch would be unable to return a SqlDataReader object since there's no way to create one that's null or empty. Instead, error handling needs to be done where you call this method. In my case, I didn't do that... so if there's an error in the SQL or a problem with a db, the whole web application will just display an error. That's ok, though... we can always go back later to add some error handling.
So there we go. We now have the blog display handled. Next we're going to handle user authentication. Before we can write a blog, we need to be able to log in and prove who we are.