SQL Server Compact 3.5 and Visual Studio

Article
02/04/2013

Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5) enables you to create compact databases that can be deployed on desktop computers, smart devices, and Tablet PCs.

When you build applications that use SQL Server Compact 3.5, you can use one of the Microsoft .NET programming languages (Microsoft Visual Basic or Microsoft Visual C#) and the .NET Framework or .NET Compact Framework to create a managed application. Or you can use Microsoft Visual C++ or Visual C++ for Devices to create a native application.

Note

When you create applications, the preferred local database is SQL Server Compact 3.5. For more information about incorporating a database as part of an application, see Local Data Overview.

The choice of whether to create a managed or native application depends on your needs and current skills. Developers who are familiar with Microsoft Visual C++ programming and who need more control over system resources can create a native application by using Visual C++. Developers who are already developing Microsoft Windows-based or Web applications by using the .NET Framework, and those who are familiar with one of the .NET programming languages, should consider creating managed applications to take advantage of the features of the .NET Framework for desktop computers and Tablet PCs or of the .NET Compact Framework for smart devices.

SQL Server Compact 3.5 Components

Note

By default, SQL Server Compact 3.5 documentation is not installed locally. To download SQL Server Compact 3.5 Books Online, go to the SQL Server Compact 3.5 Books Online Download Center.

SQL Server Compact 3.5 components are available in three Microsoft Windows Installer (.msi) files:

SSCE 3.5 Design Tools (SSCEVSTools-ENU.msi)

This file installs the SQL Server Compact 3.5 design-time components with Visual Studio. The design-time components are the user interface, dialog boxes, and design-time environment, which are used to write applications for SQL Server Compact 3.5. The SQL Server Compact 3.5 design-time components will be installed under the directory %Program Files%\Microsoft Visual Studio 9\Common7\IDE\. These components are bundled with Visual Studio and cannot be shipped independently of Visual Studio. SQL Server Compact 3.5 samples and SQL Server Compact 3.5 header files (for native development) are also installed by this .msi file.
SQL Server Compact 3.5 (SSCERuntime-ENU.msi)

This file installs the SQL Server Compact 3.5 runtime components for desktop computers and Tablet PCs in a central folder: %Program Files%\Microsoft SQL Server Compact Edition\v3.5. The SQL Server Compact 3.5 desktop and Tablet PC runtime components are required for developing applications in Visual Studio and for running SQL Server Compact 3.5-based applications on the desktop. This .msi file also installs the following:
- Microsoft Synchronization Services for ADO.NET components. The Microsoft Synchronization Services for ADO.NET components are installed at %Program Files%\Microsoft Synchronization Services\ADO.NET\v1.0.
SQL Server Compact 3.5 for Devices (SSCEDeviceRuntime-ENU.msi)

This file installs the runtime components for SQL Server Compact 3.5 devices in %Program Files%\Microsoft SQL Server Compact Edition\v3.5\Devices. The runtime components are required for developing Windows Mobile and Windows CE device-based applications in Visual Studio and for deploying the applications on mobile devices.

Notable Features in SQL Server Compact 3.5

SQL Server Compact 3.5 provides the following features to consider when you are using it as the local data store for your applications.

SQL Server Compact 3.5 is file based, which means that the connection string is a file path to the database (.sdf) file.
SQL Server Compact 3.5 does not run as a service. This is one of the main differences between using SQL Server Compact 3.5 and using SQL Server or SQL Server Express Edition.
SQL Server Compact 3.5 (like SQL Server 2005 Mobile Edition) supports multiple connections up to the 256 connection limit. Opening connections on different processes is also supported.
The current version of SQL Server Compact 3.5 supports database files up to 4 GB.

When to Use SQL Server Compact 3.5

SQL Server Compact 3.5 is a file-based database that consists of DLLs that are approximately 1.4 MB.

The following list provides some scenarios in which you might want to use SQL Server Compact 3.5 in your applications:

For applications intended for desktop computers and mobile devices. For more information, see Walkthrough: Adding a SQL Server Compact 3.5 Database to an Application and Deploying it.
For applications that will be used in occasionally connected scenarios. For more information, see Occasionally Connected Applications (Local Database Caching).
When you need a database that is free to redistribute.
When application size and memory requirements must be compact.
When you want the data access code to run in a process.

Deployment Options

SQL Server Compact 3.5 provides two deployment models that you can incorporate into your applications.

ClickOnce

You can use the traditional Microsoft setup technology, including ClickOnce deployment. You can also use a file-based deployment in which you add the SQL Server Compact 3.5 DLLs and deploy them with your project.

Data Directory Support

SQL Server Compact 3.5 now supports the Data Directory macro. This means that if you add the string |DataDirectory| (enclosed in pipe symbols) to a file path, it will resolve to the path of the database.

For example, consider the connection string:

"Data Source= c:\program files\MyApp\Mydb.sdf"

When using Data Directory, you can instead use the following connection string:

"Data Source = |DataDirectory|\Mydb.sdf"

For more information, see How to: Deploy a SQL Server Compact 3.5 Database with an Application.

Backward Compatibility

SQL Server Compact 3.5 can be installed together with the previous version (3.1), on desktop and Tablet PC computers. If you try to open database files created by earlier versions, you will receive a message stating that the file cannot be opened. To open it, you must first upgrade the database file by using the Upgrade to SQL Server Compact 3.5 dialog box in Visual Studio. To access the Upgrade to SQL Server Compact 3.5 dialog box, complete the Add Connection dialog box. If an earlier version of the database file is opened, an error message appears. If you click OK, the Upgrade to SQL Server Compact 3.5 dialog box appears. The file name extension for SQL Server Compact 3.5 files is .sdf, which is the same as that of the earlier versions (SQL Server 2005 Compact Edition and SQL Server 2005 Mobile Edition). When a database file is upgraded to SQL Server Compact 3.5, you will no longer be able to open the database file by using the earlier versions of the SQL Server Compact engine.

SQL Server Compact 3.5 Books Online

By default, SQL Server Compact 3.5 documentation is not installed locally. To download SQL Server Compact 3.5 Books Online, go to the SQL Server Compact 3.5 Books Online Download Center. SQL Server Compact 3.5 Books Online provides conceptual and programming information about how to develop, administer, and deploy SQL Server Compact 3.5 applications on desktop computers, mobile devices, and Tablet PCs.

64 Bit Development

For 64 bit development with Visual Studio and SQL Server Compact 3.5 the Target CPU option in the Advanced Compiler Settings must be explicitly set to x86. When the Target CPU is set to the default value of Any CPU an "Unable to load sqlceme35.dll" message might be encountered. Set the Target CPU option in the Advanced Compiler Settings in project properties.