Caching Data with the DataSet

Caching Data with the DataSet

The DataSet is the fundamental framework class for manipulating data with the .NET Compact Framework. The DataSet can be thought of as a tiny relational database engine in itself. It holds its tables in memory organized as tables, rows, and columns and allows developers to perform standard database operations, such as adding and removing data, sorting, and checking constraints.

Developers who fully understand how to work effectively with the DataSet on the .NET Compact Framework will write effective ADO.NET applications on the framework.

Working with the .NET Compact Framework is more difficult than working with the desktop .NET Framework because many overloads are missing on the .NET Compact Framework and it is easy to do things that bring performance to a crawl. There are more details on performance strategies in Chapter 15, "Measuring the Performance of a .NET Compact Framework Application."

The overall strategy for handling data in ADO.NET programming is to fill up a DataSet from a large database, work with the data held in the DataSet , and then write back any changes to the database. This chapter discusses how to fill the DataSet by inserting the data programmatically and to perform simple manipulations on the data.

Looking Inside the DataSet : DataTable s, DataRows , and DataColumns

The DataSet is a container for one or more DataTable s. Each DataTable corresponds to a table in a relational database. It has a set of DataRow s, and each DataRow has a set of DataColumn s that actually hold the data. To make DataSet s, DataTable s, and DataColumn s easier to work with, they can have names . Figure 6.1 diagrams the overall structure of how a DataSet stores the data for a simple phonebook.

Figure 6.1. This DataSet represents the structure of a phone book.

You can use a DataTable alone to store the data associated with a single table, but the DataSet provides methods and properties that add utility and truly make it a miniature in-memory relational database. For example, with the DataSet you can do all of the following:

• Manipulate the information inside a DataSet as a small relational database. For example, you can set up parent-child relationships, cascading updates and deletes, and create data columns that are computed from other data fields.

• Save or load the contents of all of the DataTable s to an XML file with just one line of code (see Chapter 8, "XML and the DataSet").

• Pass it to the SQL CE engine, which will fill it with tables from a relational database stored on the device or with replicated data from a remote server (see Chapter 7, "Programming with Microsoft SQL Server CE").

• Pass it to the SQL provider to be filled with tables from a remote server (see Chapter 7).

• Receive populated DataSet s, which are the return values of Web services, or pass DataSet s back to a Web service (see Chapter 9, "Using XML Web Services").

This chapter focuses mainly on understanding how the DataSet fits into the big picture and learning the basics of how to manipulate the data it holds.

Inserting Data into a DataSet

To insert new data into a DataSet , we follow these steps:

1. Get a handle to the DataTable within the DataSet you want to add the new row of data in. If necessary, create a new DataTable . The collection of DataTable s that the DataSet manages is accessible through the DataSet.Tables property. If you must create a new DataTable and add it to the DataSet.Tables collection, follow these substeps (usually, you will be adding data to an already existing table, so you can skip these substeps ):

   Create a DataTable via the DataTable constructor.

   Create new DataColumn s and add them to the DataTable.Columns collection. For each new DataColumn , you must specify the name of the column and its data type.

   Add the new DataTable to the DataSet.Tables collection by calling its .Add method.

2. Create a new DataRow by asking the DataTable to create it for you. For example, to create a new row for the first DataTable in a DataSet , use l_newRow = l_DataSet. Tables[0].NewRow . You can also specify the table by name, for example l_newRow = l_DataSet.Tables["Customers"].NewRow .

3. The new DataRow is automatically created with column entries that match the table that created it. Insert values into the columns of the DataRow .

4. Add the new DataRow to the Rows collection of the DataTable that created it, for example l_DataSet.Tables[0].Rows.Add(l_newRow); .

5. After you have added all of the rows you want, call DataSet.AcceptChanges to commit to the changes. To undo all of the new data entries that you have made, call DataSet.RejectChanges .

SOME DEVELOPERS CAN SKIP THIS SECTION

If you are an experienced ADO.NET developer, you can skip the following subsections, which cover inserting data into a DataSet and extracting it.

SPECIFYING TABLES BY NAME

Specifying the table by name is a more expensive operation and can hurt performance in large applications running on the .NET Compact Framework. Use numeric indexing whenever possible.

Building a DataSet to Hold a Phone Book

To demonstrate how to create a DataSet capable of holding relational data, we show some sample code taken directly out of the PhoneBook sample application. This sample application, which is explained in greater detail in the next section, sets up a DataSet capable of holding a simple phone book. The DataSet holds a single DataTable , which is set up with two DataColumn s, one to hold a name and the other for the phone number. Listing 6.1 demonstrates each of the five steps needed to add data to a DataSet , including creating a brand-new table.

Listing 6.1 Creating and populating a DataSet

 C# DataSet    l_DataSet = new DataSet(); // Create a DataTable that holds a "Name" and a "PhoneNumber" DataTable  l_newTable = new DataTable("Phone Contacts");         l_newTable.Columns.Add(new DataColumn("Name",         typeof(System.String))); l_newTable.Columns.Add(new DataColumn("PhoneNumber",         typeof(System.String))); // Add the DataTable to the DataSet's table collection l_DataSet.Tables.Add(l_newTable); // Now put a few names in... // GEORGE WASHINGTON DataRow l_newRow = l_DataSet.Tables[0].NewRow(); l_newRow[0] = "George Washington"; l_newRow[1] = "555 340-1776"; l_DataSet.Tables[0].Rows.Add(l_newRow); // BEN FRANKLIN l_newRow = l_DataSet.Tables[0].NewRow(); l_newRow["Name"] = "Ben Franklin"; l_newRow["PhoneNumber"] = "555 336-3211"; l_DataSet.Tables[0].Rows.Add(l_newRow); // Commit the changes l_DataSet.AcceptChanges(); VB ' Create a DataTable that holds a "Name" and a "PhoneNumber" Dim l_newTable As New DataTable("Phone Contacts") l_newTable.Columns.Add(New DataColumn("Name",         System.Type.GetType("System.String")))         l_newTable.Columns.Add(New DataColumn("PhoneNumber",         System.Type.GetType("System.String"))) ' Add the DataTable to the DataSet's table collection l_DataSet.Tables.Add(l_newTable) ' Now put a few names in... ' GEORGE WASHINGTON Dim l_newRow As DataRow l_newRow = l_DataSet.Tables(0).NewRow() l_newRow(0) = "George Washington" l_newRow(1) = "555 340-1776" l_DataSet.Tables(0).Rows.Add(l_newRow) ' BEN FRANKLIN l_newRow = l_DataSet.Tables(0).NewRow() l_newRow("Name") = "Ben Franklin" l_newRow("PhoneNumber") = "555 336-3211" l_DataSet.Tables(0).Rows.Add(l_newRow) ' ALEXANDER HAMILTON l_newRow = l_DataSet.Tables(0).NewRow() l_newRow(0) = "Alexander Hamilton" l_newRow(1) = "555 756-3211" l_DataSet.Tables(0).Rows.Add(l_newRow) ' Commit the changes l_DataSet.AcceptChanges() 

Extracting Data from a DataSet

Extracting data from a DataSet is as simple as accessing the desired DataTable in the DataSet.Tables collection and looking at the desired rows in the table. Each row has an indexer to make it easy to access the desired columns. Remember that the indexers are zero-based , as in these examples:

l_DataSet.Tables[0].Rows[0][0] Access the first column in the first row of the first DataTable .

l_DataSet.Tables[0].Rows[0][9] Access the tenth column in the first row of the first DataTable .

l_DataSet.Tables[0].Rows[29][9] Access the tenth column in the thirtieth row of the first DataTable .

Extracting PhoneBook Data from a DataSet

The following code sample is also taken directly out of the PhoneBook sample application. It loops through all of the rows in the first DataTable in a DataSet and paints the 0th and 1st DataColumn values into a ListBox:

 C# for (int i = 0; i < phonebookEntriesDataSet.Tables[0].Rows.Count; i++) {    this.listBox1.Items.Add(         phonebookEntriesDataSet.Tables[0].Rows[i][0] + "  " +         phonebookEntriesDataSet.Tables[0].Rows[i][1]); } VB Dim i As Integer For i = 0 To phonebookEntriesDataSet.Tables(0).Rows.Count - 1    Me.listBox1.Items.Add(phonebookEntriesDataSet.Tables(0)         .Rows(i)(0) + "  " +         phonebookEntriesDataSet.Tables(0).Rows(i)(1)) Next i 

Altering Data in a DataSet

To alter data held inside the DataSet , access the DataColumn that you wish to alter and set it to a new value. When all of the alterations are finished, call AcceptChanges to commit to the changes.

For example, the following code snippet sets the second column in the first row of the first table in the DataSet collection to a random number provided by l_randomGenerator , which is an instance of the Random class:

 C# // Column 1 is the phone number. //                               //                              V m_phonebookDS.Tables[0].Rows[0][1] = randomGenerator.Next().ToString(); VB ' Column 1 is the phone number. '                                '                               V m_phonebookDS.Tables(0).Rows(i)(1) = randomGenerator.Next().ToString() 

An alternative would be to index using names as follows , but this way is much slower on the .NET Compact Framework when large amounts of data are involved:

 C# m_phonebookDS.Tables["Phone Contacts"].Rows[0]["PhoneNumber"] =         l_randomGenerator.Next().ToString(); VB m_phonebookDS.Tables("Phone Contacts").Rows(i)("PhoneNumber") =         l_randomGenerator.Next().ToString() 

Designing a PhoneBook Application with a DataSet

PhoneBook is a very simple application that fills a DataSet with a series of entries from a phone book and paints the entries into a ListBox. This application uses some of the WinForms controls introduced in Chapter 3, "Designing GUI Applications with Windows Forms," while showing how to programmatically populate a DataSet and extract and alter the data inside. The source code is on the CD-ROM under the directory SampleApplications\Chapter6\PhoneBook_CSharp and PhoneBook_VB.

In this simple incarnation, the PhoneBook is not very useful, because all of the information held in the DataSet is placed there programmatically and there is no way to update the information. The section "Altering Data in a DataSet" describes how to update existing data.

Troubleshooting Common DataSet -Related Errors

The most common error users encounter occurs when they attempt to access or alter an object in a DataSet , such as a DataColumn or DataRow , that does not exist. For example, if a DataSet has only two tables, then this piece of code would be faulty because the DataSet.Tables collection uses 0-based indexing:

 C# m_phonebookDS.Tables[2].Rows[0][1] = l_randomGenerator.Next().ToString(); VB m_phonebookDS.Tables(2).Rows(0)(1) =         l_randomGenerator.Next().ToString(); 

In these situations the most common exception that developers will see is the IndexOutOfRangeException . A special pitfall with the .NET Compact Framework is that the strings describing exceptions are held in a stand-alone DLL assembly called System.SR.dll . If this DLL is not installed on the device, strange exceptions that beginner developers don't expect can show up when the runtime wants to throw a descriptive exception. If this .DLL assembly is installed on the device, it is stored in the device Global Assembly Cache (GAC) in the \Windows directory. For example, the file is typically named \GAC_System.SR_v1_0_5000_0_cneutral_1.dll .