Teach Yourself Visual Studio® .NET 2003 in 21 Days [Electronic resources]

Using Windows Controls with DataReaders 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.

Follow these steps to create the GUI:

1. 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.

2. 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
   

3. Drag a ListBox control from the Toolbox to TabPage1.

4. Drag a CheckedListBox control to TabPage1.

5. Drag a ComboBox control from the Toolbox to TabPage1.

6. Drag three Label controls from the Toolbox to TabPage1.

Refer 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.

Listing 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.

Listing 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.

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.