Connecting to a SQL Compact database via OLE DB

The generic principles of connecting to a database via an OLE DB provider are described in this MSDN page: First you create a Data Source object and then you use it to create a Session object. While the Data Source represents the target database, the Session object represents an individual connection to that database. You use the Session object to (among other things) create and execute SQL commands, open base table cursors for fast data insertion, manage transactions and make changes to the database schema.

Both the Data Source and the Session objects are COM objects and thus can be exposed thrugh a number of different interfaces (see the "CoType" definitions for both objects). Some of the interfaces are marked as mandatory while others as optional. SQL Compact implements a subset of these interfaces:

DataSource

• IDBCreateSession
• IDBDataSourceAdmin
• IDBInitialize
• IDBProperties
• IDBInfo
• ISupportErrorInfo

Interestingly, the mandatory IPersist interface is not implemented in SQL Compact.

Session

• IGetDataSource
• IOpenRowset
• ISessionProperties
• IAlterIndex
• IAlterTable
• IDBCreateCommand
• IDBSchemaRowset
• IIndexDefinition
• ISupportErrorInfo
• ITableCreation
• ITableDefinition
• ITableDefinitionWithConstraints
• ITransactionLocal

Now, we can start writing high-level code to connect to a SQL Compact database. On my next post I will publish a minimalistic approach to both these objects.