Using SQL Server Compact 3.5 Databases (Devices)

Visual Studio includes SQL Server Compact 3.5, a lightweight relational database engine that is often used in device applications.

Overview of SQL Server Compact 3.5

SQL Server Compact 3.5 is the ideal database for device applications because it provides:

• Lightweight operation (It runs in process and requires only a 2MB installation on the device.)

• A rich subset of SQL Server query programming features.

• A rich and consistent subset of SQL Server data types.

• Support for ADO.NET and OLE DB.

• Local data storage using an .sdf database file.

• Data synchronization (using a Microsoft SQL Server database through RDA, Merge Replication, and Microsoft Synchronization Services for ADO.NET).

Local security

The SQL Server Compact 3.5 database engine provides password protection and encryption to help you secure local databases on devices. It also allows you to configure a variety of connectivity security options.

Connections with a Server

SQL Server Compact 3.5 supports merge replication, remote data access, security planning, and security implementation at the server.

Installing SQL Server Compact

When the Smart Device Programmability option is selected, Visual Studio installs SQL Server Compact 3.5 for Devices at drive:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices. For more information, see SQL Server Compact 3.5 and Visual Studio.

SQL Server Compact 3.5 must also be installed on your device or emulator. For more information, see How to: Install SQL Server Compact 3.5 on a Device.

Getting Help

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

Visual Database Tools

You can use Server Explorer/Database Explorer and Visual Database Tools to create and manage SQL Server Compact 3.5 (.sdf) database files in Visual Studio. For information about how to perform specific database tasks, see:

• How to: Create a Database (Devices)

• How to: Manage Tables in a Database (Devices)

• How to: Manage Columns in a Database (Devices)

• How to: Preview Data in a Database (Devices)

• How to: Manage Passwords for a Database (Devices)

• How to: Manage Indexes in a Database (Devices)

• How to: Shrink and Repair a Database (Devices)

  Note

  The Upgrade to SQL Server Compact 3.5 Database dialog box appears if you connect to a database that was developed by using an earlier version of SQL Server Compact. Visual Database Tools in Visual Studio 2008 does not support databases that were developed by using earlier versions of the SQL Server Compact/Mobile Edition. For information, see Using SQL Server 2005 Compact Edition 3.1 Databases (Devices).

Accessing SQL Compact database in Device projects

The System.Data.SqlServerCe namespace is the .NET Compact Framework data provider for SQL Server Compact 3.5 databases. You can use the methods and classes in this namespace to access data that is stored in SQL Server Compact 3.5 databases. You can manually access data by using the SqlServerCe data provider. Alternatively, you can use the Data Source Configuration Wizard to generate data source code in your project. For documentation on the System.Data.SqlServerCe namespace, see SQL Server Compact 3.5 Books Online.

The Data Source Configuration Wizard

The Data Source Configuration Wizard can be configured to generate data source code that is based on either DataSet or System.Data.SqlServerCe.SqlCeResultSet. You can access the data by accessing the data source objects. You can also bind the data source objects to user interface controls for display. For more information about how to use the Data Source Configuration Wizard, see How to: Add a Database to a Device Project.

In general, SqlCeResultSet executes faster and uses less memory than DataSet because it accesses the database directly. However, SqlCeResultSet is not compliant with ADO.NET. For more information, see Resultsets versus Datasets (Devices) and How to: Generate SqlCeResultSet Code (Devices).

Occasionally Connected Applications with Microsoft Synchronization Services for ADO.NET

Using Visual Studio 2008 Service Pack 1 and Microsoft Synchronization Services for ADO.NET, you can build occasionally connected smart device applications that synchronize data between a remote database and a SQL Server Compact database.

Occasionally connected applications access remote data from a local database on the client. Periodically, they synchronize the local data cache with data in the remote database. Therefore, they can function even when a connection to the remote database is unavailable.

For more information, see Occasionally Connected Applications (Devices).

Data Designer Support in Visual Studio

Visual Studio provides data designer tools that enable you to perform tasks such as dragging SQL Server Compact 3.5 data sources onto the forms designer. For more information, see:

• Walkthrough: A Master-Detail Application

• How to: Create Master-Detail Applications (Devices)

• How to: Generate Summary and Edit Views for Data Applications (Devices)

• Walkthrough: A Parameterized Query Application

• How to: Create Parameterized Queries (Devices)

• How to: Add Navigation Buttons (Devices)

• How to: Persist Data Changes to the Database (Devices)

  Note

  The data designer tools in Visual Studio 2008 do not support earlier versions of SQL Server Compact/Mobile Edition. For information, see Using SQL Server 2005 Compact Edition 3.1 Databases (Devices).

Automatic Deployment

When you deploy or debug a managed smart device project that uses SQL Server Compact 3.5, Visual Studio 2008 automatically deploys and installs the SQL Server Compact 3.5 runtime on the device or emulator if the runtime is not already installed. For more information, see How to: Install SQL Server Compact 3.5 on a Device.

See Also

Concepts

SQL Server Compact 3.5 and Visual Studio

Change History