The DataSet Component

The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables; basically, it’s like having a small relational database residing in memory.

Tip 

Because the DataSet contains a lot of metadata, you need to be careful about how much data you try to stuff into it, as it will be consuming memory.

The methods and objects in a DataSet are consistent with those in the relational database model. The DataSet can also persist and reload its contents as XML, and its schema as XSD. It is completely disconnected from any database connections, so it is totally up to you to fill it with whatever data you need in memory.

ADO.NET 2.0 has added several new features to the DataSet and the DataTable classes, as well as enhancements to existing features. The new features covered in this section are as follows:

• The binary serialization format option

• Additions to make the DataTable more of a standalone object

• The capability to expose DataSet and DataTable data as a stream (DataReader), and loading stream data into a DataSet or DataTable

DataTableCollection

An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all of the DataTable objects in a DataSet.

A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by the DataColumnCollection, which defines the schema and rows of the table. It also contains a collection of rows represented by the DataRowCollection, which contains the data in the table. Along with the current state, a DataRow retains its original state and tracks changes that occur to the data.

DataRelationCollection

A DataSet contains relationships in its DataRelationCollection object. A relationship (represented by the DataRelation object) associates rows in one DataTable with rows in another DataTable. The relationships in the DataSet can have constraints, which are represented by UniqueConstraint and ForeignKeyConstraint objects. It is analogous to a JOIN path that might exist between the primary and foreign key columns in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.

Relationships enable you to see what links information within one table to another. The essential elements of aDataRelationare the name of the relationship, the two tables being related, and the related columns in each table. Relationships can be built with more than one column per table, with an array of DataColumn objects for the key columns. When a relationship is added to the DataRelationCollection, it may optionally add ForeignKeyConstraintsthat disallow any changes that would invalidate the relationship.

ExtendedProperties

DataSet (as well as DataTable and DataColumn) has an ExtendedProperties property. ExtendedProperties is a PropertyCollection where a user can place customized information, such as the SELECT statement that was used to generate the result set, or a date/time stamp indicating when the data was generated. Because the ExtendedProperties contains customized information, this is a good place to store extra user-defined data about the DataSet (or DataTable or DataColumn), such as a time when the data should be refreshed. The ExtendedProperties collection is persisted with the schema information for the DataSet (as well as DataTable and DataColumn). The following code is an example of adding an expiration property to a DataSet:

  Private Shared Sub DataSetExtended()     ' Build the SQL and Connection strings.     Dim sql As String = "SELECT * FROM authors"     Dim connectionString As String = "Initial Catalog=pubs;" _         & "Data Source=(local);Integrated Security=SSPI;"     ' Initialize the SqlDataAdapter with the SQL     ' and Connection strings, and then use the     ' SqlDataAdapter to fill the DataSet with data.     Dim adapter As SqlDataAdapter = _         New SqlDataAdapter(sql, connectionString)     Dim authors As New DataSet     adapter.Fill(authors)     ' Add an extended property called "expiration."     ' Set its value to the current date/time + 1 hour.     authors.ExtendedProperties.Add("expiration", _         DateAdd(DateInterval.Hour, 1, Now))     Console.Write(authors.ExtendedProperties("expiration").ToString)     Console.ReadLine() End Sub 

This code begins by filling a DataSet with the authors table from the pubs database. It then adds a new extended property, called expiration, and sets its value to the current date and time plus one hour. You then simply read it back. As you can see, it is very easy to add extended properties to DataSet objects. The same pattern also applies to DataTable and DataColumn objects.

Creating and Using DataSet Objects

The ADO.NET DataSet is a memory-resident representation of the data that provides a consistent relational programming model, regardless of the source of the data it contains. A DataSet represents a complete set of data, including the tables that contain, order, and constrain the data, as well as the relationships between the tables. The advantage to using a DataSet is that the data it contains can come from multiple sources, and it is fairly easy to get the data from multiple sources into the DataSet. In addition, you can define your own constraints between the data tables in a DataSet.

There are several methods of working with a DataSet, which can be applied independently or in combination:

• Programmatically create DataTables, DataRelations, and Constraints within the DataSet and populate them with data.

• Populate the DataSet or a DataTable from an existing RDBMS using a DataAdapter.

• Load and persist a DataSet or DataTable using XML.

• Load a DataSet from an XSD schema file.

• Load a DataSet or a DataTable from a DataReader.

Here is a typical usage scenario for a DataSet object:

1. A client makes a request to a Web Service.

2. Based on this request, the Web Service populates a DataSet from a database using a DataAdapter and returns the DataSet to the client.

3. The client then views the data and makes modifications.

4. When finished viewing and modifying the data, the client passes the modified DataSet back to the Web Service, which again uses a DataAdapter to reconcile the changes in the returned DataSet with the original data in the database.

5. The Web Service may then return a DataSet that reflects the current values in the database.

6. Optionally, the client can then use the DataSet class’s Merge method to merge the returned DataSet with the client’s existing copy of the DataSet; the Merge method will accept successful changes and mark with an error any changes that failed.

The design of the ADO.NET DataSet makes this scenario fairly easy to implement. Because the DataSet is stateless, it can be safely passed between the server and the client without tying up server resources such as database connections. Although the DataSet is transmitted as XML, Web Services and ADO.NET automatically transform the XML representation of the data to and from a DataSet, creating a rich, yet simplified, programming model.

In addition, because the DataSet is transmitted as an XML stream, non-ADO.NET clients can consume the same Web Service consumed by ADO.NET clients. Similarly, ADO.NET clients can interact easily with non-ADO.NET Web Services by sending any client DataSet to a Web Service as XML and by consuming any XML returned as a DataSet from the Web Service. However, note the size of the data; if your DataSet contains a large number of rows, then it will eat up a lot of bandwidth.

Programmatically Creating DataSet Objects

You can programmatically create a DataSet object to use as a data structure in your programs. This could be quite useful if you have complex data that needs to be passed around to another object’s method. For example, when creating a new customer, instead of passing 20 arguments about the new customer to a method, you could just pass the programmatically created DataSet object with all of the customer information to the object’s method.

Here is the code for building an ADO.NET DataSet object that is comprised of related tables:

  Private Sub BuildDataSet()     Dim customerOrders As New Data.DataSet("CustomerOrders")     Dim customers As Data.DataTable = customerOrders.Tables.Add("Customers")     Dim orders As Data.DataTable = customerOrders.Tables.Add("Orders")     Dim row As Data.DataRow     With customers         .Columns.Add("CustomerID", Type.GetType("System.Int32"))         .Columns.Add("FirstName", Type.GetType("System.String"))         .Columns.Add("LastName", Type.GetType("System.String"))         .Columns.Add("Phone", Type.GetType("System.String"))         .Columns.Add("Email", Type.GetType("System.String"))     End With     With orders         .Columns.Add("CustomerID", Type.GetType("System.Int32"))         .Columns.Add("OrderID", Type.GetType("System.Int32"))         .Columns.Add("OrderAmount", Type.GetType("System.Double"))         .Columns.Add("OrderDate", Type.GetType("System.DateTime"))     End With     customerOrders.Relations.Add("Customers_Orders", _     customerOrders.Tables("Customers").Columns("CustomerID"), _     customerOrders.Tables("Orders").Columns("CustomerID"))     row = customers.NewRow()     row("CustomerID") = 1     row("FirstName") = "Miriam"     row("LastName") = "McCarthy"     row("Phone") = "555-1212"     row("Email") = "[e-mail]"     customers.Rows.Add(row)     row = orders.NewRow()     row("CustomerID") = 1     row("OrderID") = 22     row("OrderAmount") = 0     row("OrderDate") = #11/10/1997#     orders.Rows.Add(row)     Console.WriteLine(customerOrders.GetXml())     Console.ReadLine() End Sub 

Here is what the resulting XML of the DataSet looks like:

  <CustomerOrders>   <Customers>     <CustomerID>1</CustomerID>     <FirstName>Miriam</FirstName>     <LastName>McCarthy</LastName>     <Phone>555-1212</Phone>     <Email>[e-mail]</Email> </Customers> <Orders>   <CustomerID>1</CustomerID>   <OrderID>22</OrderID>   <OrderAmount>0</OrderAmount>   <OrderDate>1997-11-10T00:00:00.0000</OrderDate> </Orders> </CustomerOrders> 

You begin by first defining a DataSet object (CustomerOrders) named CustomerOrders. You then create two tables: one for customers (customers) and one for orders (orders). Then you define the columns of the tables. Note that you call the Add method of the DataSet’s Tables collection. You then define the columns of each table and create a relation in the DataSet between the Customers table and the Orders table on the CustomerID column. Finally, you create instances of Rows for the tables, add the data, and then append the Rows to the Rows collection of the DataTable objects.

Tip 

If you create a DataSet object with no name, then it is given the default name of NewDataSet.

ADO.NET DataTable Objects

A DataSet is made up of a collection of tables, relationships, and constraints. In ADO.NET, DataTable objects are used to represent the tables in a DataSet. A DataTable represents one table of in-memory relational data. The data is local to the .NET application in which it resides, but can be populated from a data source such as SQL Server using a DataAdapter.

The DataTable class is a member of the System.Data namespace within the .NET Framework class library. You can create and use a DataTable independently or as a member of a DataSet, and DataTable objects can be used by other the .NET Framework objects, including the DataView. You access the collection of tables in a DataSet through the DataSet object’s Tables property.

The schema, or structure, of a table is represented by columns and constraints. You define the schema of a DataTable using DataColumn objects as well as ForeignKeyConstraint and UniqueConstraint objects. The columns in a table can map to columns in a data source, contain calculated values from expressions, automatically increment their values, or contain primary key values.

If you populate a DataTable from a database, it inherits the constraints from the database, so you don’t have to do all of that work manually. A DataTable must also have rows in which to contain and order the data. The DataRow class represents the actual data contained in the table. You use the DataRow and its properties and methods to retrieve, evaluate, and manipulate the data in a table. As you access and change the data within a row, the DataRow object maintains both its current and original state.

You can create parent-child relationships between tables within a database, such as SQL Server, using one or more related columns in the tables. You create a relationship between DataTable objects using a DataRelation, which can then be used to return a row’s related child or parent rows.

ADO.NET 2.0 Enhancements to DataSet and DataTable

One of the main complaints developers had in ADO.NET 1.x was related to the performance of the DataSet and its DataTable children - in particular, when they contained a large amount of data. The performance hit comes in two different ways. The first way is the time it takes to actually load a DataSet with a lot of data. As the number of rows in a DataTable increases, the time to load a new row increases almost proportionally to the number of rows. The second way is when the large DataSet is serialized and remoted. A key feature of the DataSet is the fact that it automatically knows how to serialize itself, especially when you want to pass it between application tiers. Unfortunately, the serialization is quite verbose and takes up a lot of memory and network bandwidth. Both of these performance problems are addressed in ADO.NET 2.0.

Indexing

The first improvement to the DataSet family was a complete rewrite of the indexing engine for the DataTable, which now scales much better for large datasets. The addition of the new indexing engine results in faster basic inserts, updates, and deletes, which also means faster Fill and Merge operations. Just as in relational database design, if you are dealing with large DataSets, then it pays big dividends now if you add unique keys and foreign keys to your DataTable. Best of all is that you do not have to change any of your code at all to take advantage of this new feature.

Serialization

The second improvement made to the DataSet family was adding new options to the way the DataSet and DataTable are serialized. The main complaint about retrieving DataSet objects from Web Services and remoting calls was that they were way too verbose and took up too much network bandwidth. In ADO.NET 1.x, the DataSet serializes as XML, even when using the binary formatter. In ADO.NET 2.0, in addition to this behavior, you can also specify true binary serialization, by setting the newly added RemotingFormat property to SerializationFormat.Binary, rather than (the default) SerializationFormat.XML. In the AdoNetFeaturesTest project of the Examples solution I have added a Button (serializationButton) to the form and its associated Click event handler that demonstrates how to serialize a DataTable in binary format:

 Private Sub _serializationButton_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles _serializationButton.Click     ' Get the authors reader.     Dim reader As SqlDataReader = GetAuthorsSqlReader()      ' Load a DataTable from the reader     Dim table As DataTable = GetTableFromReader(reader, "Authors")

This code begins by calling the helper methods GetAuthorsSqlReader and GetTableFromReader to get a DataTable of the authors from the pubs database. The next code block, shown here, is where you are actually serializing the DataTable out to a binary format:

  Using fs As New FileStream("c:\authors.dat", FileMode.Create)     table.RemotingFormat = SerializationFormat.Binary         Dim format As New BinaryFormatter         format.Serialize(fs, table)     End Using     ' Tell the user what happened.     MsgBox("Successfully serialized the DataTable!") End Sub 

This code takes advantage of the newly added Using statement for VB.NET to wrap up creating and disposing of a FileStream instance that will hold your serialized DataTable data. The next step is to set the DataTable’s RemotingFormat property to the SerializationFormat.Binary enumeration value. Once that is done, you simply create a new BinaryFormatter instance, and then call its Serialize method to serialize your DataTable into the FileStream instance. You then finish by showing users a message box indicating that the data has been serialized.

DataReader Integration

Another nice feature of the DataSet and DataTable classes is the ability to both read from and write out to a stream of data in the form of a DataReader. You will first take a look at how you can load a DataTable from a DataReader. To demonstrate this, I have added a Button (loadFromReaderButton) and its associated Click event handler to TestForm.vb of the AdoNetFeaturesTest project in the Examples solution:

  Private Sub _loadFromReaderButton_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles _loadFromReaderButton.Click     ' Get the authors reader.     Dim reader As SqlDataReader = GetAuthorsSqlReader()     ' Load a DataTable from the reader.     Dim table As DataTable = GetTableFromReader(reader, "Authors")     ' Bind the grid to the table.     BindGrid(table)     End Sub 

This method is a controller method, meaning that it only calls helper methods. It begins by first obtaining a SqlDataReader from the GetAuthorsReader helper method. It then calls the GetTableFromReader helper method to transform the DataReader into a DataTable. The GetTableFromReader method is where you actually get to see the DataTable’s new load functionality:

  Private Function GetTableFromReader(ByVal reader As SqlDataReader, _     ByVal tableName As String) As DataTable     ' Create a new DataTable using the name passed in.     Dim table As New DataTable(tableName)     ' Load the DataTable from the reader.     table.Load(reader)     ' Close the reader.     reader.Close()     Return table End Function 

This method begins by first creating an instance of a DataTable and initializing it with the name passed in from the tableName argument. Once the new DataTable has been initialized, you call the new Load method and pass in the SqlDataReader that was passed into the method via the reader argument. This is where the DataTable takes the DataReader and populates the DataTable instance with the column names and data from the DataReader. The next step is to close the DataReader, as it is no longer needed; and finally, you return the newly populated DataTable.

DataTable Independence

One of the most convenient enhancements to ADO.NET 2.0 is the addition of several methods from the DataSet class to the DataTable class. The DataTable is now much more versatile and useful than it was in ADO.NET 1.x. The DataTable now supports all of the same read and write methods for XML as the DataSet - specifically, the ReadXml, ReadXmlSchema, WriteXml, and WriteXmlSchema methods.

The Merge method of the DataSet has now been added to the DataTable as well, and in addition to the existing functionality of the DataSet class, some of the new features of the DataSet class have been added to the DataTable class - namely, the RemotingFormat property, the Load method, and the GetDataReader method.