Windows DevCenter    
 Published on Windows DevCenter (http://www.windowsdevcenter.com/)
 See this if you're having trouble printing code examples


Build a Web-Based Bug Tracking App

by Jesse Liberty
05/09/2006

For the last few articles, I've been discussing applications I was asked to build or to assist with. In this article I will cover a bit of quick ASP.NET programming I've done for myself that, one day soon, may turn out to be a product.

Please note, this article assumes you are already comfortable with creating ASP.NET applications, using the Website Application Tool, and using Data Source controls with SqlServer.

"Save time, don't put the bugs in."
--Patrick Johnson

Every time I set out to create a project, I'm confronted with the same question: "How will we track the bugs?" I find there are a plethora of solutions to this problem, but none that I want. Most of the time, they are far too complex for the kind of 1-3 person project I tend to work on. Recently a light bulb came on (actually, it came on in my co-author Dan Hurwitz' head, but it illuminated this article). What I really want is the absolute simplest, easiest to use, totally reliable bug tracker and, well, that can't be all that hard to write myself.

What you learn when you set out on such a project is that the truly hard part is exercising the self-restraint to keep it truly simple and avoid adding features. Thus, Tiny Bug Tracker (TBT) will be built in three stages:

  1. The simplest usable bug tracker
  2. The few additional features I really, really want
  3. The business version (that is, the one on which we might build a business)

I won't be writing about phase 3 unless a) it is really interesting, and b) I decide that giving away that much of the source code won't kill the business before it is born. In any event, the first two parts should, I hope, illustrate a few interesting aspects of building ASP.NET applications. This particular article (which may well turn out to be part 1 of 2 or even part 1 of 3) will focus on the absolute minimal set of functionality: just enough to keep track of the bugs in a program and not one additional feature.

Further, as a development strategy, every time I have the option to do it fancy or do it simple, I'll choose simple--as long as I create reasonably factored code that will allow me to add features and fix it up once I start using the program.

The minimal feature set

To keep the feature set very small, I've divided the features into three groups:

  1. Those features that must be in the program
  2. Those features that will be in the program eventually, but can be done externally for now
  3. Those features I want, but which don't make the first cut

Before I started coding, I created three lists, even though I knew that by the time I wrote this article (having completed step 1), the lists would have changed. Here are my original lists:

Features that must be in the program

  1. Ability to add a bug
  2. Ability to modify a bug
  3. Ability to see all the bugs

Features that will be in the program eventually, but can be done externally for now

  1. Add users (use WAT) [Note: the WAT is described below]
  2. Add roles (use WAT)
  3. Audit trail of a bug's history (use DB)

Features I want, but which don't make the first cut

  1. Filter the list of bugs (by the following, for example):
    1. Just my bugs (for developer/ manager)
    2. Just my open bugs (for developer/ manager)
    3. All open bugs (for manager/ QA)
    4. All closed bugs (for manager/ QA)
    5. Just show stoppers (for team)
    6. Etc.
  2. Reports
    1. Show me all bugs by priority that have been open for more than n days.
    2. Etc. (by Etc., I mean there are a lot of potential reports)
  3. Role management (for example):
  4. User management

Stop second-guessing

I could have spent the next month second-guessing what belonged in each list. In any case, the lists would change as I developed the product. It turned out, for example, that displaying the audit trail of a bug made it into the first cut, only because the design I used for the database (discussed in just a moment) made this wicked easy to do.

Programming ASP.NET

Related Reading

Programming ASP.NET
Building Web Applications and Services with ASP.NET 2.0
By Jesse Liberty, Dan Hurwitz

Start With The Database

There are many ways to start a project like this, but for me, the easiest was to begin by designing the tables that would capture the data I already knew I'd need. I did not expect this design to be final, but I had a good idea of what I wanted, and creating the data design first facilitated creating the program using the tools that ASP.NET provides. Thus, I started with a simple database design, as illustrated in Figure 1.

DB Design
Figure 1. A simple database design

As you can see, each Bug is represented by a single entry in the Bug table and one or more entries in the BugHistories table. The entries in the BugHistories table serve as an audit trail for each time the bug is modified. The information captured is the time the modification is made (or the bug is created) in the TimeStamp field, who modified the bug, and both a ShortDescription (to be shown in the grid of bugs) and a LongDescription (to provide extensive information). Other information captured is the current severity (one of the values stored in the Severities table) and the current status (again, a value in the Statuses table), along with Notes (additional information about the bug or about fixing the bug) and the current owner.

Design Choice: Of course, I could have combined the Bug and BugHistories tables into one, but breaking them apart has a few advantages. First, the database will ensure that each bug has a unique ID (by making the BugID an identity column). Second, if it turns out there is immutable information about a bug (if, for example, we decide that the ShortDescription should never change) that data can be moved from BugHistories to Bugs, making the relationship crystal clear.

Identities

Both the Owner and the ModifiedBy fields are populated by values stored in the Users table of the ASPNETDB.MDF database (Figure 2) created by the Web Site Administration Tool (WAT).

user table
Figure 2. The Users table of the ASPNETDB.MDF database

Design Choice - I have intentionally denormalized the BugHistories table, using the string version of the UserName rather than the UserID. This makes the coding easier without much cost, although I'm open to changing it (but not just to keep the purists happy).

Using the WAT

In phase 2, we'll create pages in the application itself to create new users and assign them to roles (if you're in a rush, see my early article on personalization), but for now letting the WAT do the heavy lifting makes a lot of sense. We get all the structure we need without having to write any code (see Figure 3).

The WAT
Figure 3. Using the WAT

By using the WAT we can also configure roles for Customers, Developers, Managers, and QA, the four essential roles we'll use in phase 1 (see Figure 4).

Manage Roles
Figure 4. Configuring roles with the WAT

Now that we've used the WAT to seed the Users table with a few names and assign them to roles, we're ready to create bugs and review their history. To do so, we'll create just three pages: TBTWelcome, which will provide the login to identify the current user, TBTReportBug to provide a form to report (or update) a bug, and TBTReview to see a list of the bugs. Further, to provide a uniform look and feel, all three of these will be contained within a Master page (TBT.master) which provides a small menu and a copyright notice, as shown in Figure 5.

Master Page
Figure 5. The Master Page

All three pages will be contained within the ContentPlaceHolder, which allows them to share the banner, menu, and copyright, illustrated in Figure 6.

Login Page
Figure 6. The Content Page

Entering A Bug

Once the user logs in and clicks on Enter A Bug, the TBTReportBug.aspx page is displayed (see Figure 7).

EnterABug
Figure 7. TBTReportBug page

There are a few important things to notice about this page (after you get over how ugly it is). First, we don't ask the user to fill in many of the fields from the Bug and BugHistory tables. These include the BugID and BugHistoryID; each of these is automatically created by the database. Every new bug gets a new record in Bugs, as well as a new record in BugHistory, which is assigned a BugHistoryID of 1. Every time a bug is edited, a new record is created in BugHistories (with an incremented BugHistoryID), thus creating a set of BugHistory records for each Bug.

We also do not ask for the TimeStamp (applied automatically by the database) or the user's name (which we obtain with the following code):

string currentUser = Page.User.Identity.Name;

Three of the fields are drop-down lists: Severity, Status and Owner. The drop-downs are bound to individual SQLDataSources; the first two provide Select statements against the Severities and Statuses tables, respectively, and the third provides a select statement against the Users table:

&ltl;asp:SqlDataSource ID="OwnerDataSource" runat="server" 
  ConnectionString="<%$ ConnectionStrings:LoginConnectionString %>"
    SelectCommand="SELECT [UserId], [UserName] FROM 
       [vw_aspnet_Users] ORDER BY [UserName]">
</asp:SqlDataSource>

This code is not, of course, written by hand, but instead is created by dragging the SQL DataSource control onto the page and clicking on the smart tag to configure it (see Figure 8).

configureDS
Figure 8. Configuring the SQL DataSource

Reusing the Bug Entry Page

None of this would be tricky at all (in fact, you wouldn't really need to write any code), except that you want to be able to use this same page to edit a bug as well as to enter a new one (after all, you are collecting the same information). When you are editing a bug you will prefill the page with the most recent data for that bug.

Determining if you have reached this page to enter a new bug or to edit an existing bug is accomplished in the Page_Load method. You check two things: first, that you are not in a post back (that is, you arrived here from another page) and second, that the Select statement in the BugsDataSource control you will add to the page returns a non-null DataView. This works because you'll populate the BugsDataSource using a stored procedure spGetBug that takes a single parameter--a BugID:

Create PROCEDURE [dbo].[spGetBug]
@BugID int
AS
BEGIN
    SET NOCOUNT ON;
   select  top 1 
   BugID, BugHistoryID, ShortDescription, LongDescription, Notes,
   sev.SeverityID as Severity,
   statistics as Status, 
   Owner from BugHistories bh
   join Severities sev on sev.SeverityID = bh.Severity
   join Statuses stat on statiStics = bh.Status
   where BugID = @BugID
   order by BugHistoryID desc
END

This code will return the latest bug history information (if any) for the BugID passed in as a parameter. Of course, that begs the question: how do you pass in that BugID? The answer is to stash the BugID of the bug you're editing into SessionState, and then instruct the DataSource control to retrieve its parameter from session state, as shown in Figure 9.

FromSession
Figure 9. Retrieving parameter from session state

You'll see how to put the value into session state when we look at the Review page.

The result of this is that if there is a BugID stored in Session ("Edit"), the page is prefilled, as shown in Figure 10.

prefilled report
Figure 10. Prefilled Bug Report

Note that at the top of the page, the current update is for Bug 13 and will be the third revision. It is expected that the user will, at this point, modify whichever fields should be changed.

Design Decision: The presentation shown in Figure 10 only works if you already understand what you are seeing (the previous revision that, once you save your changes, will be revision 3). If this product would be used by more than a few people, the UI design might have to be revisited.

There are some additional interesting decisions to make now, including:

The latter question is interesting. The argument in favor is that the short description provides a quick way to show what is happening with the bug, but the (perhaps more compelling) argument against is that the short description is, effectively, the name of the bug, and having it change every time it is modified is bound to cause confusion.

This is also a place where potential features could creep in, the most notable of which is the ability to add new Statuses if none of the available choices meet your needs. For now, to keep this tiny and simple, we'll make such adjustments directly to the Severities table, but this may well be a feature we'll want pretty quickly.

Once we know that this is a revision, we'll need to save that fact in session state (along with the current BugID and BugHistory ID) so that when the user clicks the Save button, that information will be available to our event handler:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // see if this is a revision
        DataView dv = 
          this.BugsDataSource.Select(new DataSourceSelectArguments()) as DataView
        if (dv != null)  // yes it is a revision
        {
            DataTable dt = dv.Table;

            // get the row for the latest BugHistory
            DataRow dr = dt.Rows[0];  

            int BugID = Convert.ToInt32(dr["BugID"]);
            lblHeader.Text = "Update Bug " + BugID.ToString();

            int bugHistoryID = Convert.ToInt32(dr["BugHistoryID"]);
            bugHistoryID++;  // set the new BugHistory number
            lblHeader.Text += "    Revision: " + bugHistoryID.ToString();

            this.txtShortDescription.Text = dr["ShortDescription"].ToString();
            this.txtLongDescription.Text = 
                dr["LongDescription"] == null ? 
                    string.Empty : dr["LongDescription"].ToString();

            this.txtNotes.Text = dr["Notes"] == null ? 
                string.Empty : dr["Notes"].ToString();
            this.ddlOwner.SelectedValue = dr["Owner"].ToString();
            this.ddlSeverity.SelectedValue = dr["Severity"].ToString();
            this.ddlStatus.SelectedValue = dr["Status"].ToString();

            // stash away what we'll need to save the update 
            Session["IsUpdate"] = true;
            Session["BugID"] = BugID;
            Session["BugHistoryID"] = bugHistoryID;

        }   // end if dv not null
    }       // end if not postback
}           // end method

The essence of this code is that if this is a revision, we prefill the appropriate fields and save the session data we'll need. We can put this to use in the Save_Click event handler:

protected void btnSave_Click(object sender, EventArgs e)
{
    // get the current user (to use for updated by)
    string currentUser = Page.User.Identity.Name;
    int numInserted = 0;

    // either update or insert. Either inserts a new record
    // in BugHistory but only Insert creates a new bug
    if (Session["IsUpdate"] == null)
    {
        this.BugsDataSource.InsertParameters.Clear();
        this.BugsDataSource.InsertParameters.Add(
            "ModifiedBy", currentUser);
        this.BugsDataSource.InsertParameters.Add(
            "ShortDescription", this.txtShortDescription.Text);
        this.BugsDataSource.InsertParameters.Add(
            "LongDescription", this.txtLongDescription.Text);
        this.BugsDataSource.InsertParameters.Add(
            "Severity", this.ddlSeverity.SelectedValue);
        this.BugsDataSource.InsertParameters.Add(
            "Notes", this.txtNotes.Text);
        this.BugsDataSource.InsertParameters.Add(
            "Status", this.ddlStatus.SelectedValue);
        this.BugsDataSource.InsertParameters.Add(
            "Owner", this.ddlOwner.SelectedItem.Text);
        numInserted = this.BugsDataSource.Insert();
        Session.Remove("IsUpdate");
    }
    else
    {
        this.BugsDataSource.UpdateParameters.Clear();
        this.BugsDataSource.UpdateParameters.Add(
            "BugID", Session["BugID"].ToString());
        this.BugsDataSource.UpdateParameters.Add(
            "BugHistoryID", Session["BugHistoryID"].ToString());
        this.BugsDataSource.UpdateParameters.Add(
            "ModifiedBy", currentUser);
        this.BugsDataSource.UpdateParameters.Add(
            "ShortDescription", this.txtShortDescription.Text);
        this.BugsDataSource.UpdateParameters.Add(
            "LongDescription", this.txtLongDescription.Text);
        this.BugsDataSource.UpdateParameters.Add(
            "Severity", this.ddlSeverity.SelectedValue);
        this.BugsDataSource.UpdateParameters.Add(
            "Notes", this.txtNotes.Text);
        this.BugsDataSource.UpdateParameters.Add(
            "Status", this.ddlStatus.SelectedValue);
        this.BugsDataSource.UpdateParameters.Add(
            "Owner", this.ddlOwner.SelectedItem.Text);
        numInserted = this.BugsDataSource.Update();

    }
    if (numInserted == 0)
    {
        lblHeader.Text = "Unable to update database!";
        lblHeader.BackColor = System.Drawing.Color.Red;
        lblHeader.ForeColor = System.Drawing.Color.Yellow;
    }
    else
    {
        this.txtNotes.Text = string.Empty;
        this.txtShortDescription.Text = string.Empty;
        this.txtLongDescription.Text = string.Empty;
        this.ddlOwner.SelectedIndex = 0;
        this.ddlSeverity.SelectedIndex = 0;
        this.ddlStatus.SelectedIndex = 0;
        lblHeader.Text = "Database updated";
        lblHeader.BackColor = System.Drawing.Color.White;
        lblHeader.ForeColor = System.Drawing.Color.Black;
    }
    Response.Redirect("TBTReview.aspx");
}

For this code to make sense, however, we need to look at the Insert and Update commands in the data source, each of which calls a stored procedure: spNewBug and spUpdateBug, respectively. The code for updating a bug creates a new entry in BugHistories:

Create PROCEDURE [dbo].[spUpdateBug]
@BugID int,
@BugHistoryID int,
@ModifiedBy varchar(100),
@ShortDescription varchar(50),
@LongDescription ntext,
@Severity int,
@Notes ntext,
@Status int,
@Owner varchar(100)
AS
BEGIN
      insert into BugHistories ( BugID, BugHistoryID, ModifiedBy, 
      ShortDescription,LongDescription, Severity, Notes, 
      Status, Owner)
      values (
      @bugID, @BugHistoryID, @ModifiedBy, @ShortDescription,
      @LongDescription, @Severity, @Notes, @Status, @Owner)
END

If we're creating a new bug, however, we need to add an entry in both the Bugs table and the BugHistories table, and to ensure the integrity of the database, we want to do that within a transaction:

Create PROCEDURE [dbo].[spNewBug]
@ModifiedBy varchar(100),
@ShortDescription varchar(50),
@LongDescription ntext,
@Severity int,
@Notes ntext,
@Status int,
@Owner varchar(100)
AS
BEGIN
Begin Transaction 
   declare @bugID as int
   Insert into Bugs (PlaceHolder) values (@ShortDescription)
   select @bugID = @@identity
   if @@error <> 0 goto errorHandler
   insert into BugHistories ( BugID, BugHistoryID, ModifiedBy, 
      ShortDescription,LongDescription, Severity, Notes, 
      Status, Owner)
      values (
      @bugID, 1, @ModifiedBy, @ShortDescription,
      @LongDescription, @Severity, @Notes, @Status, @Owner)
   if @@error <> 0 goto errorHandler
   commit transaction
   goto done
errorHandler:
   rollback transaction
done:
END

Of particular note here is that neither the BugID nor the BugHistoryID is passed to spNewBug. In this stored procedure, the BugID will be generated by the database (BugID is an identity column) and the BugHistoryID will be set to 1. The TimeStamp is also set by defining the field to call GetDate(). Once the Bug has been added, we assign the new bugID (held in the @@identity parameter) to our local variable @BugID, and then use that to create the record in BugHistories. If either Insert command fails, the transaction is rolled back; if all goes well, it is committed. The C# code examines the return value to see if rows were updated; if not, an error is registered.

if (numInserted == 0)
{
    lblHeader.Text = "Unable to update database!";
    lblHeader.BackColor = System.Drawing.Color.Red;
    lblHeader.ForeColor = System.Drawing.Color.Yellow;
}

The Review Page

When the user logs in (or clicks on Review/Edit bugs) the Bug review grid is displayed, as shown in Figure 11.

bugreviewgrid
Figure 11. Bug review grid

From here, there are two paths. The first is to choose a bug and click Edit. The job of the Edit button event handler is to stash the BugID into session state (as we saw earlier, the editing page will need this) and then to redirect the user to TBTReportBug.aspx:

protected void BugReviewGrid_RowEditing(object sender, 
    GridViewEditEventArgs e)
{
    Session["Edit"] = 
        BugReviewGrid.DataKeys[e.NewEditIndex].Value.ToString();
    e.Cancel = true;
    Response.Redirect("TBTReportBug.aspx");
}

The first statement indexes into the DataKeys collection of the grid to find the key for the currently selected row (the row aligned with the Edit button) and retrieves its value, storing it (the BugID) into the "Edit" value in Session state. The second statement sets the Cancel property of the GridViewEditEventArgs object to true, indicating that no further editing action is required (turning off in-place editing). The third statement transfers control to the TBTReportBug.aspx page.

Clicking the Details button will bring up a DetailsView page. This happens automatically, with no code! You accomplish this tiny miracle by dragging on the DetailsView object and creating a new SqlDataSource object to go with it. The Select statement for the new data source has a parameter (@BugID), which you define, using the wizard, as being set by a control--specifically, the BugReviewGrid (see Figure 12).

parametersource
Figure 12. Control parameter source

That's all it takes. When the Details button is clicked (which is the select button in disguise), the BugDetailDataSource recognizes that there is now a selected item and shows the DetailsView.

I added a CommandButton labeled History to the DetailsView, as shown in Figure 13.

HistoryButton
Figure 13. History button

Note that the Details view (circled) adds only two other fields (the full description and the notes) because all the remaining fields are already displayed in the grid.

Because the History button does not select a row, an event handler must be written to display the grid showing the history of the selected row:

protected void BugDetailsView_ItemCommand(object sender,
    DetailsViewCommandEventArgs e)
{
    BugHistoryGrid.DataBind();
    BugHistoryGrid.Visible = true;
}

Calling DataBind on the BugHistoryGrid causes the Select statement to be called on its associated DataSource:

SELECT [ModifiedBy], [ShortDescription], [TimeStamp], [BugHistoryID], 
sev.text as Severity, stat.text as Status, [Owner], bh.BugID FROM [BugHistories] bh
join severities sev on sev.SeverityID = bh.Severity
join statuses stat on statiStics = bh.Status
WHERE ([BugID] = @BugID) ORDER BY [BugHistoryID] DESC

The BugID is supplied by the BugReviewGrid using a ControlParameter source (as we've seen before). Finally, this grid itself has Details buttons that call up the details for any of the BugHistory entries (see Figure 14).

extendedDetails
Figure 14. Extended details

In short, what you are seeing here is the details for the second bug (Records are being corrupted) along with its history (the original entry, as well as one revision) and the details for the revision.

Ever So Much More To Do

There's plenty more to do (if nothing else, the grids need labels). As noted earlier, I certainly want to be able to see only my bugs and I'd like to sort them in complex ways (e.g., by Severity and then by age). However, even this rough-and-ready version is quite usable for tracking bugs in a small project.

The complete source for this application is available on my website ; just click on Books and then on Articles.

Jesse Liberty is a senior program manager for Microsoft Silverlight where he is responsible for the creation of tutorials, videos and other content to facilitate the learning and use of Silverlight. Jesse is well known in the industry in part because of his many bestselling books, including O'Reilly Media's Programming .NET 3.5, Programming C# 3.0, Learning ASP.NET with AJAX and the soon to be published Programming Silverlight.


Return to the Windows DevCenter.

Copyright © 2009 O'Reilly Media, Inc.