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

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

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

Fig.1 Swat Logon Page

Swat Part 3

This is the third article in a series of articles 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 first article described the application. The second article began the application design including the logon page. In this article we're adding the administration page. There's a lot of code covered in this article so grab your expresso or Jolt or whatever keeps you going. I've tried to write the text following the logic as I remember coding it. As a result it may have made the text longer than it needs to be. I may also have been too detailed in describing the procedure to do certain things. The next page page won't be that way. Anyway, that's enough for the introduction. Let's get going.

SWAT Admin Page

The admin page is provided to administer the database used by the application. The administration consists of adding/editing/deleting users, projects. Projects are composed of modules so the admin page is also used to define the modules that make up a project. The admin page is also the only way to delete bugs from the system.

The first time the application is used the user will login as ‘admin’ and the application will go directly to the admin page. One of the first things the user must do is define a new user that will have administrator ‘role’ assigned in order to secure the application. All users that will have access to the application must be defined in the system along with a corresponding password. Once the users have been defined then the project(s) that will be supported by the application must then be defined. For each project defined at least one module must also be defined. A module can be anything the user desires. However, normally it is defined as a functional unit since it will be used to identify where the bug was detected.

NOTE: The source code presented here in the aticle's text uses SQL statements to perform database access. The downloadable source code uses stored procedures to implement database functionality.

Add a new WEB page to the project and name it SwatAdmin. Refer to Fig.1 and add four image buttons and configure them as follows:

ID Image
btnEditUsers Swat_Users.gif
btnEditProjects Swat_Projects.gif
btnEditModules Swat_Modules.gif
btnEditBugs Swat_Bugs.gif

For all four buttons set the width to 120 and the height to 32. Also, set the BorderStyle to outset. You can download the images required for the buttons or create your own.

Double-click on each of the buttons to create a ‘Click’ event handler for each one (the Click event is the default event for the Button control). Alternatively you can open the properties for each of the buttons and select the events (the lightning symbol). If you double click on any of the events an event handler will be created for that specific event.

The buttons will allow the user to select which category is to be displayed on the page. The buttons will also maintain their selected state in order to indicate the currently selected category. To show the button as selected we will draw them 'pushed-in'. The best place to capture the code for doing that is in the page's PreRender event.

In the Properties pane select SwatAdmin from the drop down list and select the events tab. The events available for the page are displayed. Double-click on ‘PreRender’ to create an event handler for that event.

Add the following enum to the SwatAdmin class and edit the event handlers as shown.

...
enum EditCategories
{
   Category_None,
   Category_Users,
   Category_Projects,
   Category_Modules,
   Category_Bugs
}
...
private void Page_PreRender(object sender, System.EventArgs e)
{
   //Clear all buttons
   btnEditUsers.BorderStyle = BorderStyle.Outset;
   btnEditProjects.BorderStyle = BorderStyle.Outset;
   btnEditModules.BorderStyle = BorderStyle.Outset;
   btnEditBugs.BorderStyle = BorderStyle.Outset;
   //Change the display of the selected one.
   switch((int)ViewState["catsel"])
   {
      case (int)EditCategories.Category_Users:
      btnEditUsers.BorderStyle = BorderStyle.Inset;
      break;
      case (int)EditCategories.Category_Projects:
      btnEditProjects.BorderStyle = BorderStyle.Inset;
      break;
      case (int)EditCategories.Category_Modules:
      btnEditModules.BorderStyle = BorderStyle.Inset;
      break;
      case (int)EditCategories.Category_Bugs:
      btnEditBugs.BorderStyle = BorderStyle.Inset;
      break;
   }
}
private void btnEditUsers_Click(object sender, 
                                System.Web.UI.ImageClickEventArgs e)
{
   ViewState["catsel"] = (int)EditCategories.Category_Users;
}
private void btnEditProjects_Click(object sender, 
                                System.Web.UI.ImageClickEventArgs e)
{
   ViewState["catsel"] = (int)EditCategories.Category_Projects;
}
private void btnEditModules_Click(object sender, 
                                  System.Web.UI.ImageClickEventArgs e)
{
   ViewState["catsel"] = (int)EditCategories.Category_Modules;
}
private void btnEditBugs_Click(object sender, 
                               System.Web.UI.ImageClickEventArgs e)
{
   ViewState["catsel"] = (int)EditCategories.Category_Bugs;
}

In the event handler for each of the buttons all we're doing is persisting the selected category through a ViewState variable. We need to initialize this variable so we'll do it when the page get's loaded. Revise the Page_Load event as shown below.

...
   if (!Page.IsPostBack)
   {
      ViewState["catsel"] = (int)EditCategories.Category_Users;
   }
...

If you compile and run the application you’ll be able to log in as 'admin' (anything else will fail) and you can select each of the edit category buttons and they will maintain their state.

Now that we have a way for the user to select a category, we need to provide the user with a list showing the available items from the selected category. Using this list the user will be able to select an item to edit, delete an item , and add a new item (except for bugs which will have their own editing page). The users category will be slightly different. But for the others, all of the editing will be done within the list. For the users there’s more data fields required and we'll add those later on. The user data controls will also be shown/hidden dynamically.

Add a DataList control and place it below the buttons. Start the PropertyBuilder for the DataList and set the Layout to ‘Flow’. If the default is used the list rows will resize according to the number of items actually being displayed which is not what we want. Edit the Header/Footer Template. For the header enter ‘Name’ as the label for the header. In the footer drag and drop three Buttons and a DropDownList. Set their properties as shown below:

Ctl Type ID Text CommandName
Button btnPrev << Prev
DropDownList ddlSelProj    
Button btnAddNew Add New AddNew
Button btnNext >> Next

These will take care of paging the list, selecting the project to which modules are assigned, and providing a way for the user to add new items to the list. These controls could have been added outside of the list on the page (and would have been easier to implement as you’ll see later) but having them in the footer allows the list to grow and the controls are moved automatically with the list.

We're going to be loading the DropDownList with data from the projects table in the database and we're going to want to know when the user changes the selection. In the event handler that we'll implement for the DropDownList we're going to want to know what item the user selected. To accomplish that we'll need to set the following properties for the DropDownList: AutoPostBack = 'True', DataTextField = 'itemname', DataValueField = 'id'.

Edit the item templates as follows, ItemTemplate:

Ctl Type ID CommandName Text
Label lblItemID   <%# DataBinder.Eval(Container.DataItem, "id") %>
LinkButton lnkItem SelectItem <%# DataBinder.Eval(Container.DataItem, "itemname") %>

SelectedItem:

Ctl Type ID CommandName Text
LinkButton lnkEditItem EditItem Edit
LinkButton lnkDeleteItem DeleteItem Delete
Label lblSelID   <%# DataBinder.Eval(Container.DataItem, "itemname") %>

EditItem:

Ctl Type ID CommandName Text
LinkButton lnkEditUpdate UpdateEdit Update
LinkButton lnkEditCancel CancelEdit Cancel
TextBox txtItemName   <%# DataBinder.Eval(Container.DataItem, "itemname") %>

NOTE:You'll probably also want to set the width, background color, fore color, font size, and font name to customize the appearance.

So, what we want to do is load the list with the ID and name for each item. We need the ID so the code can determine what the user selected and act on it and the name is for the user’s benefit. Since we don't want to load the list with the whole contents of the database we need some paging mechanism that allows the user to ‘scroll’ through the contents of the database. To do that we first need to define how many items make up a page. On the admin page we are going to hard code the page size. Later , when we are designing the bug editing page we’ll implement a ‘user-throtable’ paging mechanism where the user will be able to set how many items are actually displayed on the screen.

Add the following class member variables to the SwatAdmin class:

...
const int pagesize = 4;
int pagecount;
...

When the user selects the paging buttons ‘>>’ and ‘<<’ the code will go to the database and get the next or previous page based on what is currently loaded. When the user is displaying the end of the database we want to disable the button so the user knows that s/he is at the end and the same for when the beginning of the database is being displayed. In order to do that we need to know how many items are actually in the database and then we can determine how many pages we actually have. Revise PageLoad as follows:

...
   if (!Page.IsPostBack)
   {
      ViewState["catsel"] = (int)EditCategories.Category_Users;
      ViewState["curpage"] = 1;
      ViewState["pagecount"] = 0;
      ViewState["currproj"] = 0;
      SetTotalPages();
      BindList("0",ScrollMode.UpdateInPlace);
   }
   pagecount = (int)ViewState["pagecount"];
...

SetTotalPages simply reads the database to determine how many items there are for a particular category. Then we just calculate how many pages we have and set the appropriate ViewState variables. Here’s the code for SetTotalPages.

protected void SetTotalPages()
{
   string strTable = GetCategoryTable();
   if (strTable.Length == 0)
      return;        //Since this shouldn't happen just return
   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 Count(*) FROM ");
      sqlString.Append(strTable);

      //If it's modules we need to know the project
      if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules &&
           ViewState["currproj"] != null)
      {
         sqlString.Append(" where Project=");
         sqlString.Append(ViewState["currproj"].ToString());
      }
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
      int nRecords = (int)cmd.ExecuteScalar();
      //Calculate how many pages we have
      if (nRecords%pagesize == 0)
      {
         pagecount = nRecords / pagesize;
      }
      else
      {
         pagecount = (nRecords / pagesize) + 1;
      }
      //Save it
      ViewState["pagecount"] = pagecount;
      ViewState["curpage"] = 1;
      cnn.Close();
   }
   catch(Exception e)
   {
      lblError.Text = "Database Error!";
   }
}

The GetCategoryTable is a simple translation method.

private string GetCategoryTable()
{
   string strTable = "";

   switch((int)ViewState["catsel"])
   {
      case (int)EditCategories.Category_Users:
         strTable = "Users";
         break;
      case (int)EditCategories.Category_Projects:
         strTable = "Projects";
         break;
      case (int)EditCategories.Category_Modules:
         strTable = "Modules";
         break;
      case (int)EditCategories.Category_Bugs:
         strTable = "Bugs";
         break;
      }

   return strTable;
}

To implement paging we essentially just need to get the next ‘n’ items from the database starting at our current position. The following SQL statement

SELECT TOP 4 id, itemname FROM users WHERE id > 5

will return the next four entries following the one which has record id equal to 5. Unfortunately there isn’t a ‘SELECT BOTTOM’ SQL statement to give us the items when the user is scrolling in the opposite direction. So some extra work is required. To accomplish that we need to sort the items in descending order when we query the database. And since we don't want to dislay the items to the user in reverse order we reverse the order again before we display it to the user. The list also needs to be updated when edits, updates, or delete operations are performed. In these cases, however, we want to maintain the current position in the list. The following function provides us with the functionality that we need for all these operations.

private void BindList(string strRecNum, ScrollMode direction)
{
   string strTable = GetCategoryTable();
   if (strTable.Length == 0)
      return;        //Since this shouldn't happen just return
   try
   {
      SqlConnection cnn;
      string strDirection = ">=";
      StringBuilder sqlString = new StringBuilder("SELECT TOP ");
      sqlString.Append(pagesize.ToString());
      sqlString.Append(" id, itemname FROM ");
      switch(direction)
      {
         case ScrollMode.UpdateInPlace:
            strDirection = ">=";
            break;
         case ScrollMode.UpdateNextPage:
            strDirection = ">";
            break;
         case ScrollMode.UpdatePrevPage:
            strDirection = "<";
            break;
      }

      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(strTable);
      sqlString.Append(" WHERE ");

      //If it's modules we need to specify which project
      if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules &&
          ViewState["currproj"] != null)
      {
         sqlString.Append("Project=");
         sqlString.Append(ViewState["currproj"].ToString());
         sqlString.Append(" AND ");
      }
      sqlString.Append("ID");
      sqlString.Append(strDirection);
      sqlString.Append(strRecNum);
      if (direction == ScrollMode.UpdatePrevPage)
         sqlString.Append(" ORDER BY id DESC");
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds,"ITEMS");
      ds.Tables["ITEMS"].DefaultView.Sort = "id ASC";
      DataList1.DataSource = ds.Tables["ITEMS"].DefaultView;
      DataList1.DataMember = "Name";
      DataList1.DataKeyField = "ID";
      DataList1.DataBind();
      cnn.Close();
      }
      catch(Exception e)
      {
         //Could do better...
          lblError.Text = "Database Error!";
      }
}

Revise the code for the four category button handlers as follows. All we're doing in each case is loading the DataList with the appropriate data from the database and setting the number of pages that we have of data for each category. The modules category is alittle different because we also need to load the DropDownList with the projects that have been defined so that the user can select which project's modules are to be displayed. And we're also reseting the DataList each time the user selects a new category.

private void btnEditUsers_Click(object sender,
                                System.Web.UI.ImageClickEventArgs e)
{
   ViewState["catsel"] = (int)EditCategories.Category_Users;
   ReloadList();
}
private void btnEditProjects_Click(object sender, 
                                System.Web.UI.ImageClickEventArgs e)
{
   ViewState["catsel"] = (int)EditCategories.Category_Projects;
   ReloadList();
}
private void btnEditModules_Click(object sender, 
                                System.Web.UI.ImageClickEventArgs e)
{
   try
   {
      ViewState["catsel"] = (int)EditCategories.Category_Modules;
      //Select a default project
      SqlConnection cnn;
      SqlCommand cmd;
      SqlDataReader dr;
      string ConnectionString = "user id=ASPNET;password=;" + 
             "initial catalog=swatbugs;data source=localhost;" 
             "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cmd = cnn.CreateCommand();
      cnn.Open();

      //Populate the projects combo, I don't know why it won't accept 
      // dr directly??
      cmd.CommandText = "SELECT * FROM projects";
      dr = cmd.ExecuteReader();
      if (dr.Read())
      {
         ViewState["currproj"] = dr["id"].ToString();
      }
      dr.Close();
      cnn.Close();
                
      ViewState["selproj"] = 0;
      ReloadList();
   }
   catch(Exception ex)
   {
      //Could do better...
       lblError.Text = "Database Error!";
   }
}
private void btnEditBugs_Click(object sender, 
                               System.Web.UI.ImageClickEventArgs e)
{
   ViewState["catsel"] = (int)EditCategories.Category_Bugs;
   ReloadList();
}
private void ReloadList()
{
   SetTotalPages();
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;
   BindList("0",ScrollMode.UpdateInPlace);
}

Add the following enum definition to Swat namespace. I added it in the SwatMain code source file.

   ...
   public enum ScrollMode
   {
      UpdateInPlace,
      UpdateNextPage,
      UpdatePrevPage
   }
   ...

And since we are using database classes and types we need to bring in those namespaces as well. Add the following decalaration to the top of the SwatAdmin code source file.

...
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

If you compile and run the application all you’ll get to see is an empty list. So the first thing the user wants to do is add a new item to the list. Let’s code that functionality now. If we had added the AddNew button to the page we would double click on it and an event handler would be created for it. Unfortunately as I alluded to before, because we embedded the button in the footer it is not that easy. Hooking in an event handler for buttons embedded in the DataList is just slightly harder but that’s not the case for other control types as we’ll see below.

To hook in an event handler for a button all we need to do is define a command name for the button’s CommandName property and then check for the command in the DataList’s ItemCommand event handler. If you look at the DataList’s properties you’ll see that it has several events that you can create event handler for. If you double click on ‘ItemCommand’ an event handler will be created for you.

The ItemCommand event get’s generated for every command so if you have a handler for any of the other events then you’ll be getting two messages. Here’s the code for the ItemCommand event handler:

private void DataList1_ItemCommand(object source, 
                       System.Web.UI.WebControls.DataListCommandEventArgs e)
{
   if (e.CommandName == "SelectItem")
   {
      ViewState["selitem"] = e.Item.ItemIndex;
      DataList1.SelectedIndex = e.Item.ItemIndex;
      DataList1.EditItemIndex = -1;

      BindList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);
   }
   if (e.CommandName == "EditItem")
   {
      //If we're showing users...
//    if ((int)ViewState["catsel"] == (int)EditCategories.Category_Users)
//    {
//       GetUserData((int)DataList1.DataKeys[e.Item.ItemIndex]);
//    }

      DataList1.EditItemIndex = e.Item.ItemIndex;
      BindList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);
   }
   if (e.CommandName == "CancelEdit")
   {
      //Was it an 'add new'
      DataList1.SelectedIndex = -1;
      DataList1.EditItemIndex = -1;
      BindList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);
   }
   if (e.CommandName == "UpdateItem")
   {
/*    switch((int)ViewState["catsel"])
      {
         case (int)EditCategories.Category_Users:
            UpdateUser(e);
            break;
         case (int)EditCategories.Category_Projects:
            UpdateItem("Projects",e);
            break;
         case (int)EditCategories.Category_Modules:
            UpdateItem("Modules",e);
            break;
         case (int)EditCategories.Category_Bugs:
            //We don't add new bugs from here, we can just edit their name
            UpdateItem("Bugs",e);
            break;
      }*/
   }
   if (e.CommandName == "DeleteItem")
   {
//    DeleteItem((int)DataList1.DataKeys[e.Item.ItemIndex]);
   }
   if (e.CommandName == "Prev")
   {
//    PreviousPage();
   }
   if (e.CommandName == "Next")
   {
//    NextPage();
   }
   if (e.CommandName == "AddNew")
   {
//    AddNewItem();
   }
}

If you look at the code you’ll see that all we're doing is looking for the CommandName property that we defined for each of the buttons embedded in the DataList. The ItemCommand event is generated for every command detected by the DataList. Note: We'll remove the comments as we enable each of the functions.

Go ahead and compile and run the application. Set a breakpoint at the beginning of the ItemCommand event handler. If you click on the paging buttons or on the AddNew button you'll see that the event is passed through the ItemCommand event and if you look at the e.CommnadName you'll see the command name that was assigned to each of the buttons. If you click on the DropDownList all you'll see is the list opening but no event generated. The reason is that the DropDownList does not support the CommandName property. That's what I meant above when I said that detecting the events for other controls was not as easy as for buttons.

So, we need to detect when the user selects an item in the DropDownList because this is where we'll indicate the projects that are available and allow the user to select which project will be used when we're displaying modules. The way that we can add an event for an item that is embedded in another control is to hook into the ItemCreated event for the container control. In our case we are specifically interested when the footer for the DataList is being created because that's where we've put our controls. In the DataList properties window select events and then double click the ItemCreated event to create an event handler.

This is also where we are going to control the visibility of the controls that we've embedded in the footer. For example the paging buttons need to be disabled when we are at the beginning or the end of the list. Also, showing the project list only applies when the modules are being displayed so when other categories are selected we want the hide the list. The following code shows how we will control these UI aspects. The comments indicate what we're doing.

private void DataList1_ItemCreated(object sender, 
                           System.Web.UI.WebControls.DataListItemEventArgs e)
{
   //We're only interested in the footer
   if (e.Item.ItemType == System.Web.UI.WebControls.ListItemType.Footer)
   {
      //Get a reference to each of our controls
      Control ctlAddNew = ((Control)(e.Item)).FindControl("btnAddNew");
      Control ctlPrev = ((Control)(e.Item)).FindControl("btnPrev");
      Control ctlNext = ((Control)(e.Item)).FindControl("btnNext");
      Control ctlProjects = ((Control)(e.Item)).FindControl("ddlSelProj");
      //If we are displaying the modules and we're not editing...
      if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules &&
           DataList1.EditItemIndex == -    1)
      {
         //Only if it's empty
         if (((DropDownList)ctlProjects).Items.Count == 0)
         {
            try
            {
               SqlConnection cnn;
               SqlCommand cmd;
               SqlDataReader dr;
               string ConnectionString = "user id=ASPNET;password=;" + 
                     "initial catalog=swatbugs;data source=localhost;" + 
                     "Integrated Security=false;connect timeout=30;";
               cnn = new SqlConnection(ConnectionString);
               cmd = cnn.CreateCommand();
               cnn.Open();
               //Populate the projects combo, I don't know why it won't 
               // accept dr directly??
               cmd.CommandText = "SELECT * FROM projects";
               dr = cmd.ExecuteReader();
               while (dr.Read())
               {
                  ((DropDownList)ctlProjects).Items.Add(
                 new ListItem(dr["itemname"].ToString(),dr["id"].ToString()));
               }
               dr.Close();
               cnn.Close();
            }
            catch(Exception ex)
            {
               lblError.Text = "Database Error!";
            }
         }
      
      ((DropDownList)ctlProjects).SelectedIndex = (int)ViewState["selproj"];
      //Hook in an event handler
      ((DropDownList)ctlProjects).SelectedIndexChanged
                       += new System.EventHandler(
                                       this.ddlSelProj_SelectedIndexChanged);
      ctlProjects.Visible = true;    //Show the drop down
      }
      else
         ctlProjects.Visible = false;    //Hide the drop down
      //If we are not editing show the AddNew and paging buttons
      if (DataList1.EditItemIndex == -1)
      {
         ctlAddNew.Visible = true;
         ctlPrev.Visible = true;
         ctlNext.Visible = true;
      }
      else
      {
         ctlPrev.Visible = false;
         ctlNext.Visible = false;
         ctlAddNew.Visible = false;
      }
      //The AddNew button is hidden if it's bugs 
      if ((int)ViewState["catsel"] == (int)EditCategories.Category_Bugs)
         ctlAddNew.Visible = false;
      int curpage = (int)ViewState["curpage"];
      //If we're not at the beginning enable the prev page button
      if (curpage > 1)
         ((Button)ctlPrev).Enabled = true;
      else
         ((Button)ctlPrev).Enabled = false;
      //If we're not at the end enable the next page button
      if (curpage < pagecount)
         ((Button)ctlNext).Enabled = true;
      else
         ((Button)ctlNext).Enabled = false;
   }
}

Now that we have defined an event handler for the DropDownList in the footer above, we need to implement the handler function. Add the following to the SwatAdmin class.

protected void ddlSelProj_SelectedIndexChanged(object sender, 
                                               System.EventArgs e)
{
   ViewState["currproj"] = ((DropDownList)sender).SelectedItem.Value;
   ViewState["selproj"] = ((DropDownList)sender).SelectedIndex;
   BindList("0",ScrollMode.UpdateInPlace);
}

All that the event handler needs to do is load the list of modules for the selected project. Compile and run the application and you will see the paging buttons disabled and the DropDownList will only be visible when the modules category is selected. You won't be able to detect the event handler for the DropDownList until it has some items to select from.

Well we need to tackle the AddNew function in order to get some items into the database. In the ItemCommand handler un-comment the method call when the AddNew command is detected. All that AddNewItem does is determine which category we're displaying in the DataList and then fetches the data from the database. The data will start with the current position of the DataList and we've got to put the data into a DataSet so that we can add a new row. Here's the code.

private void AddNewItem()
{
   string strTable = GetCategoryTable();
   if (strTable.Length == 0)
      return;
   try
   {
      //Same as editing except it's a new item
      SqlConnection cnn;
      string strDirection = ">=";
      StringBuilder sqlString = new StringBuilder("SELECT TOP ");
      sqlString.Append(pagesize.ToString());
      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(strTable);
      sqlString.Append(" WHERE ");
      //If it's modules we're displaying...
      if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules &&
             ViewState["currproj"] != null)
      {
         sqlString.Append("Project=");
         sqlString.Append(ViewState["currproj"].ToString());
         sqlString.Append(" AND ");
      }
      sqlString.Append(" ID");
      sqlString.Append(strDirection);
      sqlString.Append("@recnum");
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
      // Setup our parameters
      if (DataList1.Items.Count > 0)
         cmd.Parameters.Add("@recnum", SqlDbType.Int).Value = 
                                                  (int)DataList1.DataKeys[0];
      else
         cmd.Parameters.Add("@recnum", SqlDbType.Int).Value = 0;
      //Setup the data adapter
      SqlDataAdapter da = new SqlDataAdapter(cmd);
      DataSet ds = new DataSet();
      da.Fill(ds,strTable);
      //Create a new row
      DataRow dr = ds.Tables[strTable].NewRow();
      dr[0] = 0;
      ds.Tables[strTable].Rows.Add(dr);
      //Assign the table to the list
      DataList1.DataSource = ds.Tables[strTable];
      //We're editing the row that's being added
      DataList1.EditItemIndex = DataList1.Items.Count;
      DataList1.SelectedIndex = -1;
      DataList1.DataBind();
      cnn.Close();
      //If it's a user clear the data fields
      if ((int)ViewState["catsel"] == (int)EditCategories.Category_Users)
      {
         txtPassword.Text = "";
         chkAdministrator.Checked = false;
         chkManager.Checked = false;
         chkDeveloper.Checked = false;
      }
   }
   catch(Exception e)
   {
      //Could do better...
      lblError.Text = "Database Error!";
   }
}

When the user is adding or editing an item we give him the option of cancelling the operation. Until the user presses the 'Update' no data is persisted to the database. So let's add those two functions. Un-comment the code for CancelEdit and UpdateItem commands in the ItemCommand event handler.

The Users category is a special case because there are additional data fields that are associated with the user. We'll handle the user case later. For the other categories all we allow is to edit the name of the item. Note that we're using the same method to do inserts and edits. Here's the code.

void UpdateItem(string strTable, 
                System.Web.UI.WebControls.DataListCommandEventArgs e)
{
   try
   {
      SqlConnection cnn;
      string ConnectionString = "user id=ASPNET;password=;" + 
           "initial catalog=swatbugs;data source=localhost;" + 
           "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
      StringBuilder sqlString =  new StringBuilder();
      //Were we editing or adding a new one?
      if ((int)DataList1.DataKeys[e.Item.ItemIndex] == 0)
      {
         //It's an insert
         sqlString.Append("INSERT ");
         sqlString.Append(strTable);
         //If it's module
         if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules
           && ViewState["currproj"] != null)
         {
            sqlString.Append(" (itemname,project) ");
            sqlString.Append("VALUES (@itemname,@proj)");
         }
         else
         {
            sqlString.Append(" (itemname) ");
            sqlString.Append("VALUES (@itemname)");
         }
         SqlCommand cmd = cnn.CreateCommand();
         cmd.CommandText = sqlString.ToString();

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

         //If it's module
         if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules
             && ViewState["currproj"] != null)
         {
            cmd.Parameters.Add("@proj", SqlDbType.Int).Value
                             = System.Convert.ToInt32(ViewState["currproj"]);
         }
         cmd.ExecuteNonQuery();
      }
      else
      {
         //An update
         sqlString.Append("UPDATE ");
         sqlString.Append(strTable);
         sqlString.Append(" SET itemname=@itemname");
         //If it's module
         if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules
            && ViewState["currproj"] != null)
         {
            sqlString.Append(",project=@proj");
         }
         sqlString.Append(" WHERE id=@id");
         SqlCommand cmd = cnn.CreateCommand();
         cmd.CommandText = sqlString.ToString();
         // Fill our parameters
         cmd.Parameters.Add("@itemname", SqlDbType.NVarChar, 50).Value
                         = ((TextBox)e.Item.FindControl("txtItemName")).Text;
         cmd.Parameters.Add("@id", SqlDbType.Int).Value
                                       = DataList1.DataKeys[e.Item.ItemIndex];
         //If it's module
         if ((int)ViewState["catsel"] == (int)EditCategories.Category_Modules
             && ViewState["currproj"] != null)
         {
            cmd.Parameters.Add("@proj", SqlDbType.Int).Value
                             = System.Convert.ToInt32(ViewState["currproj"]);
         }
         cmd.ExecuteNonQuery();
      }
      cnn.Close();
   }
   catch(Exception ex)
   {
      lblError.Text = "Database Error!";
   }
   //Update list
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;
   SetTotalPages();
   BindList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);
}

As I mentioned above the user category is a special case because there are additional data fields that are associated with users. The controls for the additional fields are only shown when the user category is selected. Those fields consists of the password and the role(s) that can be assigned to the user. We need to add those controls to the SwatAdmin page. Refer to Fig.1 and add a TextBox and three CheckBox controls as shown. Label them as follows:

Label lblPassword
TextBox txtPassword
Label lblRoles
CheckBox chkAdministrator
CheckBox chkManager
CheckBox chkDeveloper

We only want to show the user data controls when the users category is selected. And the place to do that is before the page gets rendered. Add the following code at the end of the PreRender method.

...
//If we are editing users we need to show the user fields
//Else we hide them
if ((int)ViewState["catsel"] == (int)EditCategories.Category_Users &&
    (DataList1.EditItemIndex >= 0))
   ShowUserFields(true);
else
   ShowUserFields(false);
...

private void ShowUserFields(bool bShow)
{
   lblPassword.Visible = bShow;
   txtPassword.Visible = bShow;
   lblRole.Visible = bShow;
   chkDeveloper.Visible = bShow;
   chkAdministrator.Visible = bShow;
   chkManager.Visible = bShow;
}

Now that the user can edit the fields we need to add the function that will called to do the user update. As with the UpdateItem this handles both inserts and edits. Here's the code.

private void UpdateUser(System.Web.UI.WebControls.DataListCommandEventArgs e)
{
   try
   {
      SqlConnection cnn;
      string ConnectionString = "user id=ASPNET;password=;" + 
            "initial catalog=swatbugs;data source=localhost;" + 
            "Integrated Security=false;connect timeout=30;";
      cnn = new SqlConnection(ConnectionString);
      cnn.Open();
      StringBuilder sqlString = new StringBuilder();
      //Were we editing or adding a new one?
      if ((int)DataList1.DataKeys[e.Item.ItemIndex] == 0)
      {
         //It's an insert
         sqlString.Append("INSERT users (itemname, password, roles)");
         sqlString.Append("VALUES (@itemname, @password, @roles)");
      }
      else
      {
         //An update
         sqlString.Append("UPDATE users Set itemname=@itemname, " + 
                          "password=@password, roles=@roles");
         sqlString.Append(" WHERE id=@id");
      }
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
      // 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("@id", SqlDbType.Int).Value = 
                                        DataList1.DataKeys[e.Item.ItemIndex];

      //What roles
      int nRoles = 0;
      if (chkDeveloper.Checked)
         nRoles |= (int)AccessPrivilege.Developer;
      if (chkAdministrator.Checked)
         nRoles |= (int)AccessPrivilege.Administrator;
      if (chkManager.Checked)
         nRoles |= (int)AccessPrivilege.Manager;
      cmd.Parameters.Add("@roles", SqlDbType.TinyInt).Value = nRoles;
      cmd.ExecuteNonQuery();
      cnn.Close();
      //Update list
      DataList1.EditItemIndex = -1;
      DataList1.SelectedIndex = -1;
      SetTotalPages();
      BindList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);
   }
   catch(Exception err)
   {
      lblError.Text = "Error Updating User!";
   }
}

There's a couple of other modifications required for the user case. If we are editing a user we need to read the data for the user from the database and load the controls. Un-comment the code for the 'EditItem' command in the ItemCommand event handler. Here's the code for the GetUserData method.

private void GetUserData(int nUserID)
{
   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 users WHERE id=");
      sqlString.Append(nUserID.ToString());
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString(); 
      SqlDataReader dr = cmd.ExecuteReader();
                    
      if (dr.Read())
      {
         if (dr["Password"] != System.DBNull.Value)
            txtPassword.Text = dr["Password"].ToString();
         int nRoles = 0;
         if (dr["Roles"] != System.DBNull.Value)
            nRoles = System.Convert.ToInt16(dr["Roles"].ToString());
         chkAdministrator.Checked = 
             (nRoles & (int)AccessPrivilege.Administrator) == 
                                         (int)AccessPrivilege.Administrator;
         chkManager.Checked = (nRoles & (int)AccessPrivilege.Manager) == 
                                                (int)AccessPrivilege.Manager;
         chkDeveloper.Checked = (nRoles & (int)AccessPrivilege.Developer) == 
                                               (int)AccessPrivilege.Developer;
      }
                
      dr.Close();
      cnn.Close();
   }
   catch(Exception e)
   {
      lblError.Text = "Error getting user data!";
   }
}

Finally, add the following to the end of UpdateItem so the user fields will be initialized when adding a new user.

...
//If it's a user we're adding set up the data fields
if ((int)ViewState["catsel"] == (int)EditCategories.Category_Users)
{
   txtPassword.Text = "";
   chkDeveloper.Checked = false;
   chkAdministrator.Checked = false;
   chkManager.Checked = false;
}
...

We're almost done with the administration functionality that we set out to implement. Compile and run the application. You should be able to add some users (make sure you define at least one with Administrator role, once you add a new user the 'admin' logon won't work any more). Add some projects and then define some modules for each project. The bugs category won't work as yet because the only operation available is to delete or edit a bug's title. If you login after you've added a new user and you defined other roles for the user, the application will try to open the bug editing page which is the default page. Since we haven't designed that page yet you'll get an error. Click on the 'Admin' toolbar button to get to the SwatAdmin page.

All we've got left to do is to add functionality to allow the user to delete an item and to hook in the paging. Un-comment the code in the DataList's ItemCommand handler for the comands 'DeleteItem', 'Prev', and 'Next'. Deleting an item is trivial, here's the code.

private void DeleteItem(int nID)        
{
   string strTable = GetCategoryTable();
   if (strTable.Length == 0)
      return;        //Since this shouldn't happen just return
   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("DELETE ");
      sqlString.Append(strTable);
      sqlString.Append(" WHERE id=@itemid");
      SqlCommand cmd = cnn.CreateCommand();
      cmd.CommandText = sqlString.ToString();
      cmd.Parameters.Add("@itemid", SqlDbType.Int).Value = nID;
      cmd.ExecuteNonQuery();
      cnn.Close();
      DataList1.SelectedIndex = -1;
      DataList1.EditItemIndex = -1;
      BindList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdateInPlace);
      SetTotalPages();
   }
   catch(Exception e)
   {
      lblError.Text = "Error deleting item!";
   }
}

The hard part for paging has already been done so what's left for the event handlers is just some minor housekeeping.

private void NextPage()
{
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;
   int curpage = (int)ViewState["curpage"];
   if (curpage < pagecount)
   {
      curpage += 1;
      ViewState["curpage"] = curpage;
      BindList(DataList1.DataKeys[DataList1.Items.Count-1].ToString(),
                                                 ScrollMode.UpdateNextPage);
   }
        
}
private void PreviousPage()
{
   DataList1.EditItemIndex = -1;
   DataList1.SelectedIndex = -1;
   int curpage = (int)ViewState["curpage"];
   if (curpage > 1)
   {
      curpage -= 1;
      ViewState["curpage"] = curpage;
      BindList(DataList1.DataKeys[0].ToString(),ScrollMode.UpdatePrevPage);
   }
}

That's it. We're done with the admin functionality. Compile and run the application. You'll be able to add, edit, and delete users, projects, and modules. The only thing that can be done to bugs from the admin page is edit the title and delete them. Since we don't have any in the database you'll have to wait until we've implemented the bug editing page before you'll be able to test that functionality. The page size has been hard coded to four entries. Once you add more than four items in any of the categories you'll see the paging buttons enabled and you'll be able to page through the contents of the database.

Next time we'll add bug editing which is the main function of the application.

License

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

About the Author

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

Comments and Discussions

 
QuestionWhere is the 4th section? PinmemberSkanless13-Dec-06 19:34 
AnswerRe: Where is the 4th section? PinmemberAl Alberto15-Dec-06 9:01 
GeneralDatabase Error! Pinmemberskinnyreptile1686-Oct-06 7:12 
GeneralRe: Database Error! PinmemberAl Alberto11-Oct-06 1:01 
GeneralRe: Database Error! Pinmemberskinnyreptile16811-Oct-06 3:49 
GeneralVB.NET rewrite PinmemberSkittlesBohs17-Oct-04 22:53 
GeneralRe: VB.NET rewrite PinmemberAl Alberto18-Oct-04 12:43 
GeneralRe: VB.NET rewrite PinmemberGuythePro17-May-05 15:37 
GeneralRe: VB.NET rewrite PinmemberMatt Vardy26-Oct-05 6:03 
GeneralDatabase doesn't insert,update.... Pinmemberos5866-Apr-04 23:54 
GeneralPerfect! Pinmemberpsusong24-Jun-03 0:59 
GeneralRe: Perfect! PinmemberAl Alberto24-Jun-03 5:01 
www.codeproject.com/aspnet/SWAT1.asp and www.codeproject.com/aspnet/SWAT2.asp.

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 23 Jun 2003
Article Copyright 2003 by Al Alberto
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid