Building Applications for Desktops (SQL Server Compact)

  • Article

You can use SQL Server Compact as the data store for computer applications. In this topic, you will learn how to add SQL Server Compact to a Visual Studio product, and then code against the System.Data.SqlServerCe namespace. Managed reference topics for the System.Data.SqlServerCe namespace are available in the .NET Framework Reference Documentation.

Creating a Computer Project

To create a new computer application, you first create a Windows project in Visual Studio. For a computer application, you must then add a reference to the Microsoft.Ink namespace.

Note

If you are developing a Tablet PC application on a computer that is not running Windows XP Tablet PC Edition, you must first install the Microsoft Windows XP Tablet PC Edition Development Kit. You can download the latest version from the Mobile and Embedded Application Developer Center.

To create a new computer project

  1. In Visual Studio, on the File menu, point to New, and then select Project.

  2. In the Installed Templates list of the New Project dialog box, expand the programming language you will use, and then select Windows.

  3. In the Templates list, select Windows Forms Application.

  4. Provide a name and location for your project, and then click OK.

    Visual Studio creates a new project and displays the main form (Form1).

  5. (Optional) In Solution Explorer, right-click References and select Add Reference.

    Note

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

  6. (Optional) In the list of .NET assemblies, select Microsoft Tablet PC API, and then click OK.

    The list of references now includes Microsoft.Ink.

Adding SQL Server Compact 4.0 to the Project When Building for Central Deployment

After you have created the project, you must add a reference to the SQL Server Compact assembly. The procedures for adding references to the SQL Server Compact assemblies when you build an application for private deployment or for central deployment of SQL Server Compact are slightly different. Follow these steps for central deployment.

To add a reference to SQL Server Compact 4.0 for Central Deployment

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

    Note

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

  2. 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. In the Add References dialog box, click Browse.

    2. Navigate to the following folder: %Program Files%\Microsoft SQL Server Compact Edition\v4.0\Desktop.

    3. Select System.Data.SqlServerCe.dll, then click OK.

    The list of references in Solution Explorer now includes System.Data.SqlServerCe and your project can use this assembly.

    Note

    If your project uses the Entity Framework, repeat the same steps to add a reference to System.Data.SqlServerCe.Entity. You can add System.Data.SqlServerCe.Entity from list of .NET assemblies, or you can browse to %Program Files%\Microsoft SQL Server Compact Edition\v4.0.

  3. In Solution Explorer, right-click Form1.cs or Form1.vb and select View Code.

  4. At the top of the code for the form, add a directive to use the System.Data.SqlServerCe namespace. If you use the Entity Framework, add a directive to use the System.Data.SqlServerCe namespace. For a Tablet PC application, also add a directive to use the Tablet PC API:

    • C#

      using System.Data.SqlServerCe;
using System.Data.SqlServerCe.Entity; 
using Microsoft.Ink;

    • Visual Basic

      Imports System.Data.SqlServerCe
Imports System.Data.SqlServerCe.Entity
Imports Microsoft.Ink

Adding SQL Server Compact 4.0 to the Project When Building for Private Deployment

After you have created the project, you must add a reference to the SQL Server Compact assembly. The procedures for adding references to the SQL Server Compact assemblies when you build an application for private deployment or for central deployment of SQL Server Compact are slightly different. Follow these steps for private deployment.

To add a reference to SQL Server Compact 4.0 for Central Deployment

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

    Note

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

  2. In the Add References dialog box, click Browse.

  3. Navigate to the %Program Files%\Microsoft SQL Server Compact Edition\v4.0\Private folder.

  4. Select System.Data.SqlServerCe, then click OK.

    The list of references in Solution Explorer now includes System.Data.SqlServerCe and your project can use this assembly.

    Note

    If your project uses the Entity Framework, repeat the same steps to add a reference to System.Data.SqlServerCe.Entity. (For private deployment, System.Data.SqlServerCe.Entity is also added from the %Program Files%\Microsoft SQL Server Compact Edition\v4.0\Private folder.)

  5. In Solution Explorer, right-click Form1.cs or Form1.vb and select View Code.

  6. At the top of the code for the form, add a directive to use the System.Data.SqlServerCe namespace. If you use the Entity Framework, add a directive to use the System.Data.SqlServerCe namespace. For a Tablet PC application, also add a directive to use the Tablet PC API:

    • C#

      using System.Data.SqlServerCe;
using System.Data.SqlServerCe.Entity; 
using Microsoft.Ink;

    • Visual Basic

      Imports System.Data.SqlServerCe
Imports System.Data.SqlServerCe.Entity
Imports Microsoft.Ink

Configuring SQL Server Compact for Private Deployment

To build an application that uses private deployment for SQL Server Compact you must ensure that the .NET Framework data provider and any necessary native DLLs are deployed in the application's output directory. If you want your application to use private deployment for SQL Server Compact, you should follow the steps in this section. If your application does not use private deployment, skip this section. For more information about private deployment of SQL Server Compact, see Private Deployment vs. Central Deployment (SQL Server Compact). The following steps assume the application is being built to target any platform (32-bit and 64-bit). If your application is only targeting one of these platforms, you can omit steps that apply to the other platform.

To configure the application for private deployment of SQL Server Compact

  1. Configure the .NET Framework Data Provider for SQL Server Compact assembly to be copied to the output directory when the project is built.

    1. In Solution Explorer, expand References, right-click System.Data.SqlServerCe, and select Properties.

    2. In System.Data.SqlServerCe Reference Properties, set Copy Local to True.

    Note

    If your project uses the Entity Framework, repeat the same steps for System.Data.SqlServerCe.Entity to configure the Entity Framework Data Provider for SQL Server Compact to be copied to the output directory.

  2. Add the necessary 32-bit SQL Server Compact native libraries to the project.

    1. In Solution Explorer, right-click the project, point to Add, and select New Folder. Name the folder x86.

    2. In Solution Explorer, right-click the x86 folder, point to Add, and select Existing Item.

    3. In the Add Existing Item dialog box, browse to the folder in which the 32-bit native libraries are located. On a 32-bit version of Windows this is %Program Files%\Microsoft SQL Server Compact Edition\v4.0. On a 64-bit version of Windows this is %Program Files (x86)%\Microsoft SQL Server Compact Edition\v4.0.

    4. Control-click on each of the necessary native libraries for your project to select them in the file list, and then click OK. The libraries are added to the x86 folder.

      Note

      At a minimum you must add the following libraries: sqlceme40.dll, sqlceqp40.dll, sqlcese40.dll, and sqlceer40.dll.

  3. Add the necessary 64-bit SQL Server Compact native libraries to the project.

    1. In Solution Explorer, right-click the project, point to Add, and select New Folder. Name the folder x64.

    2. In Solution Explorer, right-click the x64 folder, point to Add, and select Existing Item.

    3. In the Add Existing Item dialog box, browse to the folder in which the 64-bit native libraries are located. On a 64-bit version of Windows this is %Program Files%\Microsoft SQL Server Compact Edition\v4.0.

      Note

      You cannot install the 64-bit version of SQL Server Compact on a computer that is running a 32-bit version of Windows. To add the 64-bit native libraries to a project being built in a 32-bit development environment, you must first extract the files from the 64-bit installer into a directory on the development computer. Then you can browse to this directory to add the 64-bit native libraries to your project. For information about how to extract files from the installer, see How to: Extract 64-bit DLLs from the SQL Server Compact Runtime Installer.

    4. Control-click on each of the necessary native libraries for your project to select them in the file list, and then click OK. The libraries are added to the x64 folder.

      Note

      At a minimum you must add the following libraries: sqlceme40.dll, sqlceqp40.dll, sqlcese40.dll, and sqlceer40.dll.

  4. Configure each of the native libraries added in the previous steps to be copied to the output directory when the project is built. You must do this for the libraries in both directories.

    1. In Solution Explorer, right-click the library (for example sqlcese40.dll), and select properties.

      Note

      If the assemblies are not listed in Solution Explorer, click Show All Files at the top of Solution Explorer.

    2. In file Properties, select Copy Always or Copy if Newer from the Copy to Output Directory drop-down list.

  5. Configure the project to build for Any CPU.

    1. In Solution Explorer, right-click the project and select Properties to open the Project Designer.

    2. Click Build and select Any CPU from the Platform Target drop-down list.

Using the SQL Server Compact 4.0 Objects

After the System.Data.SqlServerCe namespace has been added, you can start to code against it by using the SQL Server Compact objects. The following code example shows how to use the Engine object to create a new SQL Server Compact database file.

using System;
using System.Data.SqlServerCe;
using System.IO;

public class MySqlCeEngine
{
    public void CreateDB()
    {
        File.Delete("Test.sdf");
        string connString = "Data Source='Test.sdf'; LCID=1033;   Password=<enterStrongPasswordHere>; Encrypt = TRUE;";
        SqlCeEngine engine = new SqlCeEngine(connString);
        engine.CreateDatabase();
    }
}

Imports System
Imports System.Data.SqlServerCe
Imports System.IO

Public Class MySqlCeEngine

    Public Sub CreateDB()
        File.Delete("Test.sdf")
        Dim connString As String = "Data Source='Test.sdf'; LCID=1033; Password=<enterStrongPasswordHere>; Encrypt = TRUE;"
        Dim engine As New SqlCeEngine(connString)
        engine.CreateDatabase()
    End Sub
End Class

See Also

Reference

System.Data.SqlServerCe Namespace (.NET Framework Reference Documentation)