Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET
Article

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

Rate me:
Please Sign up or sign in to vote.
4.68/5 (20 votes)
25 Jul 2003CPOL10 min read 315.3K   7.8K   87   73
An account of my experience in learning to develop in the .NET environment

Image 1

Fig.1 Swat's Revised Admin Page

Swat Part 6

This is the sixth 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 the first five articles the main editing and administration functionality was implemented. In this article we will be adding Email notification capability to the application. Here are the links to the first five articles.

Since last time...

I've incorporated a couple of reader comments in this article. First, we're now storing the db connection string in the 'appSettings' section of the Web.config file. This makes it easier on the typing and if any changes are required they're in one place. At the same time I also needed to store the path for the SWAT options file and this fit the bill. The following is the change made to the Web.config file:

<appSettings> <BR> <add key="dbconn" value="server=localhost;database=SwatBugs;uid=ASPNET;pwd=;" /> <BR> <add key="optionspath" value="c:\\inetpub\\wwwroot\\SWAT\\SwatOptions.xml" /> <BR></appSettings>

The second change suggested by a reader was in the SwatLogon page. The SQL statement used to check if no users have been defined is changed from a 'SELECT' to a 'SELECT COUNT' statement since we really don't care about the contents just the existence. These changes have been incorporated into the downloadable source code.

I also revised the code to incorporate the following logic to protect the data integrity of the database.

  • Can't delete user if user has bugs
  • Can't delete module if module has bugs
  • Can't delete project if project has modules

    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.

    Image 2

    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.

    ControlIDText
    LabelLabel1E-Mail Options
    CheckBoxchkCreatorOnFixedE-Mail 'creator' on fixed
    CheckBoxchkCreatorOnClosedE-Mail 'creator' on closed
    CheckBoxchkFixerOnClosedE-Mail 'fixer' on closed
    CheckBoxchkOwnerOnOpenE-Mail owner on open
    CheckBoxchkOwnerOnFixedE-Mail owner on fixed
    CheckBoxchkOwnerOnClosedE-Mail owner on closed
    LabelLabel2SWAT E-Mail
    TextBoxtxtSwatEmail 
    LabelLabel3E-Mail Server
    TextBoxtxtEmailServer 
    ButtonbtnUpdateUpdate

    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.

    ASP.NET
    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.

    ASP.NET
    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.

    SQL
    /****** 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:

    Column0Project
    Column1Module
    Column2Email On Open
    Column3Email On Fix
    Column4Email On Close

    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:

    IDText
    lblProject'<%# DataBinder.Eval(Container.DataItem, "PName") %>'
    lblProjectID'<%# DataBinder.Eval(Container.DataItem, "ProjID") %>'

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

    IDDataValueFieldDataTextFieldAutoPostBack
    ddlProjectsiditemnametrue

    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:

    IDTextCommandName
    btnAddNewAdd NewAddNew

    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:

    IDText
    lblModule'<%# DataBinder.Eval(Container.DataItem, "MName") %>'
    lblModuleID'<%# DataBinder.Eval(Container.DataItem, "ModID") %>'

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

    IDDataValueFieldDataTextFieldAutoPostBack
    ddlModulesiditemnametrue

    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:

    Column2chkOnOpen
    Column3chkOnFixed
    Column4chkOnClosed

    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:

    ASP.NET
    ...
    // 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.

    ASP.NET
    ...
    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:

    ASP.NET
    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.

    ASP.NET
    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.

    ASP.NET
    ...
    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:

    ASP.NET
    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().

    ASP.NET
    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 
               >
    ASP.NET
    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:

    ASP.NET
    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:

    ASP.NET
    ...
    //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:

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

    Likewise for CloseBug().

    ASP.NET
    ...
    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.

    ASP.NET
    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.

    ASP.NET
    ...
    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.

    ASP.NET
    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.

    ASP.NET
    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.

  • License

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


    Written By
    Software Developer (Senior)
    United States United States
    This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

    Comments and Discussions

     
    QuestionWhat a pile of crap Pin
    Anonymous3-Mar-05 18:12
    Anonymous3-Mar-05 18:12 
    AnswerRe: What a pile of crap Pin
    Wise-Guy4-Apr-06 12:26
    Wise-Guy4-Apr-06 12:26 
    Generalproblem running in insert values mysql Pin
    Anonymous7-Jul-04 9:00
    Anonymous7-Jul-04 9:00 
    GeneralRe: problem running in insert values mysql Pin
    MR Arrogant Expert Software Engineer29-Jan-05 9:59
    sussMR Arrogant Expert Software Engineer29-Jan-05 9:59 
    GeneralInvalid object name 'tablename', help! Pin
    cmxcmz20025-Jul-04 17:19
    cmxcmz20025-Jul-04 17:19 
    General&lt;xml&gt; Pin
    Anonymous16-Jun-04 16:57
    Anonymous16-Jun-04 16:57 
    GeneralNice Code Pin
    coder4rent2-May-04 14:47
    coder4rent2-May-04 14:47 
    GeneralSPAMMER Pin
    abhadresh2-May-04 15:38
    abhadresh2-May-04 15:38 
    GeneralError :: Can not open database request Pin
    itspankajyaar14-Mar-04 2:28
    itspankajyaar14-Mar-04 2:28 
    GeneralRe: Error :: Can not open database request Pin
    Al Alberto14-Mar-04 3:31
    Al Alberto14-Mar-04 3:31 
    GeneralRe: Error :: Can not open database request Pin
    David Gao7-Jun-05 9:25
    David Gao7-Jun-05 9:25 
    GeneralProblem running part6.sql Pin
    ram8884-Dec-03 9:18
    ram8884-Dec-03 9:18 
    GeneralRe: Problem running part6.sql Pin
    Al Alberto6-Dec-03 16:01
    Al Alberto6-Dec-03 16:01 
    QuestionHow do I display data in cells? Pin
    J A Srikanth12-Nov-03 18:56
    J A Srikanth12-Nov-03 18:56 
    AnswerRe: How do I display data in cells? Pin
    Al Alberto13-Nov-03 3:42
    Al Alberto13-Nov-03 3:42 
    GeneralI don't have permission to write the xml file Pin
    Jeffster30-Oct-03 11:17
    Jeffster30-Oct-03 11:17 
    GeneralRe: I don't have permission to write the xml file Pin
    Jeffster30-Oct-03 11:23
    Jeffster30-Oct-03 11:23 
    GeneralRe: I don't have permission to write the xml file Pin
    ozkan18325-Jun-07 9:43
    ozkan18325-Jun-07 9:43 
    GeneralRe: I don't have permission to write the xml file Pin
    Al Alberto30-Oct-03 14:13
    Al Alberto30-Oct-03 14:13 
    GeneralWould love to build upon this application Pin
    seamusdromey200110-Oct-03 7:46
    seamusdromey200110-Oct-03 7:46 
    Generalparser error II Pin
    Mark Focas18-Sep-03 18:33
    Mark Focas18-Sep-03 18:33 
    GeneralRe: parser error II Pin
    Al Alberto21-Sep-03 14:13
    Al Alberto21-Sep-03 14:13 
    GeneralRe: parser error II Pin
    Mark Focas23-Sep-03 20:03
    Mark Focas23-Sep-03 20:03 
    GeneralRe: parser error II Pin
    Scott Piegdon27-Sep-03 10:48
    Scott Piegdon27-Sep-03 10:48 
    GeneralImage Paths Pin
    Paul Hatcher10-Sep-03 5:09
    Paul Hatcher10-Sep-03 5:09 

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

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