Lesson 4: Using DataSet Objects and Updating Data

Lesson 4: Using DataSet Objects and Updating Data

The DataSet is the central component in disconnected data-access architecture. The ADO.NET data architecture uses DataSet objects for the in-memory representation of data. DataSet objects can be filled by DataAdapter objects, or you can create new DataSet objects by reading data from XML or a flat-text file. You also can create new DataSet objects programmatically by adding new rows. The data held in DataSet objects can be bound to and displayed in the user interface, updated, and used to update a database through a DataAdapter. In this lesson, you will learn how to create DataSet objects programmatically, how to add DataRelation objects to your DataSet objects, and how to update databases from a DataSet.

After this lesson, you will be able to

• Describe how to create a DataSet programmatically or by reading a flat file

• Explain how to add Constraints and DataRelation objects to a DataSet

• Explain how to update a database through a DataAdapter

• Explain how to execute database transactions

• Design a scheme for handling database update errors

Estimated lesson time: 60 minutes

A DataSet is an in-memory representation of data. It is, in essence, a copy of part or all of a database, stored locally on the client machine. Accessing the data contained in a DataSet does not require interaction with the database, and changes to the data stored in the DataSet have no effect on the database until such time as the database is updated.

In the previous lesson, you learned how to fill a DataSet from a DataAdapter. Although this is the primary way to create DataSet objects, you might need to access data that is stored in some other way, such as in an array of values or in a comma-delimited text file. In this section, you will learn alternative ways of creating and filling DataSet objects.

Creating and Filling DataSet Objects Without a DataAdapter

You can create DataSet objects programmatically, just as you would create any other object. For example:

Visual Basic .NET

Dim myDataSet As New DataSet()

Visual C#

DataSet myDataSet = new DataSet();

The previous code example creates an empty DataSet. To populate a DataSet, you must first create a new DataTable and add it to the Tables collection, as follows:

Visual Basic .NET

Dim myTable As New DataTable() myDataSet.Tables.Add(myTable)

Visual C#

DataTable myTable = new DataTable(); myDataSet.Tables.Add(myTable);

Your new DataSet now has a single, empty table in its Tables collection. You can add multiple DataTable objects to the Tables collection of your DataSet programmatically.

Before you add actual data to the DataSet, you must create some kind of structure in each of the tables. You can add new columns programmatically by creating the columns and adding them to the Columns collection of the DataTable. When creating a new DataColumn, you can specify the column name. For example:

Visual Basic .NET

Dim AccountsColumn As New DataColumn("Accounts") myDataSet.Tables(0).Columns.Add(AccountsColumn)

Visual C#

DataColumn AccountsColumn = new DataColumn("Accounts"); myDataSet.Tables[0].Columns.Add(AccountsColumn);

You should create a column for each column of data you want to represent. Once all of your columns have been added to the DataTable, you can add DataRows. A DataRow is a collection of members that represents a row of a table in a database. You cannot directly instantiate a DataRow. Rather, you must call the NewRow method of the DataTable to which you are adding the DataRow. An example follows:

Visual Basic .NET

Dim myRow As DataRow myRow = myDataSet.Tables(0).NewRow()

Visual C#

DataRow myRow; myRow = myDataSet.Tables[0].NewRow();

Once the new DataRow has been created, you can fill it with members from whatever source you desire, be it a collection, an array, or user input. In the following code example, the DataRow is populated with the members of a collection of strings:

Visual Basic .NET

' This example assumes the existence of an ArrayList object named ' StringCollection Dim Counter As Integer For Counter = 0 to StringCollection.Count 1 myRow.Item(Counter) = StringCollection(Counter) Next

Visual C#

// This example assumes the existence of an ArrayList object named // StringCollection for (int i = 0; i < StringCollection.Count; i ++) { myRow[i] = StringCollection[i]; }

After the DataRow has been populated, you can add it to the Rows collection of the DataTable:

Visual Basic .NET

myDataSet.Tables(0).Rows.Add(myRow)

Visual C#

myDataSet.Tables[0].Rows.Add(myRow);

This process is repeated until all the members of the DataSet have been created and populated with data.

To create a DataSet programmatically

1. Declare and instantiate the DataSet.

2. Declare and instantiate one or more DataTable objects and add them to the Tables collection of the DataSet.

3. In each DataTable, declare and instantiate DataColumn objects to represent the columns of data in the table and add them to the Columns collection of the DataSet.

4. Create a new row of data using the DataTable.NewRow method.

5. Fill the new row with data.

6. Add the new row to the Rows collection of the DataTable.

7. Repeat Steps 4 through 6 until all of the data has been added.

Accessing Flat Files

In many legacy applications, data is stored in text files, sometimes called flat files. It might be necessary to read data from these files into an ADO.NET DataSet. Using the general scheme described in the previous section, you can read data from text files into a DataSet at run time. The System.IO namespace facilitates file access, and the methods exposed by the System.String class enable parsing the data contained in text files.

When reading data from a text file, you must first determine how the data is stored. Data stored in text files is usually separated by a common character called a delimiter. A delimiter can be a comma (,), a colon (:), a semicolon (;), or any other character. Rows of data are often stored as lines of text with a carriage return signifying the beginning of a new row. The number of entries in each row should correspond to the number of columns in the table.

Once the basic structure of the stored data has been determined, you can begin to construct your DataSet around it. You can use the techniques described in the previous section to programmatically construct your DataSet. You must create a single DataTable for each table of data you want to represent. Next, you should add the appropriate number of columns to your DataSet. Sometimes, the data in the file s first row is used for the column names. If this is the case, you should read the first line of the text file and name the columns programmatically.

A text file can be read with the System.IO.StreamReader class. This class can open a file and return the characters represented within it. Once the file has been opened, you can use the method of the System.String class to separate the data entries and add them to new data rows.

Because every flat file conceivably could have a different format, you must determine the correct procedure for accessing the data on an individual basis. The following code example demonstrates how to access data from a simple, common scenario: data stored in a text file where the rows are represented by lines and the entries are delimited by commas, a common format known as comma-separated values (CSV).

Visual Basic .NET

' This example assumes the existence of a text file named myFile.txt ' that contains an undetermined number of rows with seven entries ' in each row. Creates a new DataSet Dim myDataSet As New DataSet() ' Creates a new DataTable and adds it to the Tables collection Dim aTable As New DataTable("Table 1") myDataSet.Tables.Add("Table 1") ' Creates and names seven columns and adds them to Table 1 Dim Counter As Integer Dim aColumn As DataColumn For Counter = 0 to 6 aColumn = New DataColumn("Column " & Counter.ToString()) myDataSet.Tables("Table 1").Columns.Add(aColumn) Next ' Creates the StreamReader to read the file and a string variable to ' hold the output of the StreamReader Dim myReader As New System.IO.StreamReader("C:\myFile.txt") Dim mystring As String ' Checks to see if the Reader has reached the end of the stream While myReader.Peek <> 1 ' Reads a line of data from the text file mystring = myReader.ReadLine ' Uses the String.Split method to create an array of strings that ' represents each entry in the line. That array is then added as a ' new DataRow to Table 1 myDataSet.Tables("Table 1").Rows.Add(mystring.Split(","c)) End While

Visual C#

// This example assumes the existence of a text file named myFile.txt // that contains an undetermined number of rows with seven entries // in each row. Creates a new DataSet DataSet myDataSet = new DataSet(); // Creates a new DataTable and adds it to the Tables collection DataTable aTable = new DataTable("Table 1"); myDataSet.Tables.Add("Table 1"); // Creates and names seven columns and adds them to Table 1 DataColumn aColumn; for (int counter = 0; counter < 7; counter ++) { aColumn = new DataColumn("Column " + counter.ToString()); myDataSet.Tables["Table 1"].Columns.Add(aColumn); } // Creates the StreamReader to read the file and a string variable to // hold the output of the StreamReader System.IO.StreamReader myReader = new System.IO.StreamReader("C:\\myFile.txt"); string myString; // Checks to see if the Reader has reached the end of the stream while (myReader.Peek() != 1) { // Reads a line of data from the text file myString = myReader.ReadLine(); // Uses the String.Split method to create an array of strings that // represents each entry in the line. That array is then added as // a new DataRow to Table 1 myDataSet.Tables["Table 1"]. Rows.Add(myString.Split(char.Parse(","))); }

DataRelation Objects

A DataRelation object represents a relationship between two columns of data in different tables. For example, you might have a Customers table and an Orders table, each of which contains a CustomerID column. Each customer would be listed only once in the Customers table, but might be listed multiple times in the Orders table. The CustomerID in the Orders table specifies which customer in the Customers table placed the order. Thus, the CustomerID field of the Orders table directly refers to a particular row of data in the Customers field and can be used as a key to retrieve that row. Likewise, the CustomerID column in the Customers table can be used to retrieve any relevant rows in the Orders table. This is an example of a one-to-many relationship, where a unique identifier in one table might indicate multiple rows in another table. One-to-many is the most common type of data relationship. DataRelation objects are used to define these kinds of relationships.

You can use a DataRelation object to create this kind of relationship between two tables in a DataSet. The DataRelation objects of a particular DataSet are contained in the Relations property of the DataSet. A DataRelation is created by specifying the name of the DataRelation, the parent column, and the child column. In a typed DataSet, both columns must contain the same type. An example follows:

Visual Basic .NET

' Assumes the existence of two DataColumns, column1 and column2 Dim myRelation As New _ DataRelation("Data Relation 1", column1, column2)

Visual C#

// Assumes the existence of two DataColumns, column1 and column2 DataRelation myRelation = new DataRelation("Data Relation 1", column1, column2);

Once you have created the DataRelation, you must add it to the Relations collection of the DataSet before it becomes active. For example:

Visual Basic .NET

myDataSet.Relations.Add(myRelation)

Visual C#

myDataSet.Relations.Add(myRelation);

To create a new DataRelation

1. Declare and instantiate a new DataRelation object that specifies a parent column and a child column within the same DataSet. If you are using a typed DataSet, the two columns must contain the same type.

2. Add the new DataRelation to the Relations collection of the DataSet.

Retrieving Related Records

You can use a DataRelation to retrieve parent and child rows. Related rows are retrieved by calling the GetChildRows or GetParentRow method of a DataRow. These methods require a valid DataRelation object as a parameter. The GetChildRows method returns an array of rows that have a child relationship as defined by the DataRelation object. The GetParentRow method is similar, but it returns only a single row that has a parent relationship as defined by the DataRelation object. The following code example demonstrates how to call these methods. It assumes two tables called Customers and Orders that are related through a DataRelation named CustomersOrders. Both tables are contained within a DataSet called myDataSet.

Visual Basic .NET

Dim ChildRows() As DataRow Dim ParentRow As DataRow ' This returns all rows that have a child relationship to Row 1 of ' the Customers table as defined by the CustomersOrders DataRelation ChildRows = _ myDataSet.Tables("Customers").Rows(1).GetChildRows(CustomersOrders) ' This returns the row that has a parent relationship to row 5 of ' the Orders table as defined by the CustomersOrders DataRelation ParentRow = _ myDataSet.Tables("Orders").Rows(5).GetParentRow(CustomersOrders)

Visual C#

DataRow[] ChildRows; DataRow ParentRow; // This returns all rows that have a child relationship to Row 1 of // the Customers table as defined by the CustomersOrders DataRelation ChildRows = myDataSet.Tables["Customers"].Rows[1].GetChildRows(CustomersOrders); // This returns the row that has a parent relationship to row 5 of // the Orders table as defined by the CustomersOrders DataRelation ParentRow = myDataSet.Tables["Orders"].Rows[5].GetParentRow(CustomersOrders);

Constraints

DataRelation objects work closely with constraints. Constraints define the rules by which data is added to and manipulated in DataTables. There are two kinds of constraints: the UniqueConstraint and the ForeignKeyConstraint.

The UniqueConstraint specifies that a column or columns should have no duplicate entries. It is usually used to define a primary key for a table. The ForeignKeyConstraint defines the rules used to update child rows when a parent row is edited.

Constraints are created in the following general manner: a constraint of the appropriate type is instantiated and then added to the Constraints collection of the table that contains the constrained column.

Constraints are enforced only when the EnforceConstraints property of the DataSet is set to true.

Unique Constraints

Creating a UniqueConstraint is relatively easy. The easiest way to create UniqueConstraint is to set the Unique property of a DataColumn to True (true), like this:

Visual Basic .NET

myDataColumn.Unique = True

Visual C#

myDataColumn.Unique = true;

This creates a UniqueConstraint behind the scenes and adds it to the Constraints collection. You can also explicitly create a new UniqueConstraint that specifies a column to be unique and add it to the Constraints collection manually, as follows:

Visual Basic .NET

Dim myConstraint As New UniqueConstraint(myDataColumn) myDataTable.Constraints.Add(myConstraint)

Visual C#

UniqueConstraint myConstraint = new UniqueConstraint(myDataColumn); myDataTable.Constraints.Add(myConstraint);

Unique constraints also can be used to specify a multicolumn key. For example, you might have an Employees table where a FirstName column could contain duplicate entries and a LastName column could contain duplicate entries, but the combination of values in the FirstName and LastName columns must be unique. You can specify an array of columns that must contain a unique combined value with a UniqueConstraint. For example:

Visual Basic .NET

Dim myColumns(1) As DataColumn myColumns(0) = EmployeesTable.Columns("FirstName") myColumns(1) = EmployeesTable.Columns("LastName") Dim myConstraint As New UniqueConstraint(myColumns) EmployeesTable.Constraints.Add(myConstraint)

Visual C#

DataColumn[] myColumns = new DataColumn[2]; myColumns[0] = EmployeesTable.Columns["FirstName"]; myColumns[1] = EmployeesTable.Columns["LastName"]; UniqueConstraint myConstraint = new UniqueConstraint(myColumns); EmployeesTable.Constraints.Add(myConstraint);

Foreign Key Constraints

A ForeignKeyConstraint controls how child rows are affected when a parent row is updated or deleted. You can create a ForeignKeyConstraint by specifying a parent column and a child column. For example:

Visual Basic .NET

Dim myConstraint As New _ ForeignKeyConstraint(CustomersTbl.Columns("CustomerID"), _ OrdersTbl.Columns("CustomerID"))

Visual C#

ForeignKeyConstraint myConstraint = new ForeignKeyConstraint(CustomersTbl.Columns["CustomerID"], OrdersTbl.Columns["CustomerID"]);

Like UniqueConstraints, ForeignKeyConstraints are not active until added to the Constraints collection of the appropriate table. You should add a ForeignKeyConstraint to the Constraints collection of the parent table as follows:

Visual Basic .NET

CustomersTbl.Constraints.Add(myConstraint)

Visual C#

CustomersTbl.Constraints.Add(myConstraint);

A ForeignKeyConstraint contains three rules that can be enforced in regard to a parent-child relationship:

• UpdateRule.

  Is enforced whenever a parent row is updated

• DeleteRule.

  Is enforced whenever a parent row is deleted

• AcceptRejectRule.

  Is enforced whenever the AcceptChanges method of the DataTable to which the constraint belongs is called

Each rule is exposed as a property of the constraint and can be set to the values in Table 6.2.

Table 6-2. Constraint Property Value Settings

Value	Result
Cascade	Changes in the parent row are cascaded to the child rows.
None	When changes are made to the parent row, there is no effect on the child rows. This can result in child records containing references to invalid parent records.
SetDefault	The foreign key in the related child records is set to its default value (as established by the column s DefaultValue property).
SetNull	The foreign key in the child table is set to DBNull. This setting can result in invalid data in the child table.

NOTE
The AcceptRejectRule can be set only to values of Cascade or None. Cascade is the default value for each of these rules.

Editing and Updating Data

Data contained within a DataSet can be manipulated and edited in the client. Values in DataRow objects can be changed, new DataRow objects can be added to a DataSet, and DataRow objects can be deleted from a DataSet. No changes, however, are reflected in the database until the database is updated through the DataAdapter.

Every DataSet maintains two versions of itself. The current version, which holds the client copy of the DataSet and any changes that have occurred, and the original version, which holds the state the data was in when the DataSet was first filled. When the Update method of the DataAdapter is called, the original values are used to generate the UPDATE, INSERT, and DELETE commands that are used to perform the database update.

Editing Data

Data contained in a DataSet can be edited two ways: through data-bound controls in the user interface or programmatically. Changes made through data-bound controls are reflected in the relevant row. Data binding is discussed in greater detail in Lesson 5 of this chapter.

It is also possible to add values to a DataRow programmatically by setting the value of the appropriate DataRow item. The values represented by the Item property (in Visual Basic .NET) or the indexer (in Visual C#) correspond to each column of the row. An example follows:

Visual Basic .NET

' Because the item is the indexer for the DataRow object, ' you can access the item either by index or by column name myDataRow(2) = "Splunge" myDataRow("Customers") = "Winthrop"

Visual C#

// Because the item is the indexer for the DataRow object, you // can access the item either by index or by column name myDataRow[2] = "Splunge"; myDataRow["Customers"] = "Winthrop";

Each DataRow maintains two distinct states: an unedited version that contains the original values of the DataRow and an edited version. You can roll back changes made to the DataRow by calling the RejectChanges method at any time, as illustrated here:

Visual Basic .NET

myDataRow.RejectChanges()

Visual C#

myDataRow.RejectChanges();

If you want to commit any changes to the DataRow, you can call the AcceptChanges method. This has the effect of overwriting the original version of the DataRow with the edited version.

Visual Basic .NET

myDataRow.AcceptChanges()

Visual C#

myDataRow.AcceptChanges();

CAUTION
If you are managing your data access through a DataAdapter and intend to update your data source with your edits, you must not accept changes until you have called the DataAdapter.Update method. If you call the AcceptChanges method before calling the Update method, the DataAdapter will be unable to refer to the original version of the data and will fail to generate meaningful UPDATE commands.

You can determine the state of your DataRow by retrieving the RowState property. The RowState property can have one of five values, which are shown in Table 6.3.

Table 6-3. RowState Property Values

RowState Value	Meaning
Unchanged	The row is either in its original form or has not been modified since AcceptChanges was called.
Modified	The row has been edited since the last time AcceptChanges was called.
Added	The row is newly created and added to a DataRowCollection, and AcceptChanges has not been called.
Deleted	The row was deleted using the DataRow.Delete method.
Detached	The row was created but is not a part of any DataRowCollection.

DataTable and DataSet objects also expose RejectChanges and AcceptChanges methods, which reject or accept all of the changes in the DataTable or DataSet, respectively.

Updating the Database

Once the client copy of the data is ready to be copied back to the database, you can do so by calling the Update method of each DataAdapter. For example:

Visual Basic .NET

myDataAdapter.Update() myOtherDataAdapter.Update()

Visual C#

myDataAdapter.Update(); myOtherDataAdapter.Update();

This copies the changes in the client copy of the data to the database. You can also specify a particular DataSet, DataTable, or array of DataRows to act as the basis for the update. For example:

Visual Basic .NET

myDataAdapter.Update(myDataSet) myDataAdapter.Update(myDataTable) myDataAdapter.Update(myDataRows)

Visual C#

myDataAdapter.Update(myDataSet); myDataAdapter.Update(myDataTable); myDataAdapter.Update(myDataRows);

Transactions

At times, you might want to execute several updates in such a way that either all succeed or none succeed. A banking program is a common example. Consider a method that debits an account in one table and credits an account in a second table. If the debit operation is successful, but the credit operation fails, the results would be disastrous. Transactions can solve problems of this nature. A transaction is a set of related operations that execute as a unit. Either all are successful or none are.

You initiate a transaction by calling the BeginTransaction method of any open Connection object. This method returns a reference to the transaction. You must then assign this transaction to the Transaction property of each command involved in the transaction. Next you must execute each command in the transaction. If the result of each command is satisfactory, you can call Transaction.Commit to commit the changes to the database. If an error occurs, you can call Transaction.Rollback to roll back changes and address the error. The following code example demonstrates executing a transaction with an OleDbConnection named myConnection and two OleDbCommand objects named Update1 and Update2.

NOTE
To use data transactions, your underlying data source must support transactions.

Visual Basic .NET

' Transactions should be enclosed in a Try...Catch...Finally ' block to catch any exceptions that might occur Dim myTransaction As System.Data.OleDb.OleDbTransaction Try myConnection.Open() ' This creates a new transaction object and assigns it to ' myTransaction myTransaction = myConnection.BeginTransaction ' Adds Update1 and Update2 to the transaction. Update1.Transaction = myTransaction Update2.Transaction = myTransaction ' Executes Update1 and Update2 Update1.ExecuteNonQuery() Update2.ExecuteNonQuery() ' If no exceptions occur, commits the transaction myTransaction.Commit() Catch ex As Exception ' The transaction is not executed if an exception occurs myTransaction.Rollback() Finally ' Whether an exception occurs or not, the connection is then closed myConnection.Close() End Try

Visual C#

// Transactions should be enclosed in a Try...Catch...Finally // block to catch any exceptions that might occur System.Data.OleDb.OleDbTransaction myTransaction = null; try { myConnection.Open(); // This creates a new transaction object and assigns it to // myTransaction myTransaction = myConnection.BeginTransaction(); // Adds Update1 and Update2 to the transaction. Update1.Transaction = myTransaction; Update2.Transaction = myTransaction; // Executes Update1 and Update2 Update1.ExecuteNonQuery(); Update2.ExecuteNonQuery(); // If no exceptions occur, commits the transaction myTransaction.Commit(); } catch (Exception ex) { // The transaction is not executed if an exception occurs myTransaction.Rollback(); } finally { // Whether an exception occurs or not, the connection is then // closed myConnection.Close(); }

To execute multiple commands transactionally

1. Open the database connection.

2. Obtain a reference to a transaction by calling the Connection.BeginTransaction method.

3. Assign this transaction to the Transaction property of each command you want to execute in the transaction.

4. Execute each transaction.

5. Call the Transaction.Commit method to commit the transaction.

If you are using DataAdapter objects to facilitate interaction with the database, you still can use transactions, although the process is more complicated. To begin, you must create a transaction with the BeginTransaction method of an open connection. You must then assign that transaction to the Transaction property of the InsertCommand, UpdateCommand, and DeleteCommand of each DataAdapter that will be involved in the database update. You can then call the Update method of each Data Adapter and call the Commit or Rollback method of the transaction as necessary.

Handling Update Errors

When updating data, errors can occur from a variety of causes. For example, attempting to duplicate a primary key causes an error, as does attempting to update a database that has been modified by another user. Regardless of the source of the error, you must anticipate and plan for possible errors when updating rows.

Both SqlDataAdapters and OledbDataAdapters provide a RowUpdated event that fire after a row update has been attempted but before any exception has been thrown. You can write code in this event to handle any update errors that might occur without going through resource-intensive exception handling blocks.

The RowUpdated event provides an instance of SqlRowUpdatedEventArgs or OleDbRowUpdatedEventArgs, depending on the type of DataAdapter you are using. These event arguments provide information that can be used to determine the error that occurred and how to proceed. Table 6.4 summarizes some of the properties found in the SqlRowUpdatedEventArgs and OleDbRowUpdatedEventArgs classes.

Table 6-4. RowUpdatedEventArgs Properties

Property	Description
Command	Represents the command to execute when performing the update
Errors	Returns any errors generated by the data provider when the command executes
RecordsAffected	Returns the number of records affected by execution of the command represented by the Command property
Row	Returns the row that was updated
Status	Returns the UpdateStatus of the command

You can determine if an error occurred by examining the Status property of the event arguments in the RowUpdated event handler. The Status property has four possible settings:

• Continue.

  Means that the DataAdapter is to continue processing rows. If no errors have occurred, the Status property has this setting.

• ErrorsOccurred.

  Indicates that one or more errors occurred while attempting to update this row.

• SkipAllRemainingRows.

  Indicates that updates for the current row and any remaining rows should be skipped.

• SkipCurrentRow.

  Indicates that the update for the current row should be skipped, but the rest of the updates should proceed normally.

If an error occurs, the Status property will have a value of ErrorsOccurred. You can choose to handle the error in the RowUpdated event handler by setting the Status property to another value. If the Status property is set to SkipCurrentRow, the DataAdapter will skip the update for the row that caused the error and proceed normally with the rest of the updates. If the Status property is set to SkipAllRemaining Rows, the DataAdapter will abort the rest of the update. If the Status property is set to Continue, the DataAdapter will ignore the error and continue. Note that this can cause unpredictable results depending on the data source. If the Status property remains ErrorsOccurred, the exception will be thrown and forwarded to the application s exception handling procedure.

You can use the Errors property to obtain information about the error that occurred. The Errors property returns the exception that represents the error that occurred.

How you choose to handle database update errors depends largely upon the individual circumstances of your application. In some cases, you might want exceptions to move up to the application exception handling code, and in other cases you might want to handle errors in the RowUpdated event handler. The following code example demonstrates a simple scheme for handling update errors in the RowUpdated event handler of a SqlDataAdapter:

Visual Basic .NET

Private Sub myDataAdapter_RowUpdated(ByVal sender As Object, _ ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs) _ Handles myDataAdapter.RowUpdated ' Checks the event arguments Status property to see if an ' error occurred If e.Status = UpdateStatus.ErrorsOccurred then ' Informs the user that an error occurred and gives some ' information about it MessageBox.Show("An error of type " & e.Errors.ToString() & _ "occurred. Here is some additional information: " & _ e.Errors.Message) ' Skips the update for this row but proceeds with the rest e.Status = UpdateStatus.SkipCurrentRow End If End Sub

Visual C#

private void myDataAdapter_RowUpdated(object sender, System.Data.SqlClient.SqlRowUpdatedEventArgs e) { // Checks the event arguments Status property to see if an // error occurred if (e.Status == UpdateStatus.ErrorsOccurred) { // Informs the user that an error occurred and gives some // information about it MessageBox.Show("An error of type " + e.Errors.ToString() + "occurred. Here is some additional information: " + e.Errors.Message); // Skips the update for this row but proceeds with the rest e.Status = UpdateStatus.SkipCurrentRow; } }

Lesson Summary

• You can create DataSet objects and DataTable objects independent of DataAdapter objects and fill them programmatically.

• You can read and access text files using the System.IO.StreamReader class and parse them with methods of the String class.

• DataRelation objects represent parent-child relationships between columns of different tables. You can use DataRelation objects to enforce constraints and retrieve related rows of data.

• Constraints are rules that represent how data can be added to particular columns. There are two kinds of constraints: the UniqueConstraint and the ForeignKeyConstraint.

• A DataSet maintains two versions of data: the original version and a version that includes any modifications. When the Update method of the DataAdapter is called, the original version of the data is compared to the updated version and used to generate the commands needed for the update.

• You can execute multiple commands transactionally by obtaining a Transaction object from an open connection and assigning the Transaction property of the Command objects to that reference. After the commands have executed, all commands included in the transaction can be committed or rolled back.

• You can handle update errors in the RowUpdated event handler. You can determine if an error occurred by examining the event argument s Status property and you can handle errors by setting the Status property to an appropriate value.