Now that we have our layout, css and general HTML in place, we need to automate the dynamic parts. I'm going to begin that process by taking our blogs and manually inserting them into the database. Since the blogs also depend on a userid, that also means I need to insert a user into the user table.

Inserting the User
Above I've inserted myself into a user. As you may notice, my userid is 1. The password will be an md5 hash (because it's pretty easy to implement, though not especially secure). To figure out the md5 hash of a password, try searching google for an md5 hash generator. Creating an md5 hash in C# is fairly trivial, but we're not quite that far yet. We'll get there when we cover user logins. Here's an example of an md5 hasher. You may notice I've set my userlevel to 255... for fields of type tinyInt, that's the highest value possible and I'll be using it to denote a site admin. You can do whatever you want, of course.

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() method.

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.

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.