Creazione di indici con OLE DB

Il provider OLE DB per Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5) espone la funzionalità di creazione di indici tramite IIndexDefinition::CreateIndex.

L'utilizzo degli indici in SQL Server Compact 3.5 presenta alcune limitazioni. Per ulteriori informazioni, vedere Indici OLE DB (SQL Server Compact) e Modifica degli indici con OLE DB.

Esempi

Nell'esempio seguente viene illustrato come utilizzare il provider OLE DB per SQL Server Compact 3.5 per creare un indice composto sulle colonne LastName e FirstName nella tabella Employees di un database.

//Create an index on two columns.
HRESULT             hr;
DBID                TableName;
DBID                IndexName;
DBPROP              dbprop[1];
DBPROPSET           dbpropset[1];
DBPROP              indexdbprop[2];
DBPROPSET           indexdbpropset[1];
DBINDEXCOLUMNDESC   rgIndexColumnDescs[2];
DBID                dbidColumn1;
DBID                dbidColumn2;
IDBInitialize       *pIDBInitialize     = NULL;        
IDBProperties       *pIDBProperties        = NULL;        
IDBCreateSession    *pIDBCreateSession  = NULL;
IIndexDefinition    *pIIndexDefinition  = NULL;


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

// Create an instance of the OLE DB provider.
hr = CoCreateInstance(    CLSID_SQLSERVERCE, 0, CLSCTX_INPROC_SERVER,
    IID_IDBInitialize, (void**)&pIDBInitialize);
if(FAILED(hr))
{
    goto Exit;
}

// Initialize a property with name of database.
// Open an exsiting database myDatabase.
dbprop[0].dwPropertyID     = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions         = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt         = VT_BSTR;
dbprop[0].vValue.bstrVal = L"\\windows\\MyDB.sdf";
if(NULL == dbprop[0].vValue.bstrVal)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

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

//Set initialization properties.
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, 
    
(void **)&pIDBProperties);
if(FAILED(hr))
{
    goto Exit;
}

// Sets properties in the Data Source and initialization property groups
hr = pIDBProperties->SetProperties(1, dbpropset); 
if(FAILED(hr))
{
    goto Exit;
}

// Initializes a data source object 
hr = pIDBInitialize->Initialize();
if(FAILED(hr))
{
    goto Exit;
}

// Get the IDBCreateSession interface.
hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,
    (void**)&pIDBCreateSession);
if (FAILED(hr))
{
    //Send an error-specific message and do error handling.
    goto Exit;
}

// Create a session object. 
hr = pIDBCreateSession->CreateSession(NULL, IID_IIndexDefinition, 
    (IUnknown**) &pIIndexDefinition);

// (This sample assumes that we have information about the Employees table.
// database schema.)
// Prepare the table name DBID as Employees.
TableName.eKind          = DBKIND_NAME;
TableName.uName.pwszName = L"Employees";

// Prepare index name DBID as full_name_index.
IndexName.eKind          = DBKIND_NAME;
IndexName.uName.pwszName = L"full_name_index";

// Set up properties for IIndexDefinition::CreateIndex call. For this 
// index,we enforce uniqueness through the DBPROP_INDEX_NULLS property.
// Index properties must have colid set to DB_NULLID.
indexdbprop[0].dwPropertyID   = DBPROP_INDEX_NULLS;
indexdbprop[0].dwOptions      = DBPROPOPTIONS_REQUIRED;
indexdbprop[0].vValue.vt      = VT_I4;
indexdbprop[0].vValue.lVal    = DBPROPVAL_IN_DISALLOWNULL;
indexdbprop[0].colid          = DB_NULLID; 

// Initialize the property set.
indexdbpropset[0].guidPropertySet = DBPROPSET_INDEX;
indexdbpropset[0].rgProperties    = indexdbprop;
indexdbpropset[0].cProperties     = sizeof(indexdbprop)/sizeof(indexdbprop[0]);
 
// Set up DBINDEXCOLUMNDESC structures to define the columns in the  
// index and the ordering for each column within that index.
rgIndexColumnDescs[0].eIndexColOrder = DBINDEX_COL_ORDER_ASC;
rgIndexColumnDescs[0].pColumnID      = &dbidColumn1;
rgIndexColumnDescs[1].eIndexColOrder = DBINDEX_COL_ORDER_ASC;
rgIndexColumnDescs[1].pColumnID      = &dbidColumn2;

// Specify the column names for the composite index on 
// LastName and FirstName.
dbidColumn1.eKind = DBKIND_NAME;
dbidColumn1.uName.pwszName = L"LastName";
dbidColumn2.eKind = DBKIND_NAME;
dbidColumn2.uName.pwszName = L"FirstName";

// Create a two-column composite index named full_name_index over the 
// LastName and FirstName columns in the Employees table. 
hr = pIIndexDefinition->CreateIndex(&TableName, &IndexName,
    sizeof(rgIndexColumnDescs)/sizeof(rgIndexColumnDescs[0]),
    rgIndexColumnDescs, sizeof(indexdbpropset)/sizeof(indexdbpropset[0]),
    indexdbpropset, NULL);

Exit: 

// When finished, clear the properties arrays and release interfaces.
// Uninitialize the environment.

return;