| To make this easier, we are going to create a method that we will call to update the database. We could probably emulate the old ADO Data Control's behavior, but instead let's create an UpdateChanges method: Public Sub UpdateChanges() Dim dsTemp As DataSet Try If mAdapter.InsertCommand.CommandText = "" And _ mAdapter.UpdateCommand.CommandText = "" And _ mAdapter.DeleteCommand.CommandText = "" Then Exit Sub End If Me.ParentForm.BindingContext(Me).EndCurrentEdit() If mDataSet.HasChanges Then mAdapter.Update(mDataSet.Tables(mintBoundTable).GetChanges) mDataSet.AcceptChanges() mDataSet.Clear() mAdapter.Fill(mDataSet, mintBoundTable) End If Catch errobj As Exception Throw errobj End Try End Sub First the procedure tests if the update commands have been created. If not it exits, assuming the control is read-only. If any of the commands are created, it attempts the update. It ends the current edit using the binding context. This is equivalent to the user moving off the row in the DataGrid. Then it checks to see if there were any changes. If there were, the HasChanges property of the DataSet returns True. Then we call the Update method of the DataAdapter. We use the GetChanges method of the DataSet to only send the changes back to the server. This reduces network traffic. Then we call the AcceptChanges method of the DataSet. This makes the pending changes permanent. We clear the DataSet and refill it from the database. We don't have to do this, but it refreshes the data and verifies that the data was updated. Every example I've seen does this, but it seems redundant and would have an adverse affect on network traffic. I've tried it both ways and both work fine. The only valid reason for doing this is so we can see any changes other users may have made to the database. I prefer a Refresh method for this but it depends on how volatile (how often the database is updated) the data is. If this table is being changed by hundreds of users simultaneously , then it is better to leave it in. Compile the control. Now open frmCtlTest's code window. Double-click the Update button we created earlier. Add the following code to the event procedure: Private Sub cmdUpdate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles cmdUpdate.Click Try UcDataNav1.UpdateChanges() Catch errobj As SqlClient.SqlException MsgBox(errobj.Message & vbCrLf & errobj.Procedure & _ vbCrLf & errobj.StackTrace) End Try End Sub Now run the project. Repeat the test we did earlier. Change something in the grid. Click the Update button. Close the program and run it again. What? The data still did not change! It did not update because there is no SQL command in the DataAdapter's UpdateCommand object. Now we have a choice, we can either create one manually (sigh!) or we can have the system create one for us. As it turns out, the library provides a class that does this very thing. Remember the SqlCommandBuilder object we created way back when? That is what it does. When you use the DataAdapter's configuration wizard to create the command objects, it uses this class. Add this procedure to the control: Private Sub GenerateSchema() Try If mAdapter.SelectCommand.CommandType = _ CommandType.StoredProcedure Then mAdapter.SelectCommand.Parameters.Clear() mSQLBuilder.DeriveParameters(mAdapter.SelectCommand) Else mSQLBuilder.RefreshSchema() mAdapter.UpdateCommand = mSQLBuilder.GetUpdateCommand If Not mAdapter.UpdateCommand Is Nothing Then If mAdapter.UpdateCommand.CommandType = _ CommandType.StoredProcedure Then mSQLBuilder.DeriveParameters(mAdapter.UpdateCommand) End If End If mAdapter.InsertCommand = mSQLBuilder.GetInsertCommand If Not mAdapter.InsertCommand Is Nothing Then If mAdapter.InsertCommand.CommandType = _ CommandType.StoredProcedure Then mSQLBuilder.DeriveParameters(mAdapter.InsertCommand) End If End If mAdapter.DeleteCommand = mSQLBuilder.GetDeleteCommand If Not mAdapter.DeleteCommand Is Nothing Then If mAdapter.SelectCommand.CommandType = _ CommandType.StoredProcedure Then mSQLBuilder.DeriveParameters(mAdapter.DeleteCommand) End If End If End If Catch errobj As Exception MsgBox(errobj.Message & vbCrLf & errobj.StackTrace) End Try End Sub We will use three functions of the command builder. The Refresh-Schema method generates the Insert, Update, and Delete SQL statements based on the Select statement. Since the SQL Builder object exists independently of the DataAdapter, the commands don't automatically propagate into the DataAdapter's command objects. That is what the Getxxx-Command methods do. They return precreated command objects. These are then assigned to the Insert, Update, and Delete commands respectively. The other method, DeriveParameters, is only used if the select command is a stored procedure. It gets the parameter list from the stored procedure. It has to access the database to do this so there is network overhead involved. I haven't tested the control with this functionality so I don't know if it will work. Next we have to determine when to call GenerateSchema. Since it requires a Select statement to base its other commands on, a good place would be the SelectCommandText property procedure. Make the procedure look like this: Public Property SelectCommandText() As String Get SelectCommandText = mAdapter.SelectCommand.CommandText End Get Set(ByVal Value As String) mAdapter.SelectCommand.CommandText = Value GenerateSchema() ReadData() End Set End Property Now compile the control. Open the frmCtlTest designer. Drill down to the DataAdapter's command objects. If we did everything right, the Insert, Update, and Delete SQL statements should be populated . Open up one of the SQL statements in the query designer. See, it created all the parameters for you! Open the Parameters property page. There they all are! The command builder class is a powerful class that can be used in many other applications. If you learned nothing else from this exercise, this alone would be worth it. Final Testing Now let's run the project. Change one of the values in the grid. Click the Update button. Close the form and run it again. Notice the data does change now. You can try deleting a row and inserting a new row as well. They all work. One last thing. Suppose we want to roll back the edits we've made. We can add one more method to the control: Public Sub CancelChanges() Try Me.ParentForm.BindingContext(Me).CancelCurrentEdit() mDataSet.RejectChanges() Catch errobj As Exception Throw errobj End Try End Sub Call this method from the Cancel Changes button's click event. The CancelCurrentEdit method of the binding context ends the edit of the current row without saving the changes. It is equivalent to the user clicking the Escape key while the DataGrid has the focus. The RejectChanges method of the DataSet does just that. It cancels all changes made to the DataSet since the last Fill command was executed. Let's compile it and try it out. Run the project. Make some changes to the grid data. Click the Cancel Changes button. The data reverts to its previous state. Deleted rows reappear and inserted rows disappear. | 
