The little database that could. Deploying SQL Mobile as a local / single user database across Microsoft Mobile devices

UPDATE - April 6th 2006, Paul Flessner announced that SQL Mobile will released as SQL Server Everywhere Edition.

This will be the first of a several part series of blogicles that cover some of the thinking we’re doing around the Occasionally Connected scenarios, and what you can do today with Visual Studio 2005. …ok, so today you can use a beta or CTP build, but with technology that will be released really, really soon…

SQL Mobile now runs on the Win32 platform
In Whidbey we now support deploying SQL Mobile, the new version of SQL CE, directly on the Tablet PC. Why SQL Mobile compared to SQL Express? With SQL Mobile you can design, develop and deploy a single database, single set of data access code across your Smart Phone, Pocket PC and Tablet PC applications. Why is this important? Shops that are deploying Smart Client applications that go mobile aren’t limited to one device type. The same application may be needed across different form factors. Just as mail is read on your Smart Phone, Browser and Outlook client, so are custom applications.
A company may be deploying a beverage sales and delivery system. Some of the delivery people simply deliver based on route given to them in the morning. They barcode scan the packages as they deliver them and sync their local database with corporate when they return. This person really only needs a minimal screen, input UI and a bar code scanner.
Another delivery person has a lot of information that needs to be filled out so they opt for a Tablet PC user interface so they can still get pen based input, but now they can see a larger set of information at one time. Using SQL Mobile, developers can now build the same data access layer, business layer, sync layer and local database across all these device apps. They then simply focus on different UI projects for each device.

Comparing SQL Express and SQL Mobile
In future blogicles I’ll go into more details comparing SQL Express and SQL Mobile. Essentially both have their markets and both are excellent products, they just have slightly different user models. Both SQL Mobile and SQL Express support Merge Replication. SQL Mobile has an additional replication technology called Remote Data Access (RDA) which is very easy to use, but not as powerful merge replication. In Whidbey, Visual Studio 2005, SQL Mobile also has an updatable resultset (SqlCeResultSet) which really simplifies data access for local databases. SQL Mobile can be deployed via ClickOnce without admin rights. It's entire runtime is < 1.4mb. notice the decimal
SQL Express is essentially SQL Server, so it has all the power features from a multi-user, big/bad mofo type database. Because it’s a service, you’ll require Admin rights to install and it’s quite big. For the 32bit platform, the compressed install is 55mb, and this doesn't include the replication components. If you’re deploying a multi-user application, SQL Express is perfect. If you’re deploying a local, single user database, SQL Mobile is your starting point as it really nails the local database scenarios.

Deploying SQL Mobile via ClickOnce
To get a feel how easy it is to deploy SQL Mobile with Click Once, here’s a quick walk through:

  1. Using Visual Studio 2005 (Whidbey) create a new Windows Forms project.
  2. Using the Data Sources Window choose to add a new Database Data Source for the Northwind sample database
    • On the Choose your Data Connection step, click New Connection
    • In the top of the dialog you can change the Data Source – I know this term is overloaded…
    • Click the [Change…] button and select Microsoft SQL Server Mobile Edition as the data base data source
    • Your now given a Connection dialog where you can either create a new SQL Mobile Database or you can choose an existing SQL Mobile database.
    • Click [Browse] and you should see Northwind.sdf . .sdf is the SQL Mobile data file extension.
      If for some reason you don’t see the sample database, verify you’re looking in C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0
    • Once you select the sample database, click Ok, and click next to the Save Connection step
    • At this point you’re prompted to copy the .sdf file to your project. Choose yes so the data file travels with your application.
    • Again, choose yes save the connection string in the Settings file.
    • You can now choose which tables. For the purposes of this simplified demo, just choose Employees and click finish.
    • This creates a strongly typed DataSet, new in Whidbey for devices, for your Northwind SQL Mobile file.
  3. Drag the employees table from the Data Sources Window to the Form1 design surface. You can choose the Smart Tag on the grid to dock-fill.
    So far, this is the same experience for server based databases.

At this point you’d think you can run your application, and you can certainly try hitting F5. But, you’ll get an exception stating the runtime can’t find sqlceme30.dll. for SqlMobile can’t find the SQL Mobile runtime. No problem, we’ll just add it to our project.
But wait you say. Isn’t that an install? Or a ClickOnce boot strapper thingy? Nope. The SQL Mobile runtime is a few very small dlls. To get the full functionality for SQL Mobile you only need to add less then 1.4mb to your project. That’s less then some home page images.

  1. To add the runtime we need to place these files in a path that can be found by You could MSI install these to the System32 directory, but because they’re so small, why not just ship them with your app?
  2. Within Solution Explorer, select your project and choose Add Existing Item
  3. Navigate to C:\Program Files\Microsoft Visual Studio 8\Common7\IDE
  4. Change the files of type to all files
  5. Click in the file list window and type sqlce to get to the files that start with these letters
  6. Shift select all 7 files, sqlcera30.dll, sqlcecompact30.dll, sqlceer30en.dll, sqlceme30.dll, sqlceoledb30.dll, sqlceqp30.dll, sqlcese30.dll and click Add.

You now have the runtime in the project, but it’s not in the execution path.

  1. Select all 7 files in solution explorer and select the property grid.
  2. Change the Copy to Output Directory to Copy if newer
    This will make sure these files are copied to the output directory and can be found at runtime.
  3. Hit F5 to test your app.

Voila. You now have a local database ready to go.
Deploying with ClickOnce
Because these dll’s are just private to the project, you can simply publish your app just as you would normally. Post Beta 2 however, you’ll have a minor problem. ClickOnce actually places “Data” files in a different directory then you’re app. For more info check out my Tech Ed blog and Data Directory sample.


Post Beta 2 Changes for SQL Mobile Data Files.
SQL Mobile data files don’t leverage a new macro called |DataDirectory|. This is a macro that gets expanded by at runtime to resolve the location of the Data Directory for a given deployment model. For ClickOnce, this is a special directory associated with your application. For Web Applications, it’s yet another. To resolve the Data Directory for SQL Mobile you can handle the SettingsLoaded event in the Settings partial class.

  1. Open the Settings Designer under the My Project node in Solution Explorer
  2. Select View Code from the top of the designer and add the following code:

Partial Friend NotInheritable Class Settings

    Private Sub Settings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded

        Dim dataDirectory As String

        ' When running under debug mode, use the data file from the same directory as the executable

        ' otherwise, use the Data Directory which will be set for ClickOnce or MSI based installs

        ' This assumes that MSI based apps placed the data file in the standard non roaming data directory:

        ' C:\Documents and Settings\<UserName>\Application Data\<Company>\<Application Name>\<Version>

        If AppDomain.CurrentDomain.DomainManager IsNot Nothing AndAlso _

                AppDomain.CurrentDomain.DomainManager.ToString().Contains("VSHost") Then

            dataDirectory = Application.StartupPath


            dataDirectory = Application.UserAppDataPath

        End If

        Me.Item("NorthwindConnectionString") = My.Settings.NorthwindConnectionString.Replace(".\", dataDirectory & "\")

    End Sub

End Class

3. As the sample shows, this will replace the .\ with the appropriate directory. You may be wondering why VSHost? When running under VS, you’ll want to use the same directory as the executable. This is one way you can make sure you’re running under VS.

With the above code, you’re good to go to deploy a local data file under ClickOnce


Ok, now back to shipping Whidbey