Using DataSet Objects for Data Access

I l @ ve RuBoard

Using DataSet Objects for Data Access

Earlier, we discussed the roles of data sets and data adapters ”specifically, that a data set represents a cache of data that has a similar structure to that of a relational database, and that a data adapter acts as a bridge between a data source and a data set. In this section, we'll examine in more detail how to work with and manage data sets, and the role the data adapter plays. Specifically , we'll look at the following topics:

• Populating a table within a data set using a data adapter

• The structure of a data set and how to navigate this structure

• Defining relationships between tables within a data set

• The differences between a typed data set and an untyped data set

• Updating a data store from a data adapter

Populating a DataSet Table from a Data Adapter

Typically, a single data adapter populates a single table within a data set. The data set can contain multiple tables, each of which can be populated from different data sources. You can populate a data set in two ways: You can add data to an existing table, or you can allow the data adapter to automatically create a new table within the data set. To begin with, we'll examine code that automatically creates a table within a data set.

Before you can populate the data set, you must create an IDataAdapter instance. In this example, we'll use the SqlDataAdapter class. The SqlDataAdapter constructor is overloaded. It can accept no parameters, a command text string and a connection instance, a command text string and a connection string, or just a command instance. The following code fragment creates a new SqlDataAdapter instance by passing no parameters:

 SqlDataAdapterda=newSqlDataAdapter(); 

Once we create the SqlDataAdapter object, we must associate a stored procedure or an SQL statement with it. The following code fragment associates an SQL statement with the SqlDataAdapter by calling its set_SelectCommand accessor method:

 //Createacommand SqlCommandsqlCommand=newSqlCommand(); sqlCommand.set_CommandText("SELECTEmployeeID,FirstName,LastName FROMEmployees"); sqlCommand.set_Connection(con); //Setthedataadapter'sselectcommand da.set_SelectCommand(sqlCommand); 

We used the set_SelectCommand method because the SqlCommand object represented an SQL select statement. However, if the SQL statement had represented an SQL update, insert, or delete command, we would have used one of the following accessor methods , as appropriate:

• set_UpdateCommand

• set_InsertCommand

• set_DeleteCommand

Once you associate the command with the SqlDataAdapter , you can call the Open method of the connection object. You're then ready to create a data set from this adapter.

The DataSet constructor has two overloads: one that accepts no parameters and one that accepts a string that represent the data set's name . The following code fragment demonstrates the use of the latter overload:

 DataSetds=newDataSet("myDataSet"); 

Once you create the DataSet object, you can populate it by calling the SqlDataAdapter object's Fill method. The Fill method returns an int that represents the number of rows retrieved or affected. The method has numerous overloads. Most of these allow you to pass different combinations of DataTable and DataSet objects, named tables, and SQL commands. For a complete reference to these overloads, see the product documentation.

The following code fragment passes two parameters to the Fill command. The first is the DataSet object, and the second is a string that the command will use as a label for the table it will create within the DataSet . If we do not pass a name for the table, the Fill method will still automatically create a table, but it will call it Table (and then Table1 , Table2 , and so on, if you keep using the method without the parameter).

 inti=da.Fill(ds, "Employees"); 

That's it. The Fill method has created a table containing data from the underlying database within the DataSet . You should now call the Close method of the Connection object, and you can then perform any operations you want on the data within the DataSet . The SimpleDataAdapter.jsl sample shows the full code for creating an SqlDataAdapter and using it to populate a table within a DataSet . Once we've populated the DataSet , we can print out how many lines the data adapter's Fill method retrieved:

 TheStructureof  DataSet  ObjectsandNavigation 

So far in this chapter, we've looked at the DataSet purely at a table level, but as you might remember, we said that a DataSet has a structure like a relational database. This means that each table contains columns and rows to hold the data. Figure 7-2 shows the classes that form the structure of a DataSet .

Figure 7-2. The DataSet object model

The first thing to note about Figure 7-2 is that all of the classes it shows belong to the System.Data namespace. The DataSet object contains multiple DataTable objects, and these are contained within the DataTableCollection object as the Tables property. Each DataTable contains a DataRowCollection , as a property called Rows , which contains multiple DataRow objects representing the rows in the table. The DataTable also contains a DataColumnCollection , as a property called Columns , which contains multiple DataColumn objects representing the columns in the table. In addition, the DataSet contains a DataRelationsCollection , which contains the relationships between the tables within the DataSet .

The DataSet , DataColumn , and DataTable can also have an ExtendedProperties object in which you can place customized information. For example, you can store the SQL statement you used to populate a table within the DataSet together with a timestamp indicating when the event occurred. For the moment, we'll concentrate on the DataTable , DataColumn , and DataRow objects. Later, we'll look at properties, constraints, and relationships.

A DataSet can be either typed or untyped, and this will govern how you navigate the DataSet . A typed DataSet is a class derived from System.Data.DataSet and generated from an XML schema. An untyped DataSet is an instance of the "out-of-the-box" DataSet class, with no associated XML schema. Using a typed DataSet makes coding an application much simpler because

• You can reference DataTable and DataColumn objects by a friendly name rather than by index, or by an item name represented as a string.

• Visual Studio .NET IntelliSense fully supports typed DataSet objects, so it will display the friendly names of the objects that comprise your DataSet .

• The structure of the data is defined by the XML schema, and this definition can be passed to another process, which will then automatically understand the structure and types of your data.

Navigating a Typed DataSet

The simplest way to create a typed DataSet is to use the tools provided by Visual Studio .NET: the XML Designer and the Component Designer. The XML Designer offers you precise control over the structure and content of the XML schema associated with the DataSet . We won't discuss this designer here ”it was covered in Chapter 5. Instead, we'll look at how you can use the Component Designer to create an XML schema, and from that a new DataSet class that is derived from System.Data.DataSet .

To create a typed DataSet with Visual Studio .NET, you must first create a connection and then create any data adapter objects you want to use. In this example, we'll use a single data adapter. Looking at the IDE, the bottom section of the Designer window is the Component Designer. In the Component Designer, select the data adapter created earlier and then choose Generate Dataset from the Data menu. The Generate Dataset dialog box will appear, as shown in Figure 7-3.

Figure 7-3. The Generate Dataset dialog box

Be sure that New is selected, and that the DataSet name is a meaningful one, such as dsEmployees . Visual Studio .NET will generate an XML Schema for the DataSet , and it will also generate a new class that inherits from System.Data.DataSet and is based on the information the XML Schema contains. That's it ”you've created a typed DataSet . You can now populate this DataSet in the same way that you previously populated an untyped DataSet ” by invoking the data adapter's Fill method.

Navigating a typed DataSet is simple because the objects that represent tables and columns are assigned friendly names. For example, to reference the EmployeeID column of the Employees table, you do not have to provide the names of the items as strings or integer indexes ”instead, you reference their friendly names. The following code fragment shows how to do this, and Figure 7-4 shows Visual Studio IntelliSense providing support for the object's friendly names:

 //Establishifthecolumncanonlyacceptuniquevalues boolb=employees1.get_Employees().get_EmployeeIDColumn().get_Unique(); 

Figure 7-4. IntelliSense displaying a DataColumn object's friendly name

If you do not have access to Visual Studio .NET (or you have too much time on your hands), you can generate a strongly typed DataSet from the command line. To do this, you must first create an XML schema for your DataSet . The simplest way to do this is to create a small application that creates a DataSet populated from a query that you'll use to populate the DataSet at run time. After you create the DataSet , you invoke its WriteXMLSchema method, which generates an XML schema for the DataSet and writes this schema to file. You can then run xsd.exe (from the command line) to generate a DataSet class based on the schema, as in this example:

 xsd.exe/d/l:J#MyXSDSchema.xsd/nMyXSDSchema.SomeNameSpace 

The /d flag instructs sxd.exe to generate a DataSet , /l indicates the language, and /n instructs xsd.exe to generate a namespace for the DataSet with a name of MyXSDSchema.SomeNameSpace . The code that's generated is placed by the tool in a file named MyXSDSchema.jsl. Now that you've created the DataSet , you must compile it into a library so you can consume it from your application. To do this, you simply use the Visual J# compiler with a command such as this:

 vjc/t:libraryMyXSDSchema.jsl/r:System.dll/r:System.Data.dll 

The /t flag tells the compiler to output a library, and the /r flags reference libraries on which there are dependencies. Once you've completed this compilation, you can use the typed DataSet from your applications. You use it in exactly the same way as shown in the previous section, but you must remember to import the newly created typed DataSet 's namespace, as in this example:

 importMyXSDSchema.SomeNameSpace.*; 

Navigating an Untyped DataSet

To navigate your way around an untyped DataSet , you must first get the tables contained within the DataSet . To do this, you call the get_Tables accessor method, which returns a DataTableCollection object:

 DataTableCollectiontables=ds.get_Tables(); 

Once the get_Tables method returns a DataTableCollection , you can use this object's get_Count accessor method to return the number of tables within the collection. If you want to check whether the collection contains a specific table, you can call the Contains method of the DataTableCollection object and pass it a table name as a string. The Contains method returns a Boolean , where a value of true indicates that the table is within the DataTableCollection , as in this example:

 if(tables.Contains("Employees")==true) { //Performsomeprocessingonthetable } 

To access a table within the DataTableCollection , you call the get_Item method. The get_Item method is overloaded: One form takes an integer index, and the other form takes a table name as a string. For example, to get the Employees table from the collection:

 DataTableempTable=tables.get_Item("Employees"); 

Once you have a table, you can access its columns and rows through the DataColumnCollection and DataRowCollection objects. To get these collections, you simply call the get_Columns or the get_Rows method. Like the Data ­Table , the DataColumnCollection and the DataRowCollection both expose a get_Count method, which returns the number of rows or columns within the respective collections. To access a particular column within a DataColumnCollection , you can pass an integer index or a column name to the collection's get_Item method. Likewise, you can call the get_Item method of the DataRowCollection to get a specific row:

 //Gettherowscollection DataRowCollectionempRows=empTable.get_Rows(); //Getthefirstrowofthecollection DataRowfirstRow=empRows.get_Item(0); 

Once you get a DataRow object, you can access individual records within it by calling its get_Item method, which is overloaded. The six signatures of this method are

 get_Item(System.Data.DataColumn,System.Data.DataRowVersion) get_Item(System.String<columnname>,System.Data.DataRowVersion) get_Item(int<columnindex>,System.Data.DataRowVersion) get_Item(System.Data.DataColumn) get_Item(System.String<columnname>) get_Item(int<columnindex>) 

Instead of using the get_Item method, you can use the get_ItemArray method, which returns an Object array that contains all of the values within the DataRow . The NavigateDataSet.jsl sample file demonstrates the use of the get_ItemArray method, and it shows the complete code for navigating a DataSet .

Manipulating and Updating a Data Store from a Data Adapter

You can add and delete rows from a table within a DataSet , and when you have completed these changes you can commit the updates to the original data source. The important thing to note is that any local changes will not affect the underlying data source until you explicitly persist the updates through the data adapter.

You can add rows to a DataTable within a DataSet in a number of ways. All of these ways involve calling the Add method or the InsertAt method of the DataRowCollection . The Add method appends a row to the collection, and the InsertAt method inserts the new data at a specified index within the collection. The Add method is overloaded, so you can pass it an array of Object instances that form the row you want to insert or you can pass the method a DataRow object. To use the latter method, you must first create a new instance of the DataRow class. You cannot directly instantiate the DataRow class, so you must create a new instance by calling the NewRow method of the DataTable :

 DataRownewRow=empTable.NewRow(); 

Once you create the DataRow instance, you can set individual records within it by calling its set_Item method, which takes two parameters: a column identifier, which can be the column name as a string, the column index as an integer, or a DataColumn object, and a value for the record passed as any subclass of Object , as shown here:

 newRow.set_Item("LastName", "Roxburgh"); empRows.Add(newRow); 

Alternatively, you can call the DataRow object's set_ItemArray method, which accepts an array of objects that represent the values for each of the records within the row. For example, to create a row with a FirstName of Peter and a LastName of Roxburgh , you would use the following code:

 Object[]newValues={"Peter", "Roxburgh"}; newRow.set_ItemArray(newValues); empRows.Add(newRow); 

As mentioned earlier, the Add method appends a row to the DataRow ­Collection ; if you want to insert a row at a specific index, you must use the InsertAt method instead. This method has no overloads, and it accepts two parameters: a DataRow and an int index. If you specify an index that is greater than the length of the DataRowCollection , the new row will be inserted at the end of the collection. For example, this code snippet shows how to insert a row so that it is the second in the DataRowCollection :

 DataRownewRow=empTable.NewRow(); Object[]newValues={"Peter", "Roxburgh"}; newRow.set_ItemArray(newValues); Rows.InsertAt(newRow,1); 

If you want to delete a row, you can call the Remove method, which accepts a DataRow as a parameter. Alternatively, you can call the RemoveAt method, which accepts an int as a parameter, which indicates the index of the row to delete. If you want to the delete all the rows in the DataRowCollection , you can call the Clear method.

You can also add and delete columns within a DataColumnCollection . The Add method is overloaded; the most useful versions of this method have the following signatures:

 Add(String<columnname>) Add(String<columnname>,System.Typetype) Add(DataColumn) 

In many cases, you'll use the final overload shown above because you'll need to define column properties, such as whether the column can contain only unique values, whether the column allows null data values, and the data type of the column. You can set many of these properties by passing them as arguments to the DataColumn column constructor. Alternatively, you can set them using appropriate accessor methods after you've created a DataColumn instance. When you create a new column in an existing table, the column's value of each row is set to the value of the column's DefaultValue property. If this property is not set, it has a default value of DbNull .

The following code fragment shows how to create a new instance of the DataColumn class, and it shows how to set some of the more frequently used public properties of the class. Table 7-8 describes these public properties.

 DataColumnnewColumn=newDataColumn(); dc.set_DataType(Type.GetType("System.Int16")); dc.set_AllowDBNull=true; dc.set_AutoIncrement(false); dc.set_ColumnName("EmpoyeeShoeSize"); dc.set_Unique(false); 

Table 7-10. Frequently Used DataColumn Public Properties

Property	Data Type	Description
AllowDBNull	Boolean	Indicates whether the column can contain null values. The default value is true .
AutoIncrement	Boolean	Indicates whether the value of a new row for the column automatically increments . The default value is false .
AutoIncrementSeed	Long	Indicates the starting value for AutoIncrement .
AutoIncrementStep	Long	Indicates the value by which incrementation occurs. The default value is 1.
ColumnName	String	The name of the column.
DataType	System.Type	The data type the column can contain.
DefaultValue	Dependent on DataType	The default value for a field within a column. The value is dependent on the data type of the column.
Expression	String	Represents an expression that is used to calculate, or generate, the value of the column. For example, you can create a calculated column such as a price including sales tax column, which is generated based on the values contained in other columns.
MaxLength	Int	Defines the maximum length of a column that contains textual data.
ReadOnly	Boolean	Indicates whether the column will allow changes to a row once that row has been added to the DataSet . The default value is false .
Unique	Boolean	Indicates whether each record within the column must contain a unique value. The default value is false .

To remove a column from a DataColumnCollection , you call the collection's Remove method. The Remove method accepts either a DataColumn or the column name as a string. If the specified column does not exist, the method does not throw an exception.

Once you've implemented any change to the DataSet , you must call the data adapter's Update method to commit these changes to the underlying data source.

Warning

The Update method will throw a DBConcurrencyException if it attempts to perform an operation against the database and you haven't set an appropriate select, insert, update, or delete command for it. For example, if you delete a row from a DataSet , you must set the value of the data adapter's DeleteCommand property with an appropriate SQL Delete statement.

The Update method is overloaded, but all overloads return an int , which indicates the number of rows affected. The four main variants of the method have the following signatures:

 Update(System.Data.DataSetdataSet,StringsrcTable) Update(System.Data.DataTabledataTable) Update(System.Data.DataRow[]arrayOfDataRows) Update(System.Data.DataSetdataset) 

The UpdatingADataSet.jsl sample demonstrates modifying data within a DataTable and then committing the changes to the original data source. The code includes comments to help you navigate it. Briefly, the code connects to the database, uses the data adapter's Fill method to populate the DataSet , adds a new DataRow to the DataTable within the DataSet , adds a new DataColumn , and deletes a row from the DataTable . You should run this code and then run the TestUpdatingADataSet.jsl sample code to see the effect it has.

The TestUpdatingADataSet.jsl sample allows you to test whether the modifications have affected the underlying data source. When this code executes, it connects to the database and uses the data adapter's Fill method to populate a DataSet . The code then gets the Employees table from the DataSet and then iterates through the values contained within each row of the table, printing them to the console. The code then iterates through the table's columns and prints the name of each to the console. You'll find that the row of data added in Updating ­ADataSet.jsl has been committed to the database. However, the additional column added to the in-memory DataTable has been lost. This should be expected since allowing dynamic schema changes to a database would be tricky!

Defining Relationships and Constraints

You can define a relationship between columns in different tables by using a DataRelation object. The intention of a DataRelation is to support parent-child relationships such as those between an order and its order lines, or a customer and her orders. This type of relationship in a relational database is typically implemented by using a primary key from one table as a foreign key in another. At its simplest, you create a DataRelation by passing its constructor a name for the relation, a parent DataColumn , and a child DataColumn , where both columns are of the same data type. For example, the following code from the DataRelationships.jsl sample file, shows how to set up a relationship between two tables:

 //GetthetablesfromtheDataTableCollection DataTableemployeeTable=dtc.get_Item("Employees"); DataTableemployeeTerritoryTable=dtc.get_Item("EmployeeTerritories"); //Getthecolumnstorelate DataColumnparentColumn= employeeTable.get_Columns().get_Item("EmployeeID"); DataColumnchildColumn= employeeTerritoryTable.get_Columns().get_Item("EmployeeID"); //Definetherelationship DataRelationrelationship=newDataRelation("EmpIDRelation", parentColumn,childColumn); 

Relations are stored in the DataRelationCollection of the DataSet . To get the DataRelationCollection , you call the get_Relations method of the DataSet . You can then add the DataRelation to the collection by invoking the collection's Add method:

 DataRelationCollectiondrc=ds.get_Relations(); drc.Add(dr); 

More often than not, when we define relationships, we want to define the behavior of one party in the relationship when changes to the other party might affect it. The ForeignKeyConstraint allows you to define this type of behavior. Specifically, it allows you to specify which one of the following changes will occur to a child row when a parent row is deleted or updated:

• A cascade delete (child rows are deleted) is performed.

• Child values are set to null.

• Child values are set to default values.

• An exception is thrown.

The ForeignKeyConstraint constructor is overloaded. For full details, you can see the SDK documentation. But in short, the overloads accept a mixture of constraint names, child columns, parent columns, and arrays of all these items. The following code fragment, taken from the Constraints.jsl sample file, illustrates calling the ForeignKeyConstraint constructor by passing a name for the constraint, a parent DataColumn , and a child DataColumn :

 //GettheparentandchildDataColumnobjects DataColumnparentColumn= employeesTable.get_Columns().get_Item("EmployeeID"); DataColumnchildColumn= employeesTerritoriesTable.get_Columns().get_Item("EmployeeID"); //Createaforeignkeyconstraint ForeignKeyConstraintconstraint= newForeignKeyConstraint("EmpIDConstraint", parentColumn,childColumn); 

Once you create a ForeignKeyConstraint instance, you can use its set_DeleteRule and set_UpdateRule methods to define the behavior it defines. Both methods accept a single parameter, which must be a member of the System.Data.Rule enumeration.

 constraint.set_DeleteRule(Rule.SetNull); constraint.set_UpdateRule(Rule.Cascade); 

The enumeration has four members , which Table 7-9 describes.

Table 7-11. System.Data.Rule Members

Member	Description
Cascade	Enforces cascaded deletes and updates. This is the default value for a constraint.
None	No action taken.
SetDefault	Sets affected row to a default value. You can define the default value of a column by setting its DefaultValue property to an appropriate value.
SetNull	Sets affected values to DBNull , which represents a record that has no value.

Each table in a DataSet has a ConstraintsCollection that contains its associated constraints. These constraints can be instances of ForeignKeyConstraint or UniqueConstraint ; both descend from System.Data.Constraint . Once you set the rules for the ForeignKeyConstraint , you add the constraint to the ConstraintsCollection of the table that contains the foreign key. To add the constraint to the collection, you call the constraint's Add method. Like the other collections classes, the ConstraintsCollection class exposes the following methods:

• Add

  Adds a Constraint to the collection

• Clear

  Clears all constraints from the collection

• Contains

  Returns true if the named constraint is in the collection

• get_Item

  Returns a constraint referenced by name or index

• Remove

  Removes a constraint referenced by name or Constraint instance

• RemoveAt

  Removes a constraint at a specified index in the collection

Once you add the ForeignKeyConstraint to the ConstraintsCollection , you must set the EnforceConstraints property of the DataSet to true ; otherwise , the constraints will not be applied to your data!

 //Addtheconstrainttothetable employeesTerritoriesTable.get_Constraints().Add(constraint); //Enforceconstraintsoritwon'twork ds.set_EnforceConstraints(true); 

The UniqueConstraint is used to specify that all the values in a column must be unique. Its extensively overloaded constructor allows you to specify whether the column is also the primary key of the table. You can check whether a UniqueConstraint is specified on a primary key by checking its IsPrimaryKey property, which returns true if the constraint is on the primary key. The UniqueConstraint constructor accepts a mixture of column names, arrays of column names, and Boolean values that indicate whether it is a primary key. The Constraints.jsl sample file demonstrates creating a UniqueConstraint that is not a primary key.

 //Addaconstraintthatthecombinationoffirstname //andlastnamemustbeunique. DataColumnfirstName=employeesTable.get_Columns().get_Item("FirstName"); DataColumnlastName=employeesTable.get_Columns().get_Item("LastName"); DataColumn[]columnsToConstrain={firstName,lastName}; UniqueConstraintuConstraint=newUniqueConstraint("NameConstraint", columnsToConstrain,false); employeesTable.get_Constraints().Add(uConstraint); 

The code also creates a ForeignKeyConstraint , adds these constraints to a table, and ensures that the DataSet enforces the constraints.

I l @ ve RuBoard