Updating Your DataSet by Using the DataTable and DataRow Objects
Earlier in this chapter, we showed you how to update your database by using parameterized stored procedures. Although this is efficient for making single row changes, it isn't quite useful when you have a significant number of changes to pass to the server. What happens when you want to apply changes in bulk? Consider an e-commerce application that uses an online shopping cart. The shopping cart could have multiple rows of data that would be inserted and updated as the user browsed through the site. When it comes time to push these changes to the server, it would be much easier to pass them in one single batch, rather than call the stored procedure multiple times for each row that's modified.
In ADO 2.x, you use disconnected RecordSets along with the UpdateBatch() method to pass your changes on to the server. In ADO.NET, you pass the disconnected deltagram from the DataSet object to the DataAdapter Update() method. Once again, ADO.NET clearly draws the line between your data and your data source. The DataSet object doesn't directly contact the data source.
First, let's see how you can manage changes within a DataSet. As the user edits the in-memory cache, the changes are stored into a buffer and not yet committed to the DataSet. You can commit modifications to a DataSet by using the AcceptChanges() method of the DataSet, DataTable, or DataRow objects. If you execute this method on the parent object, it will propagate down onto the children. For example, if you call AcceptChanges() on the DataSet object, it will cascade down onto the DataTables within the DataSet's Table collection (likewise for a DataTable to its relevant DataRow collection).
When you insert a row into a DataTable, you can monitor the "dirtiness" of a row by examining the RowState property. Let's go ahead and add a new row to your dsCustomers DataSet. In Figure 6.5, we continue the logic that we used in Listing 6.3 to populate your dsCustomers DataSet.
NOTE Until you call the Update() method, your DataSet changes will not be committed to your data source.
First, let's look at the code that pulls down the data that you want to work with from your database into a DataSet. Using the existing DataSet, you will add a new row directly to the DataSet by using the DataTable and DataRow collections of the DataSet.
NOTE The code depicted in Figure 6.5 can be found in the Updating Data using ADO.NET.sln solution file, within the click event of the Inserting Data With DataSets and DataTables button.
As you see in Figure 6.5, DataSet updates are very straightforward. All you have to do is fill your DataSet, as we've shown you earlier in the chapter. Then you set up a new DataRow object with the DataTable's NewRow() method. The Add() collection of the Rows collection will add your new row to the collection. Finally, you call the AcceptChanges() method of the DataSet, which will automatically cascade all changes down to its inner DataTables and DataRows. Alternately, you could call the AcceptChanges() method specifically on the inner object you wish to update because the DataTable and DataRow also support the AcceptChanges() method.
As the note indicates, the source code for this example is available on the accompanying CD. Go ahead and load the code into Visual Studio .NET and place a breakpoint on the Add() method. Execute the code by pressing F5. When you get to your breakpoint, type the following in the Command window:
?dtcustomer.rows.count
Figure 6.5
Updating your DataSet object
File £dil View Projecl fiuild Debug Joois Window Help
■^Correction ^Microphone Tool? Handr
Commend Mode. Commend Window | AD0.NET for Ihe ADO Progremmer | Foiml ,vb [Design]1 Form I. vb' | Disassembly ' Fotml (Updaling_Dala_using_ADO.NET) l^* Button2_Click
: x i strSelectCustomers As String = "SELECT * FROH Customers ORDER BY Customer ID" i strConnString As String = "data source=[local);" £ _
"initial catalog=Northwind; integrated security=SSPI; " i connNorthwind As New SqlConnection[strConnString)
i daCustomers As New SqlDataAdapter[strSelectCustomers, connNorthwind)
Dim dsCustomers As New DataSet[) Dim dtCustomer As DataTable Dim drNewCustomer As DataRow connNorthwind.Open[)
daCustomers.Fill[dsCustomers, "dtCustomerTable") dtCustomer = dsCustomers.Tables["dtCustomerTable") Try drNewCustomer = dtCustomer.NewRow[) drNewCustomer["Customer ID") = "BABIS"
drNewCustomer["CompanyName") = "Daryl Gaitan Import/Expi
1 Add the row.
'Cascades changes from DataSet to all childre: 1 and DataRows dsCustomers.AcceptChanges[) Catch elnsertException As System.Exception HsgBox[elnsertException.Message) Throw elnsertException Finally
DataSet Table f. Create the DataRo"
intaining Customers for Insert i:
Populate DataSet
|
Command Window - Immediate |
* X |
|
93. |
3 |
|
-1 1 >1 | |
WARNING If you have difficulty working with the Command window, it might be because you are not in Immediate mode. If you see a > prompt, then this is most likely the case. Toggle the mode from Command mode to Immediate mode by typing immed at the prompt and pressing Enter. Now you should be able to debug your code.
You will see the number of rows in your Customers table, within your DataSet, prior to making changes. Hit F11 to step into the Add() method. This will update your DataSet with the newly added row. Go back to the Command window and hit the Up arrow key and Enter to re-execute the row count statement. The results will show that the Add() method increments your row count in your DataRow by one record. However, if you compare the result to the data in the database, you will see that your data still has the same number of original rows. This is an important point. None of your changes will be committed to the data source until you call the Update() method of the DataAdapter object. Finish the execution of the code to commit the changes in your DataSet.
In summary, all you have to do is execute the following steps to commit updates to your DataSet:
1. Instantiate your DataSet and DataAdapter objects.
2. Fill your DataSet object from the DataAdapter object.
3. Manipulate your DataSet by using the DataRow objects.
4. Call the AcceptChanges() method of the DataSet, DataTable, or DataRow object to commit your changes to your DataSet.
Post a comment