Creating the SpyMaster Database

The SpyMaster database puts together all the database concepts you have seen so far and adds a few more, to build a complete agent-handling solution. The SpyMaster program reassembles all the data taken apart in the data normalization process and puts it in a form that makes sense to the user.

Building the Main Form

Like many projects, the SpyMaster program features a main screen as a control panel. Each button on the main screen calls another editor. Because the code for the main form is so much like code you’ve seen before, I won’t reproduce it here. You can see it on the CD-ROM if you wish.

When I was debugging a particular form, I changed my main form’s Main() method so it immediately called up the form I was working on. For example, when I was working on the Agent Editor form (which I worked on for quite some time, incidentally) I had the following code in the Main() method of the main form:

Application.Run(new AgentEdit());

This caused the Agent Edit form to pop up immediately without the menu screen ever appearing. Of course, when you’re done debugging, you’ll need to change the Main() method back so it starts itself up instead.

Some of the editing forms are much simpler than the others, so I will show them to you from simple to complex, rather than in the order the buttons appear on the main form.

Editing the Assignments

It is reasonably easy to edit any data table in your original database. The EditAssignments form enables the user to modify the Assignments table and add new assignments. The visual layout of EditAssignments is shown in Figure 11.46.

I drug the Assignment table to the form and renamed my connection and adapter objects. I then created a data set based on the Adapter, renamed it, and bound the data grid to the data set.

If you want the editor to be able to update information, your data set must point to a table, not a view. Views cannot be edited because they often come from more than one table.

The only initialization necessary is to fill the data set from the adapter.

private void EditAssignment_Load(object sender,
        System.EventArgs e) {
  AssignAdapter.Fill(spyDS);
}

The user can modify elements in the data grid. When the user presses the Update button, the code to update the database is simplicity:

private void btnUpdate_Click(object sender, System.EventArgs e) {
  AssignAdapter.Update(spyDS);
}

Remember that the data set is just a copy of the data. To make changes to the original database, you must use the Update() method of the appropriate data adapter. This method requires you to send a data set with the new information. Because the data set is bound to the grid, any changes made in the data grid are reflected on the data set.

This is one of the advantages of .NET’s data model: The user can experiment with changes to the data set without disturbing the original database. No changes are made to the original database until the adapter’s Update() method is called. This gives you a chance to check the code and make sure the changes work before changing the original database.

Editing the Specialties

Editing the Specialties table is just like editing the Assignments table. The EditSpecialties form looks very similar to EditAssignments, as you can see from Figure 11.47.

The form’s load method fills the data table just like in EditAssignments.

private void button1_Click(object sender,
        System.EventArgs e) {
  specAdapter.Update(spyDS);
}

This form is directly connected to the Specialties table. Once again, the Update button simply calls the data adapter’s Update() method.

private void button1_Click(object sender, System.EventArgs e) {
  specAdapter.Update(spyDS);
}

Viewing the Agents

It’s a little trickier to edit the agent information, because this information comes from a number of tables. However, viewing the agent information is not too tricky if you start with a data view.

Building the Visual Layout of ViewAgents

The ViewAgents form is based on a couple of data views. It is featured in Figure 11.48.

Notice that I have made multiple data adapters. The AAAdapter connects to a view called Agents_Assignments. The data set called theAAdataSet is based on

AAAdapter. I also created another adapter called ASAdapter, which connects to the Agent_Specialty view. The ASdataSet connects to the ASAdapter. Note that only one Connection object is necessary, because both adapters can use the same connection. (It’s also possible to use only one data set, as I’ll illustrate in the EditAgent form, but multiple data sets can be quite a bit easier to work with.)

Connecting the Combo Box

Combo boxes and list boxes can be bound to data sets just like data grids. List and combo boxes have a DataSource property that can be set to any data set. The DisplayMember property determines which field of the DataSource are displayed. I set the DataSource to theAADataSet (which is the data set corresponding to the Agents_Assignments view). I set the DisplayMember property to Agent_Assignment.CodeName. When the form is displayed, the combo box is automatically populated with all the codenames from the Agent_Assignment view. Because the data is displayed in order, the SelectedIndex property of any code name on the list also refers to the ID of the corresponding agent.

Binding the Labels to the Data Set

Most of the labels also are bound to theAADataSet. Each label has a DataSource and DataMember property which can be set to determine exactly what field of what data set is displayed. If the user chooses a new element from the combo box, all the other labels on the form are automatically updated to display the corresponding agent’s information. If the components are bound to the data sets appropriately, there is no need to write any code to update the labels.

Working with Specialty Data

The specialty data does require some work, because it is not available on the Agent_Assignment view. I wrote a method called showSpecialties() (called whenever the Agents combo is changed) that re-populates the specialties list box based on a custom query.

private void showSpecialties(){
  //populate the specialties list box
  lstSpec.Items.Clear();
   //reset the agent-specialty query
  theASDataSet.Agent_Spec.Clear();
  asAdapter.SelectCommand.CommandText =
    "SELECT Specialtyname FROM Agent_Spec " +
    "WHERE CodeName = '" + 
    lblCodeName.Text + "'";
  asAdapter.Fill(theASDataSet);

  //copy each element to list box
  foreach (DataRow myRow in theASDataSet.Agent_Spec.Rows){
    lstSpec.Items.Add(myRow["SpecialtyName"]);
  } // end foreach
} // end showSpecialties

The first thing the method does is clear the specialties listbox and the Agent_Spec table of theASDataSet. Then, I set a new selection command to asAdapter which selects only those skills related to the current agent, and filled the data set to account for the new query.

The foreach loop steps through each row in the resulting data set and copies the Specialtyname field of the row to the listbox.

To directly access a particular field, first extract a DataRow object from the data set’s Rows collection. You can then use the field name as an index to the field. You can read the field values of any data row, but you can only change fields if the original data set is based on a table rather than a view.

Editing the Agent Data

The most challenging part of the program is editing the Agent information. This is difficult because the information regarding agents is spread across every table in the database.

The visual design and data connections for this form required some care. The visual design of the Edit Agent Data form is shown in Figure 11.49.

Most of the controls on the form are fairly standard, but the checked list box is new to you. This control holds a list of check boxes. It turns out to be ideal for the specialties data.

The Edit Agent Data form is different from the ViewAgent Data form in one major way. In the view form you could use a view because the information is read-only. In the edit form you must have connections to the actual table objects because the original database will be modified. Figure 11.50 illustrates the data connection structure I used for this project.

As you can see from the diagram, one connection object attaches to the original database. I have four data adapters coming from this connection, one for each table in the original database. Notice the naming convention I used for the data adapters. It’s very important to rename data adapters because otherwise you will quickly forget which adapter is related to which table. I usually use names that indicate the table associated with the adapter.

The DataAdapter objects are used to generate two data sets. The dsSpy data set holds a copy of each table from each adapter. When you create a data set from a data adapter, you can indicate that the data set should go into an existing adapter in the Create Dataset Dialog. I used this technique to add all the tables to the dsSpy data set. When this is done, dsSpy is a copy of all the tables in the original spy database. The other data set, called dsTemp is created in the code. It is used to generate temporary queries. It has one table called results which holds the results of any queries.

One other interesting feature of the form is a series of data grids. These grids are barely visible on the form in design time, and they are completely invisible to the user (their Visible property is set to false). These grids are each linked to one of the tables in the various data sets. I used them during debugging to make sure I knew what was going on in each table. When I was working with a particular table, I moved the corresponding grid to the center of the form to get a window on the data. Because the user does not need to see the data grids, I simply made them invisible, but kept them on the form for later debugging.

Preparing the Data Sets in the Form Load Method

The Form Load method fills the appropriate tables of the dsSpy data set from the appropriate adapters.

private void EditAgentData_Load(object sender, System.EventArgs e) {
   //fill all the data adapters
   adAgentSpec.Fill(dsSpy, "Agent_Specialty");
   adAgents.Fill(dsSpy, "Agents");
   adSpecialties.Fill(dsSpy, "Specialties");
   adAssignments.Fill(dsSpy, "Assignments");
   updateForm();
}

Handling the Component Events

A few of the onscreen components have methods associated with them. All of these events call various custom methods. By examining the event code first, you’ll have an overview of the rest of the form’s methods.

private void cboAgent_SelectedIndexChanged(object sender, 
         System.EventArgs e) { 
  updateForm(); 
} 

private void lstAssign_SelectedIndexChanged(object sender, 
         System.EventArgs e) { 
  getAssignInfo(lstAssign.SelectedIndex); 
} 
private void btnUpdate_Click(object sender, System.EventArgs e) {
  updateAgent();
  updateSpecialties();
} // end btnUpdate

The cboAgent combo box is bound to the CodeName field of the Agents table. Whenever the user chooses a new element from this combo box, the current agent is changed. Some elements are automatically changed, but many of them require some additional code. The updateForm() method is used to update the entire display to reflect the current agent’s status.

Likewise, whenever the user chooses a new assignment, the assignment description and assignment location labels should change. The getAssignInfo() handles this duty.

When the user is ready to update an agent’s information, he or she presses the btnUpdate button. The data that appears on the EditAgentData form is actually stored in two different tables, and it’s necessary to update them both. The updateAgent() method updates the Agent table based on the current settings of the form, and the updateSpecialties() method updates the Agent_Specialty table based on the current settings.

Getting the Assignment Information

Most of the code in the EditAgentData form involves creating and viewing data from custom queries. The getAssignInfo() method uses this technique to figure out the values to put in the description and location labels. This method expects an assignment ID as a parameter.

private void getAssignInfo(int assignID){
  //use assignID to figure out description, location
   //find only the current spy's assignment
  DataSet dsTemp;
  DataRow tempRow;
  string query;

  query = "SELECT * FROM Assignments WHERE AssignmentID = ";
  query += Convert.ToString(assignID);
  adAssignments.SelectCommand.CommandText = query;
  dsTemp = new DataSet();
  adAssignments.Fill(dsTemp, "results");
  dgTemp.SetDataBinding(dsTemp, "results"); 
  //get a row
  tempRow = dsTemp.Tables["results"].Rows[0];
  lblDescription.Text = tempRow["Description"].ToString();
  lblLocation.Text = tempRow["Location"].ToString();
} // end getAssignInfo

I began by creating a temporary data set called dsTemp, a temporary DataRow object called tempRow, and a string called query. I need to know the values of the description and location fields of the Assignments table. If I’m looking for the data associated with assignment number 1, I can use the following SQL query:

SELECT * FROM Assignments WHERE AssignmentID = 1

I built a form of this statement in the query variable, but I used the value of the assignID variable. Note that although the assignID is an integer, SQL statements are strings, so I needed to convert assignID to a string.

I then reset dsTemp, and filled it from adAssignments (the adapter associated with the Assignments table). The results (which should be one row) are stored to the results table of dsTemp and this table is bound to a datagrid called dgTemp. (Recall that dgTemp is not visible in the final version of the program. It’s still extremely handy because I could use it to ensure the query was working as expected before I did anything dangerous with the data.) The query should only return one row, which is row 0. I copied that row to the tempRow variable to simplify the next couple of lines of code. Finally, I copied the description and location fields from tempRow to the text boxes.

The value of a DataRow’s fields are returned as an object type. You usually have to do some sort of conversion to get the data into the type you need. In this case I used the object’s ToString() method to convert the field values to strings.

You see this same general strategy many times throughout this form’s code. In general, it goes like this:

Updating the Form to Reflect the Current Agent

The updateForm() method is called from the form’s load event, and whenever the user changes the agent in cboAgent.

private void updateForm(){
  fillAssignments();
  fillSpecialties();
} // end updateForm

It simply calls two other methods. Originally, all of the code in fillAssignments() and fillSpecialties() was in the updateForm() method, but this method quickly became unwieldy, so I split the data into smaller segments.

Filling Up the Assignments List Box

The fillAssignments() method uses a form of the algorithm described in the getAssignInfo() method.

First, the method creates a number of variables. You’ve already been introduced to query, dsTemp, and tempRow in the getAssignInfo() method. The agentID variable is an integer holding the ID of the current agent. I extracted this value from the SelectedIndex property of cboAgent. The first order of business is to populate the assignments list box. There is no need for a special query for this because the dsSpy.Assignments table already has all the assignments listed. I used a foreach loop to step through each row of the Assignments table. I extracted the Name field from each row and added it to the lstAssign list box.

You might wonder why I didn’t need to convert the name field to a string before adding it to the list box. I didn’t because technically the Listbox.Items.Add() method can accept an object as a parameter, and the field is returned as an object.

Determining which assignment should be selected requires a query, because I only want to know which assignment is associated with the current agent. I built a query that will select the current agent from the Agents table. I cleared the dsTemp data set and refilled it from the appropriate adapter after applying the query. The new row appears as the results table of the dsTemp data set. I put the row into the tempRow variable and extracted the AssignmentID field from it. I then copied the value of assignID to the SelectedIndex property of lstAssign. This has the effect of highlighting whichever assignment is associated with the currently displayed spy. Because the SelectedIndex might have changed, I called getAssignInfo() to ensure that the description and location labels were updated.

private void fillAssignments(){
  string query = "";
  int agentID = cboAgent.SelectedIndex;
  DataSet dsTemp = new DataSet();
  DataRow tempRow;

  //fill assignments list box
  foreach (DataRow myRow in dsSpy.Assignments.Rows){
    lstAssign.Items.Add(myRow["Name"]);
  } // end foreach

  //select appropriate assignment
  //begin by putting current agent row in dsTemp
  query = "SELECT * from Agents WHERE AgentID = " + agentID;
  adAgents.SelectCommand.CommandText = query;
  dsTemp.Clear();
  adAgents.Fill(dsTemp, "results");
  dgTemp.SetDataBinding(dsTemp, "results");

  //result is one row, grab AssignmentID
  tempRow = dsTemp.Tables["results"].Rows[0];
  int assignID = Convert.ToInt32(tempRow["AssignmentID"]);
  lstAssign.SelectedIndex = assignID;

  //fill up the assignment labels, too.
  getAssignInfo(assignID);

} // end fillAssignments

Although this example is easy to understand, it poses a serious security threat. If a value were passed, such as

DELETE FROM Agents;

your table could be wiped out. A better way of writing the statement is as follows:

							adAgents.SelectCommand = new SqlCommand("SELECT * FROM AgentsWHERE AgentID=@AgentID", new SqlConnection(connStr));
							adAgents SelectCommand.Parameters.Add("@AgentID",
							SqlDbType.Integer);
							adAgents SelectCommand.Parameters["@AgentID"].Value=agentID;

Filling Up the Specialties CheckedListBox

The Specialties field enables each spy to have any number of specialties. This can be difficult to display. Although it is possible to allow multiple selections in a list box, this use of a list box is somewhat non-intuitive. C# includes a new type of list box called the CheckedListBox that is perfect for this type of situation. A checked listbox has a number of items, just like a normal list box. Each item has a check box associated with it. You can set the value of the check box for any item in the list with the SetItemChecked() method.

private void fillSpecialties(){
  //fill clbSpec with specialties
  string query = "";
  int agentID = cboAgent.SelectedIndex;
  DataSet dsTemp = new DataSet();
  DataRow tempRow;

  clbSpec.Items.Clear();
  foreach (DataRow myRow in dsSpy.Specialties.Rows){
    clbSpec.Items.Add(myRow["Specialtyname"]);
  } // end foreach

  //find all Agent_Spec rows for current agent
  query = "SELECT * FROM Agent_Specialty WHERE ";
  query += "AgentID = " + agentID;

  dsTemp = new DataSet();
  adAgentSpec.SelectCommand.CommandText = query;
  adAgentSpec.Fill(dsTemp, "results");
  dgTemp.SetDataBinding(dsTemp, "results");

  //preset all items in clbSpec to unchecked
  for(int i = 0; i < clbSpec.Items.Count; i++){
    clbSpec.SetItemChecked(i, false);
  } // end for loop

  //check current spy's skills in clbSpec
  foreach (DataRow myRow in dsTemp.Tables["results"].Rows){
    int specID = Convert.ToInt32(myRow["SpecialtyID"]);
    clbSpec.SetItemChecked(specID, true);
  } // end foreach

} // end fillSpecialties

I began by clearing clbSpec, and adding each specialty name to the checked list box. I simply stepped through all the rows in the Specialties table and added the Specialtyname field to the list box. Then I created a query that returned all the records from the Agent_Specialty table that relate to the current agent (determined by the agentID variable.) I preset each value in cblSpec to false to clear out any values that might have been there from an earlier agent. Then I looked through the query and checked the specialty associated with each record in the query.

Updating the Agent Data

After making changes, the user can choose to update the agent. This actually occurs in two distinct phases. First, it is necessary to update the Agents table. The agent’s code name and assignment are stored in the Agents table, but they are not directly entered by the user.

private void updateAgent(){
  //updates the agents table
  DataRow agentRow;
  int agentID = cboAgent.SelectedIndex;
  int assignID = lstAssign.SelectedIndex;

  agentRow = dsSpy.Agents.Rows[agentID];

  //Change code name if new name is in text field
  if (txtCodeName.Text != ""){
    agentRow["CodeName"] = txtCodeName.Text;
    txtCodeName.Text = "";
  } // end if

  //change assignment based on lstAssign
  agentRow["AssignmentID"] = assignID;

  //update the agent in the main database
  dsSpy.AcceptChanges();
  adAgents.Update(dsSpy, "Agents");
  lstAssign.SelectedIndex = assignID;

} // end updateAgent

I created integers to hold the agentID and assignID. These values are easily determined by reading the SelectedIndex property of the associated list boxes. I then pulled the current agent’s data row from the dsSpy.Agents table.

I enabled the user to change the agent’s name by typing in the textbox. Although I could have let the user type directly into the combo box, it turns out to be cleaner to have a text box set aside for this purpose. (In fact, I set the combo box to act like a drop-down list box so the user cannot directly type into it.) If the text box is blank (which is its default state) nothing happens. However, if there is a value in the text box, that value is copied over to the CodeName field of agentRow. I then reset the text box to be empty so the code name isn’t changed for the next agent unless the user types something new in the text box.

I copied the value of the assignID variable to the AssignmentID field of agentRow.

Finally, I updated the local data set with a call to dsSpy.AcceptChanges(). This command tells the data set to register any changes made to the data. The data set is only a copy of the actual database. To make permanent changes to the original database, I used the update member of the appropriate data adapter. Remember, you can only update adapters based on data tables. As I tested this project, I discovered that sometimes the wrong element of the assignment list box is sometimes highlighted, so I reset the selectedIndex property to assignID. This ensures that the form’s display is always synchronized with the data set.

Updating the Specialty Data

All the data about one agent in the Agents table resides on one record, which is easy to extract and update. The specialty data was trickier to update, because the data about one agent can span any number of records. Instead of simply modifying one existing record, I had to delete any old records for the agent and add new ones. Remember, the Agent_Specialty table works by having one record for each relationship between agents and specialties. It took a couple of queries to make this happen.

private void updateSpecialties(){
  //find all specialties associated with this agent
  try {
    string query;
    DataSet dsTemp = new DataSet();
    DataRow tempRow;
    int agentID = cboAgent.SelectedIndex;

    //find all current rows for this agent
    query = "SELECT * FROM Agent_Specialty ";
    query += "WHERE AgentID = ";
    query += agentID.ToString();

    //delete rows from database
    adAgentSpec.SelectCommand.CommandText = query;
    adAgentSpec.Fill(dsSpy, "Agent_Specialty");
    foreach (DataRow myRow in dsSpy.Agent_Specialty.Rows){
      myRow.Delete();
    } // end foreach
    //adAgentSpec.Update(dsSpy, "Agent_Specialty");

    //find the largest id
    query = "SELECT MAX(Agent_SpecialtyID) FROM Agent_Specialty";
    adAgentSpec.SelectCommand.CommandText= query;
    dsTemp = new DataSet();
    adAgentSpec.Fill(dsTemp, "results");
    tempRow = dsTemp.Tables["results"].Rows[0];
    int largestID = Convert.ToInt32(tempRow[0]);
    int newID = largestID + 1;

    //add rows
    foreach (int specID in clbSpec.CheckedIndices){
      dsSpy.Agent_Specialty.AddAgent_SpecialtyRow(
        newID, agentID, specID);
      newID++;   
    } // end foreach
    dsSpy.AcceptChanges();
    adAgentSpec.Update(dsSpy, "Agent_Specialty");
    dgTemp.SetDataBinding(dsSpy, "Agent_Specialty");
  } catch (Exception exc){
    MessageBox.Show(exc.Message);
  } // end try

} // end updateSpecialties();

After creating the now-familiar query, DataSet, and DataTable variables, I created a query designed to return all the records of the Agent_Specialty table pertaining to the currently selected agent. I then deleted each of these rows. This is similar to clearing a list box before repopulating it. I wanted to ensure that any elements already in the database are cleared, and then add new records to handle changes in the data. Each new row requires a unique integer for its ID field. I used a special query to find the maximum value of the key field.

SELECT MAX(Agent_SpecialtyID) FROM Agent_Specialty

This query returns a special table with one row and one column. The value of this table is the largest value in the Agent_Specialty field. I then added one to that value and stored it in newID. This provides an integer guaranteed to not already be in the database.

I added a new row to the Agent_Specialty table by invoking the AddAgent_SpecialtyRow() method of the Agent_Specialty property of the dsSpy object. Recall that the Agent_Specialty property is a custom member of the extended DataSet object, and this property has a custom member to enable adding a row.

You also can add a row to a table referred by a generic DataSet. Regular data set tables have a newRow property that automatically generates a new row based on the data set’s schema. You then need to explicitly fill each field of the new row.

Finally, I used the acceptChanges() method of dsSpy and the Update() method of adAgentSpec to update the database with the new values.

Notice that I placed all of the primary code for the method inside a try-catch block. This is because SQL queries can cause all kinds of debugging headaches and the try-catch structure makes it much easier to determine exactly what went wrong and how to fix it.