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
{
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 ");
if (ddlListFilter.SelectedItem.Text != "All Items")
sqlString.Append("AND AssignedTo=@ownerid");
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandText = sqlString.ToString();
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.";
}
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();
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)
{
lblError.Text = "Database Error.";
}
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();
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.";
}
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();
if (!dr["Severity"].Equals(System.DBNull.Value))
{
int nSel = ddlSeverity.Items.IndexOf(
ddlSeverity.Items.FindByValue(
dr["severity"].ToString()));
ddlSeverity.SelectedIndex = nSel;
}
if (!dr["priority"].Equals(System.DBNull.Value))
{
int nSel = ddlPriority.Items.IndexOf(
ddlPriority.Items.FindByValue(dr["priority"].ToString()));
ddlPriority.SelectedIndex = nSel;
}
if (dr["module"] != System.DBNull.Value)
{
int nSel = ddlModules.Items.IndexOf(
ddlModules.Items.FindByValue(dr["module"].ToString()));
ddlModules.SelectedIndex = nSel;
}
if (dr["AssignedTo"] != System.DBNull.Value)
{
int nSel = ddlOwner.Items.IndexOf(
ddlOwner.Items.FindByValue(dr["AssignedTo"].ToString()));
ddlOwner.SelectedIndex = nSel;
}
if (dr["EnteredBy"] != System.DBNull.Value)
{
txtEnteredBy.Text =
ddlOwner.Items.FindByValue(
dr["EnteredBy"].ToString()).Text;
}
if (dr["ClosedBy"] != System.DBNull.Value)
{
txtClosedBy.Text = ddlOwner.Items.FindByValue(
dr["ClosedBy"].ToString()).Text;
}
if (dr["FixedBy"] != System.DBNull.Value)
{
txtFixedBy.Text = ddlOwner.Items.FindByValue(
dr["FixedBy"].ToString()).Text;
}
if (dr["EnteredDate"] != System.DBNull.Value)
{
txtEnteredDate.Text = dr.GetDateTime(
dr.GetOrdinal("EnteredDate")).ToShortDateString();
}
if (dr["FixedDate"] != System.DBNull.Value)
{
txtFixedDate.Text = dr.GetDateTime(
dr.GetOrdinal("FixedDate")).ToShortDateString();
}
if (dr["ClosedDate"] != System.DBNull.Value)
{
txtClosedDate.Text = dr.GetDateTime(
dr.GetOrdinal("ClosedDate")).ToShortDateString();
}
}
cnn.Close();
}
catch
{
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();
if ((int)DataList1.DataKeys[e.Item.ItemIndex] == 0)
{
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
{
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();
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)
{
lblError.Text = "Database Error.";
}
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;
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.
