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: 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).
Note
The Data Source Configuration Wizard in Visual Studio 2008 does not generate code for earlier versions of SQL Server Compact/Mobile Edition. For more information, see usingUsing SQL Server 2005 Compact Edition 3.1 Databases (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:
How to: Generate Summary and Edit Views for Data Applications (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
Date |
History |
Reason |
|---|---|---|
July 2008 |
Added a section about how to use Microsoft Synchronization Services for ADO.NET and SQL Server Compact to create occasionally connected smart device applications. |
SP1 feature change. |