Viewing Data with ADO.NET

Date: Feb 28, 2003

Sample Chapter is provided courtesy of Sams.

Return to the article

In this chapter you will

• Retrieve data using the DataReader object

• Retrieve results from SQL Server by using the DataTable object

• Locate records with the DataTable object

• Filter and sort records by using the DataView object

ActiveX Data Objects (ADO) was introduced a few years ago as a solution to accessing data that can be found in various forms, not only over a local area network (LAN), but over the Internet as well. ADO was the new generation of data access that replaced Remote Data Objects (RDO) and Data Access Objects (DAO), originally created for the JET database engine. JET was originally created for Microsoft Access, and was later used as a choice of databases for small- and medium-sized single- and two-tier database solutions. Then along came ADO.NET.

Differences Between ADO and ADO.NET

ADO and ADO.NET are different in several ways:

• ADO works with connected data. This means that when you access data, such as viewing and updating data, it is real-time, with a connection being used all the time. This is barring, of course, you programming special routines to pull all your data into temporary tables.

  ADO.NET uses data in a disconnected fashion. When you access data, ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates. This makes ADO.NET efficient to use for Web applications. It's also decent for desktop applications.

• ADO has one main object that is used to reference data, called the Recordset object. This object basically gives you a single table view of your data, although you can join tables to create a new set of records. With ADO.NET, you have various objects that allow you to access data in various ways. The DataSet object will actually allow you to store the relational model of your database. This allows you to pull up customers and their orders, accessing/updating the data in each related table individually.

• ADO allows you to create client-side cursors only, whereas ADO.NET gives you the choice of either using client-side or server-side cursors. In ADO.NET, classes actually handle the work of cursors. This allows the developer to decide which is best. For Internet development, this is crucial in creating efficient applications.

• Whereas ADO allows you to persist records in XML format, ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML.

Objects That Are Found in ADO.NET

As mentioned previously, the main object that is used with ADO.NET is the DataSet object. You can see the DataSet object and its properties, methods, and additional objects in Figure 3.1.

Figure 3.1 ADO.NET has several more objects than ADO does.

Take a look at Table 3.1 to see a brief description of some of the objects that you will be using during this How-To.

Table 3.1 ADO.NET Data Objects That Are Used to Manipulate Data

.NET also provides classes, called data providers, to work with ADO.NET objects to provide access to data. You can see some of those objects in Figure 3.2.

Figure 3.2 You can use either OleDb classes or SQLClient classes for better performance.

In Table 3.2, you can see a brief description of some of the objects that you will be using during this How-To.

Table 3.2 .NET Data Provider Classes That Are Used to Manipulate Data

You will get a chance to see all of the items listed in the previous two tables throughout the following How-Tos.

All of the examples in this chapter can be found in the Solution called VB.NET—Chapter 3 on the Web site.

3.1 Retrieve Data by Using the DataReader Object

In Chapter 1, you learned how to use bound controls to OleDb controls that could be included on the forms. Some developers prefer to use unbound controls to perform the same task. The DataReader object allows you to add items to a list box in a more efficient manner because it is only a read-and-forward-only type object. This How-To tells you how to generate a limited ListBox control by using a DataReader object.

You want to create a limited list of customers. You don't want to use bound controls because you are a cool VB developer who knows better than that. You heard that the DataReader object is a fast way to get data. How do you retrieve data using the DataReader object to perform this task?

Technique

For this How-To, you will be using the ListBox control and loading items into it by using the DataReader object. To get to the DataReader object, you need to look at the Command object.

The Command object in .NET works similarly to the ADO Command in that you will assign the stored procedure name or SQL statement to the CommandText property as well as the connection to use. One difference is that you will use the Open method of the Command object, and then the ExecuteReader method, to create the DataReader object.

You will then use the Read method off of the DataReader object to perform two tasks. The first task is that when used in a loop, you can test for datareader.Read() to check whether to terminate the loop and also to iterate through the rows that the DataReader object returns.

After you have the DataReader populated and you are iterating through the rows, you will load the data into the ListBox control. You will be using the Clear and Add methods, which were used in VB 6. In addition, you will use the BeginEdit and EndEdit methods, which speed up loading ListBox controls when you are loading a large amount of data.

To view this example in design view, open the form called frmHowTo3_1.vb in the chapter's solution.

Steps

Open and run the VB.NET—Chapter 3 solution. From the main form, click on the command button with the caption How-To 3.1. When the form loads, click on the Load List command button. You will see the list below fill with all the company names that start with A.

You will be creating a form similar to one that was created in Chapter 1. Instead of using bound controls, however, you will use code along with ADO.NET to populate the ListBox control.

1. Create a Windows Form. Then place a Label, TextBox, ListBox, and Command button on the form with the properties that are listed in Table 3.3 set.

Table 3.3 Label, TextBox, ListBox, and Command Button Control Property Settings

Notice that the list box does not have the DataBindings properties set. That is because you will be using the ListBox control unbound. Look at Figure 3.3 to see what the form should look like.

Figure 3.3 Arrange the controls on the form that you created to look like this form.

2. Before creating the code that will be attached to the Click event of the btnLoadList command button, you need to devise a support routine to create the connection string. Called BuildCnnStr, the function can been seen in Listing 3.1. This function takes a server and database names that are passed to it and creates a connection string.

Listing 3.1 modGeneralRoutines.vb: Creating a Connection String

Function BuildCnnStr(ByVal strServer As String, _
      ByVal strDatabase As String) As String

    Dim strTemp As String
    strTemp = "Provider=SQLOleDB; Data Source=" & strServer & ";"
    strTemp &= "Initial Catalog=" & strDatabase & ";"
    strTemp &= "Integrated Security=SSPI"

    Return strTemp
  End Function

Although you could create a routine that would pass back a Connection object, a more versatile method is to pass back a string. The reason for this is that for some objects, you are asked for a Connection object, whereas in other objects, you just need a string. You will see BuildCnnStr called in the next step.

3. On the btnLoadList Command button, add the following code from Listing 3.2 to the Click event. In this routine, a SQL string is created and stored in the strSQL string, taking Text property of the txtCustLimit text box and adding it to a literal. Then, within a Try-Catch-End-Try block, a new instance of an OleDbCommand object called ocmdCust is created. The routine then follows the steps that are discussed in the Technique section.

Listing 3.2 frmHowTo3_1.vb: Loading a List Box By Using the DataReader Object

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

    Dim ocmdCust As OleDb.OleDbCommand
    Dim odrCust As OleDb.OleDbDataReader
    Dim strSQL As String

    '-- Create the SQL String
    strSQL = "Select CompanyName From Customers Where CustomerID Like '" & 
          Me.txtCustLimit.Text & "%'"

    '-- Set up the exception catch
    Try

      '-- Create an instance of the command
      ocmdCust = New OleDb.OleDbCommand()

      With ocmdCust
        '-- Set up the connection of the command and the command text
        .Connection = _
          New OleDb.OleDbConnection(BuildCnnStr("(local)", "Northwind"))
        .Connection.Open()
        .CommandText = strSQL

        '-- Set up the data reader instance 
        odrCust = .ExecuteReader(CommandBehavior.SequentialAccess)

      End With

      '-- Add the items to the list box.
      With lstCustomers
        .Items.Clear()
        .BeginUpdate()
        Do While odrCust.Read
          .Items.Add(odrCust.Item("CompanyName"))
        Loop
        .EndUpdate()
      End With

    Catch oexpData As OleDb.OleDbException
      MsgBox(oexpData.Message)
    End Try

  End Sub

    .Connection = _
        New OleDb.OleDbConnection(BuildCnnStr("(local)",
"Northwind"))

How It Works

When the user clicks the btnLoadList button, the Command object is assigned the necessary properties, the connection is opened, and the ExecuteReader method is called.

After the list has been cleared, the DataReader is iterated through, and the ListBox control is loaded.

Comments

The DataReader object is one of the most efficient ways to get data from the server and load lists into your application. Other options besides CommandBehavior.SequentialAccess are available that make the DataReader convenient to use. Most notable is CommandBehavior.SchemaOnly, which returns information only about the columns, and no data.

You can use the Command object in a number of ways besides what was mentioned in this How-To. You will see additional examples of using the Command object with stored procedures to perform batch actions later in the chapter.

You have seen how to use the ListBox control in a total unbound technique. In the next How-To, you will see a blend of using the ListBox control in a semibound technique, where you will bind the data at runtime.

3.2 Retrieve Results from SQL Server by Using the DataTable Object

The data reader is great when you just want to load data into a ListBox or ComboBox control manually, but you can save some coding by binding the ListBox control to a data table at runtime, as well as providing the ability to get not only the displayed value, but the key column as well. This How-To demonstrates how to bind a limited ListBox control to a data table.

Although getting the quick information is great, you need to be able to refer back to the table of information, and you don't want to have to open another connection to get there. You know that the DataTable object should allow you to perform this task. How do you get results from SQL Server by using the DataTable object?

Technique

Using the Windows Forms controls that were introduced in How-To 3.1, you will use a familiar object from Chapter 1 called the DataAdapter object. This time, instead of using the OleDbDataAdapter data control, you will use the OleDbDataAdapter class from the System.Data.OleDb Namespace.

Using a similar technique that was used when filling a DataSet, you will instantiate the data adapter by assigning the SQL string and connection object. Then, instead of filling a DataSet object, you will fill a DataTable object. Because you will only be dealing with a table's worth of data, you just need to use a data table. That way, you will be able to perform lookups more conveniently, as shown in the next How-To.

For now, the next step will be to assign the following properties of the list box:

• DataSource. This will be set to the DataTable object—in this case, dtCust.

• DisplayMember. This specifies which column from the data table to use for display in the list box.

• ValueMember. Here, you will specify which column you want to use for the value that is retrieved when an item is selected from the list box.

By programming the ListBox control using this technique, you can access the ValueMember column in the SelectItem property of the list box.

Steps

Open and run the VB.NET—Chapter 3 solution. From the main form, click on the command button with the caption How-To 3.2. When the form loads, click on the Load List command button. You will see the list below fill with all the company names that start with A.

1. To save time, you can make a copy of the form that was created in the first How-To in this chapter.

2. Replace the btnLoadList Click event with the following code listed here in Listing 3.3. That's it. After creating the SQL string that will be used and storing it in strSQL, the data adapter called odaCust is created. The odtCust data table is then filled using odaCust. Last, the DataSource, DisplayMember, and ValueMember properties are set for the lstCustomers list box. This was all accomplished with a Try-Catch-End-Try block of code.

Listing 3.3 frmHowTo3_2.vb: Loading a List Box By Using the DataTable Object

Private Sub btnLoadList_Click(ByVal sender As System.Object, 
          ByVal e As System.EventArgs) Handles btnLoadList.Click

    Dim odaCust As OleDb.OleDbDataAdapter
    Dim dtCust As DataTable = New DataTable()

    Dim strSQL As String

    '-- Create the SQL String
    strSQL = "Select CustomerID, CompanyName From Customers " & _
"Where CustomerID Like '" &
          Me.txtCustLimit.Text & "%'"


    '-- Set up the exception catch
    Try

      '-- Create an instance of the data adapter, 
      ' and then fill the data table
      odaCust = New OleDb.OleDbDataAdapter(strSQL, _
BuildCnnStr("(local)", "Northwind")) 
      odaCust.Fill(dtCust)

      '-- Bind the data to the list box
      lstCustomers.DataSource = dtCust
      lstCustomers.DisplayMember = "CompanyName"
      lstCustomers.ValueMember = "CustomerID"

    Catch oexpData As OleDb.OleDbException
      MsgBox(oexpData.Message)
    End Try

  End Sub

How It Works

When the user clicks on the btnLoadList button, the data adapter called odaCust is instantiated. The data adapter is passed strSQL and the connection string that is created by the function called BuildCnnStr, which was introduced in the first How-To in this chapter. The data table is then filled, and then the DataSource, DisplayMember, and ValueMember properties of the ListBox control are assigned.

Comments

Using the data table sets up the scene for using the list box in retrieving data in the next How-To. Remember: By using the DataTable object, you can assign both the display value and the data item to be tracked.

3.3 Locate Records with the DataTable Object

Using the DataTable object, you can use another object called the DataRow object that allows you to locate a specific row in the data table. This is useful when you want to present your users with a search mechanism for your form. This How-To shows you how to locate a specific row within your data table and how to use the same data table for two different purposes.

After you have your DataTable object loaded in memory, you want to be able to locate specific records within the DataTable object. How do you locate records in the DataTable object?

Technique

For this How-To, you are going to use a ComboBox control instead of a ListBox control. You will use the same technique for loading the combo box as you would the list box. The change comes when you select an item from the combo box.

When an item is selected in the combo box, the SelectedIndexChanged event is fired off. Within this event, you will take the combo box's SelectedItem, which gives the ValueMember that is located in the selected row, and use that with the Find method off the DataTables Rows collection.

With the data row located, the corresponding columns are loaded into text boxes on the form, as shown in Figure 3.4.

Figure 3.4 This combo box will point the user to a specific customer.

Steps

Open and run the VB.NET—Chapter 3 solution. From the main form, click on the command button with the caption How-To 3.3. When the form loads, pick a new customer from the list that is presented in the customer ComboBox control. You will see the text boxes below the ComboBox control display new data that corresponds to the chosen customer.

1. Create a new Windows Form.

2. Add some labels, combo boxes, and text boxes, as listed in Table 3.4.

Table 3.4 Label, TextBox, and ComboBox Control Property Settings

You will also want to make sure that the Text properties in the TextBox controls are blank.

3. In the class module for the form, add the following two Private declarations just below the line of code that reads Windows Form Designer generated code.

   Private modaCust As OleDb.OleDbDataAdapter
   Private mdtCust As DataTable = New DataTable()

   These lines of code declare a data adapter and a data table that will be used throughout the form.

4. Add the code shown in Listing 3.4 to the Load event of the form. Almost identical to the code in the last How-To to load a ListBox control, this code sets modaCust to a SQL String and the connection string to be used. mdtCust is then filled using the Fill method of modaCust. Next, the first element in the DataColumn array called dc is set to the CustomerID column. mdtCustPrimaryKey is then set to the DataColumn array. Last, the DataSource, DisplayMember, and ValueMember properties are set.

Listing 3.4 frmHowTo3_3.vb: Loading a ComboBox by Using the DataTable Object

Private Sub frmHowTo3_3_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load

    Dim strSQL As String
    Dim dc(1) As DataColumn

    '-- Set up the exception catch
    Try

      '-- Create the data adapter and fill the data table
      modaCust = New _
         OleDb.OleDbDataAdapter("Select * From Customers", _
(BuildCnnStr("(local)", "Northwind")))
      modaCust.Fill(mdtCust)

      '-- Set up the primary key for the data table
      dc(0) = mdtCust.Columns("CustomerID")
      mdtCust.PrimaryKey = dc

      '-- Bind the data to the combo box
      cboCustomers.DataSource = mdtCust
      cboCustomers.DisplayMember = "CompanyName"
      cboCustomers.ValueMember = "CustomerID"


    Catch oexpData As OleDb.OleDbException
      MsgBox(oexpData.Message)
    End Try

  End Sub

The PrimaryKey property that was set will be used in the code for the next step by the Find method of mdtCust's Rows collection.

5. This last bit of code needs to be added to the SelectedIndexChanged event of the cboCustomers ComboBox control. As with the last step, when a data column was set up for the PrimaryKey property, in this step an array is specified to pass the SelectedItem value to find the Find method. The text boxes' Text properties are then set to the column values by using the ToString method.

Listing 3.5 frmHowTo3_3.vb: Locating a Record in the Data Table, and Then Assigning Values to Text Boxes

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

    Dim drCurr As DataRow
    Dim aFindValue(0) As Object

    '-- Load the item to look up, and use the find method
    aFindValue(0) = cboCustomers.SelectedItem(0)
    drCurr = mdtCust.Rows.Find(aFindValue)

    '-- Load up the fields on the form
    txtCustomerID.Text = drCurr("CustomerID").ToString
    txtCompanyName.Text = drCurr("CompanyName").ToString
    txtAddress.Text = drCurr("Address").ToString
    txtCity.Text = drCurr("City").ToString

  End Sub

How It Works

When a user picks a customer from the cboCustomer ComboBox control, the code then locates the desired value within the mdtCust data Table using the Find method off the rows collection. Text boxes are then loaded from the row that is retrieved.

Comments

Locating records within a data table and data row is pretty easy when you're using the methods that are supplied. ADO.NET provides the control you need, not only at the overall hierarchical level, but also at the row and column levels.

3.4 Filter and Sort Records Using the DataView Object

After your data is loaded into the data table, you will probably want to be able to view your data using different filters and sort orders. To do this, you can use the DataView object. This How-To goes into detail and shows you how to take advantage of the DataView control to manipulate your data.

Although you can put data into the DataGrid control and let the users sort data using the columns, you want to display a ComboBox control and let users pick a field from the drop-drown list. How can you filter and sort records using the DataView object to present your data in different ways?

Technique

This How-To displays a set of command buttons that display a letter and an extra command button that displays all records. A data adapter, data table, and data view are declared at the form level. The data adapter is created and the DataTable is filled when the form is loaded with all customers. Using a DataColumn object, a combo box is filled by getting the names of each column that is in the data table. You can see this form in action in Figure 3.5.

Figure 3.5 Selecting a letter here limits the data displayed in the DataGrid control.

Using the command buttons, a routine is called that creates a DataView object, sets the RowFilter property, and then assigns the data view to the DataSource property of a DataGrid control.

Table 3.5 Label, TextBox, and ComboBox Control Property Settings

The Sort property of the DataView object is used when a column name is chosen from the ComboBox. The current setting of the Sort property is compared to the column name that is chosen. If the Name matches, then the expression DESC is added to the value that is assigned to the Sort property.

Steps

Open and run the VB.NET—Chapter 3 solution. From the main form, click on the command button with the caption How-To 3.4. When the form loads, click on different letters that are displayed. You will see the data grid display different customers based on their first letter.

If you choose a column name from the Column to Sort On ComboBox control, the data grid will then be sorted based on the column chosen.

1. Create a new Windows Form.

2. Add a GroupBox control with the text property set to Click on a Letter.

3. Now you will be creating buttons that you will place within the GroupBox control you just created. The buttons will have their property set as listed in Table 3.6.

Table 3.6 Buttons Property Settings

4. Add the DataGrid, the Label, and the ComboBox controls shown in Listing 3.6.

Table 3.7 DataGrid, Label, and ComboBox Controls Property Settings

5. In the class module for the form, add the following three Private declarations just below the line of code that reads Windows Form Designer generated code. These three objects will be used throughout the form.

   Private modaCust As OleDb.OleDbDataAdapter
   Private mdtCust As DataTable = New DataTable()
   Private mdvCust As DataView = New DataView()

6. Add the following code to the Load event of the form as shown in Listing 3.6. This code starts out by setting up the modaCust data adapter to grab all the customers to fill the data table called mdtCust. Note that at this point, the data grid has not been filled.

   The next task is to load cboSortColumns with the column headings by iterating through each of the data columns in mdtCust and adding them to the Items collection in cboSortColumns. Last, the SetDataViewFilter routine is called. This routine is discussed in step 8.

Listing 3.6 frmHowTo3_4.vb: Loading the Data Table to Be Used in the Form, and Adding Column Names to a ComboBox Control

Private Sub frmHowTo3_4_Load(ByVal sender As Object, _
          ByVal e As System.EventArgs) Handles MyBase.Load

    Dim strSQL As String
    Dim dcCurr As DataColumn

    '-- Set up the exception catch
    Try

      '-- Create the data adapter and fill the data table
      modaCust = New _
         OleDb.OleDbDataAdapter("Select * From Customers", _
         (BuildCnnStr("(local)", "Northwind")))
      modaCust.Fill(mdtCust)

      '-- Load the column names into the sort ComboBox control
      For Each dcCurr In mdtCust.Columns
        Me.cboSortColumns.Items.Add(dcCurr.ColumnName)
      Next

    SetDataViewFilter("B")

    Catch oexpData As OleDb.OleDbException
      MsgBox(oexpData.Message)
    End Try

  End Sub

7. For each of the command buttons that has a single letter, add the first subroutine displayed here in Listing 3.7 to each of its Click events. For the btnAll Button control, add the second subroutine to the Click event. Each Button control will pass the letter that it represents to the subroutine called SetDataViewFilter, discussed in the next step. The btnAll code simply passes the empty string.

Listing 3.7 frmHowTo3_4.vb: Click Events for Each of the Button Controls

Private Sub btnA_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnA.Click
    SetDataViewFilter("A")
End Sub
Private Sub btnAll_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnAll.Click
    SetDataViewFilter("")
End Sub

8. Add the subroutine that is found in Listing 3.8 to the class module of the form. This routine takes the letter value that is passed in strFilterLetter as a parameter. The first task to perform is assigning the DefaultView of the mdtCust DataTable object to the mdvCust data view. Next, the RowFilter property of mdvCust is set to compare the CompanyName column with the Like expression using the strFilterLetter and the % (wildcard). Note that if "" is passed to strFilterLetter, all the records will be listed. Finally, mdvCust is set as the DataSource for dgCustomers, which is the DataGrid control.

Listing 3.8 frmHowTo3_4.vb: Setting the RowFilter Property for a DataView Object

Sub SetDataViewFilter(ByVal strFilterLetter As String)

    mdvCust = mdtCust.DefaultView
    mdvCust.RowFilter = "CompanyName Like '" & strFilterLetter & "%'"
    dgCustomers.DataSource = mdvCust

  End Sub

9. Add the piece of code that is shown in Listing 3.9 to the SelectdIndexChanged event of the cboSortColumns ComboBox control. This routine compares the current setting of mdvCust's Sort property to the current column name chosen in cboSortColumns. If the two are the same, then the column name is assigned to the Sort property with the DESC keyword added on. If not, then the name of the column is assigned to the Sort property.

Listing 3.9 frmHowTo3_4.vb: Specifying a Column on Which to Sort

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

    '-- Check to see if the column is currently the sorted field.
    '  If it is, sort on the column in descending order.
    '  Otherwise, set the sort to the name of column.

    If mdvCust.Sort = Me.cboSortColumns.Text Then
      mdvCust.Sort = Me.cboSortColumns.Text & " DESC"
    Else
      mdvCust.Sort = Me.cboSortColumns.Text
    End If

  End Sub

How It Works

When the user clicks on a letter, the data view is created, and the data grid reflects the new data. When a field is selected from the ComboBox control, the Sort property of the data view is set and the data grid automatically reflects the new sort order, also showing an arrow in the column heading. If the user chooses the field again, the column will sort in descending order.

Comments

Using the DataView object, you can keep track of multiple views of your data and display them for the users' use. You can also access all of the default views of the data tables in your data set using the DefaultViewManager.

© 2024 Pearson Education, Informit. All rights reserved.

800 East 96th Street, Indianapolis, Indiana 46240