InformIT: Sams Teach Yourself Visual Studio .NET 2003 in 21 Days > Using Windows Controls with DataReaders and Command Objects

Using Windows Controls with `DataReader`s and `Command` Objects

Now that you have a handle on the basics of the Connection, Command, and DataReader objects, you can start the Windows Forms project that you'll build for the rest of the day.

To begin, create a new C# or Visual Basic .NET Windows Forms application and call it DataAcess_vb or DataAccess_cs, depending on the language you want to use. I refer to the project as DataAccess.

Follow these steps to create the controls for the default Form1. Figure 10.3 gives you an example of where we're going with this form.

Figure 10.3 Form1 of the DataAccess project.

Follow these steps to create the GUI:

Drag a TabControl from the Toolbox to the form and set the following properties:
```
Dock: Fill
```
TabPages— Click the ellipses to bring up the TabPage Collection Editor. Click the Add button three times to add three pages. For the pages, set the Text property to the following:
```
Readers and Adapters

DataGrid Binding

Simple Data Entry
```
Click OK to close the TabPage Collection Editor.

Drag four CommandButtons to TabPage1 and set these properties:

Button1:

Text: Using a DataReader

Name: UseDataReader

Button2:

Text: Using a DataSet

Name: UseDataSet

Button3:

Text: Show Checked Items

Name: ShowCheckedItems

Button4:

Text: DataBind with DataSet

Name: DataBindWithDataSet

Drag a ListBox control from the Toolbox to TabPage1.
Drag a CheckedListBox control to TabPage1.
Drag a ComboBox control from the Toolbox to TabPage1.
Drag three Label controls from the Toolbox to TabPage1.

Refer to Figure 10.3 to see how to arrange everything. You add controls to the second and third tabs later today.

The goal of this project is to give you an understanding of how to get data from a database and, after you have the data, what you can do with it. To start, let's use what you learned earlier about DataReaders and write the code for the UsingDataReader click event. Listing 10.3 is similar to Listing 10.1; the only difference is that you're adding the data to the ListBox1 control as you call the Read method of the SqlDataReader class. Double-click on the Using a DataReader button and add the code from Listing 10.3.

Example 10.3. Populating a `ListBox` from a `DataReader`

Private Sub UseDataReader_Click(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles UseDataReader.Click

        ListBox1.Items.Clear()

        Dim cn As New SqlConnection( _
                            "Server=(local)\NetSDK;DataBase=pubs" _
                            & "Integrated Security=SSPI")

        ' Create a SqlDataReader object
        Dim dr As SqlDataReader

        ' Create a new SqlCommand object
        Dim cmd As New SqlCommand()

        ' Set the Select statement in the CommandText property and
        ' set the Connection property to the "cn" SqlConnection object
        ' you just created
        With cmd
            .CommandText = "Select au_lname, au_fname from Authors"
            .Connection = cn
        End With

        ' Open the Connection
        cn.Open()

        ' Call the ExecuteReader method of the Command object
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        ' Use this for concatenating the data from the database
        Dim strName As String

        ' Call the Read method of the DataReader to loop thru the records
        While dr.Read
            ' Add the items to the ListBox1 control
            strName = dr("au_lname") & ", " & dr("au_fname")
            ListBox1.Items.Add(strName)
        End While

    End Sub

    Private Sub UseDataSet_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles UseDataSet.Click

        ' Create a connection
        Dim cn As New SqlConnection( _
                   "Server=(local)\NetSDK;DataBase=pubs" _
                   & "Integrated Security=SSPI")

        ' Create a new SqlDataAdapter object.
        ' The overloaded constructor allows you to set the SQL Statement
        ' and the connection object or connection string at the time
        ' you create the SqlDataAdapter object
        Dim da As SqlDataAdapter = New SqlDataAdapter _
            ("SELECT au_id, au_lname + ', ' + au_fname As
           FullName FROM Authors", cn)

        ' Create a new DataSet to hold the data from the SqlDataAdapter
        Dim ds As DataSet = New DataSet("Authors")
        da.Fill(ds, "Authors")

        ' Create a DataRow object
        Dim dr As DataRow

        ' Loop thru the table rows and add the items
        ' in each dataset table row to the CheckedListBox
        For Each dr In ds.Tables("Authors").Rows
            CheckedListBox1.Items.Add(dr("FullName"))
        Next

    End Sub

private UseDataReader_Click(object sender,
         System.EventArgs e)
   {

      listBox1.Items.Clear();

      SqlConnection cn = new SqlConnection
       (@"Server=(local)\NetSDK;DataBase=pubs;Integrated Security=SSPI");

      // Create a SqlDataReader object
      SqlDataReader dr;

      // Create a new SqlCommand object
      SqlCommand cmd = new SqlCommand();

      // Set the Select statement in the CommandText property and
      // set the Connection property to the "cn" SqlConnection object
      // you just created

      cmd.CommandText = "Select au_lname, au_fname from Authors";
      cmd.Connection = cn;

      // Open the Connection
      cn.Open();

      // Call the ExecuteReader method of the Command object
      dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

      // Use this for concatenating the data from the database
      string strName;

      // Call the Read method of the DataReader to loop thru the records
      while (dr.Read())
                {
      // Add the items to the ListBox1 control
      strName = dr.GetString(0) + ", " + dr.GetString(1);
      listBox1.Items.Add(strName);
   }

Next, you write code for the SelectedIndexChanged event of the ListBox1 control. Because this is the default event for that type of control, you can double-click the control on the form and the code window takes you to the event. The code in Listing 10.4 is what must be added to the SelectedIndexChanged event for the ListBox1 control.

Example 10.4. `SelectedIndexChanged` Code for the `ListBox1` Control

Private Sub ListBox1_SelectedIndexChanged _
                (ByVal sender As System.Object, _
                ByVal e As System.EventArgs) _
                Handles ListBox1.SelectedIndexChanged

        ' Display where the event came from
        Label1.Text = "ListBox1_SelectedIndexChanged"

        ' Display the selected item
        Label2.Text = ListBox1.Items(ListBox1.SelectedIndex)

        ' Display the index
        Label3.Text = ListBox1.SelectedIndex
    End Sub

private void listBox1_SelectedIndexChanged(object sender,
        System.EventArgs e)
   {
      // Display where the event came from
      label1.Text = "ListBox1_SelectedIndexChanged";

      // Display the selected item
      label2.Text = listBox1.Text;

      // Display the index
      label3.Text =listBox1.SelectedIndex.ToString();
   }

Listing 10.4 retrieves the data from the ListBox. The Index changes each time the position changes in a ListBox, so you often need to know what the data actually represents. Listing 10.4 has nothing to do with data access; rather, it's an example of what you can do with data after you retrieve it.

To see what happens, press the F5 key to run the application. When Form1 pops up, click the Using a DataReader button. You'll see that the ListBox loads with the records from the database. As you click different items in the ListBox, the labels display the selected items in the ListBox control.

Your results should look like Figure 10.4.

Figure 10.4 Results from running the UsingDataReader code.

Using DataReaders is a common task that will make up most of your data access code. DataReaders offer an extremely fast and low-overhead method of getting data to and from a database, thereby increasing scalability and reducing server resources.

Using DataAdapters and DataSets | Next Section

Previous Section

Using Windows Controls with DataReaders and Command Objects

Example 10.3. Populating a ListBox from a DataReader

Example 10.4. SelectedIndexChanged Code for the ListBox1 Control

Using Windows Controls with `DataReader`s and `Command` Objects

Example 10.3. Populating a `ListBox` from a `DataReader`

Example 10.4. `SelectedIndexChanged` Code for the `ListBox1` Control