Crear índices con OLE DB

El proveedor OLE DB para Microsoft SQL Server Compact 4.0 permite crear índices a través de IIndexDefinition::CreateIndex.

El uso de índices en SQL Server Compact 4.0 tiene algunas limitaciones. Para obtener más información, vea Índices de OLE DB (SQL Server Compact) y Cambiar índices con OLE DB.

Ejemplos

En el siguiente ejemplo se muestra cómo usar el proveedor OLE DB para SQL Server Compact 4.0 para crear un índice compuesto de las columnas LastName y FirstName en la tabla Employees de una base de datos.

//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;