……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:DataSets – Part 5

Posted by Ravi Varma Thumati on November 18, 2009

The DataSet is a memory-resident representation of data including tables, relationships between the tables, and both unique and foreign key constraints. It is used for working with and transporting data in a disconnected environment.

There are four important characteristics of the DataSet:

  • It’s not provider-specific. It’s impossible to tell by looking at the DataSet, or at the objects contained within the DataSet, which provider was used to retrieve the data or what the original data source was. The DataSet provides a consistent programming model regardless of the data source.
  • It’s always disconnected. Information is retrieved from the data source and placed in the DataSet using another ADO.NET object—the DataAdapter. At no point does a DataSet directly reference a Connection object.
  • It can track changes made to its data. The DataSet contains multiple versions of each row of data in the tables, which allows changes to be updated back to the data source using a DataAdapter object, changes to be cancelled, and XML DiffGrams of the changes to be created.
  • It can contain multiple tables. Unlike the traditional ADO Recordset, the DataSet approximates a relational database in memory.

DataSets exist as both untyped and strongly typed. Strongly typed DataSets are a collection of automatically generated classes that inherit from the DataSet, DataTable, and DataRow classes, and provide additional properties, methods, and events based on the DataSet schema. A strongly typed DataSet can make programs more intuitive to write and allows the Visual Studio .NET IDE to provide functionality such as autocomplete and for the compiler to detect type mismatch errors and misspelled names during compilation rather than at runtime.

The data stored in the DataSet can be manipulated programmatically and populated using a DataAdapter or from XML documents or streams. The actual DataSet schema can be created programmatically, read from a data source, read from an XML schema, or inferred from an XML document or stream. The DataSet can easily be serialized to XML for marshalling between processes with .NET remoting or to meet persistent storage requirements.

Figure 6-1 shows the structure of the DataSet and the contained classes.

Creating an Untyped DataSet

There are several ways a DataSet can be created. In the simplest case, a DataSet is created using the new keyword. The constructor accepts on optional argument that allows the DataSetName property to be set. If the DataSetName argument isn’t supplied, the default name of the DataSet will be NewDataSet.

DataSet ds = new DataSet(“MyDataSet”);

A DataSet can also be created from another DataSet. The Copy( ) method can create a new DataSet containing both the schema and data from the original DataSet. The Clone( ) method creates a new DataSet with the same schema, but none of the data of the original. Finally, the GetChanges( ) method creates a new DataSet containing data that has changed since the DataSet was last loaded or the pending changes were accepted. These methods will be discussed in more detail later in this chapter.

Working with Tables in the DataSet

Tables belonging to the DataSet are stored as DataTable objects in a DataTableCollection object and accessed through the Tables property of the DataSet. This section examines some methods and properties of the DataTableCollection.

Tables are added to the DataSet using the Add( ) method of the DataTableCollection. The Add( ) method takes an optional table name argument. If this argument isn’t supplied, the tables are automatically named Table, Table1, and so on. The following example adds a table to a DataSet:

DataSet ds = new DataSet(“MyDataSet”);

DataTable dt = new DataTable(“MyTable”);

// … code to define the schema for the newly constructed DataTable


The AddRange( ) method allows more than one table to be added to the DataSet in the same statement. The method takes an array of DataTable objects as the argument, as the following example shows:

// create two new tables

DataTable dt1 = new DataTable();

DataTable dt2 = new DataTable();

// use the AddRange() method to add them to the DataSet.

ds.Tables.AddRange(new DataTable[] {dt1, dt2});

A DataTable can also be created automatically in a DataSet when the Fill( ) or FillSchema( ) method of the DataAdapter is called. A new table is created and filled with the data or schema, respectively, from the data source, as illustrated in the following code:

// connection and select command strings

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql = “SELECT * FROM Orders”;

// create a new DataSet to receive the data

DataSet ds = new DataSet();

SqlDataAdapter da = new sqlDataAdapter(selectSql, connString);

// an empty table named OrdersSchema will be created in the DataSet

da.FillSchema(ds, SchemaType.Mapped, “OrdersSchema”);

// a table named Orders will be created in the DataSet

// filled with data as specified by the SQL statement

da.Fill(ds, “Orders”);

Existing tables within the DataSet can be accessed by an indexer, which usually is passed the table name or the position of the table within the DataTableCollection as an argument as shown in the following examples:

// using the table name

DataTable dt = ds.Tables[“MyTable”];

// using the table ordinal

DataTable dt = ds.Tables[0];

The Count property returns the number of tables within the DataSet:

Int32 tableCount = ds.Tables.Count;

The Contains( ) method determines whether a table with a specified table name exists within a DataSet:

// Boolean tableExists = ds.Tables.Contains(“MyTable”);

The IndexOf( ) method returns the index of the table within the collection using either a reference to the table object or the name of a table. The following example demonstrates both techniques:

// get the index using the name of the table

Int32 tableIndex = ds.Tables.IndexOf(“MyTable”);

// get the index using a reference to a table

DataTable dt = ds.Tables.Add(“MyTable”)

// … build the table and do some work

// get the index of the table based on the table reference

Int32 tableIndex = ds.Tables.IndexOf(dt);

The Remove( ), RemoveAt(), and Clear( ) methods remove tables from the DataSet. The Remove( ) method takes an argument that specifies either a table name or a reference to the table to be removed, as shown in the following example:

DataTable dt = ds.Tables.Add(“MyTable”);

// remove by table reference


// remove using the table name


The RemoveAt( ) method removes the table at the specified index from the DataTableCollection object, as shown in the following example:

// removes the first table from the tables collection in the DataSet


The Clear( ) method removes all tables from the DataSet, as shown here:


Adding and Removing Relations

Relations belonging to the DataSet are stored as DataRelation objects in a DataRelationCollection object and are accessed through the Relations property of the DataSet. Each DataRelation object represents the relationship between a parent and child table in the DataSet. This section examines some methods and properties of the DataRelationCollection.

Relations are added to the DataSet using the Add( ) method of the DataRelationCollection, as shown in the following example:

ds.Relations.Add(“MyDataRelation”, parentTable.Columns[“PrimaryKeyField”],


The Remove( ) method removes a relation matching the relation-name argument. The following example removes the relation added in the previous example:


The Contains( ) method can determine if a specific relation exists as shown in the following example:

Boolean exists = ds.Relations.Contains(“MyRelation”);

Adding Custom Information

The DataSet contains a PropertyCollection that is exposed through the ExtendedProperties property. This collection allows a user to add custom information to the DataSet such as the date and time when the DataSet should be refreshed. The following example sets an extended property indicating that the DataSet should be refreshed in 20 minutes:



The following code can then check that value to see if the DataSet needs to be refreshed:


ds.ExtendedProperties[“RefreshDateTime”].ToString( ) ))


// … code to refresh the DataSet


Extended properties must be of type String, or else they will not persist when the DataSet is written as XML.

The DataTable, DataColumn, DataRelation, and Constraint objects also have a similar collection of extended properties.

Cloning the Schema

The Clone( ) method creates a new DataSet with the same structure, including table schemas and relations, as the original but containing none of the data in the original DataSet. The following example uses the Clone( ) method to create a new DataSet:

// create a DataSet object variable to receive the clone

DataSet cloneDs;

cloneDs = ds.Clone();

Copying the DataSet

The Copy( ) method of the DataSet creates a new DataSet with the same structure, including tables schemas and relations, and data as the original DataSet. The following example uses the Copy( ) method to create a new DataSet:

// create a DataSet object variable to receive the copy

DataSet copyDs;

copyDs = ds.Copy();

Merging Two DataSets

The Merge( ) method combines the data and structure of a second, or source, DataSet into a specified target DataSet with a similar structure. The Merge( ) method is typically used in a client application to update a DataSet with the latest changes to the underlying data in the data source.

When the Merge( ) method is called, the schemas of the source and target DataSet are compared. If there are schema differences, the MissingSchemaAction argument determines whether the target schema is updated to include the missing schema and data or whether an exception is raised. If the MissingSchemaAction is specified as Add or AddWithKey, the schema is extended to accommodate the new data, and the primary key information added in the case of the later value. Specifying Ignore results in the data for the missing schema being ignored; specifying Error results in an exception being raised.

During the merge operation, source rows with a RowState of Unchanged, Modified, or Deleted are matched to rows in the target DataSet with the same Current primary key values. Source rows with RowState of New are created in the target DataSet with the same primary key values as the Current value in the source because the Original version doesn’t exist in the source. Figure 6-2 shows the result of merging two tables with similar schemas, accepting the default MissingSchemaAction value of Add.

Figure 6-2. Merging DataSet objects

If the optional PreserveChanges argument is set to true, incoming values from the source doesn’t overwrite Current values in the target DataSet rows. Data in the target Original row version is overwritten with the Original row version of the source row, and the target RowState is set to Modified. There are two exceptions. If the target RowState is Deleted, it remains deleted and isn’t set to Modified. If the source RowState is Added, the target existing row isn’t overwritten because it doesn’t exist.

If PreserveChanges is false, both the Current and Original rows of the target are overwritten with the source data, and the RowState of the target row is set to the RowState of the source row. Again, there are two exceptions. If the source RowState is Unchanged, and the target RowState is Unchanged, Modified, Added, or Deleted, the RowState of the target row is set to Modified. If the source RowState is Added, the Original version of the target isn’t overwritten because it doesn’t exist.

During the merge operation, constraints are disabled. If constraints can’t be enabled after the merge, the EnforceContraints property of the DataSet is set to false, and all invalid rows are marked as having errors.

The following example demonstrates the most complete form of the Merge( ) method:

Boolean preserveChanges = true;

Boolean missingSchemaAction = MissingSchemaAction.Add;

ds.Merge(mergeDs, preserveChanges, missingSchemaAction);

Removing All Data

The Clear( ) method removes all rows from all tables in the DataSet:


Resetting the DataSet

The Reset( ) method returns the DataSet to its original state. This method can be used to discard the existing DataSet and start working with a new DataSet rather than instantiating a new DataSet object:


Committing and Discarding Changes

When a DataRow is modified, ADO.NET marks the row as having a changes and sets the RowState of the DataRow to Added, Modified, or Deleted, as appropriate. ADO.NET also maintains version information by storing both Original and Current versions of each row. Together, this information allows ADO.NET or an application to identify the rows and columns that have been changed.

AcceptChanges and RejectChanges

The AcceptChanges( ) and RejectChanges( ) methods either accept or reject the changes that have been made to the DataSet since it was last loaded from the data source or since AcceptChanges( ) was last called.

The AcceptChanges( ) method commits all pending changes within the DataSet. Calling AcceptChanges( ) changes the RowState of Added and Modified rows to Unchanged. Deleted rows are removed. The Original values for the DataRow are set to the Current values. Calling the AcceptChanges( ) method has no effect on the data in the underlying data source.

The AcceptChanges( ) method is implicitly called on a row when the DataAdapter successfully updates that row back to the data source when the Update( ) method is called. As a result, when a DataAdapter is used to update the data source with the changes made to the DataSet, AcceptChanges( ) doesn’t need to be called. Calling AcceptChanges( ) on a DataSet filled using a DataAdapter effectively removes all information about how the DataSet has been changed since it was loaded. This makes it impossible to reconcile those changes back to the data source using the Update( ) method of the DataSet.

The following example demonstrates the AcceptChanges( ) method:


The RejectChanges( ) method cancels any pending changes within the DataSet. Rows marked as Added are removed from the DataSet. Modified and Deleted rows are returned to their Original state. The following example demonstrates the RejectChanges( ) method:


The following example illustrates the concepts just explained:

// create a table with one column

DataTable dt = new DataTable();

dt.Columns.Add(“MyColumn”,  typeof(System.String));

// add three rows to the table

DataRow row;

row = dt.NewRow();

row[“MyColumn”] = “Item 1”;


row = dt.NewRow();

row[“MyColumn”] = “Item 2”;


row = dt.NewRow();

row[“MyColumn”] = “Item 3”;



// modify the rows

dt.Rows[0][“MyColumn”] = “New Item 1”; // DataRowState=Modified

dt.Rows[1].Delete();                   // DataRowState=Deleted

//dt.Rows[2]                           // DataRowState=Unchanged

dt.Rows[0].AcceptChanges();            // DataRowState=Unchanged,

// MyColumn value=”New Item 1″;

dt.Rows[1].RejectChanges();            // DataRowState=Unchanged,

// row not deleted

The DataTable and DataRow objects also expose an AcceptChanges( ) method and a RejectChanges( ) method. Calling these methods on the DataSet implicitly calls these methods for all DataRow objects in the DataSet.

HasChanges and GetChanges

The HasChanges( ) method of the DataSet indicates whether the DataSet has changes, including Added, Deleted, or Modified rows. The method accepts an optional DataRowState argument that causes the method to returns a value from the DataSetRow enumeration if the DataSet has changes:

// check if there are any changes to the DataSet

Boolean hasChanges = ds.HasChanges();

// check if there are modified rows in the DataSet

Boolean hasModified = ds.HasChanges(DataRowState.Modified);

The GetChanges( ) method creates a copy of the DataSet containing all the changes that have been made since it was last loaded or since AcceptChanges( ) was called. The method takes an optional DataRowState argument that specifies the type of row changes the DataSet should include. The GetChanges( ) method can select the data that has been modified in a DataSet so that only the changed data rather than the entire DataSet is returned. This subset of the DataSet that contains just the changed data can improve performance of disconnected applications by reducing the amount of information that needs to be transmitted between different application domains.

The HasChanges( ) method can be called first to determine whether GetChanges( ) needs to be called. The following example show how to use the HasChanges( ) and GetChanges( ) methods:

// check to see whether GetChanges needs to be called

if (ds.HasChanges())


// create a DataSet containing all changes made to DataSet ds

DataSet dsChange = ds.GetChanges();

// create a DataSet containing only modified rows in DataSet ds

DataSet dsModified = ds.GetChanges(DataRowState.Modified);



One Response to “ADO.NET Core Classes:DataSets – Part 5”

  1. Very effectively written information. Will probably be invaluable to anybody who usess it, including myself. Sustain the nice work – for certain i will check out extra posts.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: