VB.NET  •  Create a Common Data Adapter Object

Listing 2. InitializeDataAdapter creates a common data adapter object, complete with Insert, Update, and Delete commands. You can then use the data adapter throughout the application as a common method for updating the SQL CE local database.

Private _daDataAdapter As SqlCeDataAdapter
Private _dsContacts As DataSet
Public Function InitializeDataAdapter() As String
Dim strMessage As String
Dim objDBConfig As New CMDatabaseConfig
Dim objCommand As New SqlCeCommand
Dim objUpdateCommand As SqlCeCommand
Dim objSelectCommand As SqlCeCommand
Dim objDeleteCommand As SqlCeCommand
Dim objInsertCommand As SqlCeCommand

' Check to see if the local DB exists
If Not File.Exists( _
   "\My Documents\ContactManagement.sdf;")
Then
strMessage = "Local database does not exist" & vbCrLf
strMessage += "Please Sync with the DB server" & _
   vbCrLf
       Else
' Create a global connection and data adapter
          _daDataAdapter = New SqlCeDataAdapter

' INSERT Command
_daDataAdapter.InsertCommand = New SqlCeCommand
_daDataAdapter.InsertCommand.CommandText = _
   _strInsertCommand
_daDataAdapter.InsertCommand.Parameters.Add _
   (New SqlCeParameter("@id", _
   SqlDbType.UniqueIdentifier, 16, "id"))
_daDataAdapter.InsertCommand.Parameters.Add _
   (New SqlCeParameter("@LastName", _
   SqlDbType.NVarChar, 50, "LastName"))
_daDataAdapter.InsertCommand.Parameters.Add _
   (New SqlCeParameter("@FirstName", _
   SqlDbType.NVarChar, 50, "FirstName"))
_daDataAdapter.InsertCommand.Parameters.Add _
   (New SqlCeParameter("@Address", _
   SqlDbType.NVarChar, 127, "Address"))
_daDataAdapter.InsertCommand.Parameters.Add _
   (New SqlCeParameter("@City", SqlDbType.NVarChar, _
   50, "City"))
_daDataAdapter.InsertCommand.Parameters.Add _
   (New SqlCeParameter("@State", SqlDbType.NChar, _
   2, "State"))
_daDataAdapter.InsertCommand.Parameters.Add _
   (New SqlCeParameter("@Zip", SqlDbType.NVarChar, _
   15, "Zip"))

' SELECT Command
_daDataAdapter.SelectCommand = New SqlCeCommand
_daDataAdapter.SelectCommand.CommandText = _
   "SELECT id,CONVERT(nvarchar(50),id) as " & _
   "IDString, LastName, FirstName, Address, " & _
   "City, State, Zip FROM Contacts"

' UPDATE Command
_daDataAdapter.UpdateCommand = New SqlCeCommand
_daDataAdapter.UpdateCommand.CommandText = _
   _strUpdateCommand
_daDataAdapter.UpdateCommand.Parameters.Add _
   (New SqlCeParameter("@LastName", _
   SqlDbType.NVarChar, 50, "LastName"))
_daDataAdapter.UpdateCommand.Parameters.Add _
   (New SqlCeParameter("@FirstName", _
   SqlDbType.NVarChar, 50, "FirstName"))
_daDataAdapter.UpdateCommand.Parameters.Add _
   (New SqlCeParameter("@Address", _
   SqlDbType.NVarChar, 127, "Address"))
_daDataAdapter.UpdateCommand.Parameters.Add _
   (New SqlCeParameter("@City", SqlDbType.NVarChar, _
   50, "City"))
_daDataAdapter.UpdateCommand.Parameters.Add _
   (New SqlCeParameter("@State", SqlDbType.NChar, _
   2, "State"))
_daDataAdapter.UpdateCommand.Parameters.Add _
   (New SqlCeParameter("@Zip", SqlDbType.NVarChar, _
   15, "Zip"))
_daDataAdapter.UpdateCommand.Parameters.Add _
   (New SqlCeParameter("@id", _
   SqlDbType.UniqueIdentifier, 16, "id"))

' DELETE Command
_daDataAdapter.DeleteCommand = New SqlCeCommand
_daDataAdapter.DeleteCommand.CommandText = _
   _strDeleteCommand
_daDataAdapter.DeleteCommand.Parameters.Add _
   (New SqlCeParameter("@id", _
   SqlDbType.UniqueIdentifier, 16, "id"))
End If

Return strMessage
End Function