Click here to Skip to main content
Click here to Skip to main content

SWAT - A simple Web-based Anomalies Tracker - Part 5

, 1 Jul 2003
Rate this:
Please Sign up or sign in to vote.
An account of my experience in learning to develop in the .NET environment.

Swat Part 5

This is the fifth article in a series describing the development of an application I devised as a learning project. The purpose of the project was to gain experience developing in the .NET environment. The goal I had given myself was to define a WEB-based application and then develop the application using ASP.NET. The articles describe my implementation solution for the application. The application being developed is a full-featured bug tracking application. In this article we will be completing the implementation of the bug editing page which was started in the previous article.

Swat Bug Editing Page (continued...)

OK, so last time we had just added 'command central' and now we will start implementing each of the commands being generated by the DataList. Let's start with the easiest ones. The paging methods prevPage() and nextPage() are exactly the same as for the admin page so let’s get them out of the way. Un-comment the code for 'Prev' and 'Next' commands in DataList1_ItemCommand() event handler.

private void prevPage()
{
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;
   int curpage = (int)ViewState["curpage"];
   if (curpage > 1)
   {
      curpage -= 1;
      ViewState["curpage"] = curpage;
      BindBugList(DataList1.DataKeys[0].ToString(),
          ScrollMode.UpdatePrevPage);
   }
}
private void nextPage()
{
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;

   int curpage = (int)ViewState["curpage"];
   if (curpage < pagecount)
   {
      curpage += 1;
      ViewState["curpage"] = curpage;
      BindBugList(DataList1.DataKeys[
           DataList1.Items.Count-1].ToString(),
           ScrollMode.UpdateNextPage);
   }
}

No surprises there, all we’re doing is initializing the DataList since we cannot be editing if we’re paging. We then set up the current page according to what the user selected and load the DataList with either the next page or the previous page. Note that we check the current page against the pagecount. However, the paging buttons are disabled during PreRender() when the list is displaying the beginning or end of the list so we should never get here under those conditions. OK, the time has come to add a new bug so we need to implement the 'AddNew' command. In the code below, you will note that the similarity to what we did on the admin page.

private void AddNew()
{
try
   {
      //Same as editing except it's a new item
      SqlConnection cnn;
      string strDirection = ">=";
      StringBuilder sqlString = 
        new StringBuilder("SELECT TOP ");
      sqlString.Append(pagesize);
      sqlString.Append(" id, itemname FROM ");
 
      string ConnectionString = "user id=ASPNET;password=;"
          "initial catalog=swatbugs;data source=localhost;"
          "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
 
      sqlString.Append("bugs WHERE id");
      sqlString.Append(strDirection);
      sqlString.Append("@recnum AND Project=@projectid ");
      sqlString.Append("AND Status=@statusid ");
      //All or just mine
      if (ddlListFilter.SelectedItem.Text != "All Items")
         sqlString.Append("AND AssignedTo=@ownerid");
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
 
      // Fill our parameters
      int nID = 0;
      if (DataList1.Items.Count > 0)
         nID = (int)DataList1.DataKeys[0];
      cmd.Parameters.Add("@recnum", SqlDbType.Int).Value = nID;
      cmd.Parameters.Add("@projectid", 
        SqlDbType.Int).Value = ddlProjects.SelectedItem.Value;
      cmd.Parameters.Add("@statusid", 
        SqlDbType.Int).Value = ddlBugStates.SelectedItem.Value;
      cmd.Parameters.Add("@ownerid", 
        SqlDbType.Int).Value = Response.Cookies["UserID"].Value;
 
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds,"BUGS");
 
      DataRow dr = ds.Tables["BUGS"].NewRow();
      dr[0] = 0;
      ds.Tables["Bugs"].Rows.Add(dr);
 
      DataList1.DataSource = ds;
 
      DataList1.EditItemIndex = DataList1.Items.Count;
      DataList1.SelectedIndex = -1;
      DataList1.DataBind();
 
      cnn.Close();
   }
   catch(Exception e)
   {
      lblError.Text = "Database Error.";
   }
            
   //And initialize the bug data fields
   EnableEditing(true);
   txtDescription.Text = "";
   DateTime dt = System.DateTime.Now;
   txtEnteredDate.Text = dt.ToShortDateString();
   txtEnteredBy.Text = 
        ddlOwner.Items.FindByValue(
        Response.Cookies["UserID"].Value).Text;
}

The code above is almost identical to the code in BindBugList() except that here we have to add a new DataRow to the DataSet. We could probably extract the common code to a separate function but it might turn out to be messier. Anyway, we also enable the bug data controls and fill the ones that need to be automatically set.

Bugs can be in one of three states. When they are first created they are automatically set to ‘open’. When the problem is found and corrected they can be set to ‘fixed’. And when the bug has been verified that it has indeed been corrected they can be ‘closed’. If a bug has to go 'backwards' in the state sequence then the user will just create a new bug entry. The description for the re-entered bug can reference the original bug but logically it makes sense to have a new one because someone will have to fix it again. And as a result, should be counted as additional work. The analysis tools will also be more representative of the actual work.

Depending on the current state of a bug the appropriate button will be enabled to allow the user to change the bug state. Un-comment the code for these two commands in the DataList1_ItemCommand() event handler. Here are the methods to handle that functionality.

private void FixBug()
{
   try
   {
      SqlConnection cnn;
      string ConnectionString = "user id=ASPNET;password=;"
          "initial catalog=swatbugs;data source=localhost;"
          "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
      StringBuilder sqlString = new StringBuilder(
         "UPDATE bugs SET status=@status,"
         " fixeddate=@fixeddate, fixedby=@fixedby");
      sqlString.Append(" WHERE id=@id");
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
 
      // Fill our parameters
      cmd.Parameters.Add("@status", SqlDbType.Int).Value = 
          (int)BugState.Bug_Fixed;
      cmd.Parameters.Add("@fixeddate", SqlDbType.DateTime).Value = 
          System.DateTime.Now.ToShortDateString();
      cmd.Parameters.Add("@fixedby", SqlDbType.Int).Value = 
          Response.Cookies["UserID"].Value;
      cmd.Parameters.Add("@id", SqlDbType.Int).Value = 
          DataList1.DataKeys[DataList1.SelectedIndex];
      cmd.ExecuteNonQuery();
 
      cnn.Close();
   }
   catch(Exception e)
   {
      //We'll update this...
      lblError.Text = "Database Error.";
   }
   //Update list
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;
 
   BindBugList(DataList1.DataKeys[0].ToString(),
       ScrollMode.UpdateInPlace);
}
 
private void CloseBug()
{
   try
   {
      SqlConnection cnn;
      string ConnectionString = "user id=ASPNET;password=;"
         "initial catalog=swatbugs;data source=localhost;"
         "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
      StringBuilder sqlString = new StringBuilder(
         "UPDATE bugs SET status=@status, closeddate=@closeddate,"
         " closedby=@closedby");
      sqlString.Append(" WHERE id=@id");
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
 
      // Fill our parameters
      cmd.Parameters.Add("@status", SqlDbType.Int).Value = 
          (int)BugState.Bug_Closed;
      cmd.Parameters.Add("@closeddate", SqlDbType.DateTime).Value = 
          System.DateTime.Now.ToShortDateString();
      cmd.Parameters.Add("@closedby", SqlDbType.Int).Value = 
          Response.Cookies["UserID"].Value;
      cmd.Parameters.Add("@id", SqlDbType.Int).Value = 
          DataList1.DataKeys[DataList1.SelectedIndex];
      cmd.ExecuteNonQuery();
 
      cnn.Close();
   }
   catch(Exception e)
   {
      lblError.Text = "Database Error.";
   }
   //Update list
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;
 
   BindBugList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);
}

All that the above methods do is update the corresponding state in the database and set the data for the appropriate related fields. Of course the DataList needs to be re-loaded because the bug will have moved to a different select set.

Well, I think I painted mysel into a corner. Originally I wanted to detect the 'FixBug' event and pop up a dialog where the user could enter a resolution comment. I was then going to append "Resolution:" along with the comment to the end of the bug description. This was easy and all the information about the bug was in the same place. Unfortunately, I have a prolem. If the 'FixBug' would have been a button placed on the page instead of embedded in the footer then I could see how to implement what I wanted (using some script on the client). However, I don't see a way of doing it as it is right now. So, for the moment, the user will have to edit the description by hand and add a resolution comment when the bug is fixed.

When the user selects an item in the bug list we need to populate the bug data controls. We query the database for the bug with the given ID then we populate the controls with the retrieved data. In the code below note how we use the contents of the DropDownLists to set the selected index. We first find the index of the value retrieved from the database and then set the selected item to that index. We also check for a NULL entry for the items that may not have been set.

Check out how the DataReader has access methods for date fields and how the values are obtained using their ordinal numbers. Also, as I had indicated previously the owners DropDownList is being used as a cache for the users. We pull the text from the DropDownList to fill in the TextBoxes. Un-comment the code in the 'Select' command in the DataList1_ItemCommand() event handler and add the following method.

private void BindBugData(int nIndex)
{
   try
   {
      SqlConnection cnn;
      string ConnectionString = "user id=ASPNET;password=;"
         "initial catalog=swatbugs;data source=localhost;"
         "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
      StringBuilder sqlString = new StringBuilder(
         "SELECT * FROM bugs WHERE id=");
      sqlString.Append(DataList1.DataKeys[nIndex].ToString());
 
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
 
      SqlDataReader dr = cmd.ExecuteReader();
                
      if (dr.Read())
      {
         txtDescription.Text = dr["Description"].ToString();
         txtRevision.Text = dr["Revision"].ToString();
         //Select the severity
         if (!dr["Severity"].Equals(System.DBNull.Value))
         {
            int nSel = ddlSeverity.Items.IndexOf(
               ddlSeverity.Items.FindByValue(
               dr["severity"].ToString()));
            ddlSeverity.SelectedIndex = nSel;
         }
         //Select the priority
         if (!dr["priority"].Equals(System.DBNull.Value))
         {
            int nSel = ddlPriority.Items.IndexOf(
               ddlPriority.Items.FindByValue(dr["priority"].ToString()));
            ddlPriority.SelectedIndex = nSel;
         }
         //Select the module
         if (dr["module"] != System.DBNull.Value)
         {
            int nSel = ddlModules.Items.IndexOf(
               ddlModules.Items.FindByValue(dr["module"].ToString()));
            ddlModules.SelectedIndex = nSel;
         }
         //Select the owner
         if (dr["AssignedTo"] != System.DBNull.Value)
         {
            int nSel = ddlOwner.Items.IndexOf(
              ddlOwner.Items.FindByValue(dr["AssignedTo"].ToString()));
            ddlOwner.SelectedIndex = nSel;
         }
         //Select the creator
         if (dr["EnteredBy"] != System.DBNull.Value)
         {
            txtEnteredBy.Text = 
                ddlOwner.Items.FindByValue(
                   dr["EnteredBy"].ToString()).Text;
         }
         //Select the closer
         if (dr["ClosedBy"] != System.DBNull.Value)
         {
            txtClosedBy.Text = ddlOwner.Items.FindByValue(
                 dr["ClosedBy"].ToString()).Text;
         }
 
         //Select the fixer
         if (dr["FixedBy"] != System.DBNull.Value)
         {
            txtFixedBy.Text = ddlOwner.Items.FindByValue(
                 dr["FixedBy"].ToString()).Text;
         }
 
         //The entered date
         if (dr["EnteredDate"] != System.DBNull.Value)
         {
            txtEnteredDate.Text = dr.GetDateTime(
               dr.GetOrdinal("EnteredDate")).ToShortDateString();
         }
         //The fixed date
         if (dr["FixedDate"] != System.DBNull.Value)
         {
            txtFixedDate.Text = dr.GetDateTime(
               dr.GetOrdinal("FixedDate")).ToShortDateString();
         }
         //The closed date
         if (dr["ClosedDate"] != System.DBNull.Value)
         {
            txtClosedDate.Text = dr.GetDateTime(
              dr.GetOrdinal("ClosedDate")).ToShortDateString();
         }
      }
      cnn.Close();
   }
   catch
   {
      //We'll improve this...
      lblError.Text = "Database Error.";
   }
}

When the user cancels an operation we want to remove any edits that may have been made to the bug fields so that’s what ClearBugData() does.

private void ClearBugData()
{
   txtDescription.Text = "";
   txtRev.Text = "";
   ddlOwner.SelectedIndex = 0;
   ddlModules.SelectedIndex = 0;
   ddlSeverity.SelectedIndex = 0;
   ddlPriority.SelectedIndex = 0;
   txtEnteredBy.Text = "";
   txtEnteredDate.Text = "";
   txtClosedBy.Text = "";
   txtClosedDate.Text = "";
   txtFixedDate.Text = "";
   txtFixedBy.Text = "";
}

And finally all that we've got left is the update operation. This is just slightly more complicated but pretty much straight forward logic. The method handles both inserts and updates, notice how we detect which operation we're performing.

private void updateBug(System.Web.UI.WebControls.DataListCommandEventArgs e)
{
   bool bAddNew = false;

   try
   {
      SqlConnection cnn;
      string ConnectionString = "user id=ASPNET;password=;"
           "initial catalog=swatbugs;data source=localhost;"
           "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
      StringBuilder sqlString = new StringBuilder();

      //Were we editing or adding a new one?
      if ((int)DataList1.DataKeys[e.Item.ItemIndex] == 0)
      {
         //It's an insert
         bAddNew = true;
         SqlCommand cmd = cnn.CreateCommand();
         sqlString.Append("INSERT INTO Bugs(itemname,"
                " description, project, module, "); 
         sqlString.Append("revision, status, entereddate, "
                "assignedto, enteredby, severity, priority) VALUES ");
         sqlString.Append("(@itemname, @description, "
                "@project, @module, @revision, ");
         sqlString.Append("@status, @entereddate, @assignedto, "
                "@enteredby, @severity, @priority)");
         cmd.CommandText = sqlString.ToString();

         cmd.Parameters.Add("@itemname", 
             SqlDbType.NVarChar, 50).Value = 
            ((TextBox)e.Item.FindControl("txtBugTitle")).Text;
         cmd.Parameters.Add("@description", 
            SqlDbType.NVarChar, 1024).Value = txtDescription.Text;
         cmd.Parameters.Add("@project", 
             SqlDbType.Int).Value = ddlProjects.SelectedItem.Value;
         cmd.Parameters.Add("@module", 
             SqlDbType.Int).Value = ddlModules.SelectedItem.Value;
         cmd.Parameters.Add("@status", 
             SqlDbType.TinyInt).Value = (int)BugState.Bug_Open;
         cmd.Parameters.Add("@entereddate", 
             SqlDbType.DateTime).Value = 
             System.DateTime.Now.ToShortDateString();
         cmd.Parameters.Add("@assignedto", 
             SqlDbType.Int).Value = ddlOwner.SelectedItem.Value;
         cmd.Parameters.Add("@enteredby", 
             SqlDbType.Int).Value = Response.Cookies["UserID"].Value;
         cmd.Parameters.Add("@revision", 
             SqlDbType.NVarChar, 10).Value = txtRevision.Text;
         cmd.Parameters.Add("@severity", 
            SqlDbType.TinyInt).Value = ddlSeverity.SelectedItem.Value;
         cmd.Parameters.Add("@priority", 
            SqlDbType.TinyInt).Value = ddlPriority.SelectedItem.Value;
         cmd.ExecuteScalar();

         cnn.Close();
      }
      else
      {
         //An update

         sqlString.Append("UPDATE bugs SET itemname=@itemname,"
                 " description=@bugdescription, ");
         sqlString.Append("revision=@projrev, severity=@severity, "
                 "priority=@priority, ");
         sqlString.Append("module=@module, assignedto=@assignedto");
         sqlString.Append(" WHERE id=@bugid");
         SqlCommand cmd = cnn.CreateCommand();
         cmd.CommandText = sqlString.ToString();

         // Fill our parameters
         cmd.Parameters.Add("@itemname", SqlDbType.NVarChar, 50).Value = 
                ((TextBox)e.Item.FindControl("txtBugTitle")).Text;
         cmd.Parameters.Add("@bugdescription", 
                SqlDbType.NVarChar, 1024).Value = txtDescription.Text;
         cmd.Parameters.Add("@projrev", 
                SqlDbType.NVarChar, 10).Value = txtRevision.Text;
         cmd.Parameters.Add("@severity", 
                SqlDbType.TinyInt).Value = ddlSeverity.SelectedItem.Value;
         cmd.Parameters.Add("@module", 
                SqlDbType.Int).Value = ddlModules.SelectedItem.Value;
         cmd.Parameters.Add("@assignedto", 
                SqlDbType.Int).Value = ddlOwner.SelectedItem.Value;
         cmd.Parameters.Add("@priority", 
                SqlDbType.TinyInt).Value = ddlPriority.SelectedItem.Value;
         cmd.Parameters.Add("@bugid", 
                SqlDbType.Int).Value = DataList1.DataKeys[e.Item.ItemIndex];
         cmd.ExecuteNonQuery();
      }

      cnn.Close();
   }
   catch(Exception ex)
   {
      //We'll enhance this...
      lblError.Text = "Database Error.";
   }
   //Update list
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;

   BindBugList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);

   ClearBugData();

   if (bAddNew)
   {
      SetTotalPages();
   }
}
Oops, I forgot one thing, the GetBugs() button. Add an event handler by double-clicking on the 'Get Bugs' button. The handler simply saves the users current selection and re-loads the list based on those selections.

private void btnGetBugs_Click(object sender, System.EventArgs e)
{
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;

   //Save the users' preferences
   if (Response.Cookies["ListFilter"] == null)
      Response.Cookies.Add(Request.Cookies["ListFilter"]);
   Response.Cookies["ListFilter"].Expires = DateTime.MaxValue;
   Response.Cookies["ListFilter"].Value = ddlListFilter.SelectedItem.Value;

   if (Response.Cookies["BugState"] == null)
      Response.Cookies.Add(Request.Cookies["BugState"]);
   Response.Cookies["BugState"].Expires = DateTime.MaxValue;
   Response.Cookies["BugState"].Value = ddlBugStates.SelectedItem.Value;

   if (Response.Cookies["Project"] == null)
      Response.Cookies.Add(Request.Cookies["Project"]);
   Response.Cookies["Project"].Expires = DateTime.MaxValue;
   if (Response.Cookies["Project"].Value != ddlProjects.SelectedItem.Value)
      BindModuleCB();

   Response.Cookies["Project"].Value = ddlProjects.SelectedItem.Value;

   BindBugList("0",ScrollMode.UpdateInPlace);
   SetTotalPages();

   ClearBugData();
}

That completes all the functionality we had set out to implement for Phase1 of the project. SWAT should provide all the core functionality expected from a bug tracking application. Let me know if anything is missing or could be done better.

At this point in the development process an application would go through a test cycle and also check to see if anything that was promised is missing. The next article continues the development by adding Email notification capabilities.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Al Alberto
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
Question1.1 Issues ? PinmemberBradOsterloo9-Jul-03 11:14 
AnswerRe: 1.1 Issues ? PinmemberAl Alberto9-Jul-03 16:05 
AnswerRe: 1.1 Issues ? PinmemberDavid Gao15-Jun-05 9:16 
GeneralDB Source PinmemberRick Erickson9-Jul-03 2:23 
GeneralRe: DB Source PinmemberAl Alberto9-Jul-03 6:37 
GeneralRe: DB Source PinmemberRick Erickson10-Jul-03 2:16 
GeneralRe: DB Source PinmemberAl Alberto10-Jul-03 6:57 
GeneralConnection String Pinmembersides_dale3-Jul-03 0:23 
GeneralRe: Connection String PinmemberAl Alberto3-Jul-03 2:04 
GeneralTypo PinmemberExtraLean2-Jul-03 7:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 2 Jul 2003
Article Copyright 2003 by Al Alberto
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid