Professional ASP.NET 1.1 [Electronic resources] نسخه متنی

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.

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") 

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).

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.

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:

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):

Figure 9-11:

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:

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" 

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.

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:

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:

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) 

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:

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 .

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:

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:

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:

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:

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:

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.

''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.