Using Remote Data Access with SQL Server CE 2.0
Pages: 1, 2, 3

To make use of SQL Server CE in the project, you need to add a reference to the SQL Server CE assembly. In Solution Explorer, add a reference to the following:

Figure 8. Referencing the SQL Server CE assembly

We are now ready to code!

Creating a database

In Visual Studio .NET 2003, switch the view to Code View by clicking on the View Code button in Solution Explorer.

Figure 9. Switching to Code View

Import the following namespaces:

Imports System.Data.SqlServerCe
Imports System.Text
Imports System.IO

And define a global variable, conn, for connecting to the local SQL Server CE database:

Dim conn As New SqlCeConnection("Data Source=\My Documents\ssce.sdf")

The first block of code that we will write is for the Pull button. Essentially, the Pull button will retrieve records from SQL Server 2000 and save it into the local SQL Server CE database.

Before the records can be retrieved and saved locally, we need to create a SQL Server CE database on the mobile device. We can do this using the createDB() function:

Public Sub createDB()
    ' if database exists, delete it and create a new one
    If File.Exists( _
      "\My Documents\ssce.sdf") Then
        File.Delete("\My Documents\ssce.sdf")
    End If
    ' Create a new database
    Dim sqlEngine As New SqlCeEngine( _
      "Data Source=" & _
      "\My Documents\ssce.sdf")
    sqlEngine.CreateDatabase()
End Sub

I first check to see if the database is present. If it is, I will delete it. Finally I create a new empty database using the SqlCeEngine object.

Pulling Data from SQL Server 2000

Once the empty database is created, I will proceed to retrieve the records from the SQL Server 2000 database. We will use the Remote Data Access (RDA) object to do this.

Here's my SQL Server 2000 database design:

Figure 10. Database structure on SQL Server 2000

First, create a connection string to connect to the SQL Server 2000 on the desktop:

' Connection string to SQL Server 2000
Dim rdaOleDbConnectString As String = _
        "Provider=sqloledb; Data Source=mercury2;Initial" & _
        " Catalog=Attendance;User Id=sa;Password="

Next, create an RDA object to connect to the SQL Server CE database on the mobile device:

' Initialize the RDA object.
Dim rda As SqlCeRemoteDataAccess = Nothing
Try
        rda = New SqlCeRemoteDataAccess
        rda.InternetUrl = "http://mercury2/SQLCE/sscesa20.dll"
        rda.LocalConnectionString = _
		           "Provider=Microsoft.SQLSERVER." & _
                   "OLEDB.CE.2.0;Data Source=\My Documents\ssce.sdf"

Recall that I configured my virtual directory with anonymous access, so I do not need to pass it the user's credential. But if the virtual directory requires authentication, you need to pass in the user's info via:

    rda.InternetLogin = "UserName"
    rda.InternetPassword = "Password"

You can now retrieve records from the SQL Server 2000 using the Pull method of the RDA object:

    rda.Pull("Module", "Select * from Module", _
         rdaOleDbConnectString, _
         RdaTrackOption.TrackingOnWithIndexes, _
         "ErrorTable")
    MsgBox("Pull operation completed", _
	       MsgBoxStyle.Information, _
		   "Pull")

Note that I have turned on tracking using RdaTrackOption.TrackingOnWithIndexes. SQL Server CE will track the changes made to the table and you can update the changes back to SQL Server at a later time. Finally, catch any errors that you may have:

Catch err As SqlCeException
    ShowErrors(err)            
Finally
    rda.Dispose()
End Try

One thing I have to stress here is the importance of the ShowErrors() method for displaying the errors that you may encounter in the process of pulling from SQL Server 2000. There are several potential sources of errors that may appear when you perform the pull operation. It may be due to a missing SQL Server CE database, an existing table on SQL Server CE, or an error connecting to the remote database. And most frequently, I have problems communicating with IIS. The ShowErrors() method can be found from the SQL Server CE documentation and I reproduce it here:

Public Shared Sub ShowErrors(ByVal e As SqlCeException)
    Dim errorCollection As SqlCeErrorCollection = e.Errors

    Dim bld As New StringBuilder
    Dim inner As Exception = e.InnerException

    If Not inner Is Nothing Then
        MessageBox.Show(("Inner Exception: " & inner.ToString()))
    End If
    Dim err As SqlCeError
    ' Enumerate each error to a message box.
    For Each err In errorCollection
        bld.Append((ControlChars.Cr & _
                    " Error Code: " & _
                    err.HResult.ToString("X")))
        bld.Append((ControlChars.Cr & _
                    " Message   : " & _
                    err.Message))
        bld.Append((ControlChars.Cr & _
                    " Minor Err.: " & _
                    err.NativeError))
        bld.Append((ControlChars.Cr & _
                    " Source    : " & _
                    err.Source))
        ' Retrieve the error parameter 
        ' numbers for each error.
        Dim numPar As Integer
        For Each numPar In err.NumericErrorParameters
            If 0 <> numPar Then
                bld.Append((ControlChars.Cr & _
                            " Num. Par. : " & _
                            numPar))
            End If
        Next numPar
        ' Retrieve the error parameters for each error.
        Dim errPar As String
        For Each errPar In err.ErrorParameters
                If [String].Empty <> errPar Then
                        bld.Append((ControlChars.Cr & _
                                    " Err. Par. : " & _
                                    errPar))
                End If
        Next errPar
        MessageBox.Show(bld.ToString())
        bld.Remove(0, bld.Length)
    Next err
End Sub

Trust me, displaying errors using the ShowErrors() method can save you from some really hair-pulling experiences.

Pages: 1, 2, 3

Next Page