The DataSet Revisited Even though you learned about the DataSet yesterday, the lesson skipped over some of its concepts and properties.  | Now that you know what the DataSet is and what it does, it's important to remember that it's a completely separate entity from the data source. There are no ties at all. Because of this, the DataSet is said to be disconnected. In this way, each user receives his own copy of the data to do with as he wishes. Changes made to the DataSet are not reflected in the data source. Any changes must be explicitly pushed back to the data source with methods that you'll see later today. |
Table 10.2 shows the properties of the DataSet. Table 10.2. DataSet Properties | Property | Description | | CaseSensitive | Indicates whether string comparisons in the DataTable objects are case sensitive. | | DataSetName | Gets or sets the name of the current DataSet. | | EnforceConstraints | Indicates whether existing database constraints should be observed when performing updates. | | ExtendedProperties | Gets the collection of custom user information. | | HasErrors | Indicates whether there are errors in any of the rows in any of the tables of the DataSet. | | Relations | Gets the collection of table relations for the DataSet. | | Tables | Gets the collection of tables for the DataSet. | Table 10.3 shows the methods of the DataSet. Table 10.3. DataSet Methods | Method | Description | | AcceptChanges | Commits all changes made to the DataSet, either since it was loaded or since AcceptChanges was last called. | | Clear | Removes all rows in all tables in the DataSet. Does not delete actual database content. | | Clone | Clones the structure of the DataSet, including DataTables, relations, and constraints. | | Copy | Copies the structure and data of the DataSet. | | GetChanges | Returns a copy of the DataSet that contains all changes made to underlying data since the last load. | | HasChanges | Indicates whether the DataSet contains any changes. | | Merge | Merges this DataSet with another. | | ReadXML | Reads XML schema and data into the DataSet. | | ReadXMLSchema | Reads XML schema information into the DataSet. | | RejectChanges | Rolls back any changes made to this DataSet. | | WriteXML | Writes the XML that represents the DataSet to an XML file, including data and schema. | | WriteXMLSchema | Writes the XML that represents the DataSet to an XML file, including schema information. | Note The preceding tables didn't list all the properties and methods of the DataSet object. They skipped over some of the generic inherited items. See either the .NET Framework SDK Documentation or Appendix D, "ADO.NET Controls: Properties and Methods," for more complete information.
As you can see, the DataSet provides a lot of features that you haven't seen before, such as reading and writing XML data. Don't be afraid to experiment with any of these properties. The DataTable and DataRow objects also provide many of the same properties and methods as the DataSet, so they aren't all listed here. Figure 10.1 illustrates the object model of the DataSet. Figure 10.1. The DataSet object model.
Modifying Data in a DataRow Because you should be familiar with the workings of the DataSet, let's examine modifying the data within one. The DataSet stores data in a form similar to a database; it contains tables, columns, and rows. Often, you'll manipulate the data contained in a DataSet with SQL statements that modify more than one record at a time, but there are times when you need to have more direct control over each row. The DataRow object represents a row of data in the DataTable, and as you learned yesterday, you can directly edit the content in each DataRow. There are some more features of the DataRow and DataTable that you should know about. The first is the RowState property, which indicates the condition of the current row. Possible values include Detached, Unchanged, New, Deleted, and Modified. Detached means that the row has been created but currently isn't part of any RowsCollection in a DataSet. The last four are self-explanatory. As part of the RowState, a DataTable maintains three versions of every row: original, current, and proposed. The DataTable uses these versions to determine the RowState. The original version is the row when it was first added to the DataTable. Usually this value will be the same as the value in the data source. The current version is the row after you've made any changes. The proposed version exists in one special condition when the BeginEdit method is called on the row. The BeginEdit method is used to make multiple changes to rows without having to apply validation rules. For example, if you have a group of rows that must add up to a certain value, you can put them in edit mode and manipulate the values without having to worry about what they add up to. The row moves out of edit mode and the validation rules are applied when the EndEdit or AcceptChanges method is called. You can also use edit mode to cancel any proposed changes. Figure 10.2 shows the process for modifying a row. Figure 10.2. Modifying a row.
The original value (from the data source) is moved into the DataSet when the Fill method is called. If you make any changes to the value, it becomes the current value. You can now either revert to the original value, update the data store with the current value, or move into edit mode. In edit mode, you can accept the changes and update the data store, or cancel the changes and revert to either the original or current versions. In fact, you can revert to either of these versions and then update the data store from edit mode, if you like. Each of the versions is accessible through the DataRowVersion. Original, DataRowVersion.Current, and DataRowVersion.Proposed properties. As the data in a DataRow is modified, errors may arise for one reason or another. Each error is stored in the RowError property of the DataRow as a string. You can manually insert an error in this property as well. You can retrieve all errors at once by calling the GetErrors method, which returns an array of DataRows. If any errors are present, merges and updates to the data source will not occur; first the errors must be reconciled. Don't worry if this doesn't make much sense; after you start developing examples, it will become clearer. The DataRow provides two methods that seem very similar, Delete and Remove, but there is an important difference between the two. Delete completely destroys the row and the data it contains. Once this happens, the data can no longer be accessed. Remove, however, simply removes a row from the DataTable so that it can't be accessed programmatically. The actual data source isn't changed, so the data is still there. You just can't see it. This is helpful if you don't want to use every row of a DataTable. Finally, RejectChanges gets rid of all changes that were made since the row was loaded, or since the last time AcceptChanges was called. For example, the following code snippet loads data into a DataSet, modifies a value in the first row, and then rejects the changes: dim objConn as new OleDbConnection _ ("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\ASPNET\data\banking.mdb") dim objCmd as new OleDbDataAdapter _ ("select * from tblUsers", objConn) dim ds as DataSet = new DataSet() objCmd.Fill(ds, "tblUsers") ds.Tables("tblUsers").Rows(0)("FirstName") = "Chris" 'do some other stuff ds.Tables("tblUsers").Rows(0).RejectChanges Viewing Data in a DataTable The DataTable has a Select method that allows you to filter and sort the data in your table. The method returns an array of DataRows and is invoked as follows: Tablename.Select(filter expression, sort order, _ DataRowViewState) For example: dim ds as new DataSet("MyDataSet") dim dTable as new DataTable("MyTable") ' fill data set and datatable here Dim MyRows() as DataRow = ds.Tables("MyTable").Select _ (Nothing, "UserName", DataViewRowState. _ CurrentRows) This listing returns an array of all modified DataRows sorted by the "UserName" field. You can specify Nothing for any of the parameters you don't need. Thus, you can return any or all versions of a row, and they can be sorted or filtered, or both. Let's look at another example, shown in Listing 10.1. Listing 10.1 Retrieving Rows Using the Select Method 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDb" %> 4: 5: <script runat="server"> 6: sub Page_Load(Sender as object, e as eventargs) 7: dim objConn as new OleDbConnection _ 8: ("Provider=Microsoft.Jet.OLEDB.4.0;" & _ 9: "Data Source=c:\ASPNET\data\banking.mdb") 10: 11: dim objCmd as new OleDbDataAdapter _ 12: ("select * from tblUsers", objConn) 13: 14: dim ds as DataSet = new DataSet() 15: objCmd.Fill(ds, "tblUsers") 16: 17: dim dTable as DataTable = ds.Tables("tblUsers") 18: Dim CurrRows() as DataRow = dTable.Select(Nothing, _ 19: Nothing, DataViewRowState.CurrentRows) 20: Dim I, J as integer 21: Dim strOutput as string 22: 23: For I = 0 to CurrRows.Length - 1 24: For J = 0 to dTable.Columns.Count - 1 25: strOutput = strOutput & dTable.Columns(J). _ 26: ColumnName & " = " & _ 27: CurrRows(I)(J).ToString & "<br>" 28: next 29: next 30: 31: Response.write(strOutput) 32: end sub 33: </script> 34: 35: <html><body> 36: 37: </body></html>  | This listing retrieves all current rows from a DataSet, and displays their fields and values in the browser. In the Page_Load method, you set up OleDbConnection and OleDbDataAdapter objects on lines 7?2. After yesterday's lesson, this should look familiar. You then create a DataSet and fill it with data using the Fill method on lines 14 and 15. Then, on line 17, you retrieve the only table in the DataSet and store it in the variable dTable for easier access later in the code. |
Tip In Listing 10.1, if you wanted to access the data from a SQL database instead of Access, you would simply change the OleDbName objects to SqlName objects.
On line 18, you use the Select method to grab all the rows that have changed in the DataTable the current rows and place them in an array. You use a for loop to iterate through the array rows on line 23, and another nested for loop to go through the columns for each row on line 24. You collect each field name and its value in a string, and then print out the string on line 31. This listing produces the output shown in Figure 10.3. Figure 10.3. For loops and the Select method are able to loop through each row in the DataSet.
Another way to sort and filter data is by using DataViews. A DataView is an object that represents a DataTable, but unlike a DataTable, the DataView is bindable to Web controls. You can create multiple DataViews for a single DataTable. In an ASP.NET page, this allows you to have two different controls that are bound to the same DataTable but display different data. For example, the following code snippet shows how to create and set properties for a DataView: dim MyView as new DataView(dTable) MyView.RowStateFilter = DataViewRowState.ModifiedOriginal MyView.Sort = "UserID ASC" MyView.RowFilter = "City = ASPTown" On the first line, you create a new DataView from the dTable variable. (dTable is a previously created DataTable, filled with data.) On the second line, you set the DataView to filter out all row versions except the original versions. The third line specifies a sort order, and the last line specifies a criterion by which to return rows. You can see that the DataView contains many of the same properties the Select method uses to retrieve rows. Knowing these properties will help you immensely when you're actually modifying data sources. Concurrency | Since each user has his own view of the DataSet, he's free to do with it as he pleases and update the data source whenever he's ready. What happens, though, when two or more people try to update the same data in a table at the same time? Concurrency is a system that monitors this scenario to make sure no problems arise. There are two types of concurrency: pessimistic and optimistic. |
Whenever a user accesses or tries to change data with pessimistic concurrency, a lock is placed on the data so that no other user can modify it and interfere with the first user. As soon as the first user is done, the second user can try again. Optimistic concurrency, on the other hand, does not place any locks on data. Rather, it monitors the rows to determine whether the original data has been modified, and then applies the change. Let's say two users grab the data on a boat named U.S.S. Enterprise both users start with the same data. The first user changes the name to U.S.S. Enterprise A. If the second user tries to change it again later, his changes won't be applied because he no longer has a valid copy of the data. He'll have to grab a current version of the data and try again. In database terms, if two users retrieve the same set of data, only the first user's changes will be applied. The second user's data will no longer be valid because the data source has changed, and his attempt will fail. ADO.NET can work with either type of concurrency, and luckily, it provides built-in mechanisms to handle both transparently for you. However, it's helpful to know how the process works in case you ever run into this problem. |
