VB.NET  •  Save In-Memory Changes Locally

Listing 4. CommitUpdates is the central function that saves all in-memory DataSet changes to the local SQL CE database. A simple call to the DataSet's AcceptChanges method is all you need to save the changes, because you defined the Insert, Update, and Delete commands when you created the data adapter.

Public Function CommitUpdates() As String
Dim strMessage As String = ""
Dim iUpdateCount As Integer
Dim objSqlCon As SqlCeConnection

Try
' Need to make sure that you got a good connection 
' before
If _daDataAdapter Is Nothing Then
strMessage = "No DB Connection Available" & vbCrLf
strMessage &= "Try Synchronizing First" & vbCrLf
Return strMessage
End If

' You have to get a DB connection and associate it 
' with each of the commands
objSqlCon = InitializeDBConnection()
If objSqlCon Is Nothing Then
strMessage = "Unable to Connect to Database"
Return strMessage
End If
objSqlCon.Open()
_daDataAdapter.SelectCommand.Connection = objSqlCon
_daDataAdapter.InsertCommand.Connection = objSqlCon
_daDataAdapter.UpdateCommand.Connection = objSqlCon
_daDataAdapter.DeleteCommand.Connection = objSqlCon

' Do a mass update for the entire dataset to the 
' database
iUpdateCount = _daDataAdapter.Update(_dsContacts, _
   "Contacts")
_dsContacts.AcceptChanges()

' Now, quickly close the connection and release 
' resources
objSqlCon.Close()
objSqlCon = Nothing
_daDataAdapter.SelectCommand.Connection = Nothing
_daDataAdapter.InsertCommand.Connection = Nothing
_daDataAdapter.UpdateCommand.Connection = Nothing
_daDataAdapter.DeleteCommand.Connection = Nothing

Catch ex As SqlCeException
' Error handling code here
End Try
Return strMessage
End Function