How to: Set the File Mode when Opening a Database with OLE DB (Programmatically)

  • Article

In this topic, you will learn how to set the file mode when opening a Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) database by using the OLE DB provider for SQL Server Compact Edition. To set the mode in which the database is opened, you use the DBPROP_INIT_MODE property. The following table defines the valid values for the file mode:

Value Definition

DB_MODE_READ

Lets you open a read-only copy of the database.

DB_MODE_WRITE

Lets you open a write-only copy of the database.

DB_MODE_READWRITE

Permits multiple processes to open and modify the database.

DB_MODE_SHARE_DENY_READ

Prevents others from opening in read mode.

DB_MODE_SHARE_DENY_WRITE

Prevents others from opening in write mode.

DB_MODE_SHARE_EXCLUSIVE

Prevents others from opening in read/write mode (DB_MODE_SHARE_DENY_READ | DB_MODE_SHARE_DENY_WRITE).

DB_MODE_SHARE_DENY_NONE

Neither read nor write access can be denied to others.

Note

When you open a database that is located on a file share, the only option available is the DB_MODE_SHARE_EXCLUSIVE file mode. Reading and writing a SQL Server Compact Edition database over a network share works only from one process. Within that one process, multiple connections can be opened. For example, one connection can perform an Insert operation and a second connection can perform a Select query. However, two separate connections cannot use SQL Server Compact Edition to access the same .sdf file over a network share. The second connection will receive a file-sharing violation error.

Example

The following code shows how to set the file mode by using the OLE DB provider for SQL Server Compact Edition.

DBPROPSETdbpropset[1]; // Property Set used to initialize provider
DBPROPdbprop[2]; // Property array used in property set to initialize provider

VariantInit(&dbprop[0].vValue);
VariantInit(&dbprop[1].vValue);

// Initialize property with name of database
dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt = VT_BSTR;
dbprop[0].vValue.bstrVal = SysAllocString(L"MyDB.sdf");

if(NULL == dbprop[0].vValue.bstrVal)
{
     hr = E_OUTOFMEMORY;
     goto Exit;
}
// Initialize property with open mode for database
dbprop[1].dwPropertyID = DBPROP_INIT_MODE;
dbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[1].vValue.vt = VT_I4;
dbprop[1].vValue.lVal = DB_MODE_READ;

// Initialize property set
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties = dbprop;
dbpropset[0].cProperties = sizeof(dbprop)/sizeof(dbprop[0]);

// Set properties into the provider's DSO object
hr = pIDBProperties->SetProperties(sizeof(dbpropset)/sizeof(dbpropset[0]), dbpropset);
if(FAILED(hr))
{
      goto Exit;
}