Professional ASP.NET 1.1 [Electronic resources]

Alex Homeret

نسخه متنی -صفحه : 244/ 77
نمايش فراداده

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

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

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:

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:

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.

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:

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:

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:

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.

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.