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."
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.
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|
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.
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!
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
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
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:
You can now retrieve records from the SQL Server 2000 using the Pull method of the RDA object:
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.
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
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
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:
In each menu item, we invoke the
Private Sub MenuItem1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MenuItem1.Click
setStatus()method with the appropriate argument --
ABfor absent and
MCfor Medical Certificate:
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.
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:
And finally, create the RDA object and push the local SQL Server CE table back to the SQL Server 2000 database:
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.
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|
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.
Try-Catch-Finallyblock and send the error object to the
ShowErrors()method will reveal a lot more information and save you a lot of time wondering about the source of the error.
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.
|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.