SQL Server Compact 3.5 and Visual Studio

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.

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

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 2008. 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.

New Features in SQL Server Compact 3.5

SQL Server Compact 3.5 introduces the following new features for software developers.

• SQL Server Compact 3.5 implements the timestamp (rowversion) data type.

• SQL Server Compact 3.5 supports local TransactionScope on desktop and Tablet PC.

• SQL Server Compact 3.5 Table Designer in Visual Studio 2008 is enhanced to provide a user interface for creating primary key and foreign key relationships between tables.

• SQL Server Compact 3.5 support for Transact-SQL statements is extended as follows:

  • Nested query in SELECT FROM clause

  • CROSS APPLY and OUTER APPLY

  • CAST and DECIMAL

  • TOP

  • SET IDENTITY INSERT

• SQL Server Compact 3.5 supports data replication with SQL Server by using Microsoft Synchronization Services for ADO.NET. Microsoft Synchronization Services for ADO.NET is available for desktop and Tablet PCs only.

• SQL Server Compact 3.5–based applications can be developed for desktop and Tablet PC by using Visual Basic 2008 Express Edition and Visual C# 2008 Express Edition.

New Features in SQL Server Compact 3.5 Service Pack 1

SQL Server Compact 3.5 Service Pack 1 (SP1) is included with SQL Server 2008 and Visual Studio 2008 SP1. For detailed information, see the "What's New in SQL Server Compact" topic in the SQL Server Compact 3.5 Service Pack 1 Books Online.

Starting with the SQL Server Compact 3.5 SP1 release, SQL Server Compact provides the following new features for developers:

• SQL Server Compact supports the ADO.NET Entity Framework. The Entity Framework enables you to work with data in the form of domain-specific objects and properties, such as customers and customer addresses, without having to concern yourself with the underlying database tables and columns where this data is stored.

• Support for the ADO.NET Entity Framework enables developers to create flexible, strongly typed queries against the Entity Framework object context by using LINQ expressions and the LINQ standard query operators directly from the development environment.

• SQL Server Compact supports case-sensitive collations at the database level.

• You can administer a SQL Server Compact database that is stored on a smart device or on a desktop computer by using SQL Server Management Studio (SSMS) in SQL Server 2008.

• SQL Server Compact provides support for replicating the new data types in SQL Server 2008, such as date, time, datetime2, datetimeoffset, geography, and geometry. The new data types in SQL Server 2008 correspond to nchar, nvarchar, image, and other data types. For more information about data types in SQL Server 2008, see Data Types in SQL Server 2008 Books Online.

• SQL Server Compact can run natively in a 64-bit environment. The affected .msi files are SQL Server Compact 64-bit Runtime (SSCERuntime-ENU.msi) and SQL Server Compact 64-bit Server Tools (SSCEServerTools-ENU.msi). Support for 32-bit .msi files has not changed. Developers who are using ClickOnce deployment for their applications must specify both 32-bit and 64-bit download URLs.

• SQL Server Compact supports data replication with SQL Server 2000, SQL Server 2005, and SQL Server 2008 by using Microsoft Synchronization Services for ADO.NET. Microsoft Synchronization Services for ADO.NET is available for both desktop and mobile devices.

• SQL Server Compact supports data replication with SQL Server 2005 and SQL Server 2008 by using merge replication and Remote Data Access (RDA).

• SQL Server Compact includes enhanced version compatibility between SQL Server Compact and SQL Server for merge replication.

• Server Tools for configuring merge replication and RDA by using SQL Server Compact 3.5 SP1 can be downloaded from the Microsoft Download Center. SQL Server Compact 3.5 SP1 Server Tools cannot be installed side-by-side with earlier versions of Server Tools on the computer that is acting as the Internet Information Services (IIS) server. SQL Server Compact 3.5 SP1 Server Tools can replicate data between SQL Server Compact 3.5 and SQL Server 2005 or SQL Server 2008. SQL Server Compact 3.5 SP1 Server Tools also supports data replication between SQL Server 2005 Compact Edition or SQL Server 2005 Mobile Edition and SQL Server 2005 or SQL Server 2008.

• SQL Server Compact supports Windows Server 2008. For a list of all supported Windows versions, see Hardware and Software Requirements in the SQL Server Compact Books Online.

• SQL Server Compact includes several enhancements for logging.

Other 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.

• 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 2008. 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.

See Also

Tasks

How to: Add a SQL Server Compact 3.5 Database to a Project

How to: Deploy a SQL Server Compact 3.5 Database with an Application

Walkthrough: Creating a SQL Server Compact 3.5 Database

Walkthrough: Adding a SQL Server Compact 3.5 Database to an Application and Deploying it

Other Resources

Occasionally Connected Applications

SQL Server Compact 3.5 Books Online Download Center

Change History