CS 112D Lecture Notes - The DataSet Class (Chapter 5 part 1)

The DataSet Class

DataSet - The key class in ADO.NET. It is an in-memory cache of data from one or more data sources. It's almost like an in-memory database, and it's totally disconnected from any actual database.
The DataSet and its related classes (DataTable, DataRow, DataColumn, and DataRelation) simply store and manipulate data. They don't care where the data came from, and they aren't specific to Data Providers. So there is no SqlDataSet class.
a DataAdapter object is used to read and write data between a DataSet and its database Connection. We'll cover the DataAdapter in more detail in Chapter 6.
a DataSet can be used purely to store data in memory, like an array with the ability to sort, filter, etc.
Each DataSet contains a collection called Tables which contains DataTable objects - a DataSet may contain one or more DataTables, each of which represents a table's data. A DataTable is similar to the RecordSet from older versions of ADO. A DataTable contains all of the following Collections:
- Columns collection containing DataColumn objects - used to store column information including name, data type, and properties such as Unique, ReadOnly, AllowDBNull, and AutoIncrement. Expression property allows value to be calculated.
- Rows collection containing DataRow objects - each object represents a single record of a DataTable. It's used to add, retrieve, and/or modify data in the DataTable.
- ChildRelations and ParentRelations collections containing DataRelation objects - each object is used to define a relationship between two tables in a DataSet, such as a primary key-foreign key relationship.
- Constraints collection containing Constraint objects - each object is used to define a rule such as UniqueConstraint or ForeignKeyConstraint. Contains references to one or more DataColumns.
Here is an example from the text book to create an EmployeeInfo DataSet in code (the CreateDataSet and AddData functions in the frmDataSets form)
New rows can be added to a DataSet by simply adding the DataRow objects to the Rows collection.
Changes can be made in already-existing rows by simply setting the values in the DataRow object.
Additions and changes to DataRows are not final until the AcceptChanges() method is called.
RejectChanges() can be used to cancel any changes made since the last call to AcceptChanges().
Changes may be accepted or rejected at the DataRow, DataTable, or DataSet level.
To delete a row, you can remove it from the Rows collection by calling Remove() on the Rows collection, specifying the DataRow object to remove as the method's argument. This kind of deletion is permanent and immediate.
A safer way to remove a row is to call the DataRow's Delete() method. Then the row is deleted only after AcceptChanges() is called.
DataRow objects have a RowState property which contains one of the following values: Unchanged, Added, Deleted, Modified, or Detached.

Filtering, Sorting and Constraints

To sort and/or filter records in a DataTable, use the DataTable's Select(String, String) method, specifying the filter criteria as a string, followed by the sort order as a string. The reference value Nothing may be substituted for either String to leave data unfiltered or unsorted.
The Select method returns an array of DataRow objects, containing all the rows that matched the filter, in the specified order.
So for example the following code puts all employees from the DataTable dtEmployees with DepartmentId of 33 into the array sortedRows, sorted by LastName:
```
        Dim sortedRows As DataRow()
        sortedRows = dtEmployees.Select("DepartmentId = 33", "LastName ASC")
  
```
To constrain the data in a DataTable, define a Constraint object and add it to the DataTable's Constraints collection.
Constraint is an abstract class, so you have to define an object that's of a more specific Constraint class, such as UniqueConstraint or ForeignKeyConstraint
a UniqueConstraint can have a name, and it lists one or more columns which have to be unique. (If more than one column is listed, it means the combination of the column values must be unique.)
DataColumn objects storing the relevant column information are used to specify which column(s) are constrained. To constrain more than one column, put the DataColumn objects into an array.

For example, to constrain the DataTable dtEmployees so that no two records may have the same first and last name combination, you'd write the following code:

        Dim csName As Constraint
        Dim colsName(1) As DataColumn
        colsName(0) = dtEmployees.Columns.Item("FirstName")
        colsName(1) = dtEmployees.Columns.Item("LastName")
        csName = New UniqueConstraint("Name", colsName)
        dtEmployees.Constraints.Add(csName)

Return to the main CS 112D page