Updating the Database

Team-Fly team-fly    

ADO.NET Programming in Visual Basic .NET
By Steve  Holzner, Bob  Howell

Table of Contents
Chapter 11.   Creating Your Own Data Control

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.

Team-Fly team-fly    

ADO. NET Programming in Visual Basic. NET
ADO.NET Programming in Visual Basic .NET (2nd Edition)
ISBN: 0131018817
EAN: 2147483647
Year: 2005
Pages: 123

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net