Creating a Mobile Application with SQL Server Compact Edition

Switch View

Creating a Mobile Application with ...

Collapse All

SQL Server 2005 Compact Edition Books Online

In this walkthrough, you will learn how to create an application in Microsoft Visual Studio 2005 that uses Microsoft SQL Server 2005 Compact Edition. The SQL Server Compact Edition database will be a Subscriber to a SQL Server 2005 Publication and will use merge replication to download information from a SQL Server 2005 database to the SQL Server Compact Edition database.

In this walkthrough, you will follow these steps:

Configure a SQL Server 2005 publication.
Configure Internet Information Services (IIS) for replication.
Create a SQL Server Compact Edition subscription.
Create an application.
Deploy the application and test the subscription.

Prerequisites

To perform this walkthrough as it is written, you must have the following:

A computer that has Windows XP and IIS installed.
SQL Server 2005 Service Pack 2 (SP2) or a later version, installed on the same computer as Visual Studio.

SQL Server 2005 Tasks

Before you create the application, you must configure a publication in SQL Server 2005. In the following steps, you will create a sample database and then publish data from that database. You will create the database by using a prebuilt script file, and then create the publication with the New Publication Wizard

Create a database and populate with data

Open SQL Server Management Studio.
When you are prompted to connect to a server, type (local) for the Server Name, and then click Connect.

Open a new query window. Create a SQL Server Compact Edition database and populate the database with data.

USE master;
GO
IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'SQLMobile') 
BEGIN
    DROP Database SQLMobile;
END
GO
CREATE DATABASE SQLMobile;
GO
USE SQLMobile;
GO
CREATE TABLE MembershipData (MemberID INTEGER IDENTITY (1,1) 
    CONSTRAINT pkMemberID PRIMARY KEY, MemberName NVarChar (50));
CREATE TABLE FlightData (MemberID INTEGER FOREIGN KEY REFERENCES 
    MembershipData(MemberID), Destination NVarChar (50), FlightStatus 
    NVarChar(50), ArrivalDate DATETIME, FlownMiles INTEGER);
INSERT INTO MembershipData (MemberName) VALUES ('Mr Don Hall');
INSERT INTO MembershipData (MemberName) VALUES ('Mr Jon Morris');
INSERT INTO MembershipData (MemberName) VALUES ('Ms TiAnna Jones');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) 
    VALUES (1, 'Seattle', 'Flight Delayed 1 hour', '8/25/00', '20000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) 
    VALUES (2, 'London', 'Flight on time', '9/12/00', '15000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) 
    VALUES (3, 'Sydney', 'Flight Gate Closing', '11/5/00', '30000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) 
    VALUES (1, 'Tokyo', 'Delayed Fog', '5/25/00', '25000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) 
    VALUES (2, 'Minneapolis', 'Flight on time', '5/1/00', '1000');
INSERT INTO FlightData (MemberID, Destination, FlightStatus, ArrivalDate, FlownMiles) 
    VALUES (3, 'Memphis', 'Flight Gate Closing', '1/5/00', '1000');
GO

Click Execute (!) to run the script and create the database. The script runs and creates a new database named SQLMobile.

Note:
You can also press F5 or choose Execute from the Query menu to run the query.
To confirm that the database was created, in Object Explorer, expand (local), expand Databases, and then expand SQLMobile. If the SQLMobile database is not listed, update the list of databases by right-clicking Databases and selecting Refresh

Note:
You can also press F5 or choose Execute from the Query menu to run the query.

Prepare the Server for Publishing Data

Before you create a publication, you must prepare the server for publishing by creating a snapshot agent user account and creating a shared folder in which the snapshot files will be stored. When the snapshot folder is created, it is used for all publications on the server. If you have created publications previously on this server, you can skip these steps.

Create the snapshot user account

Open Computer Management from Administrative Tools in Control Panel.
In Computer Management, expand System Tools, expand Local Users and Groups, right-click Users, and then choose New User.
Type the following information in the New User dialog box, and then click Create:

Field	Value
User name	snapshot_agent
Password	p@ssw0rd
Confirm password	p@ssw0rd
User must change password at next logon	Not selected
Password never expires	selected

Important:
These settings should be used for testing only. In a production environment, make sure that your user account settings are in compliance with the network security requirements. Frequently, you will use a domain user account instead of a local user account for the snapshot agent.

Create the snapshot folder

In Windows Explorer, create a new folder named snapshot. For this walkthrough, you can create the folder in the root directory of drive C at c:\snapshot. Right-click the snapshot folder and select Sharing and Security.
On the Sharing tab, select Share this folder, and then click Permissions.
In Permissions for snapshot, click Add.
In Enter the object name to select, type computername\snapshot_agent where computername is the name of the local computer, click Check Names, and then click OK.
In Permissions for snapshot, select snapshot_agent, assign the Change and Read share permissions, and then click OK.
Select the Security tab.
Click Add.
In Enter the object name to select, type computername\snapshot_agent where computername is the name of the local computer, click Check Names, and then click OK.
Select snapshot_agent and add the Write permission to the list of enabled permissions. The snapshot_agent account will now be granted the following permissions:
- Read and Execute
- List Folder Contents
- Read
- Write
Click OK to close the snapshot Properties window.
Close Windows Explorer.

Create a publication

In Object Explorer in SQL Server Management Studio, expand the (local) node if it is not currently expanded, and then expand Replication.
Right-click the Local Publications folder and select New Publication.
In the New Publication Wizard introduction screen, click Next.
If you have not created a publication on this computer before, you will be prompted to configure a Distributor. Select the first option to use the local computer as its own Distributor, and then click Next.
If you have not created a publication on this computer before, you will be prompted to specify a snapshot folder. Type the share path of the snapshot folder that you created in the previous procedure. Type the share path in the form \\servername\share instead of the local path. In this walkthrough, we recommend that you type \\computer\snapshot, where computer is the name of your computer, and then click Next.
In the list of databases, select SQLMobile, and then click Next.
In the list of publication types, select Merge publication, and then click Next.
In the Subscribers Types page, select Yes to enable support for SQL Server Compact Edition subscribers, and then click Next.
In the list of objects to publish, select the Tables check box. If you expand Tables, you will see that both tables in the SQLMobile database are selected. Click Next.
You are notified that unique identifiers will be added to the tables. All merge articles require a uniqueindentifier column. Click Next.
In the Filter Table Rows page, you can now add filters to the published data. For this walkthrough, you will not filter the data. Click Next.
On the Snapshot Agent page, you can configure when the snapshot will be created and how frequently the Snapshot Agent runs. Click Next to accept the default settings.
In the Agent Security dialog box, click Security Settings.
1. In the Snapshot Agent Security dialog box, enter the logon information for the account you created in the previous procedure. The process account is computer_name\snapshot_agent (where computer_name is the name of your computer) and the password is p@ssw0rd.
2. Click OK to save the settings.
3. Click Next on the Agent Security page.
On the Wizard Actions screen, you can determine when the publication is created and whether you want a script file to be created. Clear Create a snapshot immediately, and then click Next.
On the Complete the Wizard screen, type SQLMobile for the name of the publication, and then click Finish.
The publication is created. When the wizard finishes, click Close.

Setting Permissions

You must grant permissions for the Snapshot Agent account and for the IIS anonymous user account. You must also add the IIS anonymous user account to the Publication Access List (PAL).

Set database permissions

In SQL Server Management Studio, in Object Explorer, expand Security, right-click Logins, and select New Login.
In the New Login dialog box, select Windows Authentication, click Search, type computername\snapshot_agent in the Enter the object name to select box, where computername is the name of your computer, click Check Names, and then click OK.,
In the Navigation Pane, choose the User Mapping pane.
In the list of databases, select distribution and the db_owner role, select SQLMobile and the db_owner role, and then click OK.
Right-click Logins, and select New Login.
In the New Login dialog box, select Windows Authentication, click Search, type computername\iusr_computername in the Enter the object name to select box, where computername is the name of your computer, click Check Names, and then click OK.,
In the Navigation Pane, select the User Mapping pane.
In the list of databases, select distribution and SQLMobile, and then click OK.

Granting publication access

In Object Explorer, expand Replication, expand Publications, right-click the [SQLMobile]:SQLMobile publication, and then click Properties.
In the Navigation Pane, select Publication Access List.
Click Add. In the Add Publication Access dialog box, the IUSR account is listed. Select it, and then click OK.
Make sure that the IUSR account is now in the PAL, and then click OK.

Creating the Publication Snapshot

Before you can initialize a subscription to the SQLMobile publication, you must create the publication snapshot.

Create the publication snapshot

In SQL Server Management Studio, in Object Explorer, expand the (local) computer node.
Expand the Local Publications folder, select the publication name, right-click SQLMobile, and then click View Snapshot Agent Status.
In the View Snapshot Agent Status dialog box, click Start.

Make sure that the snapshot job has succeeded before you continue.

Configuring IIS and SQL Server 2005 for Web Synchronization

Now that SQL Server has been configured with a publication, you must make that publication available over the network to SQL Server Compact Edition clients. SQL Server Compact Edition connects to SQL Server through IIS. Specifically, you create and configure a virtual directory that makes the SQL Server Compact Edition Server Agent available to the clients.

Install the SQL Server Compact Edition server components

In Windows Explorer, locate the following directory:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE
Double-click sqlce30setupen.msi to run the installation program.
On the introduction screen of the Setup wizard, click Next.
Read and accept the Microsoft Software License Terms, and then click Next.
On the System Configuration Check screen, make sure that all items pass. The last item on the list will be listed as a warning if you do not have SQL Server 2000 installed. Because you are using SQL Server 2005, this is not a problem. Click Next.
Click Next on the following screen, and then click Install to start installation.
Click Finish.

Configure the publication for Web synchronization

In SQL Server Management Studio, in Object Explorer, expand the (local) computer node.
Expand the Local Publications folder, select the publication name, right-click and then select Configure Web Synchronization.
On the introduction screen of the wizard, click Next.
On the Subscriber Type screen, choose SQL Server Compact Edition, and then click Next.
On the Web Server screen, type the name of your computer in the computer running IIS text box, if it is not already provided, and then click Create a new virtual directory.
In the tree that is displayed, expand the computer, expand Web Sites, and then select Default Web Site.
Click Next.
In the Alias text box of the Virtual Directory Information screen, type SQLMobile, and then click Next.

If you receive a warning dialog box, click Yes.
In the Secure Communications screen, select Do not require security channel (SSL).
Important In a production environment, you should enable SSL encryption to protect data exchanged over the Internet between the Subscriber and the Server Running IIS Client Authentication screen, select Clients will connect anonymously, and then click Next.
On the Anonymous Access screen, accept the default settings by clicking Next.
On the Snapshot Share Access screen, type \\computer\snapshot, where computer is the name of your computer, and then click Next.

If you receive a warning that the snapshot share is empty, click Yes.
Click Finish.
Click Close.
In Internet Explorer, enter the URL http://localhost/SQLMobile/sqlcesa30.dll?diag in Address and then click Go. This connects to the server in diagnostic mode. Make sure that the SQL Server Compact Edition Server Agent Diagnostics report shows success.

SQL Server Compact Edition Tasks

Before developing an application that will use SQL Server Compact Edition, it is frequently time-saving to pre-create the SQL Server Compact Edition database and subscription. SQL Server Management Studio lets you create and work with a SQL Server Compact Edition database on the local computer. You can then use this database when you develop your application.

Create a new SQL Server Compact Edition database

In SQL Server Management Studio, in Object Explorer, click Connect, and then choose SQL Server Compact Edition.
In the Database File field, choose <New Database…>.
In the file name text box, type c:\sqlmobile.sdf, and then click OK.

If you receive a warning about a blank password, click Yes. The database in this tutorial is not password protected or encrypted.
In the Connect to Server dialog box, click Connect.

A new node named SQL Server Compact Edition [My Computer\...\sqlmobile] is added to Object Explorer.

Create a new subscription

In Object Explorer, expand the SQL Server Compact Edition node, expand Replication, right-click Subscriptions, and then choose New Subscription.
On the introduction screen, click Next.
On the Choose Publication screen, in the Publisher drop down list, choose <Find SQL Server Publisher…>.
In the Connect to Server dialog box, type or choose the name of the local computer, and then click Connect.
On the Choose Publication screen, in the list of publications, expand SQLMobile, select the SQLMobile publication, and then click Next.
On the Identify Subscription screen, type SQLMobile for the subscription name, and then click Next.
On the Web Server Authentication screen, type the URL for the virtual directory you created in the previous procedure. In this walkthrough, you created a virtual directory with a URL of http://localhost/SQLMobile.
Click The Subscriber will connect anonymously, and then click Next.
On the SQL Server Authentication screen, click Next to accept the default settings.

On the final screen, the wizard displays sample code that you can use when you create a subscription in your application. Select the sample code (either Visual Basic or C#, depending on the language you will use to create the application) and copy the code. To do this, select the code and press CTRL+C. Start Notepad or another text editor and paste the sample code. You will use this code when you create the application in the following procedures.
After you have copied the sample code, click Finish.
Click Close.

Building an Application

Create a new Smart Device project

Open Visual Studio 2005.
On the File menu, select New Project.
In the New Project dialog box, in the Project Types tree, expand your development language and then select Smart Device.
In the list of templates, select the type of project you want to create. For this walkthrough, select Pocket PC 2003 Application.
Provide a name and location for the project, and then click OK. For this walkthrough, name the project SQLMobile.

Visual Studio creates a new project and displays Form1 as it will appear on a smart device.

Add references

In Solution Explorer, right-click References and choose Add Reference.

Note:
If the References folder is not listed in Solution Explorer, click Show All Files at the top of Solution Explorer.
In the list of .NET assemblies, select System.Data.SqlServerCe, and then click OK. If System.Data.SqlServerCe is not listed, follow these steps:
1. Click the Browse tab.
2. Locate the following directory:
  C:\Program Files\Microsoft Visual Studio 8\Common7\IDE
3. Select System.Data.SqlServerCe.dll, and then click OK.
The list of references in Solution Explorer now includes System.Data.SqlServerCe, and your project can use this assembly.
In Solution Explorer, right-click Form1.cs or Form1.vb and choose View Code.
At the top of the code for the form, add a directive to use the System.Data.SqlServerCe namespace:

[C#]
```
using System.Data.SqlServerCe;
```
[VB]
```
Imports System.Data.SqlServerCe
```

Note:
If the References folder is not listed in Solution Explorer, click Show All Files at the top of Solution Explorer.

Add a data connection

In the main window, switch back to the Design (default) view for Form1.
From the Data menu, click Add New Data Source.

Note If Add New Data Source is not displayed, select the Design view for Form1, and then see the Data menu again.
On the Choose a Data Source Type window, select Database, and then click Next.
In the Choose Your Data Connection dialog box, click New Connection.
If an Add Connection dialog box is displayed, click Change.
In the Choose Data Source dialog box, under Data Source, select Microsoft SQL Server Compact Edition (if there was an existing connection, this dialog box might be called Change Data Source). In the list of data providers, choose .NET Framework Data Provider for SQL Server Compact Edition. Click Continue or OK.
In the Add Connection dialog box, under Data Source, select My Computer.

In the Connection properties section, under Database, click Browse, and then browse for the database that you created in a previous procedure. If you have followed the steps, the database is located at c:\sqlmobile.sdf.
Click Test Connection, and then click OK to create the new data connection.
In the Choose Your Data Connection dialog box, click Next.
If a dialog box is displayed that asks you to copy your data file to the current project, click Yes.
In the Save Connection String window, click Next.
In the Choose Your Database Objects window, select Tables, and then click Finish

Choose the data to display

From the Data menu, choose Show Data Sources.
Drag the MembershipData table from the Data Sources window to the Form1 Design window. A datagrid is created on Form1 with the column names automatically provided.
Right-click the datagrid and then click Properties.
In the Properties window, change the Dock value to Top. You can do this by clicking the top bar of the graphical representation that appears, or by typing Top in the value field. The datagrid will be moved and resized to fill the top section of Form1.
In the upper-right corner of the datagrid, click the small arrow. From the menu that appears, choose Generate Data Forms.
Drag the FlightData table from the Data Sources window to the Form1 Design window. A datagrid is created on Form1 with the column names automatically provided.
You can use the Properties settings for this datagrid to set the Dock property to Bottom.

Adding code

On the code page for the application, you will add a string variable that holds the path and name of the database file, code to delete the database file if it already exists, and code to establish a connection to the SQL Server publication, synchronize the data, and create a new local database that contains the published data.

Add the code

From Solution Explorer, right-click Form1 and choose View Code.
In the code page, find the class definition for Form1. Add a string variable and assign it the path and file name for the .sdf file. The data source created by Visual Studio in previous steps expects the database file to reside in the \Program Files\ApplicationName folder, where ApplicationName is the name of the application. For example, if you named your new project SQLMobile, your string variable should be set to "\Program Files\SQLMobile\sqlmobile.sdf".

The first few lines of your class definition should resemble the following code:

[C#]

public partial class Form1 : System.Windows.Forms.Form
{
   private System.Windows.Forms.MainMenu mainMenu1;
   string filename = @"\Program Files\SQLMobile\sqlmobile.sdf";

   public Form1()
   {
      InitializeComponent();
   }

[Visual Basic]

Public Class Form1

    Dim filename As New String _
      ("\Program Files\SQLMobile\sqlmobile.sdf")

Create a new method that will delete the database file if it currently exists. This will make sure that the application loads the latest data every time that it runs. The method should be named DeleteDB. Your code should resemble the following:

[C#]
```
private void DeleteDB()
{
   if (System.IO.File.Exists(filename)) 
   {
      System.IO.File.Delete(filename);
   }
}
```
[Visual Basic]
```
Sub DeleteDB()
   If System.IO.File.Exists(filename) Then
      System.IO.File.Delete(filename)
   End If
End Sub
```

Create a new method named Sync that performs synchronization. To do this, you will use the code you copied from the New Publication Wizard in a previous step. When you paste the code, you must make two changes to the code:

Change the SubscriberConnectionString value so that it points to the correct path and file name, as specified in the file name variable.

Change the AddOption value from ExistingDatabase to CreateDatabase.
When complete, your Sync method should resemble the following:
[C#]

private void Sync()
{
   SqlCeReplication repl = new SqlCeReplication();

   repl.InternetUrl = @"http://<computername>/sqlmobile/sqlcesa30.dll";
   repl.Publisher = @"<computername>";
   repl.PublisherDatabase = @"SQLMobile";
   repl.PublisherSecurityMode = SecurityType.NTAuthentication;
   repl.Publication = @"SQLMobile";
   repl.Subscriber = @"SQLMobile";
   repl.SubscriberConnectionString = @"Data Source='" + filename + 
     "';Password='';Max Database Size='128';Default Lock Escalation 
     ='100';";
   try
   {
      repl.AddSubscription(AddOption. CreateDatabase);
      repl.Synchronize();
   }
   catch (SqlCeException e)
   {
      MessageBox.Show(e.ToString());
   }
}

[Visual Basic]

Sub Sync()
   Dim repl As New SqlCeReplication()
   repl.InternetUrl = "http://<computername>/SQLMobile/sqlcesa30.dll"
   repl.Publisher = "<computername>"
   repl.PublisherDatabase = "SQLMobile"
   repl.PublisherSecurityMode = SecurityType.NTAuthentication
   repl.Publication = "SQLMobile"
   repl.Subscriber = "SQLMobile"
   repl.SubscriberConnectionString = _
      "Data Source='" + filename + "';Password='';" _ 
      & "Max Database Size='128';Default Lock Escalation ='100';"
   try
      repl.AddSubscription(AddOption. CreateDatabase) 
      repl.Synchronize() 
   catch err as SqlCeException
      MessageBox.Show(err.ToString)
   end tryEnd Sub

Finally, add code to the beginning of the Form1_Load event handler that calls the two methods that you recently created. Your Form1_Load event handler should resemble the following:

[C#]

private void Form1_Load(object sender, EventArgs e)
{
   DeleteDB();
   Sync();

// TODO: Delete this line of code.
   this.flightDataTableAdapter.Fill(this.sqlmobileDataSet.FlightData);
// TODO: Delete this line of code.
   this.membershipDataTableAdapter.Fill(this.sqlmobileDataSet.MembershipData);
}

[Visual Basic]

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   DeleteDB()
   Sync()

   'TODO: Delete this line of code.
   Me.FlightDataTableAdapter.Fill(Me.SqlmobileDataSet.FlightData)
   'TODO: Delete this line of code ...
   Me.MembershipDataTableAdapter.Fill(Me.SqlmobileDataSet.MembershipData)
End Sub

Deploy and Test the Application

Deploy the application

From the Debug menu, choose Start Debugging.
If the Deploy dialog box is displayed, choose Pocket PC 2003 SE Emulator, and then click Deploy.
The emulator opens in a new window. The first time that you deploy the application to the emulator, the .NET Compact Framework and SQL Server Compact Edition are installed. This might take several minutes. When they are installed, your application is installed and runs.

Your application loads and displays the two datagrids. When you click a value in the MembershipData datagrid, the data in the FlightData datagrid updates automatically.
Close the application, and in Visual Studio, from the Debug menu, click Stop Debugging.

Concepts

Securing Databases (SQL Server Compact Edition)

Help and Information

Getting SQL Server Compact Edition Assistance