Working with SQL Parameters

C# offers you the capability to gain more control over the SQL you use in a data adapter by supporting SQL parameters, which are much like variables that you can use in SQL statements. You can assign values to these parameters at runtime without having to alter the SQL in a data adapter.

NAVIGATING THROUGH DATASETS IN WEB APPLICATIONS

Before leaving the topic of data navigation, it's important to note that, unlike Windows forms, there is no BindingContext property in Web forms, which means that there is no Position subproperty to use. If you want to navigate through the records in a dataset in a Web form, you must do it yourself. One good trick is to use data views for this purpose. For example, this code shows how you can move to the next record in the authors table using a data view bound to various controls in a Web form:

 int position = this.ViewState["position"]; if(position < dataSet11.Tables[0].Rows.Count - 1){   position++;   this.ViewState["position"] = position; } string id = dataSet11.Tables[0].Rows[position].Item["au_id"]; dataView1.RowFilter = "au_id = '" + id + "'"; TextBox1.DataBind(); Label1.DataBind(); DataGrid1.DataBind(); 

Here's an example, the ch09_06 example in the code for this book, which lets the users select a state in a combo box, and then click a Display Authors button to display all the authors in the authors table from that state, as you see in Figure 9.28. This example works with a SQL parameter, which is assigned the state the user selects in the combo box.

Figure 9.28. The Ch09_06 example.

In this example, a SQL parameter named @Param1 will hold the state the user has selected. Here's the SQL used in the SQL data adapter:

 SELECT au_id, au_lname, state FROM authors WHERE (state = @Param1) 

Take a look at the state = @Param1 part here. This statement creates a SQL parameter named @Param1 that you can assign values to at runtime, as we'll do here. For example, if you were to assign this parameter the value CA , the SQL in the data adapter would become:

 SELECT au_id, au_lname, state FROM authors WHERE (state = 'CA') 

Now when you use this data adapter to fill a dataset (in particular, the dataset the other controls in the ch09_06 example are bound to), all the authors in the authors table from California will be displayed.

In the code, we start by listing the available states in the combo box. To do that, create a new SqlDataAdapter object, sqlDataAdapter1 , using this SQL and the SQL DISTINCT keyword to select every unique state from the authors table (meaning no state will appear more than once):

 SELECT DISTINCT state FROM authors 

To display the available states, generate a dataset object from sqlDataAdapter1 , dataSet11 , bind that dataset to the combo box's DataSource property, and bind the state field in dataSet11 to the combo box's DisplayMember property. So far, we've made all unique states in the data in dataSet11 appear in the combo box.

The next step is to create a data adapter with parameterized SQL, allowing you to set the state at runtime, and to bind a dataset using this data adapter to the remaining controls in this example, which displays an author's first and last names , as well as navigation buttons . To do this, create a new data adapter, sqlDataAdapter2 , and select the au_id , au_lname , au_fname , and state fields from the authors table. To parameterize the state field, add the text "= @Param1" to the Criteria entry for that field in the Data Adapter Configuration Wizard's Query Builder, as you see in Figure 9.29.

Figure 9.29. Creating a SQL parameter in the Query Builder.

As you can see in Figure 9.29, this generates the following parameterized SQL:

 SELECT   au_lname,   au_fname,   au_id,   state FROM   authors WHERE   (state = @Param1) 

When you've finished creating sqlDataAdapter1 , choose Data, Generate Dataset, click the New radio button in the Generate Dataset dialog box that appears, and click OK to create a new dataset, dataSet21 . Then bind the au_fname and au_lname fields from dataSet21 to the two text boxes you see in Figure 9.28 to display the authors' first and last names.

We're almost done. We still have to set the SQL parameter in code when the user clicks the Display Authors button. In this case, we'll be working with the sqlDataAdapter2 object's SELECT command, which retrieves data from a data source, and setting its SQL parameter @Param1 , which we can access as sqlDataAdapter2.SelectCommand.Parameters["@Param1"].Value , to the state the user selected in the combo box. Then we use sqlDataAdapter2 to fill the dataset bound to the text boxes and navigation buttons:

 private void button1_Click(object sender, System.EventArgs e) {  sqlDataAdapter2.SelectCommand.Parameters["@Param1"].Value = comboBox1.Text;   sqlDataAdapter2.Fill(dataSet21);   label2.Text = "Record " +   (((this.BindingContext[dataSet21, "authors"].Position + 1).ToString() +   " of ") + this.BindingContext[dataSet21, "authors"].Count.ToString());  } 

Finally, add the code for the navigation controls you see in Figure 9.28 to let the users navigate through the authors from a particular state. This code is identical to the navigation buttons' code in the previous example, ch09_05, except that here we navigate through dataSet21 , not dataSet11 . And you're donewhen the user selects a state from the combo box in this example, ch09_06, and then clicks the Display Authors button, all the authors from that state are retrieved. The user can move through the authors using the navigation buttons you see in Figure 9.28.