Programming Microsoft® Visual Basic® .NET (Core Reference)

Author Francesco Balena (Wintellect) Pages 1616 Disk 1 Companion CD(s) Level All Levels Published 04/17/2002 ISBN 9780735613751 Price $59.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Index Companion Content Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 21: ADO.NET in Disconnected Mode

• The DataSet Object
• Exploring the DataSet Object Model
• Building a DataSet
• The DataAdapter Class
• Introducing the DataAdapter
• Reading Data from a Database
• Updating the Database
• Resolving Update Conflicts
• Advanced Techniques

21   ADO.NET in Disconnected Mode

You see how ADO.NET differs from its predecessor when you start working in disconnected mode. ADO 2.x permits you to work in disconnected mode using client-side static recordsets opened in optimistic batch update mode. This was one of the great new features of ADO that has proved to be a winner in client/server applications of any size. As a matter of fact, working in disconnected mode is the most scalable technique you can adopt because it takes resources on the client (instead of on the server) and, above all, it doesn't enforce any locks on database tables (except for the short-lived locks that are created during the update operation).

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

The DataSet Object

Even with all its great features, however, the DataSet isn't always the best answer to all database programming problems. For example, the DataSet object is great for traditional client/server applications—for example, a Windows Forms application that queries a database on a networked server—but is almost always a bad choice in ASP.NET applications and, more generally, in all stateless environments. An ASP.NET page lives only a short lifetime, just for the time necessary to reply to a browser's request, so it rarely makes sense to use a DataSet to read data from a database, then send the data to the user through HTML, and destroy the DataSet immediately afterward. (Yes, you might save the DataSet in a Session variable, but this technique takes memory on the server and might create server affinity, two problems that impede scalability, as I explain in the "State Management and Caching" section of Chapter 24.)

Exploring the DataSet Object Model

An important feature of the DataSet class is its ability to define relationships between its DataTable objects, much like what you do in a real database. For example, you can create a relationship between the Publishers and the Titles DataTable objects by using the PubId DataColumn that they have in common. After you define a DataRelation object, you can navigate from one table to another, using the DataTable's ChildRelations and ParentRelations properties.

A DataSet object consists of one or more DataTable objects, each one containing data coming from a database query, an XML stream, or code added programmatically. Table 21-1 summarizes the most important members of the DataSet class.

Click to view graphic

Figure 21-1.The DataSet object hierarchy.

Table 21-1 Main Properties, Methods, and Events of the DataSet Class

Category	Name	Description
Properties	DataSetName	The name of this DataSet object.
	Namespace	The namespace for this DataSet, used when importing or exporting XML data.
	Prefix	The XML prefix for the DataSet namespace.
	CaseSensitive	True if string comparisons in this DataSet are case sensitive.
	Locale	The CultureInfo object containing the locale information used to compare strings in the DataSet (read/write).
	HasErrors	Returns True if there are errors in any of the DataTable objects in this DataSet.
	EnforceConstraints	True if constraint rules are enforced when attempting an update operation.
	Tables	Returns the collection of child DataTable objects.
	Relations	Returns the collection of DataRelation objects.
	ExtendedProperties	Returns the PropertyCollection object used to store custom information about the DataSet.
	DefaultViewManager	Returns a DataViewManager object that allows you to create custom search and filter settings for the DataTable objects in the DataSet.
Methods	AcceptChanges	Commits all changes to this DataSet after it was loaded or since the most recent AcceptChanges method.
	RejectChanges	Rejects all changes to this DataSet after it was loaded or since the most recent AcceptChanges method.
	HasChanges	Returns True if the DataSet has changed. It takes an optional DataRowState argument that lets you check for modified, inserted, or deleted rows only.
	Merge	Merges the current DataSet with another DataSet, a DataTable, or a DataRow array.
	Reset	Resets the DataSet to its original state.
	Clone	Creates a cloned DataSet that contains the identical structure, tables, and relationships as the current one.
	Copy	Creates a DataSet that has both the same structure and the same data as the current one.
	Clear	Clears all the data in the DataSet.
	GetChanges	Gets a DataSet that contains all the changes made to the current one since it was loaded or since the most recent AcceptChanges method, optionally filtered using the DataRowState argument.
	ReadXml	Reads an XML schema and data into the DataSet.
	ReadXmlSchema	Reads an XML schema into the DataSet.
	GetXml	Returns the XML representation of the contents of the DataSet.
	InferXmlSchema	Infers the XML schema from the TextReader or from the file into the DataSet.
	WriteXml	Writes the XML schema and data from the current DataSet.
	WriteXmlSchema	Writes the current DataSet's structure as an XML schema.
Events	MergeFailed	Fires when two DataSet objects being merged have the same primary key value and the EnforceConstraints property is True.

In the remainder of this section, I'll briefly introduce all the major classes in the DataSet hierarchy, with a list of their most important properties, methods, and events. Once you have an idea of the purpose of each object, I'll describe how to perform the most common operations in disconnected mode.

The DataTable Class

A DataTable object resembles a database table and has a collection of DataColumn instances (the fields) and DataRow instances (the records). It can also have a primary key based on one or more columns and a collection of Constraint objects, which are useful for enforcing the uniqueness of the values in a column. DataTable objects in a DataSet class are often tied to each other through relationships, exactly as if they were database tables. A DataTable object can also exist outside a DataSet class, the main limitation being that it can't participate in any relationships.

Table 21-2 summarizes the most important members of the DataTable object. As you see, some of the properties and methods have the same names and meaning as properties and methods in the DataSet class.

Table 21-2 Main Properties, Methods, and Events of the DataTable Class

Category	Name	Description
Properties	TableName	The name of this DataTable object.
	Namespace	The namespace for this DataTable, used when importing or exporting XML data.
	Prefix	The XML prefix for the DataTable namespace.
	CaseSensitive	Returns True if string comparisons in this DataTable are case sensitive.
	Locale	The CultureInfo object containing the locale information used to compare strings in the DataTable (read/write).
	HasErrors	Returns True if there are errors in any of the DataRow objects in this DataTable.
	DataSet	Returns the DataSet this DataTable belongs to.
	Rows	Returns the collection of child DataRow objects.
	Columns	Returns the collection of child DataColumn objects.
	ChildRelations	Returns the collection of DataRelation objects in which this DataTable is the master table.
	ParentRelations	Returns the collection of DataRelation objects in which this DataTable is the detail table.
	Constraints	Returns the collection of the Constraint objects for this DataTable (for example, foreign key constraints or unique constraints).
	ExtendedProperties	Returns the PropertyCollection object used to store custom information about the DataTable.
	MinimumCapacity	The initial number of rows for this DataTable (read/write).
	PrimaryKey	An array of DataColumn objects that represent the primary keys for the DataTable.
	DefaultView	Returns the DataView object that you can use to filter and sort this DataTable.
	DisplayExpression	A string expression used to represent this table in the user interface. The expression supports the same syntax defined for the DataColumn's Expression property.
Methods	AcceptChanges	Commits all changes to this DataTable after it was loaded or since the most recent AcceptChanges method.
	RejectChanges	Rejects all changes to this DataTable after it was loaded or since the most recent AcceptChanges method.
	Reset	Resets the DataTable to its original state.
	Clone	Creates a cloned DataTable that contains the identical structure, tables, and relationships as the current one.
	Copy	Creates a DataTable that has both the same structure and the same data as the current one.
	Clear	Clears all the data in the DataTable.
	GetChanges	Gets a DataTable that contains all the changes made to the current one after it was loaded or since the most recent AcceptChanges method, optionally filtered by the DataRowState argument.
	NewRow	Creates a DataRow with the same schema as the current table.
	ImportRow	Copies the DataRow passed as an argument into the current table. The row retains its original and current values, its DataRowState values, and its errors.
	Select	Returns the array of all the DataRow objects that satisfy the filter expression. It takes optional arguments that specify the desired sort order and the DataViewRowState to be matched.
	GetErrors	Returns the array of all the DataRow objects that have errors.
	Compute	Calculates the expression specified by the first argument for all the rows that satisfy the filter expression specified in the second argument.
	BeginLoadData	Turns off notifications, index maintenance, and constraints while loading data; to be used in conjunction with the EndLoadData and LoadDataRow methods.
	EndLoadData	Ends a load data operation started with BeginLoadData.
	LoadDataRow	Finds and updates a specific row or creates a new row if no matching row is found.
Events	ColumnChanging	Fires when a DataColumn is changing. The event handler can inspect the new value.
	ColumnChanged	Fires after a DataColumn has changed.
	RowChanging	Fires when a DataRow is changing.
	RowChanged	Fires after a DataRow has changed.
	RowDeleting	Fires when a DataRow is being deleted.
	RowDeleted	Fires after a DataRow has been deleted.

The DataRow Class

The DataRow class represents an individual row (or record) in a DataTable. Each DataRow contains one or more fields, which can be accessed through its Item property. (Because it's the the default member, the name of this property can be omitted.) Table 21-3 lists the main properties and methods of the DataRow object.

Table 21-3 Main Properties and Methods of the DataRow Class

Category	Name	Description
Properties	Item	Gets or sets the data stored in the specified column. The argument can be the column name, the column index, or a DataColumn object. An optional DataRowVersion argument lets you retrieve the current, original, proposed, or default value for the column.
	ItemArray	Gets or sets the values of all the columns, using an Object array.
	RowState	The current state of this row; can be Unchanged, Modified, Added, Deleted, or Detached.
	HasErrors	Returns True if there are errors in the column collection.
	RowError	Gets or sets a string containing the custom error description for the current row.
Methods	AcceptChanges	Commits all changes to this DataRow after it was loaded or since the most recent AcceptChanges method.
	RejectChanges	Rejects all changes to this DataRow after it was loaded or since the most recent AcceptChanges method.
	BeginEdit	Marks the beginning of an edit operation on a DataRow.
	EndEdit	Confirms all the changes to the DataRow since the most recent BeginEdit method.
	CancelEdit	Cancels all the changes to the DataRow since the most recent BeginEdit method.
	Delete	Deletes this row.
	GetColumnError	Returns the error description for the error in the column specified by the argument, which can be the column name, the column index, or a DataColumn object.
	GetColumnsInError	Returns the array of DataColumn objects that have an error.
	SetColumnError	Sets an error description for the specified column.
	ClearErrors	Clear all errors for this row, including the RowError property and errors set with the SetColumnError method.
	IsNull	Returns True if the specified column has a null value. The argument can be a column name, a column index, or a DataColumn. An optional second argument lets you refer to a specific DataRowVersion value (original, current, etc.).
	GetChildRows	Returns an array of the DataRow objects that are the child rows of the current row, following the relationship specified by the argument, which can be a relationship name or a DataRelation object. An optional second argument lets you refer to a specific DataRowVersion (original, current, etc.) for the row to be retrieved.
	GetParentRow	Returns the parent DataRow object, following the relationship specified by the argument (which can be one of the values accepted by the GetChildRows method).
	GetParentRows	Returns an array of the parent DataRow objects, following the relationship specified by the argument (which can be one of the values accepted by the GetChildRows method).
	SetParentRow	Sets the parent DataRow for the current row.

The DataColumn Class

The DataColumn class represents a single column (field) in a DataRow or in a DataTable. Not counting the methods inherited from System.Object, this class exposes only the properties summarized in Table 21-4. All properties are read/write except where otherwise stated.

Table 21-4 Main Properties of the DataColumn Class

Name	Description
ColumnName	The name of this column.
DataType	The System.Type object that defines the data type of this column.
MaxLength	The maximum length of a text column.
AllowDBNull	A Boolean that determines whether null values can be accepted for this column (for rows belonging to a table).
Unique	A Boolean that determines whether duplicated values are accepted in this column (for rows belonging to a table).
ReadOnly	A Boolean that determines whether values in this column can be modified after the row has been added to a table.
DefaultValue	The default value for this column when a new row is added to the table.
Expression	The expression to be used for calculated columns.
AutoIncrement	A Boolean that determines whether this is an auto-incrementing column (for rows added to a table).
AutoIncrementSeed	The starting value for an auto-incrementing column.
AutoIncrementStep	The increment value for an auto-incrementing column.
Caption	The caption to be used for this column in the user interface.
Namespace	The namespace for this DataTable, used when importing or exporting XML data.
Prefix	The XML prefix for the DataTable namespace.
ColumnMapping	The MappingType of this column, which is how this column is rendered as XML. It can be Element, Attribute, SimpleContent, or Hidden.
Table	The DataTable this column belongs to (read-only).
Ordinal	The position of this column in the DataColumnCollection (read-only).
ExtendedProperties	Returns the PropertyCollection object used to store custom information about the DataTable (read-only).

The DataView Class

The DataView class represents a view over a DataTable object, a concept that doesn't really match any ADO object you might already know. For example, you can filter the data in a table or sort it without affecting the values in the original DataTable object. Or you can create a view on a table that does (or doesn't) allow insertions, deletions, or updates.

A DataView object contains a collection of DataRowView objects, which are views over the rows in the underlying DataTable object. You can insert, delete, or update these DataRowView objects, and your changes are reflected in the original table as well. Another major function of the DataView class is to provide data binding to Windows Forms and Web Forms. Table 21-5 summarizes the most important members of the DataView class.

Table 21-5 Main Properties, Methods, and Events of the DataView Class

Category	Name	Description
Properties	AllowDelete	True if rows can be deleted.
	AllowEdit	True if values in the DataView can be modified.
	AllowNew	True if new rows can be added.
	Item	Returns the Nth DataRow (default member).
	Count	Returns the number of rows in this view.
	RowFilter	An expression that determines which rows appear in this view.
	RowStateFilter	A DataViewRowState enumerated value that determines how rows are filtered according to their state. It can be None, CurrentRows, OriginalRows, ModifiedCurrent, ModifiedOriginal, Added, Deleted, or Unchanged.
	Sort	A string that specifies the column or columns used as sort keys.
	ApplyDefaultSort	True if the default sort order should be used.
	Table	The source DataTable.
	DataViewManager	The DataView associated with this view (read- only).
Methods	AddNew	Adds a new row and returns a DataRowView object that can be used to set fields' values.
	Delete	Deletes the row at the specified index.
	Find	Finds a row in the DataView given the value of its key column(s); returns the row index.
Events	ListChanged	Fires when the list managed by the DataView changes, that is, when an item is added, deleted, moved, or modified.

The DataRelation Class

The DataRelation class represents a relationship between two DataTable objects in the same DataSet. The relationship is established between one or more fields in the parent (master) table and an equal number of fields in the child (detail) table. Table 21-6 lists the main properties of the DataRelation class. (This class doesn't expose methods other than those inherited from System.Object.) Note that all properties except Nested are read-only.

Table 21-6 Main Properties of the DataRelation Class

Name	Description
RelationName	The name of this relationship.
DataSet	The DataSet object this relationship belongs to.
ParentTable	The parent DataTable object.
ChildTable	The child DataTable object.
ParentColumns	An array of DataColumn objects representing the keys in the parent table that participates in this relationship.
ChildColumns	An array of DataColumn objects representing the keys in the child table that participates in this relationship.
ParentKeyConstraint	The UniqueConstraint object that ensures that values in the parent column are unique.
ChildKeyConstraint	The ForeignKeyConstraint object that ensures that values in the child table's foreign key column are equal to a value in the parent table's key field.
Nested	A Boolean value that specifies whether child columns should be rendered as nested elements when the DataSet is saved as XML text (read/write).

Last Updated: April 10, 2002

Top of Page