Beginning Visual Basic .NET Database Programming phần 10

Disconnected Data Imagine we have to update the Products table. (The actual algorithm we`re going to put together will work on any DataSet, not just one drawn from the Products table.) Here`s what we`ll do: Create a new method called SetProductDetails on ProviderConnection. This method will accept a DataSetof rows drawn from the Products table. This will be known as the "Changed DataSet". We`ll examine each row in the Changed DataSet in turn, looking for ones that have their RowState property set to Modified. When we find one, we`ll get the same product back from the database. This time, however, we`ll keep the SqlDataAdapter around and keep it bound to the DataSet. This new DataSetwill be called the "Master DataSet". All of the columns in the applicable row in the Changed DataSet will be copied to the matching column in the Master DataSet. We`ll use the SqlDataAdapter object`s Update method to make the changes to the database itself. This technique will work whether the Changed DataSet is passed directly to DirectConnection or through RemoteConnectionand the Web Service. The only drawback is that we have to create two SqlDataAdapter objects whereas, if we only had to deal with a direct connection, we`d need just one. Building "SetProductDetails" The first thing we need to do is add SetProductDetailsto the abstract ProviderConnection object. Try It Out – Building "SetProductDetails" 1. Open the code editor for ProviderConnection. Add this method: ` GetSuppliers - get the entire supplier list... Public MustOverride Function GetSuppliers() As DataSet ` SetProductDetails - set the details for products... Public MustOverride Sub SetProductDetails(ByVal products As DataSet) End Class 2. Open RemoteConnectionand add "stub" method. As before, we`ll come back and fill this in later. Public Overrides Sub SetProductDetails(ByVal products As System.Data.DataSet) End Sub 3. Open DirectConnection and add this method: 55 Chapter 14 ` SetProductDetails - save changes to changed products... Public Overrides Sub SetProductDetails(ByVal products As System.Data.DataSet) SaveChanges("ProviderGetProductDetails", "@productId", products) End Sub 4. Then, add the SaveChanges method. ` SaveChanges - save changes to changed rows... Protected Sub SaveChanges(ByVal selectStoredProc As String, _ ByVal selectParamName As String, _ ByVal changedDataSet As DataSet) ` Need to hold a database connection... Dim connection As New SqlConnection(Provider.DbString) connection.Open() ` Go through each row in the master dataset... Dim changedRow As DataRow For Each changedRow In changedDataSet.Tables(0).Rows ` Has it changed? If changedRow.RowState = DataRowState.Modified Then ` Get the id of the changes item... Dim changedId As Integer = changedRow.Item(0) ` Get the master row by using the adapter... Dim adapter As SqlDataAdapter = _ GetDataAdapter(connection, selectStoredProc, _ selectParamName, changedId) ` Create a command builder and bind it to the adapter... Dim builder As New SqlCommandBuilder(adapter) ` Fill a new dataset... Dim masterDataSet As New DataSet() adapter.Fill(masterDataSet) ` Get the row from this dataset... Dim masterRow As DataRow = masterDataSet.Tables(0).Rows(0) ` Copy the changes from one to the other... Dim dataValue As Object, index As Integer index = 0 For Each dataValue In changedRow.ItemArray masterRow.Item(index) = dataValue index += 1 Next ` Tell the adapter to update... adapter.Update(masterDataSet) 56 Disconnected Data End If Next ` Close the connection... connection.Close() End Sub 5. Open the Form Designer for Form1 and add a new button control next to the Load button. Change the Name property of the new button to btnSave. 6. Double-click on the Save button to create a new Click event handler. Add this code: Private Sub btnSave_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnSave.Click ` Save changes... Provider.Connection.SetProductDetails(ProductDataSet) ` Report the save... MsgBox("The changes have been saved.") End Sub How It Works We`re going to hold off explaining how the code works until we can actually run SetProductDetails, which we`ll do in a short while. 57 Chapter 14 Testing The Changes Before we run the project, we have to make sure that a primary key has been defined on the Products table. Without a primary key, SqlCommandBuilderwill be unable to form the appropriate query to make the database changes. A primary key is necessary as the SqlCommandBuilder uses this information to generate the necessary SQL WHERE clause. Try It Out – Checking the Primary Key and Testing the Code 1. Using the Server Explorer, find the Products table item within NorthwindSQL. 2. Right-click on Products and select Design Table. 3. If the ProductID column does not have a small key icon in the selection margin, right-click ProductID and select Set Primary Key. You should end up with something like this: 4. Select File | Save Products from the menu to save the changes to the definition. Close down the definition window. 5. Click on the Products table in Server Explorer once more, and this time select Retrieve Data From Table. The first item should list a product with ID of 1 and a name of Chai. 6. Run the project. Click the Load button to load the product from the database and change the name to Chai Tea. 58 Disconnected Data 7. Click the Save button. You see a message box telling you that the changes have been saved. 8. Flip back to Visual Studio and find the listing of rows from the Products table again. Right-click on any column in any one of the rows and select Run. You should now see that the underlying database data is now the same as the values entered into the DataGrid. How It Works Whenever changes are made to the edit control, the related DataSet is automatically updated. We hold the DataSetcontaining the products for editing in the ProductDataSet member. When the Save button is clicked, we pass this DataSet over to the SetProductDetails member of the current ProviderConnection object, in this case DirectConnection. ` Save changes... Provider.Connection.SetProductDetails(ProductDataSet) ` Report the save... MsgBox("The changes have been saved.") SetProductDetails defers processing of the changes to an internal helper method called SaveChanges. This method is a general-purpose function that isn`t just tied to working with DataSets drawn from the Products table. ` SetProductDetails - save changes to changed products... Public Overrides Sub SetProductDetails(_ ByVal products As System.Data.DataSet) SaveChanges("ProviderGetProductDetails", "@productId", products) End Sub Let`s take a close look at SaveChanges. The first thing we need to do is establish a connection to the database. 59 ... - tailieumienphi.vn