Export (0) Print
Expand All

Using the Seek Method with OLE DB

The Seek method is the most widely used method by clients on indexes in Microsoft SQL Server Compact 4.0. Seek lets you find rows on a cursor very quickly.

The Seek method requires that an index be defined on the columns in the search key to work correctly. Most seek operations are for a particular value, but it is also possible to seek using other comparison operators, such as "greater than" or "less than".

IRowsetIndex::Seek passes values to the provider by using the accessor mechanism in OLE DB that is used to obtain and set data. Accessors used against the Seekmethod have additional restrictions over accessors for IRowset::GetData and IRowset::SetData. The accessor must bind columns in the order they appear in the index key.

The IRowsetPosition interface is only supported on scrollable cursors.

The following example shows how to use IRowsetIndex::Seek on an index using the OLE DB provider for SQL Server Compact 4.0. The example contains only the sections of the function that relate to the Seek method.

The following example has been updated for both 32-bit and 64-bit platforms. The 64-bit compatible types are from the sqlce_oledb.h native header file. You can find the sqlce_oledb.h native header file in the %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Include folder. For more information, see the OLE DB 64-bit Information topic in the OLE DB Programmer's Guide on the Microsoft Web site.

TableID.eKind            = DBKIND_NAME;
TableID.uName.pwszName    = (WCHAR*)TABLE_EMPLOYEE;

IndexID.eKind            = DBKIND_NAME;
IndexID.uName.pwszName    = L"PK_Employees";

// Request ability to use IRowsetChange interface.
rowsetpropset[0].cProperties     = 1;
rowsetpropset[0].guidPropertySet = DBPROPSET_ROWSET;
rowsetpropset[0].rgProperties    = rowsetprop;

rowsetprop[0].dwPropertyID       = DBPROP_IRowsetIndex;
rowsetprop[0].dwOptions          = DBPROPOPTIONS_REQUIRED;
rowsetprop[0].colid              = DB_NULLID;
rowsetprop[0].vValue.vt          = VT_BOOL;
rowsetprop[0].vValue.boolVal     = VARIANT_TRUE;

// Open the table using the index.
hr = pIOpenRowset->OpenRowset(NULL, &TableID, &IndexID,
    IID_IRowsetIndex, sizeof(rowsetpropset)/sizeof(rowsetpropset[0]),
    rowsetpropset, (IUnknown**) &pIRowsetIndex);
if(FAILED(hr))
{
    goto Exit;
}

// Get the IRowset interface.
hr = pIRowsetIndex->QueryInterface(IID_IRowset, (void**) &pIRowset);
if(FAILED(hr))
{
    goto Exit;
}

///////////////////////////////////////////////////////////////////////
// Steps to get column data using IcolumnsInfo have been removed
///////////////////////////////////////////////////////////////////////

// Create a DBBINDING array.
dwBindingSize = sizeof(pwszEmployees)/sizeof(pwszEmployees[0]);
prgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*dwBindingSize);
if (NULL == prgBinding)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

// Set initial offset for binding position.
dwOffset = 0;

// Prepare structures to create an accessor for each index.
for (dwIndex = 0; dwIndex < dwBindingSize; ++dwIndex)
{
    if (!GetColumnOrdinal(pDBColumnInfo, ulNumCols, pwszEmployees[dwIndex], &dwOrdinal))
    {
        hr = E_FAIL;
        goto Exit;
    }

    prgBinding[dwIndex].iOrdinal  = dwOrdinal;
    prgBinding[dwIndex].dwPart    = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
    prgBinding[dwIndex].obLength  = dwOffset;                                     
    prgBinding[dwIndex].obStatus  = prgBinding[dwIndex].obLength + sizeof(DBLENGTH);  
    prgBinding[dwIndex].obValue   = prgBinding[dwIndex].obStatus + sizeof(DBSTATUS);
    prgBinding[dwIndex].pTypeInfo  = NULL;
    prgBinding[dwIndex].pBindExt   = NULL;
    prgBinding[dwIndex].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
    prgBinding[dwIndex].dwFlags    = 0;
    prgBinding[dwIndex].bPrecision = pDBColumnInfo[dwOrdinal].bPrecision;
    prgBinding[dwIndex].bScale     = pDBColumnInfo[dwOrdinal].bScale;

///////////////////////////////////////////////////////////////////////
// Case-specific binding information has been removed.
///////////////////////////////////////////////////////////////////////

    prgBinding[dwIndex].pObject    NULL;
    prgBinding[dwIndex].wType     = pDBColumnInfo[dwOrdinal].wType;
    if(DBTYPE_WSTR == pDBColumnInfo[dwOrdinal].wType)
    {
        prgBinding[dwIndex].cbMaxLen  = pDBColumnInfo[dwOrdinal].ulColumnSize 
            * sizeof(WCHAR); 
    }
    else
    {
        prgBinding[dwIndex].cbMaxLen  = pDBColumnInfo[dwOrdinal].ulColumnSize; 
    }

    // Calculate and align the offset. 
}

// Get IAccessor interface.
hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
if(FAILED(hr))
{
    goto Exit;
}

// Create the accessor.
hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, dwBindingSize, 
    prgBinding, 0, &hAccessor, NULL);
if(FAILED(hr))
{
    goto Exit;
}

// Allocate data buffer for seek and retrieve operation.
pData = (BYTE*)CoTaskMemAlloc(dwOffset);
if (NULL == pData)
{
    hr = E_OUTOFMEMORY;
    goto Exit;
}

// Set data buffer to zero.
//
memset(pData, 0, dwOffset);

// Set data buffer for seek operation by specifying the 
// dwEmployeeID variable that is passed to the function.
*(DBLENGTH*)(pData+prgBinding[0].obLength)    = 4;
*(DBSTATUS*)(pData+prgBinding[0].obStatus) = DBSTATUS_S_OK;
*(int*)(pData+prgBinding[0].obValue)       = dwEmployeeID;

// Seek for the first row where the value of the selected column 
// is dwEmployeeID. 
hr = pIRowsetIndex->Seek(hAccessor, 1, pData, DBSEEK_FIRSTEQ);
if(FAILED(hr))
{
    goto Exit;    
}

// Retrieve a row handle for the row resulting from the seek.
hr = pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, 
    &prghRows);
if(FAILED(hr))
{
    goto Exit;    
}

///////////////////////////////////////////////////////////////////////
// Perform programming logic here on the 
// returned rowset, and then release the rowset.
///////////////////////////////////////////////////////////////////////

Exit:

///////////////////////////////////////////////////////////////////////
// This is where the resources are released.
///////////////////////////////////////////////////////////////////////

return hr;
Show:
© 2014 Microsoft