ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 6) - Disconnected Data Access

When you use disconnected data access, you keep a copy of your data in memory using the DataSet. You connect to the database just long enough to fetch your data and dump it into the DataSet, and then you disconnect immediately.

There are a variety of good reasons to use the DataSet to hold onto data in memory. Here are a few:

• You need to do something time-consuming with the data. By dumping it into a DataSet first, you ensure that the database connection is kept open for as little time as possible.

• You want to use ASP.NET data binding to fill a web control (like a GridView) with your data. Although you can use the DataReader, it won't work in all scenarios. The DataSet approach is more straightforward.

• You want to navigate backward and forward through your data while you're processing it. This isn't possible with the DataReader, which goes in one direction only—forward.

• You want to navigate from one table to another. Using the DataSet, you can store several tables of information. You can even define relationships that allow you to browse through them more efficiently.

• You want to save the data to a file for later use. The DataSet includes two methods—WriteXml() and ReadXml()—that allow you to dump the content to a file and convert it back to a live database object later.

• You need a convenient package to send data from one component to another. A DataReader wouldn't work in this scenario, because the database component would need to leave the database connection open, which is a dangerous design.

• You want to store some data so it can be used for future requests.

UPDATING DISCONNECTED DATA The DataSet tracks the changes you make to the records inside. This allows you to use the DataSet to update records. The basic principle is simple. You fill a DataSet in the normal way, modify one or more records, and then apply your update using a DataAdapter. However, ADO.NET's disconnected update feature makes far more sense in a desktop application than in a web application. Desktop applications run for a long time, so they can efficiently store a batch of changes and perform them all at once. But in a web application, you need to commit your changes the moment they happen. Furthermore, the point at which you retrieve the data (when a page is first requested) and the point at which it's changed (during a postback) are different, which makes it very difficult to use the same DataSet object, and maintain the change tracking information for the whole process. For these reasons, the great majority of ASP.NET web applications use the DataSet to store data but not to make updates. Instead, they use direct commands to commit changes.

6.1. Selecting Disconnected Data

With disconnected data access, a copy of the data is retained in memory while your code is running. Figure 15 shows a model of the DataSet.

Figure 15. The DataSet family of objects

You fill the DataSet in much the same way that you connect a DataReader. However, although the DataReader holds a live connection, information in the DataSet is always disconnected.

The following example shows how you could rewrite the FillAuthorList() method from the earlier example to use a DataSet instead of a DataReader. The changes are highlighted in bold.

Private Sub FillAuthorList()

    lstAuthor.Items.Clear()

    ' Define ADO.NET objects.
    Dim selectSQL As String
    selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors"
    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(selectSQL, con)
    Dim adapter As New SqlDataAdapter(cmd)
    Dim dsPubs As New DataSet()

    ' Try to open database and read information.

Try
        con.Open()

        ' All the information in transferred with one command.
        ' This command creates a new DataTable (named Authors)
        ' inside the DataSet.
        adapter.Fill(dsPubs, "Authors")

    Catch err As Exception
        lblResults.Text = "Error reading list of names. "
        lblResults.Text &= err.Message
    Finally
        con.Close()
    End Try

    For Each row As DataRow In dsPubs.Tables("Authors").Rows
        Dim newItem As New ListItem()
        newItem.Text = row("au_lname") & ", " & _
          row("au_fname")
        newItem.Value = row("au_id").ToString()
        lstAuthor.Items.Add(newItem)
    Next
End Sub

The DataAdapter.Fill() method takes a DataSet and inserts one table of information. In this case, the table is named Authors, but any name could be used. That name is used later to access the appropriate table in the DataSet.

To access the individual DataRows, you can loop through the Rows collection of the appropriate table. Each piece of information is accessed using the field name, as it was with the DataReader.

6.2. Selecting Multiple Tables

A DataSet can contain as many tables as you need, and you can even add relationships between the tables to better emulate the underlying relational data source. Unfortunately, you have no way to connect tables together automatically based on relationships in the underlying data source. However, you can add relations with a few extra lines of code, as shown in the next example.

In the pubs database, authors are linked to titles using three tables. This arrangement (called a many-to-many relationship, shown in Figure 16) allows several authors to be related to one title and several titles to be related to one author. Without the intermediate TitleAuthor table, the database would be restricted to a one-to-many relationship, which would allow only a single author for each title.

Figure 16. A many-to-many relationship

In an application, you would rarely need to access these tables individually. Instead, you would need to combine information from them in some way . On its own, the Titles table indicates only the author ID. It doesn't provide additional information such as the author's name and address. To link this information together, you can use a special SQL Select statement called a Join query. Alternatively, you can use the features built into ADO.NET, as demonstrated in this section.

The next example provides a simple page that lists authors and the titles they have written. The interesting thing about this page is that it's generated using ADO.NET table linking.

To start, the standard ADO.NET data access objects are created, including a DataSet. All these steps are performed in a custom CreateList() method, which is called from the Page.Load event handler so that the output is created when the page is first generated:

' Define the ADO.NET objects.
Dim connectionString As String = _
  WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString
Dim con As New SqlConnection(connectionString)

Dim selectSQL As String = "SELECT au_lname, au_fname, au_id FROM Authors"
Dim cmd As New SqlCommand(selectSQL, con)
Dim adapter As New SqlDataAdapter(cmd)
Dim dsPubs As New DataSet()

Next, the information for all three tables is pulled from the database and placed in the DataSet. This task could be accomplished with three separate Command objects, but to make the code a little leaner, this example uses only one and modifies the CommandText property as needed.

Try
    con.Open()
    adapter.Fill(dsPubs, "Authors")

    ' This command is still linked to the data adapter.
    cmd.CommandText = "SELECT au_id, title_id FROM TitleAuthor"
    adapter.Fill(dsPubs, "TitleAuthor")

    ' This command is still linked to the data adapter.
    cmd.CommandText = "SELECT title_id, title FROM Titles"

adapter.Fill(dsPubs, "Titles")

Catch err As Exception
    lblList.Text = "Error reading list of names. "
    lblList.Text &= err.Message
Finally
    con.Close()
End Try

6.3. Defining Relationships

Now that all the information is in the DataSet, you can create two DataRelation objects to make it easier to navigate through the linked information. In this case, these DataRelation objects match the foreign key restrictions that are defined in the database.

NOTE

A foreign key is a constraint that you can set up in your database to link one table to another. For example, the TitleAuthor table is linked to the Titles and the Authors tables by two foreign keys. The title_id field in the TitleAuthor table has a foreign key that binds it to the title_id field in the Titles table. Similarly, the au_id field in the TitleAuthor table has a foreign key that binds it to the au_id field in the Authors table. Once these links are established, certain rules come into play. For example, you can't create a TitleAuthor record that specifies author or title records that don't exist.

To create a DataRelation, you need to specify the linked fields from two different tables, and you need to give your DataRelation a unique name. The order of the linked fields is important. The first field is the parent, and the second field is the child. (The idea here is that one parent can have many children, but each child can have only one parent. In other words, the parent-to-child relationship is another way of saying a one-to-many relationship.) In this example, each book title can have more than one entry in the TitleAuthor table. Each author can also have more than one entry in the TitleAuthor table:

Dim Titles_TitleAuthor As New DataRelation("Titles_TitleAuthor", _
  dsPubs.Tables("Titles").Columns("title_id"), _
  dsPubs.Tables("TitleAuthor").Columns("title_id"))

Dim Authors_TitleAuthor As New DataRelation("Authors_TitleAuthor", _
  dsPubs.Tables("Authors").Columns("au_id"), _
  dsPubs.Tables("TitleAuthor").Columns("au_id"))

Once you've create these DataRelation objects, you must add them to the DataSet:

dsPubs.Relations.Add(Titles_TitleAuthor)
dsPubs.Relations.Add(Authors_TitleAuthor)

The remaining code loops through the DataSet. However, unlike the previous example, which moved through one table, this example uses the DataRelation objects to branch to the other linked tables. It works like this:

1. Select the first record from the Author table.

2. Using the Authors_TitleAuthor relationship, find the child records that correspond to this author. To do so, you call the DataRow.GetChildRows() method, and pass in the DataRelationship object that models the relationship between the Authors and TitleAuthor table.

3. For each matching record in TitleAuthor, look up the corresponding Title record to get the full text title. To do so, you call the DataRow.GetParentRows() method and pass in the DataRelationship object that connects the TitleAuthor and Titles table.

4. Move to the next Author record, and repeat the process.

The code is lean and economical:

For Each rowAuthor As DataRow In dsPubs.Tables("Authors").Rows

    lblList.Text &= "<br /><b>" & rowAuthor("au_fname")
    lblList.Text &= " " & rowAuthor("au_lname") & "</b><br />"

    For Each rowTitleAuthor As DataRow In _
     rowAuthor.GetChildRows(Authors_TitleAuthor)

        Dim rowTitle As DataRow
        rowTitle = rowTitleAuthor.GetParentRows(Titles_TitleAuthor)(0)

        lblList.Text &= "&nbsp;&nbsp;"
        lblList.Text &= rowTitle("title") & "<br />"
    Next
Next

Figure 17 shows the final result.

Figure 17. Hierarchical information from two tables

If authors and titles have a simple one-to-many relationship, you could use simpler code, as follows:

For Each rowAuthor As DataRow In dsPubs.Tables("Authors").Rows
    ' Display author.

    For Each rowTitle As DataRow In rowAuthor.GetChildRows(Authors_Titles)
        ' Display title.
    Next
Next

But having seen the more complicated example, you're ready to create and manage multiple DataRelation objects on your own.