Working with the DataTable Object


This section explores some of the ways that you can work with DataSet objects and their contents. This is by no means an exhaustive exploration of the capabilities of the DataSet “ you'll be seeing more as you proceed through this chapter and the next .

Here we're interested in looking at the ways that DataSet objects can be created and filled with data, extending the techniques introduced in the previous chapter. Along the way, you'll see:

  • How to fill a DataSet and the DataTable objects using code.

  • How to specify the columns in a table.

  • How to work with constraints, calculated columns, and default values.

  • How to add and remove rows in the tables in a DataSet .

  • How to use table and column mappings in a DataSet .

  • How to sort and filter the data in a table.

There are often occasions when you'd want to create a data table directly in code, rather than filling it from a data store. Under .NET, this is a useful technique when you want to insert values into a data store. You can create a DataTable object containing the new data within a DataSet , and then push the data into a database or other type of data store. Creating a DataSet dynamically is also a useful approach when you just want to package data up into a format that you can pass from one tier of an application to another, or use as the DataSource property of a data-bound control like a DataGrid , a listbox or some other type of ASP.NET list control.

The Creating and Populating a New DataTable example page ( create-new-datatable.aspx ) shown in Figure 9-8 demonstrates the basic techniques for creating a DataTable object. In this case, you aren't specifically creating a DataSet object “ you're just creating the DataTable as a standalone object and binding it to an ASP.NET DataGrid control to display the contents.

click to expand
Figure 9-8:

The Code to Create and Fill a New DataTable

Creating a new DataTable object is simply a matter of calling the constructor. You can give the table a name at the same time by providing this as the parameter to the constructor. Then you need to define the columns in the new table, as shown in the following code. Call the Add method of the Columns collection for the table, and specify the column name and the data type. The data types that you can use are listed in the System namespace of the class library, and are basically the same as the data types (such as Int16 , Int32 , Single , Double , Char , String , and Boolean ) available for use with all the .NET languages.

  Dim objTable As New DataTable("NewTable")     'define four columns (fields) within the table   objTable.Columns.Add("ISBN", System.Type.GetType("System.String"))   objTable.Columns.Add("Title", System.Type.GetType("System.String"))   objTable.Columns.Add("PublicationDate", _   System.Type.GetType("System.DateTime"))   objTable.Columns.Add("Quantity", System.Type.GetType("System.Int32"))  

Adding Data Rows to the Table

Having defined the four columns in your new table, you can now add some data rows. The following code shows how to define a variable to hold a DataRow object, and then call the NewRow method of the DataTable object. This creates the new row based on the schema for this table, and returns a reference to it.

You can then fill in the values for that row. Once complete, call the Add method of the table's Rows collection to add the row to the table, and repeat the process to add two more rows. Finish by assigning the DefaultView property of the new table to the DataSource property of the DataGrid that was placed in the HTML section of the page. A call to the DataBind method then causes the contents of your table to be displayed.

  'create a new row that matches the schema of the table   Dim objDataRow As DataRow   objDataRow = objTable.NewRow()     'and fill in the values   objDataRow("ISBN") = "1234567800"   objDataRow("Title") = "Professional Video Recorder Programming"   objDataRow("PublicationDate") = "2001-03-01"   objDataRow("Quantity") = 3956   objTable.Rows.Add(objDataRow)     'repeat for two more rows   objDataRow = objTable.NewRow()   objDataRow("ISBN") = "1234567801"   objDataRow("Title") = "Professional WAP Phone Programming"   objDataRow("PublicationDate") = "2001-06-01"   objDataRow("Quantity") = 29   objTable.Rows.Add(objDataRow)     objDataRow = objTable.NewRow()   objDataRow("ISBN") = "1234567802"   objDataRow("Title") = "Professional Radio Station Programming"   objDataRow("PublicationDate") = "2001-04-01"   objDataRow("Quantity") = 10456   objTable.Rows.Add(objDataRow)     dgrResult.DataSource = objTable.DefaultView   dgrResult.DataBind() 'and bind (display) the data  

Adding a Table to a DataSet

In the previous example, you created a standalone DataTable object purely to be able to use it to populate an ASP.NET DataGrid (and, of course, to demonstrate the technique). However, a physical DataTable object cannot exist alone, and must be part of a DataSet . What happened in the previous example was that a DataSet was created automatically behind the scenes and your new table is part of that DataSet .

However, sometimes you may want the table to be part of an existing, or an explicitly created new DataSet object. You can create a new instance of a DataSet object from scratch as follows :

  Dim objDataSet As New DataSet("BooksDataSet")  
Note

The parameter is the name of the new DataSet , and is optional.

Alternatively, you can use an existing DataSet that is empty, or that already has some tables in existence. Once you've got a reference to the DataSet object, just declare a variable to hold a DataTable object and set it to the result of a call to the Tables collection's Add method:

  Dim objTable As DataTable   objTable = objDataSet.Tables.Add("NewTable")   'populate the DataTable with the required values here   ....  

Managing Constraints and Default Values

If the DataSet object is going to be any use as a package that can be used to store and transport disconnected data, you need the ability to exert fine control over the structure and content of that DataSet . You saw in the previous chapter how to add relationships between the tables in a DataSet to check and enforce referential integrity.

You've also seen how you can add tables to a DataSet and fill them with data. In earlier examples, you did this simply by filling them from an existing data source, for example with a SQL SELECT statement that returns a rowset from a relational database.

And in the previous example, you saw how to do the same directly, using code. The data in that example was hardcoded into the page, but could just as easily have come from user input, or from processing data collected from some other kind of data store “ perhaps one that doesn't support the SQL-based data access methods you've been using so far.

When you fill a table in a DataSet from an existing data source using the Fill method of a DataAdapter object, information about each column's data type is automatically collected from the data source and added to the table. If you use the DataAdapter object's FillSchema method first, the column constraints (that is, primary keys, default values, unique values, nullability, and so on) are also added to the table. So, if a column in the original source table in the database is an integer data type that does not accept Null values, the table in the DataSet will exhibit the same properties.

Of course, when you create tables in a DataSet using code (or if the FillSchema method was not used), you have to specify all these extra properties yourselves . The example page shown in Figure 9-9, and the following example, demonstrates how this can be done. The Adding Expressions, Defaults and Constraints to a Table example ( column-constraints.aspx ) shows how you can create a table that has nonnullable columns, default column values, and calculated columns (that is, columns that use an expression based on other column values as their value source).

click to expand
Figure 9-9:

The example demonstrates several ways that you can add properties to the columns. The kBookKey column is an AutoNumber or Auto-Increment (IDENTITY ) column that has 1000 as the seed value and 10 as the increment value, so the values in this column are created automatically as you add new rows to the table. The ISBN column cannot contain Null values, and each value must be unique.

The StockQty and OrderedQty columns are Integer types and have a default value specified so that they will contain zero (rather than Null ) when a new record is created, if there is no value specified for one or both of these fields. Finally, the AvailableQty field is based on an expression. The value in this column is automatically adjusted when rows are added or edited to reflect the actual quantity available ( StockQty minus OrderedQty ).

The Code for This Example

The following code is similar to that of the previous example in that you first create a new DataTable object. However, in this case you should be able to access each of the new DataColumn objects as you add the columns to the table, so that you can set the extra properties. So, you also declare a variable that will be used to hold DataColumn objects as you create them:

  'create a new empty DataTable object   Dim objTable As New DataTable("NewBooks")     'declare a variable to hold a DataColumn object   Dim objColumn As DataColumn  
Creating the Columns

The first column created is the kBookKey auto-increment column with a data type of Int32 . In the code that follows, you can see the property settings made for specifying that it's an auto-increment column. Next, the ISBN column is added to the table. This is a String , which cannot contain Null , can be a maximum of ten characters , and must be unique. Then come two columns to hold the Title and the PublicationDate . As you don't need to set any extra properties on these, don't collect the column reference returned from the Add method of the Columns collection.

  'add an IDENTITY column named kBookKey   objColumn = objTable.Columns.Add("kBookKey", _   System.Type.GetType("System.Int32"))   objColumn.AutoIncrement = True   objColumn.AutoIncrementSeed = 1000   objColumn.AutoIncrementStep = 10     'add a unique String column with max length 10 chars for the ISBN   objColumn = objTable.Columns.Add("ISBN", System.Type.GetType("System.String"))   objColumn.AllowDBNull = False   objColumn.Unique = True   objColumn.MaxLength = 10     'add two String columns for the Title and PublicationDate   objTable.Columns.Add("Title", System.Type.GetType("System.String"))   objTable.Columns.Add("PublicationDate",System.Type.GetType("System.DateTime"))  
Specifying Column Default Values and Expressions

The next two columns, StockQty and OrderedQty , are of data type Int32 , which you want to automatically have a default value of zero if no value is specified for new rows. As shown in the following code, this is easy “ just set the DefaultValue property for each column.

The final column is a calculated column that shows the available stock quantity. Again, it's of type Int32 . To make it a calculated column, just set the Expression property to a string that contains the expression to evaluate for each row:

  'add columns for stock and order quantities with default values of zero   objColumn = objTable.Columns.Add("StockQty", _   System.Type.GetType("System.Int32"))   objColumn.DefaultValue = 0   objColumn = objTable.Columns.Add("OrderedQty", _   System.Type.GetType("System.Int32"))   objColumn.DefaultValue = 0     'add a column containing an expression showing the quantity availability   objColumn = objTable.Columns.Add("AvailableQty", _   System.Type.GetType("System.Int32"))   objColumn.Expression = "[StockQty]  [OrderedQty]"  

Note that column names containing special characters ( .~()#\/=><+-*%&^'"[] ) or spaces must be enclosed in square brackets. Doing this even when not actually required probably makes more complex expressions easier to read.

Note

If any of your column names contains a closing square bracket , you must escape it with a backslash character; for example, a column named Tax[Basic]Value would be expressed as [Tax[Basic\]Value] .

Adding Data Rows to the New Table

The table schema is now complete, and you can add some rows. Although the technique is the same as the previous example, we've shown the code used in this example so that you can see how the end result compares to the values placed in the columns:

  Dim objDataRow As DataRow   objDataRow = objTable.NewRow()   objDataRow("ISBN") = "1234567800"   objDataRow("Title") = "Professional Video Recorder Programming"   objDataRow("PublicationDate") = "2001-03-01"   objDataRow("StockQty") = 3956   objDataRow("OrderedQty") = 450   objTable.Rows.Add(objDataRow)   objDataRow = objTable.NewRow()   objDataRow("ISBN") = "1234567801"   objDataRow("Title") = "Professional WAP Phone Programming"   objDataRow("PublicationDate") = "2001-06-01"   objDataRow("StockQty") = 329   'note  no "OrderedQty" provided so default value used   objTable.Rows.Add(objDataRow)   objDataRow = objTable.NewRow()   objDataRow("ISBN") = "1234567802"   objDataRow("Title") = "Professional Radio Station Programming"   objDataRow("PublicationDate") = "2001-04-01"   'note  no "StockQty" provided so default value used   objDataRow("OrderedQty") = 1200   objTable.Rows.Add(objDataRow)  

The final step (not shown here) is to assign the DefaultView of the new table to a DataGrid object declared elsewhere in the page, so that the contents of the table are visible.

Specifying Primary and Foreign Keys

You've seen how to create a DataSet , add tables and relationships, fill the tables with data, and set several properties on each column. The one remaining aspect to consider is how to create columns that act as primary keys and foreign keys.

The Adding Primary Keys and Foreign Keys to a Table example ( key-constraints.aspx ) shown in Figure 9-10 illustrates the techniques:

click to expand
Figure 9-10:

When you open the page, you can see that data is being selected from two tables in your data store and used to fill a DataSet object. This automatically sets the appropriate data types for the columns. What it doesn't do is specify within the DataSet which columns are the primary key and foreign key in the tables.

Obviously, you can create a relationship between the tables within the DataSet . You can then use this relationship to navigate from parent row to child row and back when accessing the data in the tables, as demonstrated earlier in this chapter. But again, this does not change the table structure or specify which columns are the primary and foreign keys for each table.

So, you need to be able to create these keys yourself. Unlike the previous example, where you set the values of properties for each column object to define nullability and expressions, you have to create the primary and foreign key constraints as objects and add them to the Constraints collection of the table object.

This is because a primary or foreign key can encompass more than one column “ for example, the only possible primary key for the BookPrices table in the database is the combination of the ISBN code and the currency name. None of the individual columns has values that are unique within the table, and only a combination of columns can provide a unique key value. However, the ISBN column alone provides the link to the parent BookList table, and so it is specified as a foreign key in the BookPrices table.

You can see from the DataSet.Tables collection that there are two tables in the DataSet : Books and Authors . These are filled with a subset of values from the BookList and BookAuthors tables in the database. Also shown is the content of the Constraints collection for both the tables in the DataSet , followed by the data in the two tables.

The Code for the Primary and Foreign Keys Example

The example page uses exactly the same techniques as earlier examples to fill the two tables with data from the database, so you won't be looking at that part of the code again here. What we're interested in is how to specify the primary and foreign keys for the two tables within the DataSet , after they have been filled with data.

As shown in the code that follows, start by getting a reference to each of the tables and to the columns that will become the primary and foreign keys. For one or more columns to become the primary key, you first need to create a UniqueConstraint object for that table which refers to the column(s) in question, and make sure that they cannot contain Null values. The new constraint in our example is named Unique_ISBN .

Then you can specify that it is the primary key. As we discussed, the primary key could include more than one column, and so the way you specify it is through an array of columns (even though in this case there is only one). Create an array that will contain DataColumn objects, and set the first and only item (at index zero) to the column that will be the primary key in the Books table. Afterwards, you can simply specify this array as the PrimaryKey property of the table itself.

  'declare variables to refer to the DataTable and DataColumn objects   Dim objParentTable As DataTable = objDataSet.Tables("Books")   Dim objChildTable As DataTable = objDataSet.Tables("Authors")   Dim objParentColumn As DataColumn = objParentTable.Columns("ISBN")   Dim objChildColumn As DataColumn = objChildTable.Columns("ISBN")     'create a new UniqueConstraint object and add to Constraints collection   Dim objUnique As New UniqueConstraint("Unique_ISBN", objParentColumn)   objParentTable.Constraints.Add(objUnique)     'prevent the column from accepting Null values   objParentColumn.AllowDBNull = False     'create an array of columns containing this column only   Dim objColumnArray(0) As DataColumn   objColumnArray(0) = objParentColumn     'and set this array as the columns for the Primary Key of the table   objParentTable.PrimaryKey = objColumnArray  

The next step is to specify the primary key for the Authors table. In this case, the ISBN cannot be used on its own, as the values in it are not unique “ there could be more than one author row for each book row. While not strictly the correct approach (the ideal would be a unique author reference number), we've chosen , as an illustration, to use the combination of the ISBN and the last name in each row as the primary key for the table. However, it will suffice for this example where there are no authors with the same last name for any one book.

As shown in the following code, the process is the same as for the Books table, except that “ as there is more than one column in the primary key “ you have to add the other ( Lastname ) column to the array as well before assigning it to the table's PrimaryKey property:

  'now we can process the child table named "Authors"   'create an array of columns containing the ISBN and Lastname columns   ReDim objColumnArray(1)   objColumnArray(0) = objChildColumn 'the ISBN column   objColumnArray(1) = objChildTable.Columns("Lastname")     'prevent either of these columns containing Null   objColumnArray(0).AllowDBNull = False   objColumnArray(1).AllowDBNull = False     'set this column array as the primary key   objChildTable.PrimaryKey = objColumnArray  
Creating the Foreign Key Constraint

The foreign key constraint can now be added to the child table named Authors . This is just the ISBN column, which forms the link between the two tables in the DataSet . The code that follows shows how to create a ForeignKeyConstraint object (named here FK_BookAuthors ), and specify the parent and child columns to which it applies.

You can then specify the other properties that apply to a foreign key. In this case, you're specifying that any deletes should cascade; in other words, deleting a row in the parent table will automatically delete all matching child rows. Also specify that any updates to the primary key value in the parent table should be cascaded to all matching child rows; the value of the foreign key in each matching row will be changed to the new value of the parent row's primary key (the Rule enumeration will be discussed later in this section). Finally, this constraint is added to the table's Constraints collection:

  'create a new ForeignKeyConstraint object   Dim objFKey As New ForeignKeyConstraint("FK_BookAuthors", _   objParentColumn, objChildColumn)     'set the "update" properties   objFKey.DeleteRule = Rule.Cascade   objFKey.UpdateRule = Rule.Cascade     'and add it to the Constraints collection   objChildTable.Constraints.Add(objFKey)  
Displaying the DataSet Contents

Most of the code that displays the contents of the DataSet object is the same as used in previous examples, so it is not repeated here. You can examine the sourcecode for this (and any other) page using the [view source] link at the bottom of the page. However, notice how the contents of the Constraints collections for the two tables are displayed. As these are collections, you can bind them directly to the DataSource of a couple of ASP.NET DataGrid controls declared within the HTML section of the page:

  'bind the collections of Constraints to DataGrids on the page   dgrBookCons.DataSource = objDataSet.Tables("Books").Constraints   dgrBookCons.DataBind()   dgrAuthorCons.DataSource = objDataSet.Tables("Authors").Constraints   dgrAuthorCons.DataBind()  

And you can see the new foreign key constraint, and the primary key constraints created earlier for this table and the Books table, in the Constraints collections that are displayed in the page (repeated in Figure 9-11):

click to expand
Figure 9-11:
Note

Notice that we haven't explicitly specified a named UniqueConstraint object for this table, so it has the default name Constraint1 .

The DeleteRule and UpdateRule Property Values

You used a couple of values from the Rule enumeration in the code shown for creating the ForeignKeyConstraint , to specify how deletes and updates should be handled for related child rows in the Authors table, by setting the DeleteRule and UpdateRule properties of a ForeignKeyConstraint instance.

In most cases like this, to provide the highest level of integrity maintenance for your data, you'll use Cascade . Other choices will leave unlinked (orphan) rows in the child table. However, depending on how you want to edit and manage your data (particularly when doing bulk updates), you may prefer to specify a different setting and perform manual integrity checks afterwards. The values of the Rule enumeration are summarized in the following table:

Value

Description

Cascade

Updates to the primary key value in the parent table are copied to the foreign key in all linked child rows. Deleting a parent row deletes all linked child rows.

SetDefault

Updates to the primary key value in the parent table or deletion of a parent row both cause the foreign key in all linked child rows to be set to its default value.

SetNull

Updates to the primary key value in the parent table or deletion of a parent row both cause the foreign key in all linked child rows to be set to Null .

None

Updates to the primary key value in the parent table or deletion of a parent row have no effect on child rows.

Adding, Modifying, Removing, and Deleting Rows

You've now seen all the important techniques available for building DataTable objects within a DataSet , and filling them with data.

Next we'll confirm just how easy it is to add and edit the data in your DataSet tables, and then show how you can delete and/or permanently remove existing rows.

Adding Rows to a DataTable

Adding rows to a DataTable was demonstrated in several of the previous examples. The NewRow method of the DataTable object returns a new empty DataRow object for the table. After filling in the values, use the Add method of a table's Rows collection to add the new row.

At a minimum, you must provide appropriate (legal) values for any primary and foreign keys in the table, and for any columns that cannot accept Null . Any other columns you don't set a value for will be Null when the row is added to the table (unless, of course, they have a default value constraint assigned to them):

  objDataRow = objTable.NewRow()   objDataRow("ISBN") = "1234567801"   objDataRow("Title") = "Professional WAP Phone Programming"   objDataRow("PublicationDate") = "2001-06-01"   objDataRow("Quantity") = 329   objTable.Rows.Add(objDataRow)  
Adding Rows with an Object Array

You can also add a row to a table using an array of the basic Object types. As shown in the following code, you can simply create a one-dimensional array to hold the correct number of column values, fill in the values, and call the Add method of the Rows collection with the array as the single parameter:

  'add a new row using an array of values   Dim objValsArray(3) As Object   objValsArray(0) = "1234567900"   objValsArray(1) = "Impressionist Guide to Painting Computers"   objValsArray(2) = "05-02-2002"   objValsArray(3) = 150   objTable.Rows.Add(objValsArray)  

Editing Values in a DataTable

To change the contents of a row in a table, you can simply access the row through the table's Rows collection, and access the column through the collection of items in the DataRow object that represents that row, as shown in the following code:

  objTable.Rows(0)("Title") = "Amateur Theatricals for Windows 2000"   objTable.Rows(2)("PublicationDate") = "01-01-2002"   objTable.Rows(5)("ISBN") = "200000000"  
Note

Remember that the first row in the table is at row index zero. And if you specify a row index that is greater than the number of rows in the tables minus one (a row that is past the end of the table), you'll obviously get an error.

Using the BeginEdit, CancelEdit, and EndEdit Methods

An alternative technique is to use the BeginEdit , EndEdit , and CancelEdit methods of the DataRow object. Unlike the previous technique of just referencing the column and poking a new value into it, you can perform a controlled update to several values in a row without the values being immediately persisted to the row.

The BeginUpdate method effectively creates a copy of the row so that all the changes are made to this copy rather than to the original row. This means that all the updates made to any of the columns can be cancelled with a call to the CancelEdit method of that row, whereupon the original row is unchanged. To accept all the changes, effectively replacing the original row with the updated row, you can simply call the EndEdit method.

The Editing Existing Values in the Rows of a DataTable example page ( edit-rows.aspx ) demonstrates this technique in action. After filling the Books table in a DataSet object with some rows from your database, as shown in Figure 9-12, it changes the ISBN value of the first row but then calls the CancelEdit method. Next it changes the value again, but this time calls the EndEdit method. You can see only in this case is the value persisted into the table.

click to expand
Figure 9-12:
The Code for the Example Page

After filling the table from the WroxBooks database as demonstrated several times previously, the code in this page creates a reference to the table and displays the original values of the rows in the first DataGrid control on the page (as shown in the code that appears next).

Then call BeginEdit and update the first row in the table, after which the current values in the next DataGrid control are displayed. Next, perform some arbitrary test (is the value greater than 1999999999 ) and based on this make a decision whether to keep the changes made to the row. Notice that the copy of the row that is being edited is referenced using the Proposed value of that row. We'll discuss this in more detail later on. In the first case, this test fails and so the CancelEdit method is executed. After this, the contents of the table are displayed again.

To demonstrate the effect of the EndEdit method, the next section of code repeats the whole process. This time it sets the value of the ISBN field to "1999999999" so that the subsequent test succeeds, and the EndEdit method is called. Again, the contents of the table are displayed during and after the edit process (see the following code):

  'get a reference to a DataTable in an existing DataSet   Dim objTable As DataTable = objDataSet.Tables("Books")     'assign the DataTable's DefaultView object to the DataGrid control   dgrResult1.DataSource = objTable.DefaultView   dgrResult1.DataBind() 'and bind (display) the data     'now edit the first row   Dim objRow As DataRow = objTable.Rows(0)   objRow.BeginEdit()     'change some of the values in the row   objRow("ISBN") = "2000000000"   objRow("Title") = "Professional Video Recorder Programming"   objRow("PublicationDate") = "2001-03-01"     'display the edited values   dgrResult2.DataSource = objTable.DefaultView   dgrResult2.DataBind()     'now check if the values are valid   If objRow("ISBN", DataRowVersion.Proposed) > "1999999999" Then   objRow.CancelEdit()   Else   objRow.EndEdit()   End If     'display the values after canceling the update   dgrResult3.DataSource = objTable.DefaultView   dgrResult3.DataBind()     'now repeat edit, but this time using value that will pass the test   objRow.BeginEdit   objRow("ISBN") = "1999999999"   objRow("Title") = "Professional Video Recorder Programming"   objRow("PublicationDate") = "2000-10-10"     'display the edited values   dgrResult4.DataSource = objTable.DefaultView   dgrResult4.DataBind()     'now we can check if the values are valid   If objRow("ISBN", DataRowversion.Proposed) > "1999999999" Then   objRow.CancelEdit   Else   objRow.EndEdit   End If     'display the values after accepting the update   dgrResult5.DataSource = objTable.DefaultView   dgrResult5.DataBind()  
The Original, Current, and Proposed Column Values

In the previous code, notice a special syntax used when accessing the value of a column:

  If objRow("ISBN", DataRowVersion.Proposed) > "1999999999" Then ...  

As you'll see in more detail in the next chapter, every column in every row of a table maintains three values for that item. These values are defined in the DataRowVersion enumeration, shown in the following table, and are used to help maintain concurrency when updating data:

Value

Description

Original

The value that was in the column when the DataTable was created and filled with data. It is compared to the value in the original database table when an update is performed, to see if another user or process has changed the value since the DataTable data was created.

Proposed

The proposed value for this column after changes have been made following BeginEdit , but before EndEdit, CancelEdit , AcceptChanges or RejectChanges has been executed.

Current

The actual column value after changes have been made to it, and after these changes have been accepted (after EndEdit or AcceptChanges has been executed).

The AcceptChanges and RejectChanges methods mentioned in the table are described next.

Accepting and Rejecting Changes in a Row, Table, or DataSet

As you saw earlier, you can access any of the three values that are stored for every column in every row of a table at any time to get the appropriate value for a comparison test, or to check whether values in a row have been changed or are in the process of being changed.

Apart from using the BeginEdit , EndEdit , and CancelEdit methods to manage updates to a table row, you can also use the AcceptChanges and RejectChanges methods. Their actions are self- explanatory, with AcceptChanges effectively calling EndEdit on any rows currently being edited, and RejectChanges effectively calling CancelEdit on any rows currently being edited.

As far as the DataRow is concerned :

  • After execution of the BeginEdit method, if you change the value in any column, the Current and Proposed values of all the columns become accessible. The Proposed value is the same as the Current value until you edit that particular column.

  • After execution of the EndEdit method, the Current value for each column is replaced by the Proposed value.

  • After execution of the CancelEdit method, the Proposed value is discarded and the Current value is unchanged.

  • After execution of the AcceptChanges method, the Original value for each column is replaced by the Current value.

  • After execution of the RejectChanges , the Current value is discarded and the Original value is unchanged.

Notice that the effects of the AcceptChanges and RejectChanges methods are subtly different from BeginEdit , EndEdit , and CancelEdit . The AcceptChanges and RejectChanges methods affect the Current and the Original values (rather than the Current and Proposed values).

The AcceptChanges and RejectChanges methods can also be used at DataTable - and DataSet -level. After execution of the DataTable (rather than the DataRow ) object's AcceptChanges method, the Original value for every column in all rows in the table is set to the same as the Current value. After execution of the DataSet object's AcceptChanges method, the Original value for every column in every row in all tables in the Dataset is set to the same as the Current value.

It's important to not call these methods on a DataSet or a DataTable if you intend to update the original source data from the DataSet object, as it depends on the difference between the Original and Current values to be able to correctly detect any concurrency errors. The next chapter looks at this topic in detail.

The RowState Property of the DataRow Object

Each row in a table exposes another useful property named RowState . This is related to inserting, editing, and deleting rows in a table, and provides a useful indication of the current state of each row. The DataRowState enumeration values are summarized in the following table:

Value

Description

Unchanged

No changes have been made to the row since it was created or since the last call to the AcceptChanges method of the row, table, or DataSet .

Added

The row has been added to the table and AcceptChanges has not yet been executed.

Modified

At least one value or property of the row has been changed since the last call to the AcceptChanges method of the row, table, or DataSet .

Deleted

The row has been deleted from the table using the Delete method and AcceptChanges has not yet been executed.

Detached

The row has been created with the NewRow method but has not yet been added to the table with the Add method. Hence, it is not actually classed as being a row within that table.

You can access the RowState property at any time to see the state of any row. However, it is most useful when you come to update the original source data. You'll see this in the next chapter.

Deleting and Removing Rows from a DataTable

Deleting a row from a table is easy “ all you do is call the Delete method of the DataRow object you want to delete. You can specify the index of the row to delete within the Rows collection:

  'delete first and third rows in table referenced by objTable   objTable.Rows(0).Delete()   objTable.Rows(2).Delete()  

Or you can use a reference to the actual DataRow object you want to delete:

  objThisRow.Delete()   objOtherRow.Delete()  

The deleted rows remain in the table. The Delete method just sets the RowState property to DataRowState.Deleted (as you saw in the previous section). However, the next time you call AcceptChanges for the table, or for the DataSet object that contains the table, the row is removed from the table. This means that you can undelete rows simply by calling RejectChanges instead of AcceptChanges .

Thus, you can write code to delete some rows (or update and insert rows for that matter) in a table, and then carry out some comparison tests to decide whether to accept or reject all the changes in one go. Of course, (as you saw a little earlier) you can access the appropriate DataRowVersion for each column, as you do so to get the Original , Current , or Proposed value.

Removing Versus Deleting Rows

Removing a row from a table is an alternative and entirely different process from deleting a row. When you execute the Remove method, you immediately and irretrievably remove the row from the table in the DataSet . It isn't marked as deleted “ it just disappears from the table. As a result, the row indices change to reflect the new row positions as they all shuffle up to fill the gap left by the removed row.

Notice the difference in syntax, as shown in the following code. The Delete method is a member of the DataRow object. The Remove method is a member of the Rows collection:

  'remove the third row from the table   objTable.Rows.Remove(2)     'using the Remove method on row 2 (rather than marking it as deleted   'with the Delete method) means that the next row then becomes row 2   'so, to remove the next row from the table as well we repeat the use of   objTable.Rows.Remove(2)  
Note

If you intend to use the DataSet to update the original data store, avoid using Remove to delete rows. Always use the Delete method so that the rows remain in the table but are marked as being deleted. These deletes will then be made in the original data source when you call the Update method.

To see how the Delete and Remove methods work, you can try the example Removing versus Deleting Rows in a DataTable ( remove-delete-rows.aspx ). Figure 9-13 shows this example page in action:

click to expand
Figure 9-13:
The Code for the Deleting versus Removing Rows Example

The code in this example is relatively straightforward. Start by creating a new DataSet and inserting three rows into it using the same kind of code as in earlier examples. All these rows will now have a RowState property of DataRowState.Added , so call the AcceptChanges method to fix (accept) these changes “ which updates the RowState property of all the rows to DataRowState.Unchanged .

Then, after displaying the contents of the table in the first DataGrid control, call the Delete method on the second row, and then display the contents again in the second DataGrid control. The RowState property of the deleted row is set to DataRowState.Deleted and it disappears from view. However, the next line of code calls the RejectChanges method of the table, and then displays the contents again in the third DataGrid control. The RowState property of the deleted row is set back to DataRowState.Unchanged and it reappears in the table.

  'create a new empty Table object   Dim objTable As New DataTable("NewTable")   ... fill table with three new rows using code here ...     'call AcceptChanges to accept the changes to the table so far   objTable.AcceptChanges()     'assign the DataTable's DefaultView object to the DataGrid control   dgrResult1.DataSource = objTable.DefaultView   dgrResult1.DataBind() 'and bind (display) the data     'now Delete the second row and display the contents again   objTable.Rows(1).Delete()   dgrResult2.DataSource = objTable.DefaultView   dgrResult2.DataBind()     'call RejectChanges to restore deleted row and display contents again   objTable.RejectChanges()   dgrResult3.DataSource = objTable.DefaultView   dgrResult3.DataBind()  

Next, call the Remove method of the Rows collection of the table, specifying the second row as the one to be removed. Then display the contents of the table again in the fourth DataGrid control to show that it has been removed. Finally, call the RejectChanges method of the table and display the rows again in the final DataGrid control. However, this time, the row does not reappear. It has been permanently removed from the table and cannot be restored:

  'now Remove the second row from the table   'note that this is a method of the Rows collection not the Row object   objTable.Rows.Remove(1)   dgrResult4.DataSource = objTable.DefaultView   dgrResult4.DataBind()     'call RejectChanges  the deleted row is not restored   objTable.RejectChanges()   dgrResult5.DataSource = objTable.DefaultView   dgrResult5.DataBind()  

Working with DataTable Events

The DataTable object exposes a series of events that you can use to monitor changes to the content of a table in a DataSet . The ColumnChanging event is raised for a column in a row that is being edited, before the change is applied to that column (allowing the change to be cancelled). The ColumnChanged event is raised after the column has been changed and the change has been persisted to the column.

Some events occur for the row as a whole, rather than for each column in a row. The RowChanging and RowChanged events are raised when the content of any row in the table is changed “ the first event occurring before the change is applied to the row (allowing the change to be cancelled) and the second event occurring after the change has been persisted in the table.

Finally, there are two events that occur when a row is deleted from a table. The RowDeleting event occurs before the row is deleted, allowing the deletion to be cancelled, and the RowDeleted event occurs after the row has been deleted from the table. We don't have room to demonstrate all these events “ however, we will show you an example of how they can be used.

Using the RowUpdated Event

The Validating Edits in a Table with the RowUpdated Event example page ( update-check-errors.aspx ) demonstrates how you can use the RowUpdated event of a DataTable object to validate the values that are entered into each row. Code in this page fills a DataSet object with data from the sample database and then changes the values in two rows. Then, as shown in Figure 9-14, it displays the changed rows in a DataGrid .

click to expand
Figure 9-14:

At the bottom of the page, you can see that two errors have been reported . You placed an event handler in the page that detects when a row is updated, and it applies a couple of simple validation rules to the data in the row. Code in the page then checks the data for errors, and summarizes any it finds.

The Code for the Validating Edits Example

We've used the same techniques as most of the earlier examples to fill the DataSet with a Books table that contains details of several books from the WroxBooks database. We won't repeat this code here.

After filling the DataSet , call the AcceptChanges method to fix the current contents. Then set up the event handler you need to react to the RowChanged event. You can use the AddHandler method in Visual Basic, specifying the event you want to react to and the name of the event handler ( OnRowChanged ) that is defined elsewhere in the page. This event handler will be called when any row is updated, after the changes have been applied to it.

In the event handler code, you can apply your validation rules. If the update is not valid, you need to be able to flag this up, though not at this point. You want to be able to detect errors at some point in the future, perhaps before you submit the DataSet back to the database to update the original data. Of course, if you only wanted to flag up errors at the point when the users entered them, you could validate the values in the page where they were entering the data:

 'accept changes to "fix" current state of the DataSet contents objDataSet.AcceptChanges() 'set up event handler to react to changes to rows in the table Dim objTable As DataTable = objDataSet.Tables("Books") AddHandler objTable.RowChanged, _            New DataRowChangeEventHandler(AddressOf OnRowChanged) 

This VB.NET code adds an event handler to the DataTable , using the AddHandler statement. However, in C#, you just append the event handler to the event property, as follows:

  objTable.RowChanged += new DataRowChangeEventHandler(OnRowChanged);  
Row Errors and DataRow Actions

Each DataRow object has a RowError property, which is basically just a String value. You can write an error message to this string property, and then later detect if there is an error in the row (and retrieve this error message). Thus, all your event handler has to do if it detects an invalid value is write this error message to the RowError property of the row that has just been updated.

The event handler receives a DataRowChangeEventArgs object that contains details of the row that is being updated. This includes a reference to the DataRow object that has changed, and the Action that is being taken on the row. The Action can be one of the DataRowAction enumeration values shown in the following table:

Value

Description

Add

The row has been added to the table

Change

One or more column values in the row have been changed

Delete

The row has been deleted from the table

Nothing

The row has not changed

Commit

The changes to the row have been committed as part of a database transaction

Rollback

The changes to the row have been abandoned following the rolling back of a database transaction

The OnRowChanged Event Handler

As you can see from the preceding table, the RowChanged event is actually raised in several circumstances, not just when values in a row are modified. Therefore, in the example page event handler, you need to ensure that you react only to the event when the Action is DataRowAction.Change . The following is the complete code for the event handler you use. You can see that the code applies two simple validation tests and sets the appropriate value(s) in the RowError property if either or both of the tests fail:

  Sub OnRowChanged(objSender As Object, objArgs As DataRowChangeEventArgs)   'only react if the action is "Change"   If objArgs.Action = DataRowAction.Change Then   'validate a new title   If InStr(objArgs.Row("Title"), "Amateur") > 0 Then   objArgs.Row.RowError &= "'Amateur' is not a recognized " _   & "book series prefix"   End If   'validate a new publication date   If objArgs.Row("PublicationDate").DayOfWeek = 0 _   Or objArgs.Row("PublicationDate").DayOfWeek = 6 Then   objArgs.Row.RowError &= "Publication date must be a weekday"   End If   End If   End Sub  
Back to the Page_Load Event Code

Having seen what happens if an edit produces an invalid value in the row, let's go back to where you were in the Page_Load event code that runs when the page is opened from the server. So far, you've created the DataSet and filled a table within it, and set up the event handler that will validate the values in the rows as they are edited.

So, as shown in the code that follows, the next step is to perform some edits. As these edits are applied to the rows, the RowChanged event is fired and the validation tests are carried out in your OnRowChanged event handler. The values being used will cause a validation error in both rows, and so the RowError property will be set to a text description of the error for these rows (notice that the error message is appended to any value that might already be there).

Next you can display the contents of the changed rows in the page using a DataGrid as before. To display just the changed rows, create a DataView based on the table and then set the RowStateFilter property of the DataView to DataRowState.Modified (data row states will be looked in detail later in this chapter and in the next chapter):

  'change some records in the Books table   objTable.Rows(0)("Title") = "Amateur Theatricals for Windows 2000"   objTable.Rows(2)("PublicationDate") = "11-02-2001"     'declare a variable to hold a DataView object   Dim objDataView As DataView     'get DataView and set to show only modified rows   objDataView = objDataSet.Tables(0).DefaultView   objDataView.RowStateFilter = DataRowState.Modified     'display the contents of the modified rows   dgrResult.DataSource = objDataView   dgrResult.DataBind()  
Checking for Invalid DataRows

Finally, you can check the data to see if any of the rows contain an error. Thankfully, you don't have to query the RowError property of every row in all the tables in a DataSet to find out if there actually are any errors. Both the DataSet , and each of the DataTable instances it contains, provide the HasErrors property, which is True if any row in that DataSet or DataTable has a non-empty value for its RowError property.

In the example page, as shown in the following code, first query the HasErrors property of the DataSet to see if there are any errors at all. If so, iterate through the Tables collection looking at the HasErrors property of each DataTable because you then know that one (or possibly more if there are several tables) contains a row that has an error.

Once the search is narrowed down to the appropriate table, iterate through each row checking if it contains an error. You can use the HasErrors property of each row here, which is much faster than comparing the string value of the RowError with an empty string. When you find a row with an error, add the value of the RowError property to the output message string. And having completed this table, go round and look for the next table that contains errors:

  Dim strResult As String = "" 'to hold the result   'see if there are any update errors anywhere in the DataSet   If objDataSet.HasErrors Then   'check for errors in each table   Dim objThisTable As DataTable   For Each objThisTable In objDataSet.Tables   If objThisTable.HasErrors Then   strResult &= "One or more errors found in table '" _   & objThisTable.TableName & "'<br />"   'check each row in this table for errors   Dim objThisRow As DataRow   For Each objThisRow In objThisTable.Rows   If objThisRow.HasErrors Then   strResult &= "* Row with ISBN=" & objThisRow("ISBN") _   & " has error " & objThisRow.RowError & "<br />"   End If   Next 'row   End If   Next 'table   End If   outResult.InnerHtml = strResult 'display the result  

After extracting all the error messages, display them in a <div> at the bottom of the page. Figure 9-15 shows just this section of the page again so that you can see the results:

click to expand
Figure 9-15:

An alternative approach is to use the GetErrors method of each DataTable object to get an array of all the rows in that table that contain an error. This is also somewhat more efficient if there is only a small percentage of the total number of rows where an error has occurred.

The DataTable events you've seen here are very useful for validating data when you have a remoted DataSet object. There are also similar events that are raised by the DataAdapter object when you update the original data source from your DataSet . We'll look at these in the next chapter.

Using Table and Column Mappings

The next topic to consider when working with DataSet and DataTable objects is how to specify custom mappings for columns and tables. When you fill a table in a DataSet from a data source such as a relational database, you specify the name of the table within the database as the (optional) second parameter of the DataAdapter object's Fill method. For example, in this code the table is named Books :

  objDataAdapter.Fill(objDataSet, "Books")  

However, in this case you have no direct control over the names of the columns in the newly filled table. They automatically adopt the names of the columns returned by the stored procedure, table name, or SQL statement that fills the table. One way round this is to specify the column names within the SQL statement or stored procedure. For example, in a SQL statement, you can change the column names as follows:

  SELECT ISBN AS BookCode, Title AS BookTitle, PublicationDate AS Published   FROM BookList WHERE ISBN LIKE '18610053%'  

Now the data set returned by the SQL statement will have the new name (or alias) for each of the columns, and these will be used for the table's column names within your DataSet . Figure 9-16 shows the results viewed in SQL Server's Query Analyser tool:

click to expand
Figure 9-16:

However, it's convenient to be able to specify the names of both the tables and the columns within the DataSet independently. That way, you can create reusable code that automatically maps data to the correct column and table names. It also allows you to push the changes to the data back into the database by simply calling the Update method “ something you wouldn't be able to do if you renamed the columns in the SQL statement or stored procedure.

The Using Default and Specific Table and Column Mappings example page ( table-mappings.aspx ) demonstrates how you can use table and column mappings to manage the connection between the original table and its column names in the database with the table and column names in the DataSet . The result of running this page is shown in Figure 9-17:

click to expand
Figure 9-17:

The code and data used to build the DataSet shown in Figure 9-17 is the same as in many of the examples earlier in this chapter. However, the names of the tables and the columns in each table are different from the ones you got in the earlier examples. All these are defined as custom mappings, and the conversion from the default to the custom name is automatically applied when you load the data.

All the mappings for both table names and column names are stored in the DataAdapter object that is used to fill the DataSet . This means that you can create multiple DataAdapter objects for a DataSet object with different mappings, and use the one that meets the requirements of the current task.

Figure 9-18 shows the way that the DataAdapter uses a TableMappings collection to store individual mappings between tables ( TableMapping instances). Also, each TableMapping contains a collection of ColumnMapping instances that define the aliases relevant to that table:

click to expand
Figure 9-18:

Note that the two objects used for creating custom mappings are members of a different namespace than the other objects used so far. To be able to create these objects, you have to add a reference to the System.Data.Common namespace to your page:

  <%@Import Namespace="System.Data.Common" %>  

The Code for the Table and Column Mappings Example

The example page uses the now familiar code to access the database and extract subsets of rows from the BookList and BookAuthors tables. Along the way, it creates a new instance of a DataAdapter object to use for accessing the database and pushing the values into the DataSet . It's at this point, before calling the Fill method to actually fetch the data, that it creates custom mappings for the tables and the columns within them.

As shown in the code that follows, you create a default table mapping, so that any call to Fill that doesn't specify the name of the table (in the second parameter) will create a table named DefaultBookList . First declare a variable to hold a DataTableMapping object, and then call the Add method of the DataAdapter object's TableMappings collection.

The Add method takes two parameters: the name of the source table that will be specified in the Fill method, and the name to use for the new table in the DataSet instead of the source tablename. For a default mapping, use the value "Table" for the first parameter.

Note

The names used in the mappings are case-sensitive for the OleDb -prefixed and Odbc -prefixed objects, but not for the Sql -prefixed objects.

  'create a new DataAdapter object   Dim objDataAdapter As New OleDbDataAdapter()     'declare a variable to hold a DataTableMapping object   Dim objTableMapping As DataTableMapping     'add the default table mapping "Table"  this table name will be used   'if you don't provide a table name when filling the DataSet   objTableMapping = objDataAdapter.TableMappings.Add("Table", "DefaultBookList")  
Specifying the Column Mappings

Once you've created the TableMapping object, its ColumnMappings collection can be accessed to create the column mappings. The simplest syntax is to use the With construct, as shown in the following code. You can call the Add method of the ColumnMappings collection to create each column mapping, specifying the name of the column in the data source and the name you want that column to have in the table within the DataSet .

You can then do the same to create the custom mappings for the AuthorList table. There's already a default table mapping (using the value Table ), so you can only create specific table mappings now. First specify that a table in the data source named BookAuthors will create a table in the DataSet named AuthorList , and then finish up by specifying the column mappings from the source Authors database table to the new AuthorList table in the DataSet :

  'now add the column mappings for this table   With objTableMapping.ColumnMappings   .Add("ISBN", "BookCode")   .Add("Title", "BookTitle")   .Add("PublicationDate", "Published")   End With   'add a table mapping so that data from the table named "BookAuthors" in   'the database will be placed in a table named "AuthorList"   objTableMapping = objDataAdapter.TableMappings.Add("BookAuthors", _   "AuthorList")   'add the column mappings for this table   With objTableMapping.ColumnMappings   .Add("ISBN", "BookCode")   .Add("FirstName", "Forename")   .Add("LastName", "Surname")   End With  
Using the Column Mappings

One point to be aware of is that the mapped table and column names are now the ones that must be used for all operations with the DataSet and its contents. When you create a relationship between the tables, for example, use the mapped tablenames as shown in the following code:

  'create a Relation object to link the two tables   'note that it uses the new mapped table and column names   objRelation = New DataRelation("BookAuthors", _   objDataSet.Tables("DefaultBookList").Columns("BookCode"), _   objDataSet.Tables("AuthorList").Columns("BookCode"))  

The same applies, of course, when you access tables and columns in the DataSet in code. The only other issue is that you must be sure to specify the same table and column mappings in the DataAdapter that you use when you come to update the original data source. If not, the DataAdapter will not be able to associate the tables and columns in the DataSet with the ones in the source data store or database. You'll see how all this works in the next chapter.




Professional ASP. NET 1.1
Professional ASP.NET MVC 1.0 (Wrox Programmer to Programmer)
ISBN: 0470384611
EAN: 2147483647
Year: 2006
Pages: 243

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net