Developing SQL Server CE based Pocket PC Applications using Visual Studio.NET

This article focuses on installing, working with and developing SQL Server CE based applications using a Pocket PC emulator with Visual Studio.NET 2003.

Contributed by Jagadish Chaterjee
Rating:

/ 24
May 17, 2006

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Installing SQL Server CE onto a Pocket PC emulator: developing the application

Before proceeding with the following steps, make sure that you have "Smart Device Extensions" installed (which need to be selected during the Visual Studio.NET setup).

Let us begin now:

Open Microsoft Visual Studio.NET 2003.
Go to File -> New -> Project.
Within the "New Project" Dialog, select "Visual Basic Projects" from the "Project Types" and "Smart Device Application" from the "templates" (as shown in Fig1).

Currently, I left the name of the application "SmartDeviceApplication1." You can change it according to your requirements.
Once you click "OK," you will be presented with the "Smart Device Application Wizard." Select "Pocket PC" as the platform of target and "Windows application" as "project type" (as shown in Fig2).

Click "OK" to accept your settings. You will be presented with a form (very similar to a Visual Basic 6 form), where you can design with the controls available in the toolbox.
Right click on your project and go to "Add References." Select "System.data.common" and "System.data.SqlServerCE" and click "select," then finally click on "OK" (as shown in Fig3).

Drag a "Button" control from your toolbox onto the form and write the following code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click

MessageBox.Show("This app mainly used for deploying SQL
Server CE on to this mobile device")

End Sub

The above is not necessary. You can consider it a dummy. Our main intention is to have SQL Server CE deployed on the Pocket PC emulator. For that purpose, I need to have those two assemblies added to the project.

Installing SQL Server CE onto a Pocket PC emulator: deploying the application

Before proceeding with the following steps, make sure that you completed all the steps in the previous section.

Execute your application by pressing F5 and you should be looking at something like the following (Fig4).

If necessary, you can also click the "Set As Default" button and switch off the check box to not show in the future.
Finally click on "Deploy."
After a few seconds, your Pocket PC emulator should look like the following image (fig5).

After a few more seconds, you should observe the emulator behaving as shown in the following image (fig6).

You can observe that the first part of the above figure (fig6) shows the installation of .NET Compact Framework and the second part shows the SQL Server CE installation (which is after the .NET Compact Framework). Finally, after a few more seconds, we will see our application with the button. Once you click on that button, it simply shows a message something like the following (fig7):

The above process is repeated every time you execute your application. If you face any deployment errors, just close any existing emulators and try pressing F5 again (without switching on the emulators manually by yourself).

Where did the installation actually take place in the Pocket PC?

Don't just be happy after your deployment! We need to really understand where our applications actually were installed.

Let us check where our "SmartDeviceApplication1" was installed in the Pocket PC. Go through the following steps:

Within the "Pocket PC" emulator, click on Start -> Programs. You should be able to see the "File Explorer" icon as follows (fig8).

Click on the "File Explorer" icon. You will be taken to "My Documents" (by default) as shown in the following image (Fig9).

You click on "My Documents" (boxed off in red in the above image) and select "My Device."
Once you see "My Device" at the top, proceed by clicking on green marks (and try to observe the red marks) as shown in the following image (fig10). In fact, you are simply clicking on MyDevice -> Program Files -> SmartDeviceApplication1->SmartDeviceApplication1.exe.

Now, as you understand our application and where it stays, we need to search for the SQL Server CE folder. Actually, it is pretty simple; once you search, you should be able to find something like the following (fig11):

You can play with the "Query Analyzer" (shown in the above figure) to create and work with SQL Server CE databases.

Creating an SQL Server CE database programmatically

Now, it is time to create an SQL Server CE database programmatically (or dynamically).

To work with SQL Server CE, we need to import two namespaces (which are to be referred as shown in previous sections), namely "System.Data.Common" and "System.Data.SqlServerCe."

To create (or manage) an SQL Server CE database, we need to work with the class "System.Data.SqlServerCe.SqlCeEngine." We need to create an object out of it and call a method, "CreateDatabase," which simply creates a database based on the connection string we pass (along with an optional password). It is always a good idea to "dispose" of the "SqlCeEngine" once it completes its job.

Putting it all together, you can modify your code in the previous example in such a way that it looks like the following:

Imports System.Data.Common

Imports System.Data.SqlServerCe

Public Class Form1

Inherits System.Windows.Forms.Form

Friend WithEvents Button1 As System.Windows.Forms.Button

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

Dim connStr As String

connStr = "Data Source = emp.sdf; Password = jagchat"

Dim engine As SqlCeEngine

engine = New SqlCeEngine(connStr)

engine.CreateDatabase()

engine.Dispose()

MessageBox.Show("Successfully created")

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

End Class

Execute the above code by pressing F5. Once you click on "Button1," you should be able to see a message box showing "Successfully created." You can check the file "emp.sdf" in your root directory (or in "My Device" as specified in the previous section) as shown in the following image (fig12).

You can try clicking on the file (emp.sdf) to open it using Query Analyzer. It will give the message "Invalid Password" and ask for the password. Once you provide the password (in this case it is "jagchat"), it will open the database (which is generally empty) and show something like the following (Fig13).

I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Pocket PC SDK 2003 (along with additional emulators downloaded). The entire solution for this article is freely available in the form of a zip downloadable.

In my upcoming articles, I shall concentrate further on developing advanced Pocket PC applications. So, keep close tabs on this website :)

Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com