The DataSet Component

The DataSet 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.

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.

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

• 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 to load 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 a DataRelation are 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 ForeignKeyConstraints that disallow any changes that would invalidate the relationship. Relationships are also an important tool in maintaining valid data, not just a tool for seeing links.

ExtendedProperties

The DataSet (as well as the DataTable and DataColumn) has an ExtendedProperties property. ExtendedProperties is a PropertyCollection in which a user can place customized information, such as the SELECT statement that is 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 example adds an expiration property to a DataSet (code file: DataSetExtendedPropertiesExample):

    'Build the SQL and Connection strings.
    Dim cmdString As String = "SELECT * FROM [Sales].[SalesOrderHeader]"
    Dim connection As SqlConnection
    Dim command As SqlCommand

    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True; " +
                                   "Asynchronous Processing=true;")

    command = New SqlCommand(cmdString, connection)

    '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(cmdString, connection)
    Dim salesOrders As New DataSet
    adapter.Fill(salesOrders)

    'Add an extended property called "expiration."
    'Set its value to the current date/time + 1 hour.
    salesOrders.ExtendedProperties.Add("expiration", 
                                        DateAdd(DateInterval.Hour, 1, Now))
    MessageBox.Show(salesOrders.ExtendedProperties("expiration").ToString,
                    "Sales Orders Expiration")

This code begins by filling a DataSet with the Sales.SalesOrderHeader table. 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 DataTables in a DataSet.

There are several methods for working with a DataSet, and they 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:

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, it will eat up a lot of bandwidth (code file: DataSetLoadingExample):

    'Build the SQL and Connection strings.
    Dim cmdString As String = "SELECT * FROM [Sales].[SalesOrderHeader]"
    Dim connection As SqlConnection
    Dim command As SqlCommand

    connection = New SqlConnection("Data Source=.SQLEXPRESS;" +
                                   "Initial Catalog=AdventureWorks;" +
                                   "Integrated Security=True; " +
                                   "Asynchronous Processing=true;")

    command = New SqlCommand(cmdString, connection)

    '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(cmdString, connection)
    Dim salesOrders As New DataSet
    adapter.Fill(salesOrders)

    'Add an extended property called "expiration."
    mainGrid.DataSource = salesOrders.Tables(0)

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

Advanced ADO.NET Features of the DataSet and DataTable Objects

One of the main complaints developers had about 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 have been addressed since ADO.NET 2.0.

Indexing

The first improvement made since ADO.NET 2.0 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 if you first add unique keys and foreign keys to your DataTable. Even better, you don't 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. Using ADO.NET, 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 DataSetLoadingExample project of the Examples solution, a Button (serializeButton_Click) has been added to the Form and its associated Click event handler that demonstrates how to serialize a DataTable in binary format (DataSetLoadingExample):

    Dim table As DataTable = salesOrders.Tables(0)

    ' Save the table in a binary format
    Dim filename As String = Path.Combine(FileIO.SpecialDirectories.MyDocuments,
                                          "SalesOrders.dat")

    Using fs As New FileStream(filename, FileMode.Create)
      salesOrders.RemotingFormat = SerializationFormat.Binary
      Dim format As New BinaryFormatter
      format.Serialize(fs, table)
    End Using

    'Tell the user what happened
    MessageBox.Show(String.Format("Successfully serialized the DataTable to {0}",
                                  filename))

This code takes advantage of the Using statement for Visual Basic 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 capability 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, a Button (loadFromDataReaderButton) has been added to the project (DataSetLoadingExample):

    Dim reader As SqlDataReader
    reader = command.ExecuteReader()

    'Load the DataTable from the reader.
    Dim table As New DataTable("SalesOrderHeader")
    table.Load(reader)

    'Close the reader.
    reader.Close()

    'Load Grid
    mainGrid.DataSource = table

This method begins by first creating an instance of a DataTable and initializing it with the table name. 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, DataTable is loaded into the grid control.

DataTable Independence

One of the most convenient capabilities in ADO.NET is the inclusion of several methods from the DataSet class in the DataTable class. The DataTable is now much more versatile and useful than it was in the early ADO.NET days. 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.