(c) 2003 Visual Studio Magazine 
Fawcette Technical Publications

VB.NET	Implement Synchronization
Listing 1	This code allows you to create and maintain a synchronization with merge replication using the SqlCeReplication object. The object's properties should match those that you use to create the Publication. You can use the InternetUrl and InternetPassword properties to match the required IIS virtual directory authentication credentials. If this is the first synchronization, use the Add.Subscription method, which creates a new local database. Subsequent calls to the Synchronize method synchronize changes with the remote Merge Agent (through IIS).

Imports System.Data.SqlServerCe
Imports System.Data.SqlServerCe.SqlCeException
Dim oRepl As New SqlCeReplication

' Set the values for the CE replication object
oRepl.Publisher = "<SQL Server Name>"
oRepl.PublisherDatabase = "ContactManagement"
oRepl.Publication = "CMMerge"
oRepl.PublisherLogin = "<Valid SQL Login>"
oRepl.PublisherPassword = "SQL Password"
oRepl.SubscriberConnectionString = _
	"Provider=Microsoft.SQLServer.OLEDB.CE.2.0; " & _
	"Data Source=\My Documents\ContactManagement.sdf;"
oRepl.Subscriber = "CEDevice"
oRepl.InternetUrl = "http://www.yoursever.com/sqlce"
oRepl.InternetLogin = _
	"<Username From Virtual Directory Security>"
oRepl.InternetPassword = "Password"

' Make sure we get updates as well as send them
oRepl.ExchangeType = ExchangeType.BiDirectional
Try
	' If the local DB exists, then just synch to it,
	If Not File.Exists( _
	"\My Documents\ContactManagement.sdf") Then
	' Tell SQL CE to create the database
	oRepl.AddSubscription(AddOption.CreateDatabase)
	End If
	' Now synchronize with the current snapshot 
	' in Merge Replication
	oRepl.Synchronize()
	Catch ex As SqlCeException
	'TODO: Add Error handling code here
	End Try

	' Cleanup
	oRepl.Dispose()

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

VB.NET	Retrieve the Contacts
Listing 3	RetrieveContacts provides a common function to get a list of contacts from the local SQL CE database. Notice that the properties and methods for a SQL CE database operation (SqlCeCommand, SqlCeDataAdapter) are similar to their SQL Server counterparts.

Public Function RetrieveContacts() As String
Dim ds1 As New DataSet
Dim strMessage As String = ""
Dim objCommand As SqlCeCommand
Dim drRow As DataRow

' Check to see if we have a cached data adapter
If _daDataAdapter Is Nothing Then
strMessage = InitializeDataAdapter()
End If
' Check for an error initializing the data adapter to 
' the local DB
If strMessage <> "" Then
Return strMessage
Else
Try
' Get a database connection first
Dim objSqlCon As SqlCeConnection
objSqlCon = InitializeDBConnection()
If Not (objSqlCon Is Nothing) Then
' Open the database connection
objSqlCon.Open()

' Open the local DB and retrieve all records into the 
' dataset
_daDataAdapter.SelectCommand.Connection = objSqlCon
_daDataAdapter.Fill(ds1, "Contacts")
_dsContacts = ds1

' Quickly close the database connection
objSqlCon.Close()
End If
Catch ex As SqlCeException
' Error handling code here
End Try
End If
Return strMessage
End Function

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 we 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

' We 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


??

??

??

??

Creating mobile database applications with the .NET Compact Framework and SQL CE 2.0
Author:  D. Thews
Last Updated: 7/22/2003
Page 5 of 18




