Managing Reference Data

In an enterprise application, reference data typically does not change. It represents fairly constant values that are displayed in selection lists within the user interface. In the IssueTracker application, this includes values that represent issue types, priority levels, and status codes. Although these values may also be managed as application business objects, their static nature lends themselves well to the cached hierarchical support that the DataSet object offers.

Understanding the DataSet Object

The ADO.NET DataSet is for disconnected database interactions against a memory image of a database. Applications can execute commands that store and retrieve data from this memory image, which is periodically synchronized with the actual database.

You can create a DataSet object by invoking the DataSet constructor, specifying an optional name, or accepting the default DataSet name of NewDataSet . You can also create a new instance by cloning an existing DataSet instance as an exact copy, with or without its data. The following example creates a DataSet object and assigns it the name IssueTrackerReference :

 DataSet dataset = new DataSet("IssueTrackerReference"); 

As Figure 2-2 illustrated , the DataSet object comprises five principal objects: DataTable, DataColumn, DataRow, Constraint, and DataRelation. Each of these objects has properties and methods that mimic the behavior of actual database elements.

Working with DataTables

The DataSet object maintains a collection of objects that represent database tables. As in the case of databases, each DataTable object comprises column, row, and constraint information. You can create a DataTable object by using the DataTable constructor or by invoking the Add method of the Tables property within the DataSet object.

Once you have added a DataTable to the collection, you cannot add it to any other DataSet Tables collection. Also, when creating a DataTable object, it does not require the TableName property. You can specify the name can be specified later or leave its default value, Table n , where n represents an incrementing number beginning with zero. All references to names of DataSet tables and relations are case sensitive.

Working with DataColumns

When a DataTable is first created, it does not have any table structure. To define a schema, you must create and add DataColumn objects to the columns collection of the DataTable. Each column represents a piece of data that is captured and identified by a column name and column data type.

For performance gains, every database table should have at least one primary key designated. A primary key is a column that contains a unique value for each row within the table. This is normally an identifier that helps an application find a specific record. For a column to serve as a primary key, it must be unique and it must not be null. You can insert DataRows into the table as long as the table's primary key column is not null and does not already exist.

Working with DataRows

Once you have defined a schema for a DataTable object, you can insert the actual data. You insert data one row at a time using the DataRow object. To add a new row of data to a table, begin by creating a new DataRow object and initializing it by invoking the NewRow method provided by the DataTable object. Next , set the column values. Finally, invoke the Add method and provide the new DataRow object as a parameter:

 DataRow row = table.NewRow(); row["ID"] = 100; row["Name"] = "Simon West"; row["Active"] = true; //when working with a DataSet object dataset.Tables.["Employees"].Rows.Add(row); //when working directly with a DataTable object table.Rows.Add(row); 

To edit a row of data already existing with a table, begin by invoking the BeginEdit method and putting the DataRow object into edit mode. When in edit mode, events are suspended and applications are free to make multiple edits without triggering any validation rules. Next, set values to the different columns. Finally, invoke the EndEdit method to end editing. Alternatively, if an application needs to revert to the DataRow's original state before editing, invoke the CancelEdit method to clear all changes:

 row.BeginEdit(); row["Name"] = "Simon West"; row.EndEdit(); 

To delete a row of data from a table, identify the row to be deleted by its zero-based array index. Next, invoke the Delete method to mark the row for deletion. Finally, invoke the AcceptChanges method provided by the table object to commit the deletion. Alternatively, to unmark a row for deletion, invoke the RejectChanges method instead of the AcceptChanges method:

 table.Rows[intRowID].Delete(); table.AcceptChanges(); 

A DataRow object exists in one of five states as defined by the DataRowState enumeration. Knowing which rows are in which state provides an application with more control over how data can be processed . For example, when an application is about to close, it can lose all records that have changed and prompt the user to either save or discard them. Obtain the state value by checking the RowState property of a DataRow. Table 2-1 describes these DataRow states.

Table 2-1: DataRow States

DATAROWSTATE	DESCRIPTION
Deleted	This row has been deleted and no longer exists.
Detached	This row has been created but not associated with any DataTable.
Modified	This row has been modified, but the changes have not been accepted.
New	This row has been associated with a table.
Unchanged	This row has not been changed since last accepted.

Another approach to reading from or writing to a DataRow object is to work with the ItemArray property. This approach lets the application set and get values within a DataRow object based upon column positions rather than column names.

Working with DataRelations

A DataRelation defines the relationship between multiple DataTable objects within a single DataSet object. An application can use a DataRelation to navigate from one table to the next and to return parent or child rows from a related table. Typically, relationships bind a DataTable object with a validation lookup table. To create a new DataRelation, all that is required is a name for the relation and a list of columns that will be bound together. Once a relation is added to a DataSet object, a UniqueConstraint is associated with the parent table and a ForeignKeyConstraint is automatically associated to the child table:

 dataset.Relations.Add( "IssueType",     custDS.Tables["Dat_Issue"].Columns["TypeID"],     custDS.Tables["Val_IssueType"].Columns["TypeID"] ); 

In this case, the code created a new relation, named IssueType , to bind the Val_IssueType table to the Dat_Issue table. The Val_IssueType table is the parent table, and the Dat_Issue table is the child table. Both tables are bound by the TypeID column. When a relationship between parent and child tables is defined, invoking GetChildRows will return all child rows based on a specific column value:

 DataRelation relation = dataset.Relations.Add( "IssueType",     custDS.Tables["Dat_Issue"].Columns["TypeID"],     custDS.Tables["Val_IssueType"].Columns["TypeID"] ); foreach( DataRow row in dataset.Tables["Dat_Issue"].Rows ) {     Console.WriteLine( row["TypeID"] );     foreach( DataRow childrow in row.GetChildRows( relation ) )         Console.WriteLine( childrow["TypeID"] ); } 

Creating Stored Procedures

Implementing the reference DataSet object begins with creating the stored procedures. The process for creating them is the same as described earlier. The only difference should be in the naming convention. It is important to enter the stored procedure names consistently in the naming convention specified by the data access framework:

 ref_[table_types][action] 

This can apply to the Val_IssueType table by defining a query stored procedure named ref_IssueTypeSelect .

Creating a DataSet Object

With the database tables defined and the stored procedures in place, it is time to create the DataSet object. From the design view of the data access component, select the sqlDataAdepter object or add one from the Toolbox if one does not exist. Next, select Generate Dataset from its context menu. The Generate Dataset dialog box appears, as shown in Figure 2-11.

Figure 2-11: The Generate Dataset dialog box

Select the New radio button and enter ReferenceDataSet as its new name. Select all reference tables that should be added to the DataSet object. Select the option to add the DataSet to the designer and click the OK button. A new object representing the DataSet object, named referenceDataSet1 , appears in the designer. For clarity and consistency, rename this DataSet object to _IssueTrackerReference .

Filling a DataSet Object

You fill a DataSet object with structure and values from the database with the help of a DataProvider object. Each DataProvider includes a DataAdapter object with a Fill method that defines a table schema and populates a DataSet. You can replace the data access component's default constructor with one that uses a DataAdapter object to fill the DataSet object using multiple stored procedures:

 public DataComponent() {     string strStoredProc;     SqlCommand command;     SqlDataAdapter adapter;     InitializeComponent();     //load IssueType types     strStoredProc = "ref_IssueTypeSelect";     command = new SqlCommand( strStoredProc, Connection );     command.CommandType = CommandType.StoredProcedure;     adapter = new SqlDataAdapter( command );     adapter.Fill( _IssueTrackerReference, "Val_IssueType" );     //load Priority types     strStoredProc = "ref_PrioritySelect";     command = new SqlCommand( strStoredProc, Connection );     command.CommandType = CommandType.StoredProcedure;     adapter = new SqlDataAdapter( command );     adapter.Fill( _IssueTrackerReference, "Val_Priority" );     //load Status types     strStoredProc = "ref_StatusSelect";     command = new SqlCommand( strStoredProc, Connection );     command.CommandType = CommandType.StoredProcedure;     adapter = new SqlDataAdapter( command );     adapter.Fill( _IssueTrackerReference, "Val_Status" );     //load MailMessage types     strStoredProc = "ref_MailMessageSelect";     command = new SqlCommand( strStoredProc, Connection );     command.CommandType = CommandType.StoredProcedure;     adapter = new SqlDataAdapter( command );     adapter.Fill( _IssueTrackerReference, "Val_MailMessage" );     //load Report types     strStoredProc = "ref_ReportsSelect";     command = new SqlCommand( strStoredProc, Connection );     command.CommandType = CommandType.StoredProcedure;     adapter = new SqlDataAdapter( command );     adapter.Fill( _IssueTrackerReference, "Val_Reports" );     return; } 

The DataComponent object constructor begins by initializing the component's connections, commands, and adapters. Next, it specifies each stored procedure that ties to reference data and associates each with a SqlCommand object. The SqlCommand object is supplied to the DataAdapter object, which invokes the appropriate stored procedure to load records into the DataSet object. The only remaining detail is an accessor method that returns the ReferenceDataSet object to a data component client:

 public ReferenceDataSet ReferenceDataSet {     get     {          return _IssueTrackerReference;     } }