原始 C++使用 OLE DB 显示 SQL 紧凑型服务器中表名称的代码

Question

有没有人有给定数据库文件显示所有用户表名称的示例代码？

我对 .NET 代码不感兴趣，只对 C++ 感兴趣。

我正在尝试使用 OLE DB 来完成任务 - 相比之下，火箭科学似乎是小儿科。

Answer 1

我将假设 SQL Server CE 3.0/3.1，但为了供您参考，以下是我所知道的提供程序字符串：

• SQL Server CE 3.0 / 3.1 - Microsoft.SQLLITE.MOBILE.OLEDB.3.0
• SQL Server CE 3.5 - Microsoft .SQLSERVER.CE.OLEDB.3.5

另外，我将为您提供两个代码示例：

• “SELECT TABLE_NAME FROM”上的 ICommandText INFORMATION_SCHEMA.TABLES”
• DBSCHEMA_TABLES 上的 IDBSchemaRowset

我以 C++ 控制台应用程序的形式向您提供示例，并使用 ATL 来简化代码。该代码完整且可以工作，但不包括对 HRESULT hr 进行必要的错误检查。

以下是用于查询 SQL Server CE 3.0/3.1 数据库以使用 ICommandText 和 IRowset 执行“SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES”的 C++ 控制台应用程序示例：

#include <stdio.h>
#include <tchar.h>
#include <windows.h>
#include <oledb.h>
#include <atlbase.h>

int _tmain(int argc, _TCHAR* argv[])
{
    HRESULT hr = S_OK;
    hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);

    // Connect to SQL Server.
    CComPtr<IDBInitialize> spIDBInitialize;
    hr = spIDBInitialize.CoCreateInstance(OLESTR("Microsoft.SQLLITE.MOBILE.OLEDB.3.0"));
    CComPtr<IDBProperties> spIDBProperties;
    hr = spIDBInitialize->QueryInterface(&spIDBProperties);
    CComVariant varDataSource(OLESTR("InsertYourSampleDatabase.SDF"));
    DBPROP prop = { DBPROP_INIT_DATASOURCE, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varDataSource };
    DBPROPSET propSet = {&prop, 1, DBPROPSET_DBINIT};
    hr = spIDBProperties->SetProperties(1, &propSet);
    spIDBProperties = NULL;
    hr = spIDBInitialize->Initialize();

    // Execute the query.
    CComPtr<IDBCreateSession> spIDBCreateSession;
    hr = spIDBInitialize->QueryInterface(&spIDBCreateSession);
    CComPtr<IDBCreateCommand> spIDBCreateCommand;
    hr = spIDBCreateSession->CreateSession(NULL, IID_IDBCreateCommand, (IUnknown**) &spIDBCreateCommand);
    spIDBCreateSession = NULL;
    CComPtr<ICommandText> spICommandText;
    hr = spIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown**) &spICommandText);
    spIDBCreateCommand = NULL;
    hr = spICommandText->SetCommandText(DBGUID_SQL, OLESTR("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"));
    DBROWCOUNT cRowsAffected = 0;
    CComPtr<IRowset> spIRowset;
    hr = spICommandText->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, (IUnknown**) &spIRowset);
    spICommandText = NULL;

    // Retrieve records.
    HROW hRow = NULL;
    HROW *rghRow = &hRow;
    DBCOUNTITEM cRowsObtained = 0;
    hr = spIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &rghRow);
    while (hr == S_OK && cRowsObtained == 1)
    {
        // Fetch the TABLE_NAME field.
        struct
        {
            DBSTATUS dbTableNameStatus;
            ULONG nTableNameLength;
            WCHAR szTableName[128 + 1];
        } data = {0};
        DBBINDING Binding = {0};
        Binding.iOrdinal    = 1;
        Binding.obValue     = sizeof(DBSTATUS) + sizeof(ULONG);
        Binding.obLength    = sizeof(DBSTATUS);
        Binding.obStatus    = 0;
        Binding.pTypeInfo   = NULL;
        Binding.pObject     = NULL;
        Binding.pBindExt    = NULL;
        Binding.dwPart      = DBPART_STATUS | DBPART_LENGTH | DBPART_VALUE;
        Binding.dwMemOwner  = DBMEMOWNER_CLIENTOWNED;
        Binding.eParamIO    = DBPARAMIO_NOTPARAM;
        Binding.cbMaxLen    = (128 + 1) * sizeof(WCHAR);
        Binding.wType       = DBTYPE_WSTR;
        Binding.dwFlags     = 0;
        Binding.bPrecision  = 0;
        Binding.bScale      = 0;
        CComPtr<IAccessor> spIAccessor;
        hr = spIRowset->QueryInterface(IID_IAccessor, (void**) &spIAccessor);
        HACCESSOR hAccessor = NULL;
        hr = spIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 1, &Binding, 0, &hAccessor, NULL);
        hr = spIRowset->GetData(hRow, hAccessor, &data);
        DBREFCOUNT cRefCount = 0;
        hr = spIAccessor->ReleaseAccessor(hAccessor, &cRefCount);
        spIAccessor = NULL;

        // @@TODO: Do something with data.szTableName and data.nTableNameLength
        _tprintf(_T("%s\n"), data.szTableName);

        // Fetch next row of data.
        hr = spIRowset->ReleaseRows(1, rghRow, NULL, NULL, NULL);
        cRowsObtained = 0;
        hr = spIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &rghRow);
    }

    // Release everything
    spIRowset = NULL;
    spIDBInitialize = NULL;

    CoUninitialize();
    return 0;
}

以下是用于查询 SQL Server CE 3.0/3.1 数据库以浏览 SQL Server CE 3.0/3.1 数据库的 C++ 控制台应用程序示例使用 IDBSchemaRowset 和 IRowset 的表（请注意，TABLE_NAME 现在位于 iOrdinal 3 而不是1）：

#include <stdio.h>
#include <tchar.h>
#include <windows.h>
#include <oledb.h>
#include <atlbase.h>

int _tmain(int argc, _TCHAR* argv[])
{
    HRESULT hr = S_OK;
    hr = CoInitializeEx(NULL, COINIT_MULTITHREADED);

    // Connect to SQL Server.
    CComPtr<IDBInitialize> spIDBInitialize;
    hr = spIDBInitialize.CoCreateInstance(OLESTR("Microsoft.SQLLITE.MOBILE.OLEDB.3.0"));
    CComPtr<IDBProperties> spIDBProperties;
    hr = spIDBInitialize->QueryInterface(&spIDBProperties);
    CComVariant varDataSource(OLESTR("InsertYourSampleDatabase.SDF"));
    DBPROP prop = { DBPROP_INIT_DATASOURCE, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varDataSource };
    DBPROPSET propSet = {&prop, 1, DBPROPSET_DBINIT};
    hr = spIDBProperties->SetProperties(1, &propSet);
    spIDBProperties = NULL;
    hr = spIDBInitialize->Initialize();

    // Execute the query.
    CComPtr<IDBCreateSession> spIDBCreateSession;
    hr = spIDBInitialize->QueryInterface(&spIDBCreateSession);
    CComPtr<IDBSchemaRowset> spIDBSchemaRowset;
    hr = spIDBCreateSession->CreateSession(NULL, IID_IDBSchemaRowset, (IUnknown**) &spIDBSchemaRowset);
    spIDBCreateSession = NULL;
    CComVariant rgRestrictions[CRESTRICTIONS_DBSCHEMA_TABLES];
    // rgRestrictions[2] = OLESTR("CENSUS"); TABLE_NAME restriction
    rgRestrictions[3] = OLESTR("TABLE"); // TABLE_TYPE restriction
    CComPtr<IRowset> spIRowset;
    hr = spIDBSchemaRowset->GetRowset(NULL, DBSCHEMA_TABLES, CRESTRICTIONS_DBSCHEMA_TABLES, rgRestrictions, IID_IRowset, 0, NULL, (IUnknown**) &spIRowset);
    spIDBSchemaRowset = NULL;

    // Retrieve records.
    HROW hRow = NULL;
    HROW *rghRow = &hRow;
    DBCOUNTITEM cRowsObtained = 0;
    hr = spIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &rghRow);
    while (hr == S_OK && cRowsObtained == 1)
    {
        // Fetch the TABLE_NAME field.
        struct
        {
            DBSTATUS dbTableNameStatus;
            ULONG nTableNameLength;
            WCHAR szTableName[128 + 1];
        } data = {0};
        DBBINDING Binding = {0};
        Binding.iOrdinal    = 3;
        Binding.obValue     = sizeof(DBSTATUS) + sizeof(ULONG);
        Binding.obLength    = sizeof(DBSTATUS);
        Binding.obStatus    = 0;
        Binding.pTypeInfo   = NULL;
        Binding.pObject     = NULL;
        Binding.pBindExt    = NULL;
        Binding.dwPart      = DBPART_STATUS | DBPART_LENGTH | DBPART_VALUE;
        Binding.dwMemOwner  = DBMEMOWNER_CLIENTOWNED;
        Binding.eParamIO    = DBPARAMIO_NOTPARAM;
        Binding.cbMaxLen    = (128 + 1) * sizeof(WCHAR);
        Binding.wType       = DBTYPE_WSTR;
        Binding.dwFlags     = 0;
        Binding.bPrecision  = 0;
        Binding.bScale      = 0;
        CComPtr<IAccessor> spIAccessor;
        hr = spIRowset->QueryInterface(IID_IAccessor, (void**) &spIAccessor);
        HACCESSOR hAccessor = NULL;
        hr = spIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 1, &Binding, 0, &hAccessor, NULL);
        hr = spIRowset->GetData(hRow, hAccessor, &data);
        DBREFCOUNT cRefCount = 0;
        hr = spIAccessor->ReleaseAccessor(hAccessor, &cRefCount);
        spIAccessor = NULL;

        // @@TODO: Do something with data.szTableName and data.nTableNameLength
        _tprintf(_T("%s\n"), data.szTableName);

        // Fetch next row of data.
        hr = spIRowset->ReleaseRows(1, rghRow, NULL, NULL, NULL);
        cRowsObtained = 0;
        hr = spIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &rghRow);
    }

    // Release everything
    spIRowset = NULL;
    spIDBInitialize = NULL;

    CoUninitialize();
    return 0;
}

Answer 2

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES