Share via

Managed Data Provider (SQL Server Compact)

  • Article

The System.Data.SqlServerCe namespace provides programmatic access to Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5) databases from a managed application. System.Data.SqlServerCe provides a set of classes designed to reveal the functionality of SQL Server Compact 3.5. For a list of classes in this namespace, see System.Data.SqlServerCe.

Supported Functionality

The System.Data.SqlServerCe namespace provides the following functionality:

  • Consistent access to SQL Server Compact 3.5 data sources.
    Consumer applications that share data can use the SQL Server Compact 3.5 data provider to connect to SQL Server Compact 3.5 data sources and then retrieve, work with, and update data.
  • Components for connecting to a database, executing commands, and retrieving results.
    The retrieved results can be processed directly or put in an ADO.NET DataSet or ResultSet object to be revealed to the user, combined with data from multiple sources, or propagated remotely between tiers.
  • Functionality to developers writing managed code.
    This functionality is similar to that provided by ADO to native COM developers.
  • Index functionality for optimal performance.
    SqlCeCommand.SetRange restricts the set of row entries visible through calls to SqlCeDataReader.Read and SqlCeDataReader.Seek. SqlCeDataReader.Seek permits direct positioning at a key value within the current range.
  • Interfaces needed to interact with the SQL Server Compact 3.5 Replication and Remote Data Access (RDA) objects through the SqlCeReplication and SqlCeRemoteDataAccess classes.
  • SqlCeEngine class methods that enable database creation and compaction.
  • Database encryption and password protection.

Provider Limitations

The data provider for SQL Server Compact 3.5 has the following limitations:

  • No support for batch queries. Queries must be a single SQL statement. For example, the following statement is valid:

    SELECT * FROM Customers

    This statement is not valid:

    SELECT * FROM Customers; SELECT * FROM Customers2

    Datasets must be refreshed from a SqlCeDataAdapter. If you are using code from a Visual Studio project, you must modify your SQL statement to comply with this restriction.

  • There is no support for nested transactions, but parallel transactions are supported.

  • SQL Server Compact 3.5 is not currently optimized to serve as a database for Web sites. By default, connections from ASP.NET-connected applications are blocked in SQL Server Compact 3.5. SQL Server Compact 3.5 is optimized for use as an embedded database within applications. Using SQL Server Compact 3.5 as a database for Web sites requires support for multiple users and concurrent data changes. This can cause performance problems. Therefore, these scenarios are not supported. Other editions of SQL Server, including SQL Server 2005 Express Edition and later versions, are optimized to serve as a database for Web sites. SQL Server Compact 3.5 can be used with ASP.NET in application scenarios where ASP.NET is used to create SQL Server Compact 3.5 databases for synchronization. Use the following code to change the default behavior of SQL Server Compact 3.5 to work within ASP.NET.

    AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true)

See Also

Other Resources

Building Managed Applications (SQL Server Compact)
Managed Programming How Tos (SQL Server Compact)

Help and Information

Getting Assistance (SQL Server Compact 3.5 Service Pack 1)