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

SWAT Does Email

The functionality we want to add to the application is to send out an Email notifying users that the state of a bug has changed. The three states we have defined are: when a bug is created (bug is open), when a bug is fixed, and when a bug is closed. So who gets the Email? There are two categories of recipients for the Emails. One set is defined by global flags and are not project specific. The other set is specified on a project/module basis for each user defined in the database.

For example, the global flags can specify that the owner of a bug gets notified whenever a bug is opened. The second set can be used to specify that a project lead or manager gets notified whenever a bug for a specific module gets opened, fixed, and/or closed. The global flags are specified on a new SwatOptions page that we'll be defining below. The project specific notifications will be set through new controls that will be added to the SwatAdmin page.

Fig.2 Swat's Options Page

Let's start by defining the Swat options page. Add a new WEB page to the project and name it 'SwatOptions'. Refer to Fig. 2 and add the following controls to the page.

There may be other combinations that may be appropriate so the ones I've included may not be complete. The other information that we need to provide the application is the name of the mail server that is to be used by the application and it would be appropriate for SWAT to have a mail account on that server.

The information being acquired by the SwatOptions page does not lend itself to be persisted to the database and is better suited to store in a file on the server. In fact, because it is data that we are persisting, it really fits as an XML file and we have all the facilities available to create and maintain such a file. The options will be read from the file when the SwatOptions page is first loaded and will be persisted to the file when the user presses the 'Update' button. Here's the code for reading the file and setting the page controls when the page gets loaded.

private void Page_Load(object sender, System.EventArgs e)
{
  if (!Page.IsPostBack)
  {
    XmlTextReader myXmlTextReader = null;
    StringBuilder strPath = new StringBuilder();
    strPath.Append(ConfigurationSettings.AppSettings["optionspath"]);

    try
    {
      myXmlTextReader = new XmlTextReader(strPath.ToString());

      while (myXmlTextReader.Read())
      {
        if(myXmlTextReader.NodeType == XmlNodeType.Element)
        {
          if(myXmlTextReader.Name ==  "SWATEmail")
          {
            txtSwatEmail.Text = myXmlTextReader.ReadElementString();
          }
          if(myXmlTextReader.Name ==  "EmailServer")
          {
            txtEmailServer.Text = myXmlTextReader.ReadElementString();
          }
          if(myXmlTextReader.Name ==  "SendCreatorOnFixed")
          {
            chkCreatorOnFixed.Checked = 
              System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
          }
          if(myXmlTextReader.Name ==  "SendCreatorOnClosed")
          {
            chkCreatorOnClosed.Checked = 
              System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
          }
          if(myXmlTextReader.Name ==  "SendFixerOnClosed")
          {
            chkFixerOnClosed.Checked = 
              System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
          }
          if(myXmlTextReader.Name ==  "SendOwnerOnOpen")
          {
            chkOwnerOnOpen.Checked = 
              System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
          }
          if(myXmlTextReader.Name ==  "SendOwnerOnFixed")
          {
            chkOwnerOnFixed.Checked = 
              System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
          }
          if(myXmlTextReader.Name ==  "SendOwnerOnClosed")
          {
            chkOwnerOnClosed.Checked = 
              System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
          }
        }
      }
    }
    catch(Exception err)
    {
      //We could indicate an error but we need to check for
      //initial condition, no file.
      string s = err.ToString();
    }
    finally
    {
      myXmlTextReader.Close();
    }
  }
}

Double click on the 'Update' button to create an event handler for the button click event. Revise the handler as shown below.

private void btnUpdate_Click(object sender, System.EventArgs e)
{
  XmlTextWriter myXmlTextWriter = null;
  //Save Swat Options to an XML file
  try
  {
    StringBuilder strPath = new StringBuilder();
    strPath.Append(ConfigurationSettings.AppSettings["optionspath"]);
    myXmlTextWriter = new XmlTextWriter(strPath.ToString(),null);

    myXmlTextWriter.Formatting = Formatting.Indented;
    myXmlTextWriter.WriteComment(
      "This file is used to persist SWAT's options");
    myXmlTextWriter.WriteStartElement("SWAT_Options");
    myXmlTextWriter.WriteElementString("SWATEmail",
                     txtSwatEmail.Text);
    myXmlTextWriter.WriteElementString("EmailServer",
                     txtEmailServer.Text);
    myXmlTextWriter.WriteElementString("SendCreatorOnFixed",
                     chkCreatorOnFixed.Checked.ToString());
    myXmlTextWriter.WriteElementString("SendCreatorOnClosed",
                     chkCreatorOnClosed.Checked.ToString());
    myXmlTextWriter.WriteElementString("SendFixerOnClosed",
                     chkFixerOnClosed.Checked.ToString());
    myXmlTextWriter.WriteElementString("SendOwnerOnOpen",
                     chkOwnerOnOpen.Checked.ToString());
    myXmlTextWriter.WriteElementString("SendOwnerOnFixed",
                     chkOwnerOnFixed.Checked.ToString());
    myXmlTextWriter.WriteElementString("SendOwnerOnClosed",
                     chkOwnerOnClosed.Checked.ToString());
    myXmlTextWriter.WriteEndElement();

    //Write the XML to file
    myXmlTextWriter.Flush();
  }
  catch (System.Exception err)
  {
    StringBuilder s = new StringBuilder("Error saving options: ");
    s.Append(err.ToString());
    Response.Write(s.ToString());
  }
  finally
  {
    //Close the writer
    myXmlTextWriter.Close();
    //All we want to do is close the window
    Response.Write("<script language="'javascript'">window.close();</script>");
  }
}

That takes care of the global options. As I mentioned above we are also going to allow the administrator to set up Email notifications based on when bugs change state for each user. And the notifications are going to be based per module so for example only the author of a module will be notified. Alternatively a manager may want to be notified of every bug state change for every module in a project. The scheme we'll set up will allow for both extremes. We need to add a table in the database to hold the notification options. Here's the script to create the table.

/****** Object:  Table [dbo].[NOTIFY]  ******/
CREATE TABLE [dbo].[NOTIFY] (
  [ID] [int] NOT NULL ,
  [UserID] [int] NOT NULL ,
  [ProjID] [int] NOT NULL ,
  [ModID] [int] NOT NULL ,
  [OnOpen] [smallint] NULL ,
  [OnFixed] [smallint] NULL ,
  [OnClosed] [smallint] NULL 
) ON [PRIMARY]
GO

The easiest way to set up what we want to do is to use a DataGrid to define the options that we want. The DataGrid will be used to define a list of Email options for each user. The option will consist of a state change for bugs defined in a module. So the contents of the grid will be the notification options for the user being edited. Open the SwatAdmin page and refer to Fig.1 and let's add that functionality.

Drag a DataGrid and place it as shown and label it 'grdNotify'. Right click and select 'AutoFormat...' and then pick your style for the grid. We're going to need five columns for our grid so right click the grid and select 'PropertyBuilder...'. Select 'Columns' on the Property Builder dialog and de-select the check box 'Create Columns Automatically...'. Add five template columns and set the Header Text for each one as follows:

The first two columns will allow the user to select which project and module the notification will apply to. The last three columns contain a checkbox to select which state change(s) will initiate the notification. Right click on the grid and select 'Edit Template->Column0'. Drag two Label controls to the ItemTemplate and label them as follows:

Note, set the 'Visible' property for lblProjectID to 'false'. Drag a DropDownList control onto the EditItem Template. Set the properties as follows:

Finally drag a Button control onto the Footer Template. As we've done on the DataList controls we'll use this to allow the user to add new items to the list. Set the button's properties as follows:

Right click on the grid control to end editing Column0. Let's continue with Column1. Drag two Label controls to the ItemTemplate and label them as follows:

Note, set the 'Visible' property for lblModuleID to 'false'. Drag a DropDownList control onto the EditItem Template. Set the properties as follows:

Right click on the grid control to end editing Column1. For each of the remaining columns all that we need is to drag a CheckBox control to the ItemTemplate. Set the ID for each as follows:

And since we need to know the user's Email address in order to send the notifications, add a Label control and a Text control to capture the user's Email address. Revise UpdateUser() to persist the Email options to the database:

...
// Fill our parameters
cmd.Parameters.Add("@itemname", SqlDbType.NVarChar, 64).Value = 
  ((TextBox)e.Item.FindControl("txtItemName")).Text;
cmd.Parameters.Add("@password", SqlDbType.NVarChar, 128).Value = 
  txtPassword.Text;
cmd.Parameters.Add("@email", SqlDbType.NVarChar, 50).Value = 
  txtEmail.Text;

//What roles
...
cmd.Parameters.Clear();

//We also need to update the notification options
ArrayList lstNotify =  new ArrayList();
if (GetNotifications(ref lstNotify))
{
  //If there's any item with no checboxes checked it will be removed.
  cmd.CommandText = "SWATDeleteUserNotifications";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("@userid",SqlDbType.Int).Value = 
    DataList1.DataKeys[DataList1.EditItemIndex];

  cmd.ExecuteNonQuery();

  cmd.CommandText = "SWATInsertUserNotification";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Clear();
  cmd.Parameters.Add("@userid",SqlDbType.Int);
  cmd.Parameters.Add("@projid",SqlDbType.Int);
  cmd.Parameters.Add("@modid",SqlDbType.Int);
  cmd.Parameters.Add("@onopen",SqlDbType.SmallInt);
  cmd.Parameters.Add("@onfixed",SqlDbType.SmallInt);
  cmd.Parameters.Add("@onclosed",SqlDbType.SmallInt);

  for (int nItems = 0; nItems < lstNotify.Count; nItems++)
  {
    //If no checks don't add
    if (((NotifyItem)lstNotify[nItems]).boolOnOpen ||
      ((NotifyItem)lstNotify[nItems]).boolOnFixed ||
      ((NotifyItem)lstNotify[nItems]).boolOnClosed)
    {
      cmd.Parameters["@userid"].Value = 
        System.Convert.ToInt32(DataList1.DataKeys[DataList1.EditItemIndex]);
      cmd.Parameters["@projid"].Value = 
        ((NotifyItem)lstNotify[nItems]).nProjID;
      cmd.Parameters["@modid"].Value = 
        ((NotifyItem)lstNotify[nItems]).nModID;
      cmd.Parameters["@onopen"].Value = 
        ((NotifyItem)lstNotify[nItems]).boolOnOpen;
      cmd.Parameters["@onfixed"].Value = 
        ((NotifyItem)lstNotify[nItems]).boolOnFixed;
      cmd.Parameters["@onclosed"].Value = 
        ((NotifyItem)lstNotify[nItems]).boolOnClosed;
      cmd.ExecuteNonQuery();
    }
  }
}
cnn.Close();
...

And GetUserData() to retrieve the Email options from the database.

...
if (dr.Read())
{
  if (dr["Password"] != System.DBNull.Value)
    txtPassword.Text = dr["Password"].ToString();
  if (dr["Email"] != System.DBNull.Value)
    txtEmail.Text = dr["Email"].ToString();
...
dr.Close();

//We also need the user notification options
cmd.CommandText = "SWATGetUserNotifications";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();

cmd.Parameters.Add("@userid", SqlDbType.Int).Value = 
  nUserID.ToString();
SqlDataReader drNotify = cmd.ExecuteReader();
grdNotify.DataSource = drNotify;
grdNotify.DataBind();
drNotify.Close();

drNotify = cmd.ExecuteReader();
//Set the checkboxes
int nCount = 0;
while (drNotify.Read())
{
  bool chkState;

  if (System.Convert.ToByte(drNotify["OnOpen"]) == 1)
    chkState = true;
  else
    chkState = false;
  ((CheckBox)grdNotify.Items[nCount].FindControl("chkOnOpen")).Checked = 
    chkState;
  if (System.Convert.ToByte(drNotify["OnFixed"]) == 1)
    chkState = true;
  else
    chkState = false;
  ((CheckBox)grdNotify.Items[nCount].FindControl("chkOnFixed")).Checked = 
    chkState;
  if (System.Convert.ToByte(drNotify["OnClosed"]) == 1)
    chkState = true;
  else
    chkState = false;
  ((CheckBox)grdNotify.Items[nCount].FindControl("chkOnClosed")).Checked = 
    chkState;
  nCount++;
}

drNotify.Close();

grdNotify.SelectedIndex = -1;
grdNotify.EditItemIndex = -1;

cnn.Close();
...

Just as we did on the DataList we need to add an event handler for the embedded DropDownList control to detect when the user changes the project so we can load the appropriate modules to the modules DropDownList. Add a handler for the DataGrid 'ItemCreated' event and revise the code as follows:

private void grdNotify_ItemCreated(object sender, 
  System.Web.UI.WebControls.DataGridItemEventArgs e)
{
  if (e.Item.ItemType == System.Web.UI.WebControls.ListItemType.EditItem)
  {
    Control ctlProjects = ((Control)(e.Item)).FindControl("ddlProjects");
    ((DropDownList)ctlProjects).SelectedIndexChanged += 
      new System.EventHandler(this.ddlProjects_SelectedIndexChanged);
  }
}

And since we added an event for the selection change, here's the code for the handler.

protected void ddlProjects_SelectedIndexChanged(object sender, 
               System.EventArgs e)
{
  try
  {
    SqlConnection cnn;
    string ConnectionString = ConfigurationSettings.AppSettings["dbconn"];
    cnn = new SqlConnection(ConnectionString);
    cnn.Open();
    string sqlString;
    SqlCommand cmd = cnn.CreateCommand();

    DropDownList ddlProj = 
     (DropDownList)
     grdNotify.Items[grdNotify.EditItemIndex].FindControl("ddlProjects");

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@projid", SqlDbType.Int).Value = 
      ddlProj.SelectedItem.Value;
    cmd.CommandText = "SWATGetProjModules";

    DropDownList ddlMod = 
     (DropDownList)
     grdNotify.Items[grdNotify.EditItemIndex].FindControl("ddlModules");
    ddlMod.DataSource = cmd.ExecuteReader();
    ddlMod.DataBind();

    cnn.Close();
  }
  catch(Exception err)
  {
    lblError.Text = "Error loadng modules!";
  }
}

Since the Email option is per user we will also need to control the visibility of the grid along with the other options available for the user. Revise the ShowUserFields() method to display the new page items only when a user is being edited.

...
lblEmail.Visible = bShow;
txtEmail.Visible = bShow;
grdNotify.Visible = bShow;

Next, we need to add code to implement the 'AddNew' functionality. We already have a button setup in the footer of the DataGrid and we set it's ComandName property. Add an event handler for the DataGrid's ItemCommand() event and revise the code as follows to handle the 'AddNew' command:

private void grdNotify_ItemCommand(object source, 
  System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
  if (e.CommandName == "AddNew")
  {
    try
    {
      //We can't save what's in the grid because the user 
      //may decide to 'cancel' later.
      //So we'll just update the grid each time 'AddNew' 
      //is called and post it to
      //the database only when the user does an update.
      ArrayList lstNotify = new ArrayList();
      GetNotifications(ref lstNotify);

      SqlConnection cnn;
      string ConnectionString = 
        ConfigurationSettings.AppSettings["dbconn"];
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
      SqlCommand cmd = cnn.CreateCommand();

      cmd.CommandText = "SWATGetUserNotifications";
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@userid", SqlDbType.Int).Value = 
        DataList1.DataKeys[DataList1.EditItemIndex];
      
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds,"EmailNotify");

      for (int nItems = ds.Tables["EmailNotify"].Rows.Count; 
           nItems < lstNotify.Count; nItems++)
      {
        DataRow row = ds.Tables["EmailNotify"].NewRow();
        row["ProjID"] = ((NotifyItem)lstNotify[nItems]).nProjID;
        row["PName"] = ((NotifyItem)lstNotify[nItems]).strProjName;
        row["ModID"] = ((NotifyItem)lstNotify[nItems]).nModID;
        row["MName"] = ((NotifyItem)lstNotify[nItems]).strModName;
        if (((NotifyItem)lstNotify[nItems]).boolOnOpen)
          row["OnOpen"] = 1;
        else
          row["OnOpen"] = 0;
        if (((NotifyItem)lstNotify[nItems]).boolOnFixed)
          row["OnFixed"] = 1;
        else
          row["OnFixed"] = 0;
        if (((NotifyItem)lstNotify[nItems]).boolOnClosed)
          row["OnClosed"] = 1;
        else
          row["OnClosed"] = 0;

        ds.Tables["EmailNotify"].Rows.Add(row);
      }

      //Now add the new row
      DataRow r = ds.Tables["EmailNotify"].NewRow();
      r["OnOpen"] = 0;
      r["OnFixed"] = 0;
      r["OnClosed"] = 0;

      ds.Tables["EmailNotify"].Rows.Add(r);
      grdNotify.DataSource = ds.Tables["EmailNotify"];

      grdNotify.EditItemIndex = grdNotify.Items.Count;
      grdNotify.SelectedIndex = -1;
      grdNotify.DataBind();

      //Set the checkboxes
      for (int nCount = 0; nCount < ds.Tables["EmailNotify"].Rows.Count; 
           nCount++)
      {
        bool chkState;

        if (System.Convert.ToByte(
            ds.Tables["EmailNotify"].Rows[nCount]["OnOpen"]) == 1)
          chkState = true;
        else
          chkState = false;
        ((CheckBox)
           grdNotify.Items[nCount].FindControl("chkOnOpen")).Checked 
           = chkState;
        if (System.Convert.ToByte(
            ds.Tables["EmailNotify"].Rows[nCount]["OnFixed"]) == 1)
          chkState = true;
        else
          chkState = false;
        ((CheckBox)
          grdNotify.Items[nCount].FindControl("chkOnFixed")).Checked 
          = chkState;
        if (System.Convert.ToByte(
             ds.Tables["EmailNotify"].Rows[nCount]["OnClosed"]) == 1)
          chkState = true;
        else
          chkState = false;
        ((CheckBox)
          grdNotify.Items[nCount].FindControl("chkOnClosed")).Checked 
          = chkState;
      }

      //Load the projects ddl
      cmd.Parameters.Clear();
      cmd.CommandText = "SWATGetAllProjects";
      cmd.CommandType = CommandType.StoredProcedure;
      SqlDataReader dr = cmd.ExecuteReader();

      DropDownList ddlProj = 
       (DropDownList)grdNotify.Items[<BR>                         grdNotify.Items.Count-1].FindControl("ddlProjects");
      ddlProj.DataSource = dr;
      ddlProj.DataBind();
      dr.Close();

      //Now select the first project and load the modules for it
      ddlProj.SelectedIndex = 0;
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add("@projid", SqlDbType.Int).Value = 
        ddlProj.SelectedItem.Value;
      cmd.CommandText = "SWATGetProjModules";
      dr = cmd.ExecuteReader();

      DropDownList ddlMod = 
        (DropDownList)
        grdNotify.Items[grdNotify.Items.Count-1].FindControl("ddlModules");
      ddlMod.DataSource = dr;
      ddlMod.DataBind();
      dr.Close();

      cnn.Close();
    }
    catch(Exception err)
    {
      string s = err.ToString();
      lblError.Text = "Error Adding Notification!";
    }
  }
}

Since the user can cancel out of any edits s/he may have made to the DataGrid we don't update the database when the user adds a new entry but instead use the DataGrid itself to persist the information until the user elects to do update. So before we add a new row to the DataGrid we first find out if there are any items that have not been saved to the database. We can detect which ones they are because they will not have been assigned an ID. At the same time we're also going to check to make sure there's no duplicated entries. Here's the code for those helper methods().

bool GetNotifications(ref ArrayList lstNotify)
{
  bool bReturn = false;

  //While we're at it we'll also remove any duplicates
  for (int nCount = 0; nCount < grdNotify.Items.Count; nCount++)
  {
    int nProjID = 0;
    int nModID = 0;
    bool bOnOpen;
    bool bOnFixed;
    bool bOnClosed;
    string strProj = "";
    string strMod = "";

    //If it's not edit item
    if (nCount == grdNotify.EditItemIndex)
    {
      if (((DropDownList)
           grdNotify.Items[nCount].FindControl("ddlProjects")).SelectedIndex 
           >= 0)
      {
        nProjID = 
         System.Convert.ToInt32(((DropDownList)
         grdNotify.Items[nCount].FindControl("ddlProjects")).SelectedItem.Value);
        strProj = ((DropDownList)
         grdNotify.Items[nCount].FindControl("ddlProjects")).SelectedItem.Text;
      }
      if (((DropDownList)
          grdNotify.Items[nCount].FindControl("ddlModules")).SelectedIndex 
           >

bool CheckNotifyDupes(int nProjID, int nModID, ref ArrayList lstNotify)
{
  bool bReturn = false;
  for (int nCount = 0; nCount < lstNotify.Count; nCount++)
  {
    if (((NotifyItem)lstNotify[nCount]).nProjID == 
          nProjID && ((NotifyItem)lstNotify[nCount]).nModID == nModID)
    {
      bReturn = true;
      break;
    }
  }
  return bReturn;
}

Add the following struct definition that we used in the above methods to the SwatAdmin class:

struct NotifyItem
{
  public int nProjID;
  public string strProjName;
  public int nModID;
  public string strModName;
  public bool boolOnOpen;
  public bool boolOnFixed;
  public bool boolOnClosed;
}

OK, so we can set up notifications for each user. To remove a notification all that has to be done is to clear all checkboxes for a specific module.

Now all that we have to do is check the notification list whenever a bug changes state and send out an appropriate Email. To do that we have to go to the SwatBugs page and modify the code for each state change. Revise the updateBug() method's insert operation as follows:

...
//It's an insert
bAddNew = true;
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandText = "SWATInsertBug";
cmd.CommandType = CommandType.StoredProcedure;

//We need to know the assigned id in order to send Email
SqlParameter RetVal = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;

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();
//Send Email
SendNotifications(BugState.Bug_Open, System.Convert.ToInt16(RetVal.Value));
...

Notice how we obtain the id that was assigned to the bug as a return parameter since we need it to send the Email.

Revise FixBug() method as follows:

...
cnn.Close();

//Send Email
SendNotifications(BugState.Bug_Fixed, 
  System.Convert.ToInt32(DataList1.DataKeys[DataList1.SelectedIndex]));
...

Likewise for CloseBug().

...
cnn.Close();

//Send Email
SendNotifications(BugState.Bug_Closed, 
  System.Convert.ToInt32(DataList1.DataKeys[DataList1.SelectedIndex]));
...

That's it. All the hooks are in and the lists are set up. All that's left is to compose the message and send it. But first add the following enum to SwatBugs class.

public enum BugSeverity
{
  ShowStopper = 1,
  Level1,
  Level2,
  Level3,
  Enhancement
}

And since we are using SMTP mail class we need to bring in that namespace. Add the following declaration to the top of the SwatBugs code source file.

...
using System.Web.Mail;
using System.Xml;
using System.Configuration;

Finally the actual code that sends out the Email. As you can see from above we've got one method that is called every time a bug changes state. So if we've got to make any changes to the notification code it will be here. It's a big one but it's just formatting the message so you can easily follow along with what it's doing.

First we get the bug that has changed state from the database. We save some of the fields that we'll need later in the code. Then the body of the message is composed with the information that was retrieved from the database. Next, the options file is read in to see if there are any global options set and for the name of the mail server. The database is again queried to get the notifications that have been defined for the module that this bug was found in. A list of user IDs is created and we go to the database again to get the Email address for the users to be notified.

private void SendNotifications(BugState state, int nBugID)
{
  int nModID = 0;
  int nProjID = 0;
  int nEnteredBy = 0;
  int nFixedBy = 0;
  int nOwner = 0;

  StringBuilder strRecipient = new StringBuilder("");
  StringBuilder strSubject = new StringBuilder("");
  StringBuilder strBody = new StringBuilder("");
  StringBuilder strOwnerEmail = new StringBuilder("");

  SqlConnection cnn;
  string ConnectionString = ConfigurationSettings.AppSettings["dbconn"];
  cnn = new SqlConnection(ConnectionString);
  cnn.Open();
  SqlCommand cmd = cnn.CreateCommand();
  cmd.CommandText = "SELECT * FROM bugs WHERE id=" + nBugID.ToString();

  SqlDataReader dr = cmd.ExecuteReader();
    
  if (dr.Read())
  {
    //Save the mod/proj
    nModID = System.Convert.ToInt32(dr["Module"]);
    nProjID = System.Convert.ToInt32(dr["Project"]);
    nOwner = System.Convert.ToInt32(dr["AssignedTo"]);
    nEnteredBy = System.Convert.ToInt32(dr["EnteredBy"]);
    if (dr["FixedBy"] != System.DBNull.Value)
      nFixedBy = System.Convert.ToInt32(dr["FixedBy"]);
    //The body
    strBody.Append("Bug details.\n");
    strBody.Append("Title: " + dr["itemname"].ToString() + "\n");
    strBody.Append("Project: " + 
      ddlProjects.Items.FindByValue(dr["Project"].ToString()).Text + "\n");
    strBody.Append("Module: " + 
      ddlModules.Items.FindByValue(dr["Module"].ToString()).Text + "\n");
    strBody.Append("Owner: " + 
      ddlOwner.Items.FindByValue(dr["AssignedTo"].ToString()).Text + "\n");
    switch (state)
    {
      case BugState.Bug_Open:
        strBody.Append("Open date: " + 
          dr.GetDateTime(dr.GetOrdinal("EnteredDate")).ToShortDateString() 
           + "\n");
        strBody.Append("Opened by: " + 
          ddlOwner.Items.FindByValue(dr["EnteredBy"].ToString()).Text 
           + "\n");
        break;
      case BugState.Bug_Fixed:
        strBody.Append("Fix date: " + 
          dr.GetDateTime(dr.GetOrdinal("FixedDate")).ToShortDateString() 
            + "\n");
        strBody.Append("Fixed by: " + 
          ddlOwner.Items.FindByValue(dr["FixedBy"].ToString()).Text + "\n");
        break;
      case BugState.Bug_Closed:
        strBody.Append("Close date: " + 
          dr.GetDateTime(dr.GetOrdinal("ClosdDate")).ToShortDateString() 
            + "\n");
        strBody.Append("Closed by: " + 
          ddlOwner.Items.FindByValue(dr["ClosedBy"].ToString()).Text + "\n");
        break;
    }
    
    BugSeverity bs = (BugSeverity)System.Convert.ToInt16(dr["Severity"]);
    strBody.Append("Severity: " + bs.ToString() + "\n");
    strBody.Append("Priority: " + dr["Priority"].ToString() + "\n");
    strBody.Append("Reported Revision: " + dr["revision"].ToString() + "\n");
    strBody.Append("Description:\n");
    strBody.Append(dr["Description"].ToString());

    dr.Close();

    //Who'll get this? Check global options.
    bool bCreatorOnFixed = false;
    bool bFixerOnClosed = false;
    bool bCreatorOnClosed = false;
    bool bOwnerOnOpen = false;
    bool bOwnerOnFixed = false;
    bool bOwnerOnClosed = false;

    string strSender = "";
    string strServer = "";
    XmlTextReader myXmlTextReader = null;
    StringBuilder strPath = new StringBuilder();
    strPath.Append(ConfigurationSettings.AppSettings["optionspath"]);
    strPath.Append("SWAT\\SwatOptions.xml");

    myXmlTextReader = new XmlTextReader(strPath.ToString());
    while (myXmlTextReader.Read())
    {
      if(myXmlTextReader.NodeType == XmlNodeType.Element)
      {
        if(myXmlTextReader.Name ==  "SWATEmail")
        {
          strSender = myXmlTextReader.ReadElementString();
        }
        if(myXmlTextReader.Name ==  "EmailServer")
        {
          strServer = myXmlTextReader.ReadElementString();
        }
        if(myXmlTextReader.Name ==  "SendCreatorOnFix")
        {
          bCreatorOnFixed = 
           System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
        }
        if(myXmlTextReader.Name ==  "SendCreatorOnClose")
        {
          bCreatorOnClosed = 
           System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
        }
        if(myXmlTextReader.Name ==  "SendFixerOnClose")
        {
          bFixerOnClosed = 
           System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
        }
        if(myXmlTextReader.Name ==  "SendOwnerOnOpen")
        {
          bOwnerOnOpen = 
           System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
        }
        if(myXmlTextReader.Name ==  "SendOwnerOnFixed")
        {
          bOwnerOnFixed = 
           System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
        }
        if(myXmlTextReader.Name ==  "SendOwnerOnClosed")
        {
          bOwnerOnClosed = 
           System.Convert.ToBoolean(myXmlTextReader.ReadElementString());
        }
      }
    }
    myXmlTextReader.Close();

    switch (state)
    {
      case BugState.Bug_Open:
        cmd.CommandText = "SWATGetOpenNotifications";
        break;
      case BugState.Bug_Fixed:
        cmd.CommandText = "SWATGetFixedNotifications";
        break;
      case BugState.Bug_Closed:
        cmd.CommandText = "SWATGetClosedNotifications";
        break;
    }
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@modid", SqlDbType.Int).Value = nModID;
    cmd.Parameters.Add("@projid", SqlDbType.Int).Value = nProjID;
    
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataReader drNotifications = cmd.ExecuteReader();

    StringBuilder strUserList = new StringBuilder();
    while(drNotifications.Read())
    {
      if (strUserList.Length == 0)
      {
        strUserList.Append("(");
        strUserList.Append(drNotifications["UserID"].ToString());
      }
      else
      {
        strUserList.Append(",");
        strUserList.Append(drNotifications["UserID"].ToString());
      }
    }
    drNotifications.Close();

    //Add any global notifications defined
    switch (state)
    {
      case BugState.Bug_Fixed:
        if (bCreatorOnFixed)
        {
          if (strUserList.Length == 0)
          {
            strUserList.Append("(");
            strUserList.Append(
             ddlOwner.Items.FindByValue(
              nEnteredBy.ToString()).Value.ToString());
          }
          else
          {
            strUserList.Append(",");
            strUserList.Append(
             ddlOwner.Items.FindByValue(
               nEnteredBy.ToString()).Value.ToString());
          }
        }
        break;
      case BugState.Bug_Closed:
        if (bFixerOnClosed)
        {
          if (strUserList.Length == 0)
          {
            strUserList.Append("(");
            strUserList.Append(
             ddlOwner.Items.FindByValue(
               nFixedBy.ToString()).Value.ToString());
          }
          else
          {
            strUserList.Append(",");
            strUserList.Append(
             ddlOwner.Items.FindByValue(
               nFixedBy.ToString()).Value.ToString());
          }
        }
        if (bCreatorOnClosed)
        {
          if (strUserList.Length == 0)
          {
            strUserList.Append("(");
            strUserList.Append(
             ddlOwner.Items.FindByValue(
               nEnteredBy.ToString()).Value.ToString());
          }
          else
          {
            strUserList.Append(",");
            strUserList.Append(
             ddlOwner.Items.FindByValue(
               nEnteredBy.ToString()).Value.ToString());
          }
        }
        break;
    }
    //Add the owner notifications
    if (strUserList.Length == 0)
      strUserList.Append("(");
    else
      strUserList.Append(",");
    strUserList.Append(
     ddlOwner.Items.FindByValue(nOwner.ToString()).Value.ToString());
    strUserList.Append(")");

    //Get the user's Emails
    StringBuilder strSQLUsers = 
      new StringBuilder("SELECT id, email FROM users WHERE id in");
    strSQLUsers.Append(strUserList);
    cmd.CommandText = strSQLUsers.ToString();
    cmd.CommandType = CommandType.Text;
    SqlDataReader drUsers = cmd.ExecuteReader();

    while (drUsers.Read())
    {
      //We'll save the owner's E-Mail separate
      if (System.Convert.ToInt32(drUsers["id"]) == nOwner)
        strOwnerEmail.Append(drUsers["email"].ToString());
      else
      {
        if (strRecipient.Length != 0)
          strRecipient.Append(";");
        strRecipient.Append(drUsers["email"].ToString());
      }
    }

    switch (state)
    {
      case BugState.Bug_Open:
        //Does the owner get notified...
        if (bOwnerOnOpen)
        {
          strSubject.Append("Bug #" + nBugID.ToString() + 
           " Has been assigned to you");
          SendEmail(strOwnerEmail.ToString(),strSubject.ToString(),
           strBody.ToString(),strServer,strSender);
        }
        strSubject.Append("Bug #" + nBugID.ToString() + 
          " Has been opened");
        SendEmail(strRecipient.ToString(),strSubject.ToString(),
          strBody.ToString(),strServer,strSender);
        break;
      case BugState.Bug_Fixed:
        strSubject.Append("Bug #" + nBugID.ToString() + 
          " Has been fixed");
        if (bOwnerOnFixed)
          SendEmail(strOwnerEmail.ToString(),strSubject.ToString(),
            strBody.ToString(),strServer,strSender);
        SendEmail(strRecipient.ToString(),strSubject.ToString(),
          strBody.ToString(),strServer,strSender);
        break;
      case BugState.Bug_Closed:
        strSubject.Append("Bug #" + nBugID.ToString() + 
          " Has been closed");
        if (bOwnerOnClosed)
          SendEmail(strOwnerEmail.ToString(),strSubject.ToString(),
            strBody.ToString(),strServer,strSender);
        SendEmail(strRecipient.ToString(),strSubject.ToString(),
          strBody.ToString(),strServer,strSender);
        break;
    }
  }
}

And the code that sends the formatted Email message.

private void SendEmail(string strRecipient, string strSubject, 
               string strBody, string strServer, string strSender)
{
  SmtpMail.SmtpServer = strServer;
  MailMessage mail = new MailMessage();
  mail.To = strRecipient;
  mail.From = strSender;
  mail.Subject = strSubject;

  mail.Body = strBody;
  SmtpMail.Send(mail);
}

Well, there's a lot of code above but I think it provides the flexibility that I had set out to accomplish. What do you think? Are the requirements being met?

This completes the basic functionality necessary for a bug tracking application. In the next article we'll be adding some analysis features that I think are beneficial (if not necessary) in determining the quality and state of a project.