Creating and Manipulating DataSets

Before you can begin working with DataSets, you must understand how to retrieve data from the database and load it into the DataSet. To do this you must learn how the DataAdapter is used. The DataAdapter handles the job of retrieving data from the database and filling the DataSet. The DataAdapter is also responsible for sending updates back to the database when the client has made changes to the data in the DataSet. The DataSet object is a disconnected local data store that can be used by client applications to work with data locally, or easily pass data from one component to another. Data stored in the DataSet is further broken down into DataTable and DataRow objects, which you will also look at in this chapter. The DataAdapter and DataSet objects must be used together. The DataAdapter has the necessary information to connect to a specific database and run a query to retrieve data. The DataAdapter Fill method then loads that data into a DataSet. The DataSet can be much more complex than the RecordSet object that you might be familiar with from previous versions of ADO. The DataSet can hold data from multiple sources, can manage client updates, and has many other features.

Like the SqlConnection or OleDbConnection objects that were discussed in the previous chapter, the SqlDataAdapter and OleDbDataAdapter objects are responsible for connecting to a specific database, so the DataAdapter is implemented in each data-provider-specific namespace: System.Data.SqlClient and System.Data.OleDb.

The SqlDataAdapter object and the OleDbDataAdapter object are responsible for connecting to the database and retrieving the data that will be stored in the DataSet. They are also responsible for submitting updates back to the database when the local client is finished making changes to the data inside a DataSet.

Note 

Aside from the SqlDataAdapter and OleDbDataAdapter, all the other objects discussed in this chapter belong to the System.Data namespace itself. Because they are not specific to a particular provider, we do not need to qualify their names with a reference to the data provider. For simplicity, in the rest of the chapter we refer to the DataAdapter class generically, unless we are providing a specific code sample.

Working with the DataSet requires the use of many cooperating classes. In the following sections, you will see how these classes are used together to perform common tasks, such as retrieving data from the database and submitting updates to the database.

Using DataAdapter Objects

The DataAdapter object is used to fill a DataSet. It is responsible for connecting to the database and retrieving information via its SelectCommand property. Then the DataAdapter can also send updates back to the database via its InsertCommand, UpdateCommand, and DeleteCommand properties. These properties can also be set to reference an existing Command object.

Similarly, the DataAdapter can be associated with an existing Connection object or can use a connection string that is passed to its constructor method. If you are not using an explicit Connection object that you created in your code, then the DataAdapter creates and uses an implicit Connection object (with the connection string you supply). The DataAdapter can also implicitly open and close an existing connection, or it can detect that the referenced Connection object is already open and can make use of it.

Table 6.1 lists all properties and methods that apply to both the SqlDataAdapter and OleDbDataAdapter classes.

Table 6.1: Important Properties and Methods of the SqlDataAdapter and OleDbDataAdapter Classes

Property	Description
SelectCommand	Defines the SQL statement or stored procedure used to retrieve records from the data source.
DeleteCommand	Defines the SQL statement or stored procedure used to delete records from the data source.
InsertCommand	Defines the SQL statement or stored procedure used to insert new records into the data source.
UpdateCommand	Defines the SQL statement or stored procedure used to update records in the data source.
AcceptChangesDuringFill	Indicates whether AcceptChanges is called on a DataRow after it is added to the DataTable.
ContinueUpdateOnError	Specifies whether to generate an exception, or skip the row in error and continue with the rest of the updates. The default is False.
TableMappings	Provides access to a collection that provides the master mapping between a source table and a DataTable.
MissingMappingAction	Specifies the action to take when incoming data does not have a matching table or column in the DataSet mappings collection. The default action is to create the table or column, but you can choose to ignore the data or force an exception.
MissingSchemaAction	Specifies the action to take when existing DataSet schema does not match incoming data. The default action is to add the new information to the schema. You can also choose to add the columns with primary key information, ignore the extra columns, or force an exception.
Method	Description
Fill	Adds, or refreshes (when the AddWithKey property is True), rows in the DataSet to match those in the data source.
FillSchema	Adds a DataTable to a DataSet and configures the schema to match that in the data source.
GetFillParameters	Provides access to the parameters set by the user when executing a SQL SELECT statement.
Update	Calls the appropriate INSERT, UPDATE, or DELETE statement for each row in the DataSet that was changed by the user.

The most important properties of the DataAdapter are those that control how data is retrieved and updated. The SelectCommand, DeleteCommand, InsertCommand, and UpdateCommand properties can be set to string values, which are the SQL statements that define what data is retrieved by the DataAdapter and how changes are submitted back to the database.

The most common DataAdapter methods are Fill and Update. The Fill method will connect to the database and execute the SQL statement (or Command object) associated with the DataAdapter’s SelectCommand property, loading the records that are returned to a specified DataSet. After the DataSet is filled, the connection to the database is closed and your code can work with the data locally.

When you call the Update method, a new connection to the database is opened and each row in the DataSet that has been added, changed, or deleted by the client application is automatically submitted back to the database by using the appropriate DeleteCommand, InsertCommand,or UpdateCommand SQL instruction.

Listing 6.1 shows how to set up a simple DataAdapter to fill a DataSet. We are using the Fill method with two parameters. The first parameter is a reference to the DataSet object, and the second parameter assigns a name for the DataTable that will be created to hold the results of this operation. A DataSet object can consist of multiple DataTable objects, each receiving their data from a different DataAdapter instruction.

Listing 6.1: Using a DataAdapter to Fill a DataSet

Public Sub GetData()    Dim connectString As String    Dim sqlSelect As String    connectString = "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI; "    sqlSelect = "SELECT pub_id, pub_name, city, state, " & _        "country FROM publishers"    Dim pubAdapter As SqlDataAdapter = New _       SqlDataAdapter(sqlSelect, connectString)    Dim pubSet As DataSet = New DataSet()    pubAdapter.Fill(pubSet, "Publishers")    'continue working with the DataSet End Sub

We will continue working with the DataAdapter and show you how to use its other properties and methods later in this chapter, in the section titled “Using DataSets to Manage Updates to Databases.” But first we are going to discuss the structure, properties, and methods of the DataSet object.

Working with the DataSet’s Constituent Objects

A DataSet is a complex, in-memory store for data that can mimic many of the features of the database engine itself. The DataSet object can be used as a simple container for holding data, perhaps for passing information between components, but it has many additional capabilities. The DataSet itself is made up of many other types of objects. As you saw in Listing 6.1 , even a simple DataSet will contain a DataTable object.

The default behavior of the DataSet class is to create and configure the objects necessary to perform its work even if the user does not explicitly specify all the details. For example, in Listing 6.1 the parameters passed to the Fill method indicated that we wanted to assign the name of Publishers to the DataTable. If we had not specified this, the DataTable would still be created and we could access it through the DataSet.Tables collection, as shown by this code snippet:

DataGrid1.DataSource = pubSet.Tables(0)

When you want to use the DataSet to perform more complex tasks, or to generate the entire data structure at runtime from your code, you can work directly with the constituent objects to control exactly how they will operate. Table 6.2 lists the main classes that make up the internal structure of a DataSet.

Table 6.2: Classes in System.Data Namespace That Make Up the Internal Structure of the DataSet

Class	Description
DataTable	A DataSet is made up of one or more DataTables.
DataTableCollection	The DataSet.Tables collection provides access to the DataTable objects.
DataColumn	A DataTable is made up of one or more DataColumns. DataColumn properties describe characteristics of the column such as name, data type, and size. DataColumns do not provide access to data values.
DataColumnCollection	The DataTable.Columns collection provides access to the DataColumn objects.
DataRow	Each DataTable is made up of one or more DataRows.
DataRowCollection	The DataTable.Rows collection provides access to the DataRow object. By accessing the Item collection of a DataRow, you can read or change data values.
Constraint	Constraints are applied to an individual DataColumn, including the derived types ForeignKeyConstraint and UniqueConstraint.
ConstraintCollection	The DataTable.Constraints collection provides access to the Constraint objects.
DataRelation	A DataRelation is created by specifying the parent/child relationship between a DataColumn that contains the primary key in one DataTable and a DataColumn with the matching ForeignKey in the related table.
DataRelationCollection	The DataSet.Relations collection provides access to the Relation objects.
DataView	The DataView creates a custom view of the data in a table by applying sort, filter, or search criteria.

Table 6.3 lists the properties and methods of the DataSet class. Some of these properties and methods also apply to constituent objects (such as DataTables and DataRows) so they can be applied at different levels of scope. In the examples and exercises that follow, you will see the most common of these properties and methods demonstrated.

Table 6.3: Selected Properties and Methods of the DataSet Class

Property	Description
CaseSensitive	Indicates whether string comparisons within DataTable objects are case-sensitive.
DataSetName	The name of the current DataSet.
DefaultViewManager	Allows filtering, searching, and navigating by using a custom DataViewManager.
EnforceConstraints	Indicates whether constraint rules are followed when attempting any update operation. A ConstraintException is generated if an update would violate a constraint.
ExtendedProperties	Retrieves the collection of custom user information.
HasErrors	Indicates whether there are errors in any of the rows in any of the tables of this DataSet.
Locale	Sets or retrieves the locale information used to compare strings within the table.
Namespace	The namespace of the DataSet.
Prefix	An XML prefix that aliases the namespace of the DataSet.
Relations	Retrieves the collection of relations that link tables and allow navigation from parent tables to child tables.
Tables	Retrieves the collection of tables contained in the DataSet.
Method	Description
AcceptChanges	Commits all the changes made to this DataSet since it was loaded or since the last time AcceptChanges was called.
Clear	Clears the DataSet of any data by removing all rows in all tables.
Clone	Copies the structure of the DataSet, including all DataTable schemas, relations, and constraints. Does not copy any data.
Copy	Copies both the structure and data for this DataSet.
GetChanges	Gets only the rows of the DataSet that have changed since the DataSet was last loaded or since AcceptChanges was called.
GetXml	Gets the XML representation of the data stored in the DataSet.
GetXmlSchema	Gets the XSD schema for the XML representation of the data stored in the DataSet.
HasChanges	Indicates whether the DataSet has changes, including new, deleted, or modified rows.
InferXmlSchema	Infers the XML schema from the specified TextReader or file into the DataSet.
Merge	Merges this DataSet with a specified DataSet.
ReadXml	Reads XML schema and data into the DataSet.
ReadXmlSchema	Reads an XML schema into the DataSet.
RejectChanges	Rolls back all the changes made to the DataSet since it was created, or since the last time AcceptChanges was called.
Reset	Resets the DataSet to its original state.
WriteXml	Writes XML data, and optionally the schema, from the DataSet.
WriteXmlSchema	Writes the DataSet structure as an XML schema.

Table 6.4 lists properties and methods that can be used with the individual DataTable objects that make up a DataSet.

Table 6.4: Selected Properties and Methods of the DataTable Class

Property	Description
CaseSensitive	Indicates whether string comparisons within the table are case-sensitive.
ChildRelations	Retrieves the collection of child relations for this DataTable.
Columns	Retrieves the collection of columns that belong to this DataTable.
Constraints	Retrieves the collection of constraints maintained by this DataTable.
DataSet	Retrieves the DataSet that this DataTable belongs to.
DefaultView	Retrieves a customized view of the DataTable, which might include a filtered view, or a cursor position.
DisplayExpression	The expression that will return a value used to represent this DataTable in the user interface.
ExtendedProperties	Retrieves the collection of customized user information.
HasErrors	Retrieves a value indicating whether there are errors in any of the rows in any of the tables of the DataSet to which the DataTable belongs.
Locale	The locale information used to compare strings within the table.
MinimumCapacity	The initial starting size for this table.
Namespace	The namespace for the XML representation of the data stored in the DataTable.
ParentRelations	Retrieves the collection of parent relations for this DataTable.
Prefix	The namespace for the XML representation of the data stored in the DataTable.
PrimaryKey	An array of columns that function as primary keys for the DataTable.
Rows	Retrieves the collection of rows that belong to this DataTable.
TableName	The name of the DataTable.
Method	Description
AcceptChanges	Commits all the changes made to this table since it was created or since the last time AcceptChanges was called.
BeginInit	Begins the initialization of a DataTable that is used on a form or used by another component. The initialization occurs at runtime.
BeginLoadData	Turns off notifications, index maintenance, and constraints while loading data.
Clear	Clears the DataTable of all data.
Clone	Clones the structure of the DataTable, including all DataTable schemas and constraints.
Compute	Computes the given expression on the current rows that pass the filter criteria.
Copy	Copies both the structure and data for this DataTable.
EndInit	Ends the initialization of a DataTable that is used on a form or used by another component. The initialization occurs at runtime.
EndLoadData	Turns on notifications, index maintenance, and constraints after loading data.
GetChanges	Creates a copy of the DataTable containing all changes made to it since it was loaded or since AcceptChanges was called.
GetErrors	Creates an array of DataRow objects that contain errors.
ImportRow	Copies a DataRow into a DataTable, preserving any property settings, as well as original and current values.
LoadDataRow	Finds and updates a specific row. If no matching row is found, a new row is created by using the given values.
NewRow	Creates a new DataRow with the same schema as the table.
RejectChanges	Rolls back all changes that have been made to the table since it was loaded or since the last time AcceptChanges was called.
Reset	Resets the DataTable to its original state.
Select	Retrieves an array of DataRow objects.

It is often useful to work at the level of the DataRow object. By working at this level, you can retrieve and change the column data values for a specific DataRow object and can add new information to a DataSet object programmatically. Table 6.5 lists the properties and methods that can be used with a DataRow object.

Table 6.5: Selected Properties and Methods of the DataRow Class

Property	Description
HasErrors	Retrieves a value indicating whether errors exist in a row
Item	Reads or writes the data stored in a specified column
ItemArray	Reads or writes all of the values for this row through an array
RowError	Reads or writes the custom error description for a row
RowState	Retrieves the current state of the row in regard to its relationship to the DataRowCollection
Table	Retrieves the DataTable for which this row has a schema
Public Method	Description
AcceptChanges	Commits all the changes made to this row since it was created or since the last time AcceptChanges was called
BeginEdit	Begins an edit operation on a DataRow object
CancelEdit	Cancels the current edit on the row
ClearErrors	Clears the errors for the row, including the RowError and errors set with SetColumnError
Delete	Deletes the DataRow
EndEdit	Ends the edit occurring on the row
GetChildRows	Retrieves the child rows of a DataRow
GetColumnError	Retrieves the error description for a column
GetColumnsInError	Retrieves an array of columns that have errors
GetParentRow	Retrieves the parent row of a DataRow
GetParentRows	Retrieves the parent rows of a DataRow
HasVersion	Indicates whether a specified version exists
IsNull	Indicates whether the specified column contains a null value
RejectChanges	Rejects all changes made to the row since AcceptChanges was last called
SetColumnError	Sets the error description for a column
SetParentRow	Sets the parent row of a DataRow