Using DataSets to Manage Updates to Databases

It is important to understand how the DataAdapter and DataSet process updates and how they store data while a user is working with it. Changes are managed at the DataRow level. When the DataAdapter.Update method is called, only the rows that have been added, changed, or marked for deletion are processed. The DataSet contains multiple versions of the data items. The original values (the values that were retrieved from the database when the DataSet was filled) are available until the AcceptChanges method is called. The new values that the user has entered (or changed) are available as well.

The DataRow versions go through a transition when the user begins to edit. The new data is considered the proposed value, but the current value (the one that is likely to be displayed) is still the same as the original value. At the end of the edit, the current value is replaced with the proposed value, but the original value is still available.

Note 

In Exercise 6.1, you will be working with the Windows forms DataGrid control to edit data. The control enables you to transition through the editing and updating phases transparently as you navigate the grid and make changes to data. It is also possible to control these states in your code by responding to objects’ events and calling BeginEdit and EndEdit methods. Having both the updated and original values of the data available is very useful. In Listing 6.2, you will see an example of how to retrieve the original value of a column.

These different versions of data exist only while the user is working with the data. After the AcceptChanges method or RejectChanges method is called, all values are set to an identical state. When AcceptChanges is called, all versions are set to the new, user-provided values, and the original values are no longer available. When RejectChanges is called, all user-provided values are discarded and the original values are restored.

The AcceptChanges and RejectChanges methods are supported by the DataSet, DataTable, and DataRow objects, giving you control over the scope of the operation. AcceptChanges also has the effect of changing the DataRow.RowState property. When a user (or your code) makes a change to a data value, the RowState property is changed to indicate that the row has been modified. When an Update method is called, only those rows with a RowState of Modified will be submitted to the database. Remember, calling AcceptChanges immediately before an Update method will result in no user changes being sent back to the database, even though they are visible at the client. After database updates have been processed successfully, you can call AcceptChanges to keep the local DataSet in sync with the database.

Table 6.6 lists the enumerated values that are valid for the RowState property and for other properties and methods, such as DataRow.HasVersion, that use the RowVersion enumeration.

Table 6.6: RowState and RowVersion Enumerations

DataRowVersion Enumerated Value	Description
Current	The row contains current values.
Default	The default row version (Current, Default, or Original), according to the current DataRowState. For most DataRowStates, the default row version is Current. The default row version for a deleted row is Original. The default row version for a detached row is Proposed.
Original	The row contains its original values.
Proposed	The row contains its proposed values. Exists during an edit operation.
DataRowState Enumerated Value	Description
Added	The row has been added to a DataRowCollection, and AcceptChanges has not been called.
Deleted	The row was deleted by using the Delete method of the DataRow, and AcceptChanges has not been called.
Detached	The row has been created but is not part of any DataRowCollection. A DataRow is in this state immediately after it has been created and before it is added to a collection, or if it has been removed from a collection.
Modified	The row has been modified, and AcceptChanges has not been called.
Unchanged	The row has not changed since AcceptChanges was last called.

In order to use the Update method to send the local changes that have been made to the DataSet to the database, you must add the additional SQL statements to perform delete, insert, and update operations and assign them to the DataAdapter’s properties. Listing 6.2 shows how to configure the InsertCommand, UpdateCommand, and DeleteCommand properties. This code assumes that you have previously created a valid SqlConnection object named myConn that we are referencing as we configure the DataAdapter.

Listing 6.2: Configuring a DataAdapter to Update Data

Public Sub GetData() Dim pubAdapter As SqlDataAdapter = New SqlDataAdapter() Dim pubSet As DataSet = New DataSet()    pubAdapter.SelectCommand = New SqlCommand( _       "SELECT pub_id, pub_name, city, state, " & _       "country FROM publishers", myConn)    pubAdapter.UpdateCommand = New SqlCommand( _       "UPDATE publishers SET pub_name = @pub_name, " & _       "city = @city, state = @state, " & _       "country = @country WHERE pub_id = " & _        "@original_id", myConn)    pubAdapter.UpdateCommand.Parameters.Add( _       "@pub_name", SqlDbType.VarChar, 40, "pub_name")    pubAdapter.UpdateCommand.Parameters.Add( _       "@city", SqlDbType.VarChar, 20, "city")    pubAdapter.UpdateCommand.Parameters.Add( _       "@state", SqlDbType.Char, 2, "state")    pubAdapter.UpdateCommand.Parameters.Add( _       "@country", SqlDbType.VarChar, 30, "country")    pubAdapter.UpdateCommand.Parameters.Add( _       "@original_id", SqlDbType.Char, 4, "pub_id" _       ).SourceVersion = DataRowVersion.Original    pubAdapter.Fill(pubSet, "Publishers")    'continue working with the DataSet End Sub

The SQL statement that determines how the update is performed contains parameters, such as @pub_name and @city. The parameters in the SQL statement represent the DataRowVersion.Current value (including user input) of the data items in the row of the DataTable that is being processed. The last parameter in Listing 6.2 shows how to access the DataRowVersion.Original value. This parameter is used in the WHERE clause of the SQL Update statement because we want to make sure that the user didn’t accidentally try to change the pub_id (primary key) value, and that we are selecting the correct row in the database, based on the primary key that was originally retrieved.

Real World Scenario—DataSet versus DataReader

As a software developer, you probably enjoy discussions with fellow developers about the merits of different design choices. One issue that has been frequently discussed on Internet mailing lists and newsgroups is when to use a DataReader versus a DataSet, and which object will provide better performance. Performance of course is a relative term, based on exactly what you are measuring. Also, consider what is most important to the success of your application: is it raw speed, or is a sophisticated user interface, enabling extensive user interaction with the data, preferable?

Remember that the DataSet object provides a local, in-memory store of data that can be nearly as complex as the database structure itself. Users can sort, filter, and change data as much as they want. Users have some measure of control over when their updates will be sent to the database. Although this provides a nice user experience, it creates problems for the developer who has to manage update conflicts. It also requires powerful resources on the client computers and adds to network traffic.

The DataReader object provides fast forward-only, read-only access to your data. Users have no ability to interact with the data; it is good only for display. This behavior works well for web applications, which cannot depend on an uninterrupted connection to the server and database. As a developer, you will have to plan an additional strategy to capture new information or changes from users and communicate those back to the database, perhaps by using ADO.NET commands or stored procedures.

Your selection of one class over the other can greatly affect your application’s effectiveness and should be considered carefully.

In Exercise 6.1, you will create a Windows application that uses a DataAdapter to fill a DataSet. Your user interface will use a Windows forms DataGrid control to display this data and to enable the users to edit and add new data to the pubs sample database.

Note 

The exercises in this chapter (as well in Chapter 7) use the Microsoft SQL Server 2000 sample database called pubs. This sample database is a part of the default installation of SQL Server 2000.

Exercise 6.1: Creating the DataSet and Updating the Database

Creating the DataSet:

1. Start a new Windows application project in Visual Studio .NET. Name the project DataSetExample.

2. Change the name of the form to frmJobs. Add a DataGrid and two Command Button controls to the form. Name the command buttons btnSave and btnRejectChanges. Your form should look something like this:

   

3. Add the following Imports statements to the form’s code module:

   Imports System.Data Imports System.Data.SqlClient

4. Declare class-level variables for the SqlConnection, SqlDataAdapter, and DataSet objects:

   Public Class frmJobs    Inherits System.Windows.Forms.Form Private myConn As SqlConnection = New SqlConnection( _         "Data Source=localhost; Initial " & _         "Catalog=pubs; Integrated Security=SSPI;") Private jobAdapter As SqlDataAdapter = New SqlDataAdapter() Private jobSet As DataSet = New DataSet()

5. In the frmJobs_Load event procedure, add code to set the SelectCommand property of the SqlDataAdapter:

   jobAdapter.SelectCommand = New SqlCommand( _    "SELECT job_id, job_desc, min_lvl, max_lvl " & _    "FROM jobs", myConn) 

6. Call the Fill method to retrieve data into the DataSet, and set the data binding for the DataGrid control to display this data:

   Try    jobAdapter.Fill(jobSet, "Jobs")    DataGrid1.SetDataBinding(jobSet, "Jobs")

7. Add a simple error handler to help you diagnose any errors that might occur:

   Catch exp As Exception    MessageBox.Show(exp.Message) End Try

8. Save and test your work. The form should display the data from the jobs table of the pubs sample database.

   

   Updating the Database:

   Remember that you can use the Server Explorer to find information about the database, such as the field names, data types, and field sizes that are used in the code after step 9.

   Although the DataGrid control enables you to edit the information displayed on the screen, you have not yet added any code to perform updates so that these changes are saved permanently to the database. In the rest of this exercise, you are going to add code to create the SqlDataAdapter’s InsertCommand, UpdateCommand, and DeleteCommand properties.

9. Create the new Command object. Write a SQL statement that will insert the data. Create three Parameter objects, which will map to the three columns in the DataTable that contain the new information you are sending to the database: job_desc, min_lvl, and max_lvl. Because the job_id column is defined in the database as an Identity column (autonumber), you do not have to supply any data for that column.

   Here is the code to do this:

   jobAdapter.InsertCommand = New SqlCommand( _    "INSERT INTO jobs (job_desc, " & _    "min_lvl, max_lvl) VALUES " & _    "(@job_desc, @min_lvl, @max_lvl)", myConn) jobAdapter.InsertCommand.Parameters.Add( _    "@job_desc", SqlDbType.VarChar, 50, "job_desc") jobAdapter.InsertCommand.Parameters.Add( _    "@min_lvl", SqlDbType.TinyInt, 1, "min_lvl") jobAdapter.InsertCommand.Parameters.Add( _    "@max_lvl", SqlDbType.TinyInt, 1, "max_lvl")

10. Create the UpdateCommand. This command has four parameters: the three columns that contain the changed data and a new parameter, called @original_id. This new parameter is set to the DataRowVersion.Original value, which is the value that was present when the data was retrieved from the database, before any user changes. The SQL statement used for the UpdateCommand uses this parameter in the WHERE clause to make sure that you are updating the correct row. You will notice that the SQL statement does not allow changes to the job_id column. Because this is an Identity column and the primary key for the table, it would not be a good idea to allow the user to change it. Here is what your code should look like:

    jobAdapter.UpdateCommand = New SqlCommand( _    "UPDATE jobs SET job_desc = @job_desc, " & _    "min_lvl = @min_lvl, max_lvl = @max_lvl " & _    "WHERE job_id = @original_id", myConn) jobAdapter.UpdateCommand.Parameters.Add( _    "@job_desc", SqlDbType.VarChar, 50, "job_desc") jobAdapter.UpdateCommand.Parameters.Add( _    "@min_lvl", SqlDbType.TinyInt, 1, "min_lvl") jobAdapter.UpdateCommand.Parameters.Add( _    "@max_lvl", SqlDbType.TinyInt, 1, "max_lvl") jobAdapter.UpdateCommand.Parameters.Add( _    "@original_id", SqlDbType.SmallInt, 2, "job_id" _    ).SourceVersion = DataRowVersion.Original

11. Create the DeleteCommand. This command has only one parameter, the @original_id. The SQL statement used for the DeleteCommand uses this parameter in the WHERE clause to make sure that you are deleting the correct row. Here is what your code should look like:

    jobAdapter.DeleteCommand = New SqlCommand( _    "DELETE FROM jobs WHERE job_id = @original_id", myConn) jobAdapter.DeleteCommand.Parameters.Add( _    "@original_id", SqlDbType.SmallInt, 2, "job_id" _    ).SourceVersion = DataRowVersion.Original 

12. Add code to the Command Button control’s Click event procedures to either save the user’s changes or to cancel them:

    Private Sub btnSave_Click(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles btnSave.Click    Try       jobAdapter.Update(jobSet, "Jobs")       MessageBox.Show("Changes successfully made to the database.")    Catch ex As Exception       MessageBox.Show(ex.Message)    End Try End Sub Private Sub btnReject_Click(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles btnRejectChanges.Click    jobSet.RejectChanges() End Sub

13. Save your project. You will be adding to it in future exercises in this chapter.

14. Test your UpdateCommand, InsertCommand, and DeleteCommand properties by changing some of the data.

15. Add a new entry to the blank row at the bottom of the DataGrid control (don’t supply a value for the job_id column).

16. Click on the left margin of any row to select the row and then press the Delete key to delete it.

17. Click the Reject Changes button. Your changes will disappear, and the data will be returned to its original state.

18. Click the Save button. Your changes will be sent to the database.

19. Shut down the project and restart it, or open the table in the Server Explorer, to verify that your changes and new rows are in the database.

The complete listing for the frmJobs_Load procedure from Exercise 6.1 is shown in Listing 6.3.

Listing 6.3: The Complete frmJobs_Load Procedure from Exercise 6.1

Private Sub FrmJobs_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load       jobAdapter.SelectCommand = New SqlCommand( _          "SELECT job_id, job_desc, min_lvl, max_lvl " & _          "FROM jobs", myConn)       jobAdapter.InsertCommand = New SqlCommand( _          "INSERT INTO jobs (job_desc, " & _          "min_lvl, max_lvl) VALUES " & _          "(@job_desc, @min_lvl, @max_lvl)", myConn)       jobAdapter.InsertCommand.Parameters.Add( _          "@job_desc", SqlDbType.VarChar, 50, "job_desc")       jobAdapter.InsertCommand.Parameters.Add( _          "@min_lvl", SqlDbType.TinyInt, 1, "min_lvl")       jobAdapter.InsertCommand.Parameters.Add( _          "@max_lvl", SqlDbType.TinyInt, 1, "max_lvl")       jobAdapter.UpdateCommand = New SqlCommand( _          "UPDATE jobs SET job_desc = @job_desc, " & _          "min_lvl = @min_lvl, max_lvl = @max_lvl " & _          "WHERE job_id = @original_id", myConn)       jobAdapter.UpdateCommand.Parameters.Add( _          "@job_desc", SqlDbType.VarChar, 50, "job_desc")       jobAdapter.UpdateCommand.Parameters.Add( _          "@min_lvl", SqlDbType.TinyInt, 1, "min_lvl")       jobAdapter.UpdateCommand.Parameters.Add( _          "@max_lvl", SqlDbType.TinyInt, 1, "max_lvl")       jobAdapter.UpdateCommand.Parameters.Add( _          "@original_id", SqlDbType.SmallInt, 2, "job_id" _          ).SourceVersion = DataRowVersion.Original       jobAdapter.DeleteCommand = New SqlCommand( _          "DELETE FROM jobs WHERE job_id = @original_id", myConn)       jobAdapter.DeleteCommand.Parameters.Add( _          "@original_id", SqlDbType.SmallInt, 2, "job_id" _          ).SourceVersion = DataRowVersion.Original       Try          jobAdapter.Fill(jobSet, "Jobs")          DataGrid1.SetDataBinding(jobSet, "Jobs")       Catch exp As Exception          MessageBox.Show(exp.Message)       End Try End Sub

Now that you understand the basics of creating a DataSet and using the DataAdapter to retrieve and update data, you are ready to look at some of the additional capabilities that you have available for working with the DataSet. First you will consider error handling, and then see how to use DataViews to sort, search, and filter data in a DataSet. Finally, you will look at using Constraints and DataRelations to enforce data integrity in the local DataSet.

Handling DataExceptions

As a developer, you know that robust error handling is one of the most important aspects of creating high-quality applications. In addition to handling general application errors by using System.Exception, the System.Data namespace provides the DataException class. The DataException class inherits from System.Exception and defines specific kinds of errors that are likely to occur when you are working with ADO.NET objects. Your error-handling scheme should include provisions for dealing with these common data-related exceptions.

Table 6.7 lists the specific DataException types that are available.

Table 6.7: Derived Types of the System.Data.DataException Class

Type	Description
ConstraintException	This exception is thrown when an attempted update violates a database constraint.
DeletedRowInaccessibleException	This exception is thrown when you try to access a DataRow that has previously been deleted.
DuplicateNameException	This exception is thrown when you attempt to add objects to a DataSet with duplicate names.
InRowChangingEventException	This exception is thrown when you try to call EndEdit at an invalid time.
InvalidConstraintException	This exception is thrown when a relation is found to be invalid.
InvalidExpressionException	This exception is thrown when a DataColumn expression is invalid.
MissingPrimaryKeyException	This exception is thrown when no primary key has been specified.
NoNullAllowedException	This exception is thrown when attempting to add a null value to a column that does not allow nulls.
ReadOnlyException	This exception is thrown when attempting to change a read-only column.
RowNotInTableException	This exception is thrown when the DataRow cannot be found in the specified DataTable.
StrongTypingException	This exception is thrown when a null value is used with a strongly-typed DataSet.
TypedDataSetGeneratorException	This exception is thrown when duplicate names are found when generating a strongly typed DataSet.
VersionNotFoundException	This exception is thrown when the requested DataRowVersion is no longer available.
Other Data-related exceptions Derived from System.Exception	Description
DBConcurrencyException	This exception is thrown when the DataAdapter Update operation cannot update a row in the database.

Listing 6.4 shows how to use multiple Catch blocks to vary your error handling based on the type of error that has occurred.

Listing 6.4: Handling DataExecptions

Private Sub btnTest_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnTest.Click    Try       MessageBox.Show(CType(jobSet.Tables( _          "Jobs").Rows(14)("job_desc"), String))    Catch deletedEx As DeletedRowInaccessibleException       MessageBox.Show( _          "That row has been deleted from the DataSet.")    Catch dbConEx As DBConcurrencyException       MessageBox.Show("Error at the database.")    Catch dataEx As DataException       MessageBox.Show("Data Exception")    Catch ex As Exception       MessageBox.Show("Generic Exception: " & ex.Message)    End Try End Sub

In this example, there are three specific types of exceptions that we are interested in. The DeletedRowInaccessibleException occurs when a row is deleted from the local DataSet but other code tries to access it. The DBConcurrencyException will occur when an update fails at the database. The DataException will catch any of the special types of exceptions shown in Table 6.7. The generic Exception will catch any type of exception that occurs in the application, whether data related or not.

The DataAdapter has a property named ContinueUpdateOnError. When this property is set to False (which is the default), the first error that occurs during a DataAdapter Update operation will cause an exception to be fired and the process to stop. Any further updates that might be required for the rest of the data in the DataSet will not be submitted. When the property is set to True, no exception will be fired and all updates will be processed and sent to the database. Any rows that could not be updated because of an error (perhaps the user typed an invalid data value for the column as defined in the database) will have a RowError property setting of True. Because no exception occurs, you will not know whether any errors occurred unless your code actively tests the HasErrors property of the DataSet and uses the GetErrors method of the DataTable to programmatically identify the rows that failed to update at the database. You will have an opportunity to test this behavior in Exercise 6.2.

In Exercise 6.2, you will add code to the DataSetExamples project from Exercise 6.1, and then test several scenarios and see which errors are fired.

Exercise 6.2: Testing DataExceptions

1. Open the project that you created in Exercise 6.1 named DataSetExamples.

2. Add two Command Button controls to the form, named btnTest and btnHasErrors. It should look like this:

   

3. Add the following code to the Click event procedure of btnTest:

      Try       'try to access the data in the deleted row       MessageBox.Show(CType(jobSet.Tables("Jobs").Rows(14) _          ("job_desc"), String))    Catch ex As Exception       MessageBox.Show("Generic Exception: " & ex.Message)    Catch deletedEx As DeletedRowInaccessibleException       MessageBox.Show("That row has been deleted from the DataSet.")    Catch dbConEx As DBConcurrencyException       MessageBox.Show("Error at the database.")    Catch dataEx As DataException       MessageBox.Show("Data Exception")    End Try

4. Save your work and run the application. Depending on changes that you have made to the jobs table in Exercise 6.1, your data might look a little different. The original sample data has 14 numbered entries in the table.

5. Add or delete as many rows as necessary to make 14, and click the Save button.

   

6. Click the Test button. Your code will try to access the 15th row (index value 14) and read data. You should see the generic exception message, informing you the row was deleted.

   

7. To see why you received the generic exception and not the DeletedRowInaccessibleException, close the application, set a breakpoint at the beginning of the procedure, and try this test again. Step through the code in the procedure. Because the generic Catch ex as Exception was listed first in the code, that syntax will catch any error that occurs. That error handler is used, and the others are ignored.

   

8. Change the code so that the Catch blocks are listed in this order: DBConcurrencyException, DeletedRowInaccessibleException, DataException, and Exception:

      Try       MessageBox.Show(CType(jobSet.Tables("Jobs").Rows(14) _          ("job_desc"), String))    Catch dbConEx As DBConcurrencyException       MessageBox.Show("Error at the database.")    Catch deletedEx As DeletedRowInaccessibleException       MessageBox.Show("That row has been deleted from the DataSet.")    Catch dataEx As DataException       MessageBox.Show("Data Exception")    Catch ex As Exception       MessageBox.Show("Generic Exception: " & ex.Message)    End Try 

9. With the breakpoint from step 7 still in your code, run the application again and, if necessary, delete items so that there are only 14 items in the list.

10. Click the Test button. Step through the code, and you will see that DeletedRowInaccessibleException is caught. Although the row has been marked as deleted, and you are not allowed to access its data, it still exists in the DataSet.

    

11. Click the Save button. This will make the change permanent in the database.

12. Click the Test button again. You should see the generic Exception, informing you that there is no item at position 14. You receive the generic exception because after making the change permanent to the database, the deleted row is completely gone from the DataSet.

    

13. Add another new row of data so you can try another test. Click the Save button to update the database. Close the application.

14. Change the error-handling code for the bntSave Click event procedure to this:

    Catch dbex As DBConcurrencyException    MessageBox.Show("DBC: " & dbex.Message) Catch ex As Exception    MessageBox.Show("Generic: " & ex.Message) End Try

15. Save and run your application.

16. Using Windows Explorer, locate the DataSetExamples.exe executable in the \bin subdirectory of your project. Double-click the filename to run a second instance of your application. You should see the same data in both instances.

17. In the first instance of the application, delete the last row and then click the Save button.

18. The local DataSet in the second instance still contains the row. Make a change to one of the data items in that row and click the Save button. You should see a DBConcurrency error.

    

    The Update command failed because it could not find a row with that primary key value in the database. Notice that the DataGrid control displays a red exclamation point icon to the left of the row that was in error.

    

    You can change the way that the DataAdapter handles errors by setting its ContinueUpdateOnError property. This is set to False by default, so anytime an error occurs, no updates are written to the database and an exception is generated.

19. Change the ContinueUpdateOnError property to True by adding this line of code before the DataAdapter Update method call in the btnSave Click event procedure:

    jobAdapter.ContinueUpdateOnError = True

20. Save your application and run the test as described in steps 16–18 again. This time, notice that the “success” message box is displayed, but the row is still marked with the error icon in the DataGrid.

21. Change some data in other rows and click the Save button. The other rows will be updated successfully.

22. Verify your updates by opening the jobs table with the Server Explorer. The ContinueUpdateonError property enables the successful updates to the database to complete and lets you handle the error rows later.

23. Although the DataGrid control provides a convenient user interface to see which rows had an error, at times you will want to access this information through code. To do this, implement the btnHasErrors Click event procedure to test for errors and display error information programmatically:

    Private Sub btnHasErrors_Click(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles btnHasErrors.Click       If jobSet.HasErrors Then          Dim row As DataRow          For Each row In jobSet.Tables("Jobs").GetErrors             MessageBox.Show(row.RowError)          Next       End If End Sub

24. Repeat the test again. Click the btnHasErrors button to test your code.

25. Save your work. You will be adding to this project in the remaining exercises in this chapter.

Working with DataView Objects

The strength of the DataSet object is that it enables you to retrieve data once from the database and enables local clients to work with the data for as long as they need to without having to keep a connection open to the database. When users are viewing large amounts of data, it is a common requirement that the user interface allow them to sort the information in various ways, to filter out subsets of data based on some selection criteria, or to search for a specific value. The DataView object enables your application to create these different ways to view the data in a DataSet, without changing the underlying data and without having to make additional queries to the database server. This can improve the performance of your user interface and provide a powerful tool for your users.

The DataView has a Sort property that changes the order in which data is displayed, and a RowFilter property that determines what subset of the data is displayed. The RowStateFilter property lets you filter the data in the table based on the status of the row: original, changed, added, deleted, and so on. The DataView also has a Find method that searches through the data in specified columns. After you have created a DataView, you can work with it just as if it were the table itself.

Note 

Sort, RowFilter, and RowStateFilter are the most common operations that you will be performing with the DataView. Table 6.8 shows the complete list of properties and methods of the DataView class.

The DataView has other related objects that you can make use of, such as the DataViewManager, to make settings for all DataViews associated with a DataSet and the DataRowView.

Note 

You will see examples of using these objects in Exercise 6.3.

The most common use of the DataView is to provide the user with customized subsets of all the data contained in a DataSet by applying different filter and sort keys. This code snippet shows an example:

authViewMan.DataViewSettings("Authors").Sort = "au_lname" authViewMan.DataViewSettings("Authors").RowFilter = _    "state = 'CA'"

You can sort in reverse order by using the DESC modifier in the sort string:

authViewMan.DataViewSettings("Authors").Sort = "au_lname DESC"

A DataView is also useful when using the Find method to locate a specific row in a DataTable in the DataSet. You will see an example of this in Exercise 6.4. This code snippet shows the basic syntax:

findView.Sort = "pub_id" rowIndex = findView.Find("9999")

To use the Find method, first you set the sort key to the column that contains the data that you want to search, and then you specify the value to search for. The Find method returns an integer value that indicates the row index in the DataTable of the matching row.

You can also search multiple columns by providing an array of strings to the Find method:

 findView.Sort = "au_lname, au_fname" Dim objValues(1) As Object objValues(0) = "Green" objValues(1) = "Marjorie" rowIndex = findView.Find(objValues) 

Table 6.8 lists the properties and methods of the DataView class.

Table 6.8: Properties and Methods of the DataView Class

Property	Description
AllowDelete	Indicates whether deletes are allowed.
AllowEdit	Indicates whether edits are allowed.
AllowNew	Indicates whether the new rows can be added by using the AddNew method.
ApplyDefaultSort	Indicates whether to use the default sort.
Count	Retrieves the number of records in the DataView after RowFilter and RowStateFilter have been applied.
DataViewManager	Retrieves the DataViewManager associated with this view.
Item	Retrieves a row of data from a specified table.
RowFilter	The expression used to filter which rows are viewed in the DataView.
RowStateFilter	The row state filter used in the DataView.
Sort	The sort column or columns, and the sort order for the DataTable.
Table	The source DataTable.
Method	Description
AddNew	Adds a new row to the DataView.
BeginInit	Begins the initialization of a DataView that is used on a form or used by another component. The initialization occurs at runtime.
CopyTo	Copies items into an array. Only for Web forms interfaces.
Delete	Deletes a row at the specified index.
EndInit	Ends the initialization of a DataView that is used on a form or used by another component. The initialization occurs at runtime.
Find	Finds a row in the DataView by the specified sort key value.
FindRows	Retrieves an array of DataRowView objects whose columns match the specified sort key value.
GetEnumerator	Retrieves an enumerator for this DataView.

In Exercise 6.3, you will work with the DataView and the DataViewManager classes to sort and filter data in a DataSet.

Exercise 6.3: Sorting and Filtering with the DataView and DataViewManager

1. Open the DataSetExamples project that you originally created in Exercise 6.1 and added to in Exercise 6.2. Add a new Windows form to the project and name it frmAuthors.

2. Add a DataGrid, a ComboBox, and a Command Button control to the form. Name the command button btnDisplayAll. Your form should look like this:

   

3. Add Imports statements at the top of the code module for the form:

   Imports System.Data Imports System.Data.SqlClient

4. Declare class-level variables for a SqlConnection, two SqlDataAdapters, and a DataSet:

   Public Class frmAuthors    Inherits System.Windows.Forms.Form Private myConn As SqlConnection = New SqlConnection( _    "Data Source=localhost; Initial " & _    "Catalog=pubs; Integrated Security=SSPI;") Private authAdapter As SqlDataAdapter = New SqlDataAdapter() Private stateAdapter As SqlDataAdapter = New SqlDataAdapter() Private authSet As DataSet = New DataSet() 

5. In the frmAuthors_Load event procedure, do the following:

   • Set up the SelectCommand properties for the two SqlDataAdapters.

   • Open the connection.

   • Fill the DataSet by adding two tables—Authors and States—to the DataSet.

   • Open the connection explicitly, rather than letting the SqlDataAdapter do it implicitly, because you have more than one Fill method to execute.

   • Bind the Authors table to the DataGrid and bind the States table to the ComboBox.

   • Add simple error handling for this procedure and make sure to close the connection in the Finally block of the error handler.

6. Your code should look like this:

   Private Sub frmAuthors_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    Try       stateAdapter.SelectCommand = New SqlCommand( _          "SELECT DISTINCT state " & _          "FROM authors", myConn)       authAdapter.SelectCommand = New SqlCommand( _          "SELECT au_id, au_lname, au_fname, state " & _          "FROM authors", myConn)       myConn.Open()       authAdapter.Fill(authSet, "Authors")       stateAdapter.Fill(authSet, "States")       DataGrid1.SetDataBinding(authSet, "Authors")       ComboBox1.DataSource = authSet.Tables("States")       ComboBox1.DisplayMember = "state"      Catch exp As Exception         MessageBox.Show(exp.Message)      Finally         myConn.Close()      End Try End Sub

7. In the Solution Explorer, right-click the DataSetExample project and choose Properties from the menu. Set the startup object for the project to frmAuthors.

8. Save and test your work. The DataGrid should display all the authors from the pubs sample database Authors table, and the ComboBox should display a list of United States state code abbreviations.

   

9. In the ComboBox1_SelectedIndexChanged event procedure, create a DataViewManager for the DataSet that will change the RowFilter property each time the user changes the selection in the ComboBox. Then, change the data binding of the DataGrid control to bind to the filtered DataView instead of the entire table. Here is the code to do this:

   Private Sub ComboBox1_SelectedIndexChanged(ByVal sender _    As System.Object, ByVal e As System.EventArgs) _    Handles ComboBox1.SelectedIndexChanged    Dim authViewMan As DataViewManager = New _       DataViewManager(authSet)    authViewMan.DataViewSettings("Authors").Sort = "au_lname"    authViewMan.DataViewSettings("Authors").RowFilter = _       "state = '" & ComboBox1.Text & "'"    'Bind to a DataGrid.     DataGrid1.SetDataBinding(authViewMan, "Authors")     End Sub 

10. Add code to the btnDisplayAll_Click event procedure to restore the data bindings of the DataGrid control to the complete DataSet and display all authors:

    Private Sub btnDisplayAll_Click(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles btnDisplayAll.Click    DataGrid1.SetDataBinding(authSet, "Authors") End Sub

11. Run the application. You will see only California authors at first. When you change the selection in the ComboBox, you will see a different list of authors displayed in the DataGrid.

    

12. Click the Display All button to display the complete list of authors.

13. Save your work. You will be adding to this project in later exercises in this chapter.

Configuring DataSet Constraints and DataRelations

When you are working with a full-featured database engine such as Microsoft SQL Server 2000, you can take advantage of features to maintain consistency between related data in multiple tables when data is changed, and make sure related child records are deleted when a parent record is deleted. Maintaining this consistency between related data is an important aspect of maintaining the data integrity of the database. Depending on the needs of your application, it is sometimes desirable to enforce these same data integrity rules on data in a DataSet. By enforcing the rules on the DataSet, and therefore catching and fixing any data integrity violations locally, before updates are attempted at the database, you can eliminate unnecessary traffic back and forth to the database server.

DataSet Constraints and DataRelations are used to enforce data integrity rules. These settings often match those that are defined in the source database. They might also be used to enforce constraints specific to the application that do not apply to all data in the database.

There are two types of Constraints that can be applied to a DataSet:

ForeignKeyConstraint The ForeignKeyConstraint specifies how rows in a related table are deleted or changed (Cascade), or the row values are set to null (SetNull), or the values are set to a default value (SetDefault), or not changed (None). This behavior is based on the values that are set for the AcceptRejectRule, DeleteRule, and UpdateRule properties of the Constraint.

UniqueConstraint The UniqueConstraint requires that each value in a column or combination of values in a specified set of columns must be unique in that table. This constraint can apply to one column or to a combination of column values. The IsPrimaryKey property indicates that the column value(s) should be treated as a primary key, such as they are in the database.

Listing 6.5 shows how to create a ForeignKeyConstraint by defining DataColumn objects that reference the specific parent and child columns in the related tables.

Listing 6.5: Creating a ForeignKeyConstraint

Dim parentColumn As DataColumn Dim childColumn As DataColumn Dim pubKey As ForeignKeyConstraint parentColumn = pubSet.Tables("Publishers").Columns("pub_id") childColumn = pubSet.Tables("Titles").Columns("pub_id") pubKey = New ForeignKeyConstraint("PubTitleFKConstraint", _    parentColumn, childColumn) pubKey.DeleteRule = Rule.SetNull pubKey.UpdateRule = Rule.Cascade pubKey.AcceptRejectRule = AcceptRejectRule.Cascade pubSet.Tables("Publishers").Constraints.Add(pubKey) pubSet.EnforceConstraints = True

The constructor method for the ForeignKeyConstraint class accepts three parameters: a string name for the constraint, and the two object references to the parent and child DataColumn objects. Values are set for the rule properties that determine whether changes (or deletions) to the parent table affect the child table. Finally, the constraint must be added to the DataSet.Constraints collection of the DataTable.

As already noted, a UniqueConstraint can be added to a column in a DataTable to ensure that each row has a unique value for that column or set of columns. This will prevent users from entering duplicate data and guard against sending inaccurate information back to the database. Listing 6.6 shows how to create a UniqueConstraint.

Listing 6.6: Creating a UniqueConstraint

Dim idColumn As DataColumn idColumn = pubSet.Tables("Publishers").Columns("pub_id") Dim pubUniqueConst As UniqueConstraint = New _    UniqueConstraint("PubIDConstraint", idColumn) pubTable.Constraints.Add(pubUniqueConst)

Now you have seen an example of creating a Constraint for a particular DataColumn. Table 6.9 lists the complete set of properties and methods for the Constraint class.

Table 6.9: Properties of the Constraint Class

Property	Description
ConstraintName	The name of a constraint in the DataSet.Constraints.
ExtendedProperties	Returns the collection of user-defined constraint properties.
Table	Returns the DataTable to which the constraint applies. For ForeignKeyConstraint, it returns the child table. For UniqueConstraint, it returns the original DataTable.
ForeignKeyConstraint only
AcceptRejectRule	Indicates the action that should take place across this constraint when AcceptChanges is invoked: either None or Cascade.
Columns	Retrieves the child columns of this constraint.
DeleteRule	Retrieves or sets the action that occurs across this constraint when a row is deleted: Cascade, None, SetDefault, or SetNull.
RelatedColumns	The parent columns of this constraint.
RelatedTable	Retrieves the parent table of this constraint.
UpdateRule	Indicates the action that occurs across this constraint when a row is updated: Cascade, None, SetDefault, or SetNull.
UniqueConstraint only
Columns	Retrieves the array of columns that this constraint affects.
IsPrimaryKey	Indicates whether the constraint is on a primary key.

The DataRelation object is used to model the same parent/child relationships that are defined in the database itself. Specifying DataRelations in the DataSet can be useful in locating related records in two tables.

Note 

Exercise 6.4 shows an example of using a DataRelation to create DataViews based on related records.

The basic syntax for creating a DataRelation is shown in Listing 6.7.

Listing 6.7: Creating a DataRelation Object

Dim pubRelation As DataRelation pubRelation = bookSet.Relations.Add("PubTitles", _    bookSet.Tables("Publishers").Columns("pub_id"), _    bookSet.Tables("Titles").Columns("pub_id"))

This code declares a DataRelation object and then uses the DataSet.Relations.Add method to add the new DataRelation to the DataSet’s collection. The parameters for the Add method are a string name for the DataRelation and two column references. These column references represent the matching columns in the parent and child tables. Table 6.10 lists the properties of the DataRelation class.

Table 6.10: Properties of the DataRelation Class

Property	Description
ChildColumns	Retrieves the child DataColumn objects of this relation
ChildKeyConstraint	Retrieves the ForeignKeyConstraint for the relation
ChildTable	Retrieves the child table of this relation
DataSet	Retrieves the DataSet to which the DataRelation belongs
ExtendedProperties	Retrieves the collection that stores customized properties
Nested	Indicates whether DataRelation objects are nested
ParentColumns	Retrieves an array of DataColumn objects that are the parent columns of this DataRelation
ParentKeyConstraint	Retrieves the UniqueConstraint that ensures values in the parent column of a DataRelation are unique
ParentTable	Retrieves the parent DataTable of this DataRelation
RelationName	The name used to retrieve a DataRelation from the DataRelationCollection

Exercise 6.4 will practice what you learned earlier about using the DataView Find method to locate a selected row in the data. You will also create a DataRelation that defines the parent/child relationship between two tables in the DataSet. After you have selected a row from the Publishers table, you will use the DataView.CreateChildView method to locate related records in the Titles table.

Exercise 6.4: Using a DataRelation and Creating a ChildView

1. Open the DataSetExamples project that you originally created in Exercise 6.1 and modified in Exercises 6.2 and 6.3. Add a new Windows form to the project. Name it frmPublishers.

2. Add a ComboBox and a ListBox control to the form. Your form should look like this:

   

3. Add Imports statements at the top of the code module for the form:

   Imports System.Data Imports System.Data.SqlClient

4. Declare class-level variables for a SqlConnection, two SqlDataAdapters, and a DataSet:

   Public Class frmPublishers     Inherits System.Windows.Forms.Form Private myConn As SqlConnection = New SqlConnection( _    "Data Source=localhost; Initial " & _    "Catalog=pubs; Integrated Security=SSPI;") Private pubAdapter As SqlDataAdapter = New SqlDataAdapter() Private titleAdapter As SqlDataAdapter = New SqlDataAdapter() Private bookSet As DataSet = New DataSet() 

5. In the frmPublishers_Load event procedure, add code to set up the SelectCommand properties for the two SqlDataAdapters, open the connection, and fill the DataSet. Add two tables—Publishers and Titles—to the DataSet. Open the connection explicitly, rather than letting the SqlDataAdapter do it implicitly, because there is more than one Fill method to execute. Here is the code to do this:

   myConn.Open() pubAdapter.SelectCommand = New SqlCommand( _    "SELECT pub_id, pub_name " & _    "FROM publishers", myConn) titleAdapter.SelectCommand = New SqlCommand( _    "SELECT title_id, pub_id, title, price " & _    "FROM titles", myConn) Try    pubAdapter.Fill(bookSet, "Publishers")    titleAdapter.Fill(bookSet, "Titles")

6. Create a DataRelation to link the Publishers and Titles tables by using the pub_id column that exists in each table:

   Dim pubRelation As DataRelation pubRelation = bookSet.Relations.Add("PubTitles", _    bookSet.Tables("Publishers").Columns("pub_id"), _    bookSet.Tables("Titles").Columns("pub_id")) 

7. Bind the Publishers table to the ComboBox. There is also simple error handling for this procedure, so make sure to close the connection in the Finally block of the error handler. Your code should look like this:

   ComboBox1.DataSource = bookSet.Tables("Publishers") ComboBox1.DisplayMember = "pub_name" ComboBox1.ValueMember = "pub_id" Catch exp As Exception    MessageBox.Show(exp.Message) Finally    myConn.Close() End Try 

8. In the Solution Explorer, right-click the DataSetExamples project and choose Properties from the menu. Set the startup object for the project to frmPublishers.

9. Save and test your work. The application should display a list of publisher names in the ComboBox.

   

   Add code in the ComboBox_SelectedIndexChanged event procedure to locate a selected publisher ID when the user changes the ComboBox selection.

10. First, declare variables:

    Dim rowIndex As Integer Dim childView As DataView Dim findView As DataView = New _    DataView(bookSet.Tables("Publishers"))

11. Set the DataView.Sort property to the column you want to search. Then call the DataView.Find method, which will return an integer value that gives you the row index of the row you are looking for. Here is the code to do this:

    Try    findView.Sort = "pub_id"    rowIndex = findView.Find(ComboBox1.SelectedValue) 

12. If the rowIndex value is zero or greater, then you know you have located a matching row. If so, create another DataView that contains child rows from the titles table. The CreateChildView method takes the name of the DataRelation that you defined in step 6 as an argument. Then you can loop through all the rows in the child view and add the name of the book to the ListBox control.

13. Your code should look like this:

    'test to see if the Find method was successful If rowIndex > -1 Then    childView = findView(rowIndex).CreateChildView("PubTitles")    Dim row As DataRowView    ListBox1.Items.Clear()       For Each row In childView          'add names to list box          ListBox1.Items.Add(row.Item(2))       Next End If Catch exp As Exception    MessageBox.Show(exp.Message) End Try 

14. Save and test your work. The application should display a list of book names in the ListBox when you select one of the publisher names in the ComboBox. Note that not all publishers have matching book titles. The complete code for this exercise is shown in Listing 6.8.

    

Listing 6.8: The Complete Code for Exercise 6.4

Option Strict On Imports System.Data Imports System.Data.SqlClient Public Class frmPublishers    Inherits System.Windows.Forms.Form    Private myConn As SqlConnection = New SqlConnection( _       "Data Source=localhost; Initial " & _       "Catalog=pubs; Integrated Security=SSPI;")    Private pubAdapter As SqlDataAdapter = _       New SqlDataAdapter()    Private titleAdapter As SqlDataAdapter = _       New SqlDataAdapter()    Private bookSet As DataSet = New DataSet() ' Windows Form Designer generated code Private Sub frmPublishers_Load(ByVal sender As _    System.Object, ByVal e As System.EventArgs) _    Handles MyBase.Load       myConn.Open()       pubAdapter.SelectCommand = New SqlCommand( _          "SELECT pub_id, pub_name " & _         "FROM publishers", myConn)       titleAdapter.SelectCommand = New SqlCommand( _          "SELECT title_id, pub_id, title, price " & _          "FROM titles", myConn)       Try          pubAdapter.Fill(bookSet, "Publishers")          titleAdapter.Fill(bookSet, "Titles")         Dim pubRelation As DataRelation         pubRelation = bookSet.Relations.Add("PubTitles", _           bookSet.Tables("Publishers").Columns("pub_id"), _           bookSet.Tables("Titles").Columns("pub_id"))         ComboBox1.DataSource = bookSet.Tables("Publishers")         ComboBox1.DisplayMember = "pub_name"         ComboBox1.ValueMember = "pub_id"      Catch exp As Exception         MessageBox.Show(exp.Message)      Finally         myConn.Close()      End Try    End Sub     Private Sub ComboBox1_SelectedIndexChanged( _        ByVal sender As System.Object, _        ByVal e As System.EventArgs) _        Handles ComboBox1.SelectedIndexChanged           Dim rowIndex As Integer           Dim childView As DataView           Dim findView As DataView = New _              DataView(bookSet.Tables("Publishers"))           Try              findView.Sort = "pub_id"              rowIndex = findView.Find( _                 ComboBox1.SelectedValue)              If rowIndex > -1 Then                 childView = findView( _                    rowIndex).CreateChildView("PubTitles")                 Dim row As DataRowView                 ListBox1.Items.Clear()                 For Each row In childView                     'add names to list box                     ListBox1.Items.Add(row.Item(2))                 Next              End If           Catch exp As Exception              MessageBox.Show(exp.Message)       End Try    End Sub End Class