Extending the Sample Application

Now that you have a taste for casting (and for working with superheroes ), it's time to work on the feedback form for the sample application. Once again, you'll be working with data from a database. Therefore, before adding any code to the sample application, add a reference to the dbProvider.dll. Remember that dbProvider.dll is the DLL from eInfoDesigns that lets us talk to the MySQL database. You learned how to add a reference to this DLL in Chapter 4, "Strings."

To extend the sample application:

1. Right-click on the file feedback.aspx and select View Designer from the pop-up menu.

2. Create a form that looks like the form in Figure 7.21 . Optionally you could download the skeleton file for this project from Peachpit's Web site.

   Figure 7.21 The job feedback form has a drop-down listbox at the top that lets you select the name of a superhero. The jobs for the superhero will be displayed in a textbox under the Select Job label, along with the ranking. A superhero may have more than one job, thus, you can scroll through the jobs for the superhero by using the < and > buttons . Once you find the job, you can change the ranking and click the Update button.

    private void Page_Load(object sender, System.EventArgs e) {  if (IsPostBack == false)   {   string sql = "select * from superheroes";   MySqlConnection conn = new   MySqlConnection (ConnectionString);   conn.Open();   MySqlCommand cmd = new MySqlCommand (sql,conn);   IDataReader reader = cmd.ExecuteReader();   lstHeroes.DataSource = reader;   lstHeroes.DataTextField = "Name";   lstHeroes.DataBind();   ViewState["index"]=0;   SelectRecords();   }  } 

3. Double-click on a blank space in the form. This will cause VS.NET to open the code editor and add a Page_Load function.

4. Scroll up to the top of the file and under the last using statement add the following: using eInfoDesigns.dbProvider.MySqlClient;

5. Right before the Page_Load function after all the variable declarations add the following declarations:

    protected System.Data.DataSet ds = new DataSet(); protected const string ConnectionString = "server=localhost; uid=;pwd=;database=csharpvqs;"; 

   The first line declares a variable to store a Dataset. A Dataset is a class that stores records from a database in memory. It will help us maintain and display the information for the fields in this form. The second line declares a string variable to hold the connection string. You should already be familiar with connection strings from previous chapters.

6. Inside the Page_Load function add the code in Figure 7.22 .

   Figure 7.22 One of the jobs of this code is to populate a drop-down listbox in the form that enables you to select a superhero and view all the jobs the superhero has done. Notice that the code begins with an if statement that asks if IsPostBack is false. The purpose of this statement is to execute the code only oncethe very first time the form is loaded. Other events, like clicking buttons, or selecting a hero from the list will cause the Page_Load event to trigger again, but it's important we don't execute the same code again.

    private void Page_Load(object sender, System.EventArgs e) {  if (IsPostBack == false)   {   string sql =   "select * from superheroes";   MySqlConnection conn = new MySqlConnection(ConnectionString);   conn.Open();   MySqlCommand cmd =   new MySqlCommand(sql,conn);   IDataReader reader =   cmd.ExecuteReader();   lstHeroes.DataSource = reader;   lstHeroes.DataTextField = "Name";   lstHeroes.DataBind();   ViewState["index"]=0;   SelectRecords();   }  } 

   This code makes a connection to the csharpvqs database and reads the records in the superheroes table. Then it binds the heroes drop-down box to the result of the query so that the drop-down box lists the names of the heroes in the Superheroes table. The tricky part of this code is the use of ViewState. The ViewState object lets us send hidden information to the client, information that we can then retrieve each time they interact with the server. Think of ViewState as the equivalent of a cookie, if you're familiar with Web browser terminology. The difference is that ViewState doesn't require the browser to have cookie support turned on.

   In this example we're using ViewState to persist an index. The reason we need that is that a superhero may have done several jobs, and each job can have its own ranking. When you select a superhero from the list, the feedback form lets you scroll through the jobs that the superhero has done and rank each one. The index number lets the application know what record is being displayed in the browser. Please note that ViewState information is only retained when we do a post back to the same page.

7. After the Page_Load function add the SelectRecords function in Figure 7.23 .

   Figure 7.23 The job of this function is to collect all records for jobs for the superhero selected. To fetch the records we use two classes: Dataset and DataAdapter . The DataAdapter reads the records from the database that match the criteria and saves the records it found in memory for our use in the Dataset. To find out more information about Datasets and DataAdapters consult the ADO.NET documentation in MSDN.

    void SelectRecords() {      string name =      lstHeroes.SelectedItem.Text;      string sql = string.Format(      "select * from herojobs where name='{0}'",name);      MySqlConnection conn = new      MySqlConnection(ConnectionString);      conn.Open();      MySqlCommand cmd = new      MySqlCommand(sql,conn);      MySqlDataAdapter da = new      MySqlDataAdapter();      da.SelectCommand = cmd;      da.Fill(ds);      conn.Close();      RefreshJob(); } 

   This code makes a connection to the database and runs a query that selects all the records in the herojobs table where the name of the hero is equal to the value selected in the drop-down listbox. This code also uses a class called Dataset. A full explanation of this class is way beyond the scope of this book; it's sufficient to know that the purpose of this class is to keep a copy of the records that were found in memory.

8. After the SelectRecords function, add the RefreshJob function in Figure 7.24 .

   Figure 7.24 RefreshJob is a little function that refreshes the Job description textbox and the ranking textbox to display the currently selected record for a particular superhero.

    void RefreshJob() {    int index = (int)ViewState["index"];    System.Data.DataRow row =    ds.Tables[0].Rows[index];    txtJob.Text = (string)row["Job"];    txtRanking.Text =    (row["Ranking"] is DBNull) ? "0"    : row["Ranking"].ToString() ; } 

   This little function shows several situations in which casting is necessary. The purpose of this function is to display the description of the current job for the superhero and the current ranking. As I mentioned earlier, a superhero may have done a number of jobs. We keep track of the current job with the index variable that we store in ViewState. We retrieve the index from ViewState and it tells us what row in the Dataset the client is currently looking at.

9. Return to the visual representation of the feedback form (right click on the file and choose View Designer from the pop-up menu). Then double-click on the hero selection drop-down listbox. This will cause the wizard to add a SelectedIndexChanged function to your code. Add the code in Figure 7.25 inside this function.

   Figure 7.25 This code triggers whenever the user selects a different superhero from the list of superheroes. The job of the code is to call SelectRecords again, which fetches records from the herojobs table for the selected hero.

    private void lstHeroes_SelectedIndexChanged(object sender,                       System.EventArgs e) {  ViewState["index"]=0;   SelectRecords();  } 

   This function is triggered whenever the user selects a different name from the hero listbox. The listbox control has its AutoPostback property set to true. This means that each time the user selects a name from the listbox, the client requests a new view of the page from the server. The server code runs the SelectRecords function, which reads the jobs that the selected superhero has done and resets the index value back to the first record.

10. Return to the visual designer and double-click on the < button. Repeat the process for the > button. This will cause the designer to add the skeleton for the btnNext_Click function and for the btnPrevious_Click function. Add the code in Figure 7.26 .

    Figure 7.26 The purpose of this code is to let the user navigate through the jobs that a superhero has done and view the description and the ranking. This code implements the next and previous button. The basic operation is to add one to the index or subtract one from the index and refresh the description and ranking textboxes.

     private void btnNext_Click(object sender,                           System.EventArgs e) {  SelectRecords();   int index = (int)ViewState["index"];   if (index == ds.Tables[0].Rows.Count-1)   index = 0;   else   index ++;   ViewState["index"] = index;   RefreshJob();  } private void btnPrevious_Click(object sender,                               System.EventArgs e) {  SelectRecords();   int index = (int)ViewState["index"];   if (index == 0)   index = ds.Tables[0].Rows.Count-1;   else   index --;   ViewState["index"] = index;   RefreshJob();  } 

    The purpose of this code is to add one to the index when the > button is clicked, and to subtract one from the index when the < is clicked. Then the code calls RefreshJob , which refreshes the job textbox and the ranking textbox with the information for the current record.

11. Return to the visual designer and double-click the Update button. The wizard will add a skeleton for the btnUpdate_Click function. Add the code in Figure 7.27 to this function.

    Figure 7.27 This code updates the ranking in the database for a particular job. It runs what is called an update query. The update query takes as input the value the user entered in the ranking textbox, then uses the name of the superhero and the job the client is viewing to select the appropriate record and update it.

    [View full width]

     private void btnUpdate_Click(object sender,                       System.EventArgs e) {  string name = lstHeroes.SelectedItem.Text;   string job = txtJob.Text;   string ranking = txtRanking.Text;   string sql = string.Format(@"update herojobs set ranking = {0} where name='{1}' and  job = '{2}'",   ranking,   name,   job);   MySqlConnection conn = new   MySqlConnection(ConnectionString);   conn.Open();   MySqlCommand cmd = new   MySqlCommand(sql,conn);   cmd.ExecuteNonQuery();   conn.Close();  } 

    The purpose of this code is to update the ranking for the particular job. All the code does is run an update query on the database that changes the ranking to whatever is in the ranking textbox.

Tips

• Figure 7.24 has several type conversions of interest. The first one is casting the value stored in ViewState to an int . This is because everything in ViewState is returned as the generic data type object . Your job is to then cast to the appropriate type. The same thing happens when reading data from a row in the Dataset. Reading the job field in the line that reads (string)row["Job"] returns a generic object that you need to cast to a string in order to set the text for the job textbox.

• Figure 7.24 also shows an example of using the is operator for a type comparison. The last line uses it when comparing to the type DBNull . The reason we do this comparison is that sometimes a field in the database may not have a value. It's not zero, or an empty string, it's simply null ; a value has not been saved yet. In these cases when you read the data, the .NET database layer actually creates an instance of the class DBNull and returns this object to you. You then have to see if the object returned from the database is of type DBNull before you attempt to use it.