Creating Databases with OLE DB
The OLE DB provider for SQL Server Compact 4.0 reveals the IDBDataSourceAdmin interface through which you can create and manage databases in SQL Server Compact 4.0.
Creating a Database
To create a new database, you must specify the DBPROP_INIT_DATASOURCE property to specify a name for the database. For security reasons, the OLE DB provider for SQL Server Compact 4.0 does not support deleting databases by using IDBDataSourceAdmin::DestroyDataSource. If you want to delete a database and you have access to the file system on the computer, you can delete the database files that are no longer needed.
Securing Databases
For improved security, SQL Server Compact 4.0 supports creating encrypted databases. Encrypted databases cannot be created without a password. Data in an encrypted database can be accessed by SQL Server Compact 4.0 only after the correct password has been provided.
SQL Server Compact 4.0 supports two modes of encryption:
DBPROPVAL_SSCE_EM_PLATFORM_DEFAULT (Platform Default)
DBPROPVAL_SSCE_EM_ENGINE_DEFAULT (Engine Default)
The default encryption mode is Platform Default. For more information on encryption modes, see Upgrading from Earlier Versions (SQL Server Compact).
Note
If you forget the password for an encrypted database, the data cannot be recovered and remains inaccessible.
To create a protected database by using the OLE DB provider for SQL Server Compact 4.0, you must pass the provider-specific property DBPROP_SSCE_ENCRYPTIONMODE as VARIANT_14 and specify a password by using the provider-specific property DBPROP_SSCE_DBPASSWORD.
The following table summarizes the properties that must be specified to create each type of database.
Type of database to create |
Required properties |
|---|---|
Standard database |
DBPROP_INIT_DATASOURCE |
Protected database |
DBPROP_INIT_DATASOURCE DBPROP_SSCE_DBPASSWORD DBPROP_SSCE_ENCRYPTIONMODE |
Case-sensitive database |
DBPROP_SSCE_DBCASESENSITIVE .gif "Note") Note
This property is introduced starting with the SQL Server Compact 4.0 release. For more information, see Working with Collations (SQL Server Compact).
|
For an existing database, these properties can be changed when the database is compacted. For more information, see CompactDatabase Method (SQL Server Compact).
Examples
For an example of creating a database with the OLE DB provider for SQL Server Compact 4.0, see How to: Create a Database by Using OLE DB (Programmatically).