ASP.NET Website #6: Guest Book

This is the sixth website of my project to create 30 ASP.NET websites, highlighting various aspects of this amazing technology! For this project, I wanted to do something with databases and what could be more ubiquitousthan a form for leaving comments? Everyone needs one and now you can have one, too :-) Let’s start by taking a look at how the main screen looks.

You can see that the main input form is quite simple and that there is already a comment added by myself.

How To Use It

Using it couldn’t be simpler; add your name in the first text box, then your comment in the larger one below, before clicking on Add Comment. To help you find and order the comments, any of the column headers (Name, Added and Comment) can be clicked to sort the data in ascending or descending order.

Main Focus

Now that’s out of the way, let’s get down to the “nitty-gritty”. There are two main things that I’d like to focus on in this post and they are how I persist the data (Entity Framework) and how I present it (using asp:GridView and asp:ObjectDataSource). To start with, I am going to tackle my use of Entity Framework because without that, there is no data.

Entity Framework

Entity Framework, or EF, is an ORM which is an acronym for Object Relational Mapping and basically allows us to map class objects to tables in a database. What makes this even more special is that it allows us to convert the non-scalar values (typical in OO languages) into scalar ones which are more suited to relational databases. Oh…and it’s pretty seamless as you’ll see. Step one is to use the NuGet package manager to install Entity Framework.

Tools > NuGet Package Manager > Manage NuGet Packages for Solution…

For this example, I used version 6.0 but be aware that a new one is coming out soon which amongst other things, strips things down into a leaner model. It shouldn’t matter for this though because we are going to use a code first approach. Code first? Yep! Code first means we start with…code…first. We basically create a model of our data (as a class) and that becomes the lynch pin on which all of this technology sits. Since we’re creating a guest comment book, in terms of data, all we need is something like the following: name, date and comment.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
namespace GuestForm
{
public class GuestComment
{
public int ID { get; set; }

[Required]
[StringLength(50)]
public string Name { get; set; }

[Required]
public DateTime Added { get; set; }

[Required]
[StringLength(1000)]
public string Comment { get; set; }
}
}

It’s mostly self explanatory but some key things worth noting are:

  • The use of the ID field. That’s needed as a primary key and
  • [Required] is how you label non-null fields.

That effectively describes what our data will look like, but there’s still a little more needed - we have to create something called a DbContext.

1
2
3
4
5
6
7
8
9
10
11
12
13
namespace GuestForm
{
public class GuestCommentDbContext : DbContext
{
public GuestCommentDbContext()
: base("name=GuestCommentDbContext")
{

}

public DbSet<GuestComment> GuestComments { get; set; }
}
}

In a sense, this acts like a database connection but it’s automatic and effectively creates/manages a list of your object (GuestComment). Can you see where we call the base constructor? Here, we’re telling EF the name of the connection string in the Web.Config file, so it knows which database to connect to, and how. Which, conveniently, leads us onto the actual entry in that file:

1
2
3
<connectionStrings>
<add name="GuestCommentDbContext" connectionString="Data Source=(localdb)\v11.0;AttachDbFileName=|DataDirectory|\GuestComment.mdf;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

One last thing I wanted was to use the repository pattern to wrap up some of this data access.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
namespace GuestForm
{
public class GuestCommentRepository
{
GuestCommentDbContext _Context = new GuestCommentDbContext();

public void AddComment(string name, string comment)
{
GuestComment gc = new GuestComment()
{
Name = name,
Comment = comment,
Added = DateTime.Now
};
_Context.GuestComments.Add(gc);
_Context.SaveChanges();
}
}
}

That’s not all of it, but just enough for us to see how we use it to add comments to the database. Lines 15 and 16 are the ones that add the new object to the list and then finally persist it. I haven’t mentioned this yet, but one really exceptional advantage about EF is that we don’t need to create, open or close the database, since it is all done for us. In this case, I just need to think about adding to the GuestComments list and asking the DbContext to save the data. Sit back with a warm glass of milk and relax - all the hard work is done for us. Just to make this complete, below you can see how I wire this into the button on the main page, referencing the other controls.

1
2
3
4
5
6
7
8
protected void buttonAdd_Click(object sender, EventArgs e)
{
var gcr = new GuestCommentRepository();
gcr.AddComment(nameTextbox.Text, commentTextbox.Text);
nameTextbox.Text = String.Empty;
commentTextbox.Text = String.Empty;
gridView.DataBind();
}

Fairly simple so far, but now we need to be able to see what has been added and retrieved so let’s take a look at the ASP.NET controls.

The GridView and ObjectDataSource

ObjectDataSource

Displaying the data is quite easy using Web Forms. Let’s start with the ObjectDataSource though because that links the controls with the EF classes.

1
<asp:ObjectDataSource ID="objDS" runat="server" SortParameterName="sortColumn" SelectMethod="GetGuestComments" TypeName="GuestForm.GuestCommentRepository"></asp:ObjectDataSource>

One thing to notice is the SelectMethod. That points to GetGuestComments which is a method in GuestForm.GuestCommentRepository. We did see the repository earlier but here we’re referring to a method I didn’t show because we didn’t need it at the time. The declaration also refers to an attribute SortParameterName which names sortColumn. That’s the parameter of GetGuestComments which helps us to sort the data. It’s time to take a look at some of that method so that you can pin what I have said into some kind of framework.

1
2
3
4
public List<GuestComment> GetGuestComments(string sortColumn)
{
if (sortColumn == String.Empty)
return (_Context.GuestComments.OrderByDescending(l => l.Added).ToList());

You can see that if no sortColumn is provided (== String.Empty) then we use LINQ to sort the list in descending order based on the date added. That makes a lot of sense since we usually want to see the most recent stuff first. In effect, the purpose of this class is to return a list of items sorted based on sortColumn. Grab your pogo stick because I now want to jump to the GridView and see how things threads into that, before returning here and tackling the sorting in more detail.

GridView

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<asp:GridView ID="gridView" runat="server" AllowPaging="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="objDS" ForeColor="#333333" GridLines="None" AllowSorting="True" HorizontalAlign="Center" Width="80%">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Added" HeaderText="Added" SortExpression="Added" />
<asp:BoundField DataField="Comment" HeaderText="Comment" SortExpression="Comment" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>

There’s a lot going on there, but really, once the GridView was bound to the ObjectDataSource, much of that was automatically taken care of. Some things worth noting are, though:

  • Sorting is turned on with AllowSorting="True" in the main tag.
  • The SortExpression in the asp:BoundField tags handle the field references for sorting.

Most of the other items are just presentational or auto-generated for us. The next step is to think about what is really happening when we click on a column heading.

Sorting the Columns

Let’s start by looking at the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public List<GuestComment> GetGuestComments(string sortColumn)
{
if (sortColumn == String.Empty)
return (_Context.GuestComments.OrderByDescending(l => l.Added).ToList());

// All strings are at least 4 chars, so this is safe:
bool descending =
(sortColumn.Substring(sortColumn.Length - 4, 4) == "DESC" ?
true :
false);

if (sortColumn.Substring(0, 4) == "Name")
{
if (descending == true)
return (_Context.GuestComments.OrderByDescending(l => l.Name).ToList());
else
return (_Context.GuestComments.OrderBy(l => l.Name).ToList());
}
else if (sortColumn.Substring(0, 5) == "Added")
{
if (descending == true)
return (_Context.GuestComments.OrderBy(l => l.Added).ToList());
else
return (_Context.GuestComments.OrderByDescending(l => l.Added).ToList());

}
else if (sortColumn.Substring(0, 7) == "Comment")
{
if (sortColumn.Length > 7)
return (_Context.GuestComments.OrderByDescending(l => l.Comment).ToList());
else
return (_Context.GuestComments.OrderBy(l => l.Comment).ToList());
}

throw new ArgumentException("Invalid sortColumn");
}

I’m going to start by saying that this is messy. I know. I know. :-( I just don’t know a better way of fusing EF and the GridView sorting, so if anyone knows a nicer approach, I would love to hear about it.

That aside, let’s talk about how I chose to do it. If there isn’t a parameter passed in, the default way of sorting is in descending order of date. You can see that happen in lines 3-4 and was what we talked about a little earlier. The next thing I do is look for the string DESC on the end of the sort column; that’s my big clue that this should be descending order.

Allow your eyes to rest on lines 7-10 if you’d like to see where that can of magic happens. To illustrate some input parameter situations, let’s look at an example. If the ADDED column is to be sorted, the asp controls will pass the string “ADDED" into sortColumn. If it should then later be desired in descending order, the parameter value will become “ADDED DESC". Much the same happens for the other columns, too. The next three blocks are each checking for which field is being referenced. Since this is quite clanky (technical term tm), I need to be careful of the order of comparison to avoid out of bounds references. Each follows a standard pattern:

  1. Is it field X?
    1. Yes. Does it need to be sorted in descending order?
      1. Yes, use OrderByDescending.
      2. No, use OrderBy.

So, depending on the choices supplied, a new list is generated which is sorted in the order requested. The GridView then takes care of displaying it for us. That more or less sums up how this works but if you have any questions, please ask.

What Did I Learn?

  • There are a number of ways to use Entity Framework in addition to code first but I am glad I started with this. In EF7, it will all be code first since they are removing the visual designer.
  • This is the first ASP.NET database backed project I have created so switching between a local db, an SQL hosted instance on my machine and a remote one was fun.

What Might I Do Differently?

  • I really want to know of a more efficient way to join the controls and EF when it comes to sorting. There must be a better way :-)
  • One thing I used in the last project but not this one are validators. They certainly are valid but I was more focussed on the other aspects, this time.
  • What if I didn’t use EF? I think as a comparison, I might try doing it by ‘hand’ next time.

Before you go, please take a look at the Guest Book application source in the GitHub repository for more information.


Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com