Using Remote Data Access with SQL Server CE 2.0

Microsoft SQL Server CE edition is the database server built by Microsoft to run on mobile devices. Besides being a standalone database for mobile applications, SQL Server CE also allows you to connect to your desktop SQL Server 2000 and perform remote data access and merge replication. In this article, you will learn how to build a .NET Compact Framework mobile application using Visual Studio .NET 2003 and how it can perform remote data access using SQL Server CE 2.0.

For more information on .NET Compact Framework, see my previous article, "Developing Pocket PC Apps with SQL Server CE."

Features of SQL Server CE 2.0

Figure 1 shows the main components in SQL Server CE and its relationship to SQL Server 2000 (on the desktop).

Note: Figure is from SQL Server CE Books Online

Figure 1. Architecture of SQL Server CE and its relationship to SQL Server

On the client's end (mobile devices), the SQL Server CE Engine takes care of the data stored in the local SQL Server CE database. It also tracks records that are inserted, modified, or deleted when there is a need to maintain connectivity with the SQL Server on the desktop. The SQL Server Client Agent serves as the layer for programmatically manipulating SQL Server CE. It implements SQL Server CE objects such as the SQLCEEngine and Remote Data Access (RDA). This is the layer that your application interacts with when programming SQL Server CE.

On the server side, the SQL Server CE Server Agent acts as the middleman, mediating between SQL Server and SQL Server CE for connectivity issues. All of these interactions are done via HTTP, through the Web server.

Our Sample Application

In this article, I will build a mobile application that runs on Windows CE .NET devices (the code discussed will also work on Pocket PCs, as well). This application allows a teacher to take attendance in class using a mobile device and updates the information on a SQL Server 2000 database when he returns to the office.

The following diagram shows the flow of the system:

Figure 2. Flow of the system

1. User pulls students record from the SQL Server 2000 database, using ActiveSync, and stores the records in the local SQL Server CE database.
2. User takes attendance in class and modifies the local SQL Server CE database.
3. User pushes the records to SQL Server 2000 when he gets back to office.

Configuring IIS

To get started, let's now configure IIS for SQL Server CE's use:

Figure 3. Configuring virtual directory using the "Configure Connectivity Support in IIS" tool

Essentially, you want to create a virtual directory that contains the SQL Server CE Server Agent. As discussed, the use of the SQL Server Agent is to handle all requests via HTTP. It acts as a middleman between SQL Server and the SQL Server CE Client Agent. In our case, I will create a virtual directory named SQLCE and map it to the directory C:\Program Files\Microsoft SQL Server CE 2.0\Server\, which contains the sscesa20.dll SQL Server CE Server Agent file. You also need to configure the security for this virtual directory. You have the options of choosing Anonymous, Basic Authentication, or Integrated Windows Authentication. For simplicity, I have chosen Anonymous access, but you really should consider carefully against this option if you are deploying the application.

Configuring SDE

Visual Studio .NET 2003 comes with Smart Device Extension (SDE) integrated, so you can immediately start creating a .NET Compact Framework mobile application.

Figure 4. Visual Studio .NET 2003 now comes with Smart Device Application support

For this article, I will create a Windows CE application (though you can choose Pocket PC if you are targeting Pocket PC users):

Figure 5. Choosing the platform -- Pocket PC or Windows CE

On the design pane, populate the default Form1 with the following controls:

Figure 6. Populating the form

Also, add a Context Menu control with the following menu items:

Figure 7. Adding items to the Context Menu

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.

Related Reading ADO.NET in a Nutshell By Bill Hamilton, Matthew MacDonald

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"

    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.

Selecting, Updating, and Deleting Records

Now that the records are saved locally, the user can display the student information by selecting the class and lesson:

Figure 11. Displaying the student records from the local SQL Server CE database

So let's code the Display button now. I will have a function displayStudent() to do this. First, ensure that the class and lesson are selected:

Public Sub displayStudents()
    ' display the students according to Class and Lesson
    If cmbClass.Text = "" Or cmbLesson.Text = "" Then
            MsgBox("Please select Class and Lesson", _
                 MsgBoxStyle.Exclamation, "Select Class and Lesson")
            Return
    End If

Then, open the connection to the local SQL Server CE database and retrieve the appropriate records, and add three columns to the ListView control:

    conn.Open()
    Dim reader As SqlCeDataReader
    Dim cmd As New SqlCeCommand( _
        "SELECT StudentID, StudentName, " & _
        cmbLesson.Text & _
        " FROM Module WHERE ClassID='" & _
        cmbClass.Text & "'", conn)
    reader = cmd.ExecuteReader

    ListView1.Clear()
    ListView1.Columns.Add("StudentID", -1, _
                          HorizontalAlignment.Center)
    ListView1.Columns.Add("Name", 150, _
                          HorizontalAlignment.Left)
    ListView1.Columns.Add(cmbLesson.Text, -1, _
                          HorizontalAlignment.Center)

Finally, add the record to the ListView control:

    While reader.Read
        Dim ls As New ListViewItem(reader.Item("StudentID").ToString)
        ls.SubItems.Add(reader.Item("StudentName").ToString)
        ls.SubItems.Add(reader.Item(cmbLesson.Text).ToString)
        ListView1.Items.Add(ls)
    End While
    conn.Close()
End Sub

To change the attendance information of a student, select the student and right-click on it (on the emulator) to invoke the context menu.

Figure 12. Invoking the context menu to change the attendance of a student

This is done by servicing the Click event of each menu item:

Private Sub MenuItem1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MenuItem1.Click

Public Sub setStatus(ByVal status As String)
    ' set the status of the attendance
    Dim i As Integer
    Dim studentID As String
    Try
        i = ListView1.SelectedIndices.Item(0)
    Catch ex As Exception
        MsgBox("Please select item", MsgBoxStyle.Exclamation, _
         "Student not selected")
        Return
    End Try

    studentID = ListView1.Items.Item(i).Text
    Dim sql As String = "UPDATE Module Set " & cmbLesson.Text & _
       "='" & status & "' WHERE StudentID='" & studentID & "'"
    conn.Open()
    Dim cmd As New SqlCeCommand(sql, conn)
    cmd.ExecuteNonQuery()
    conn.Close()
    displayStudents()
End Sub

Essentially, the local SQL Server CE database is updated.

Pushing Data Back to SQL Server 2000

When the user gets back to the office, he can then push the updated data back into the SQL Server 2000. Here, we code the Push button.

As usual, set the connection string to connect to SQL Server 2000:

    Dim rdaOleDbConnectString As String = _
                    "Provider=sqloledb; Data Source=mercury2;" & _ 
                    "Initial Catalog=Attendance; User Id=sa;Password="

    ' 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"
        rda.Push("Module", rdaOleDbConnectString, _
                 RdaBatchOption.BatchingOn)
        MsgBox("Push operation completed", _
               MsgBoxStyle.Information, "Push")
    Catch err As SqlCeException
        ShowErrors(err)
    Finally
        rda.Dispose()
    End Try

That's it! You can now make changes to the SQL Server CE database and update the SQL Server 2000 database.

SQL Server CE Analyzer

Before I end this article, I want to quickly show you the included SQL Server CE Query Analyzer. When you first run your mobile application with the SQL Server CE assembly added, the SQL Server CE Query Analyzer will automatically be installed. You can find it at Programs->SQLCE Query:

Figure 13. Running SQL Server CE Query Analyzer

Using the SQL Server CE Query analyzer, you can connect to the local database and examine the database structure, as well as execute SQL queries.

Figure 14. Examining the local SQL Server CE database

Debugging Tips

As I worked through the sample codes in this article, there are various sources of errors that I encountered. Some are silly mistakes that I made, while a significant portion were caused by the quirks of Visual Studio .NET and the emulators. I will share with you some of the pitfalls and hopefully, you can save yourself some frustrating hours chasing after an error that wasn't caused by you.

• Always trap your errors by using the Try-Catch-Finally block and send the error object to the ShowErrors() method. The ShowErrors() method will reveal a lot more information and save you a lot of time wondering about the source of the error.
• Always stop your debugging process by closing the application within the emulator. Using the Stop button is Visual Studio .NET often causes errors the next time you run the application again. You will see an error message like the following:

The file 'SmartDeviceApplication3.pdb' cannot be copied to the run directory. The process cannot access the file because it is being used by another process. Could not copy temporary files to the output directory. In such cases, simply restarting Visual Studio .NET should solve the problem.

• I constantly encounter the following errors when I try the pull operation on my application for the first time:

Figure 15. A common error connecting to the IIS

After some trial and error, I realized that the problem could be solved most of the time by invoking Internet Explorer on the emulator and connecting to the URL http://mercury2/SQLCE/sscesa20.dll. This URL points to the SQL Server CE Server Agent, and you should see the following message in return: "SQL Server CE Server Agent." Try this a few times and your application should work fine. If not, restart your emulator and Visual Studio .NET.

Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.

Return to ONDotnet.com

Copyright © 2009 O'Reilly Media, Inc.