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.
Hint 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.
365
Figure 11.49: This form uses combos, list boxes, a new form of list box, and some more standard controls.
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.
Figure 11.50: This form uses one data connection, several data adapters, and two datasets. The dotted lines indicate temporary connections.
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
366
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
367
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,
368
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.
Trap 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:
1.Determine the information you want.
2.Construct an SQL query to extract the information.
3.Attach that query to the appropriate data adapter.
4.Fill a temporary data set with the results.
5.Examine the rows of the data set for more details.
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.
Hint 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
369
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
Trap 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 Agents WHERE 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"]);
370
} // 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
371
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.
In the Real World
What is reusability as it applies to programming? Reusability in programming is in fact smart programming. When faced with routine programming tasks, smart programmers create reusable code through classes or functions that save them time and their employer’s money.
Although often found in the object−oriented paradigm, reusability can and should be implemented in other paradigms, such as the event−driven model of Visual Basic, through functions and subprocedures.
Any time you find yourself in a situation that will or could require repeated code, go ahead and create modularized or reusable code through procedures.
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;
372
//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.
Hint 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.
373