Visual C++中使用OLE DB讀寫SQL Server簡明指南

Visual C++中使用OLE DB讀寫SQL Server簡明指南
 
黃友生。本文由原作者發佈於MSN Space、CSDN。你可以儲存、在非商業軟體中使用、在非盈利性文章中引用本文中的部分或全部文字，但請註明作者及原文地址。要用於其它用途，請先聯絡作者（eien@eyou.com）。作者不保證本文完全正確無誤、不對因本文中的理論或代碼缺陷造成的損失負責。

在需要對資料庫進行操作時，OLE DB總是被認為是一種效率最高但最難的方法。但是以我最近使用OLE DB的經驗看來，OLE DB的效率高則高矣，但卻一點都不難。說它難恐怕主要是因為可參考的中文資料太少，為了協助以後需要接觸OLE DB的同行，我撰寫了這篇文章。本文包含如下內容：

1. OLE DB寫資料庫；
2. OLE DB讀資料庫；
3. OLE DB對位元據(text、ntext、image等)的處理。

首先來看看對SQL Server進行寫操作的代碼，有一定VC基礎的讀者應該可以很順利地看懂。OLE DB寫資料庫，就是這麼簡單！

注:
1.以下代碼中使用的模板類EAutoReleasePtr<T>與ATL中的CComPtr<T>類似，是一個在析構時自動調用Release的類。CComPtr<T>的代碼在ATLBASE.H中定義。
2.以下代碼均在UNICODE環境下編譯，因為執行的SQL語句必須是UNICODE的。設定工程為UNICODE的方法是:首先在project->settings->C/C++的屬性頁面中的Preprocessor中，刪除_MBCS寫入UNICODE,_UNICODE。然後在link屬性頁面中Category中選擇output，在Entry-Point symbol 中添加wWinMainCRTStartup。

EAutoReleasePtr<IDBInitialize> pIDBInitialize;
HRESULT hResult = ConnectDatabase( &pIDBInitialize, _T("127.0.0.1"), _T(“sa”), _T("password") );
if( FAILED( hResult ) )
{
    //失敗，可能是因為資料庫沒有啟動、使用者名稱密碼錯等等
    return;
}

EAutoReleasePtr<IOpenRowset> pIOpenRowset;
hResult = CreateSession( pIDBInitialize, &pIOpenRowset );
if( FAILED( hResult ) )
{
    //出錯
    return;
}

EAutoReleasePtr<ICommand> pICommand;
EAutoReleasePtr<ICommandText> pICommandText;
hResult = CreateCommand( pIOpenRowset, &pICommand, &pICommandText );
if( FAILED( hResult ) )
{
   //出錯
    return;
}

hResult = ExecuteSQL( pICommand, pICommandText, _T("USE PBDATA") );
if( FAILED( hResult ) )
{
    //如果這裡失敗，那就是SQL語句執行失敗。在此處，就是PBDATA還未建立
    return;
}

// 建立表
ExecuteSQL( pICommand, pICommandText, _T("CREATE TABLE 2005_1(Volume real NOT NULL,ID int NOT NULL IDENTITY)") );

// 添加記錄
ExecuteSQL( pICommand, pICommandText, _T("INSERT INTO 2005_1 VALUES(100.0)") );

//...
 
其中幾個函數的代碼如下:

HRESULT ConnectDatabase( IDBInitialize** ppIDBInitialize, LPCTSTR pszDataSource, LPCTSTR pszUserID, LPCTSTR pszPassword )
{
    ASSERT( ppIDBInitialize != NULL && pszDataSource != NULL && pszUserID != NULL && pszPassword != NULL );

    UINT uTimeout ＝ 15U; // 串連資料庫逾時（秒）
    TCHAR szInitStr[1024];
    VERIFY( 1023 >= wsprintf( szInitStr, _T("Provider=SQLOLEDB;Data Source=%s;Initial Catalog=master;User Id=%s;Password=%s;Connect Timeout=%u"), pszDataSource, pszUserID, pszPassword, uTimeout ) );
    //Initial Catalog=master指明串連成功後，"USE master"。

    EAutoReleasePtr<IDataInitialize> pIDataInitialize;
    HRESULT hResult = ::CoCreateInstance( CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER,
            IID_IDataInitialize, ( void** )&pIDataInitialize );
  if( FAILED( hResult ) )
    {
        return hResult;
    }

    EAutoReleasePtr<IDBInitialize> pIDBInitialize;
    hResult = pIDataInitialize->GetDataSource( NULL, CLSCTX_INPROC_SERVER, ( LPCOLESTR )szInitStr,
              IID_IDBInitialize, ( IUnknown** )&pIDBInitialize );
    if( FAILED( hResult ) )
    {
        return hResult;
    }
    hResult = pIDBInitialize->Initialize( );
    if( FAILED( hResult ) )
    {
        return hResult;
    }
    * ppIDBInitialize = pIDBInitialize.Detach( );
    return S_OK;
}

HRESULT CreateSession( IDBInitialize* pIDBInitialize, IOpenRowset** ppIOpenRowset )
{
    ASSERT( pIDBInitialize != NULL && ppIOpenRowset != NULL );
    EAutoReleasePtr<IDBCreateSession> pSession;
    HRESULT hResult = pIDBInitialize->QueryInterface( IID_IDBCreateSession, ( void** )&pSession );
    if( FAILED( hResult ) )
    {
        return hResult;
    }
    EAutoReleasePtr<IOpenRowset> pIOpenRowset;
    hResult = pSession->CreateSession( NULL, IID_IOpenRowset, ( IUnknown** )&pIOpenRowset );
    if( FAILED( hResult ) )
    {
        return hResult;
    }
    * ppIOpenRowset = pIOpenRowset.Detach( );
    return S_OK;
}

HRESULT CreateCommand( IOpenRowset* pIOpenRowset, ICommand** ppICommand, ICommandText** ppICommandText )
{
    ASSERT( pIOpenRowset != NULL && ppICommand != NULL && ppICommandText != NULL );
    HRESULT hResult;
    EAutoReleasePtr<ICommand> pICommand;
    {
        EAutoReleasePtr<IDBCreateCommand> pICreateCommand;
        hResult = pIOpenRowset->QueryInterface( IID_IDBCreateCommand, ( void** )&pICreateCommand );
        if( FAILED( hResult ) )
        {
            return hResult;
        }
 
        hResult = pICreateCommand->CreateCommand( NULL, IID_ICommand, (IUnknown**)&pICommand );
        if( FAILED( hResult ) )
  {
            return hResult;
        }
    }
    EAutoReleasePtr<ICommandText> pICommandText;
    hResult = pICommand->QueryInterface( &pICommandText );
    if( FAILED( hResult ) )
    {
        return hResult;
    }
    * ppICommand = pICommand.Detach( );
    * ppICommandText = pICommandText.Detach( );
    return S_OK;
}

HRESULT ExecuteSQL( ICommand* pICommand, ICommandText* pICommandText, LPCTSTR pszCommand, LONG* plRowsAffected )
{
    ASSERT( pICommand != NULL && pICommandText != NULL && pszCommand != NULL && pszCommand[0] != 0 );

    HRESULT hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )pszCommand );
    if( FAILED( hResult ) )
    {
        return hResult;
    }
    LONG lAffected;
    hResult = pICommand->Execute( NULL, IID_NULL, NULL, plRowsAffected == NULL ? &lAffected : plRowsAffected, ( IUnknown** )NULL );
    return hResult;
}

以上就是寫資料庫的全部代碼了，是不是很簡單呢？下面再來讀的。

// 先用與上面代碼中一樣的步驟擷取pICommand，pICommandText。此處省略

HRESULT hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )_T("SELECT Volume FROM 2005_1 WHERE ID = @@IDENTITY") ); //取我們剛剛添加的那一條記錄
if( FAILED( hResult ) )
{
    return;
}

LONG lAffected;
EAutoReleasePtr<IRowset> pIRowset;
hResult = pICommand->Execute( NULL, IID_IRowset, NULL, &lAffected, ( IUnknown** )&pIRowset );

if( FAILED( hResult ) )
{
    return;
}

EAutoReleasePtr<IAccessor> pIAccessor;
hResult = pIRowset->QueryInterface( IID_IAccessor, ( void** )&pIAccessor );

if( FAILED( hResult ) )
{
    return;
}

// 一個根據表中各欄位的數實值型別而定義的結構，用於儲存返回的各欄位的值
struct CLoadLastFromDB
{
     DBSTATUS dwdsVolume;
     DWORD     dwLenVolume;
     float          fVolume;
};

// 此處我們只查詢了一個欄位。如果要查詢多個欄位，CLoadLastFromDB中要添加相應的欄位定義，下面的dbBinding也要相應擴充。dbBinding[].iOrdinal要分別指向各個欄位，dbBinding[].wType要根據欄位類型賦合適的值。

DBBINDING dbBinding[1];
dbBinding[0].iOrdinal            = 1;   // Volume 欄位的位置，從 1 開始
dbBinding[0].obValue           = offsetof( CLoadLastFromDB, fVolume );
dbBinding[0].obLength         = offsetof( CLoadLastFromDB, dwLenVolume );
dbBinding[0].obStatus         = offsetof( CLoadLastFromDB, dwdsVolume );
dbBinding[0].pTypeInfo       = NULL;
dbBinding[0].pObject           = NULL;
dbBinding[0].pBindExt         = NULL;
dbBinding[0].dwPart            = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
dbBinding[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
dbBinding[0].eParamIO       = DBPARAMIO_NOTPARAM;
dbBinding[0].cbMaxLen       = 0;
dbBinding[0].dwFlags          = 0;
dbBinding[0].wType            = DBTYPE_R4; // float就是DBTYPE_R4，int就是DBTYPE_I4。參見MSDN
dbBinding[0].bPrecision       = 0;
dbBinding[0].bScale             = 0;

HACCESSOR hAccessor = DB_NULL_HACCESSOR;
DBBINDSTATUS dbs[1];
hResult = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, dbBinding, sizeof( CLoadLastDataFromDB ), &hAccessor, dbs );

if( FAILED( hResult ) )
{
    return;
}

ASSERT( dbs[0] == DBBINDSTATUS_OK );
ULONG uRowsObtained = 0;
HROW  hRows[1];                          // 這裡我們只查詢了最新的那一條記錄
HROW* phRows = hRows;
CLoadLastFromDB rmd;
hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows );
if( SUCCEEDED( hResult ) && uRowsObtained != 0U )
{
    hResult = pIRowset->GetData( phRows[0], hAccessor, &rmd );
    if( FAILED( hResult ) )
    {
        ASSERT( FALSE );
    }
    ASSERT( rmd.dwdsVolume == DBSTATUS_S_OK );
    // rmd.fVolume 就是我們要取的值
}

pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL );
pIAccessor->ReleaseAccessor( hAccessor, NULL );
pIAccessor.Release( );
pIRowset.Release( );
 
讀操作也完成了，是不是仍然很簡單呢？下面我們再來看看最麻煩的位元據（text、ntext、image等）的讀寫。要實現BLOB資料的讀寫，我們需要一個輔助的類，定義如下：

class CSequentialStream : public ISequentialStream    // BLOB 資料訪問類
{
public:
    CSequentialStream( );
    virtual ~CSequentialStream( );
    virtual BOOL Seek( ULONG uPosition );
    virtual BOOL Clear( );
    virtual ULONG GetLength( ) { return m_uBufferUsed; };
virtual operator void* const( ) { return m_pBuffer; };
    STDMETHODIMP_( ULONG ) AddRef( ) { return ++ m_uRefCount; };
    STDMETHODIMP_( ULONG ) Release( ) { ASSERT( m_uRefCount != 0U ); -- m_uRefCount; if( m_uRefCount == 0U ) { delete this; } return m_uRefCount; };
    STDMETHODIMP QueryInterface( REFIID riid, LPVOID* ppv );
    STDMETHODIMP Read( void __RPC_FAR* pv, ULONG cb, ULONG __RPC_FAR* pcbRead );
    STDMETHODIMP Write( const void __RPC_FAR* pv, ULONG cb, ULONG __RPC_FAR* pcbWritten );
void    ResetPosition( ) { m_uPosition = 0U; };
HRESULT PreAllocBuffer( ULONG uSize );

private:
    ULONG m_uRefCount;     // reference count
    void* m_pBuffer;           // buffer
    ULONG m_uBufferUsed;  // buffer used
    ULONG m_uBufferSize;   // buffer size
    ULONG m_uPosition;       // current index position in the buffer
};

實現如下：

CSequentialStream::CSequentialStream( ) : m_uRefCount( 0U ), m_pBuffer( NULL ), m_uBufferUsed( 0U ), m_uBufferSize( 0U ), m_uPosition( 0U )
{
    AddRef( );
}

CSequentialStream::~CSequentialStream( )
{
    Clear( );
}

HRESULT CSequentialStream::QueryInterface( REFIID riid, void** ppv )
{
    if( riid == IID_IUnknown || riid == IID_ISequentialStream )
    {
        * ppv = this;
        ( ( IUnknown* )*ppv )->AddRef( );
        return S_OK;
    }
    * ppv = NULL;
    return E_NOINTERFACE;
}

BOOL CSequentialStream::Seek( ULONG uPosition )
{
    ASSERT( uPosition < m_uBufferUsed );
    m_uPosition = uPosition;
    return TRUE;
}

BOOL CSequentialStream::Clear( )
{
    m_uBufferUsed = 0U;
    m_uBufferSize = 0U;
    m_uPosition = 0U;
    ( m_pBuffer != NULL ? CoTaskMemFree( m_pBuffer ) : 0 );
    m_pBuffer = NULL;
    return TRUE;
}

HRESULT CSequentialStream::PreAllocBuffer( ULONG uSize )
{
    if( m_uBufferSize < uSize )
    {
        m_uBufferSize = uSize;
        m_pBuffer = CoTaskMemRealloc( m_pBuffer, m_uBufferSize );
        if( m_pBuffer == NULL )
        {
            Clear( );
            return STG_E_INSUFFICIENTMEMORY;
        }
    }
    return S_OK;
}

HRESULT CSequentialStream::Read( void* pv, ULONG cb, ULONG* pcbRead )
{
    ( pcbRead != NULL ? ( * pcbRead = 0U ) : 0 );
    if( pv == NULL ) { return STG_E_INVALIDPOINTER; }
    if( cb == 0U ) { return S_OK; }

    ASSERT( m_uPosition <= m_uBufferUsed );
    ULONG uBytesLeft = m_uBufferUsed - m_uPosition;

    if( uBytesLeft == 0U ) { return S_FALSE; } //no more bytes

    ULONG uBytesRead = ( cb > uBytesLeft ? uBytesLeft : cb );
    memcpy( pv, ( BYTE* )m_pBuffer + m_uPosition, uBytesRead );
    m_uPosition += uBytesRead;

    ( pcbRead != NULL ? ( * pcbRead = uBytesRead ) : 0 );
    return ( cb != uBytesRead ? S_FALSE : S_OK );
}

HRESULT CSequentialStream::Write( const void* pv, ULONG cb, ULONG* pcbWritten )
{
    if( pv == NULL ) { return STG_E_INVALIDPOINTER; }
    ( pcbWritten != NULL ? ( * pcbWritten = 0U ) : 0 );
    if( cb == 0U ){ return S_OK; }

    ASSERT( m_uPosition <= m_uBufferUsed );
    if( m_uBufferSize < m_uPosition + cb )
    {
        m_uBufferSize = m_uPosition + cb;
        m_pBuffer = CoTaskMemRealloc( m_pBuffer, m_uBufferSize );
        if( m_pBuffer == NULL )
        {
            Clear( );
            return STG_E_INSUFFICIENTMEMORY;
        }
    }
    m_uBufferUsed = m_uPosition + cb;
    memcpy( ( BYTE* )m_pBuffer + m_uPosition, pv, cb );
    m_uPosition += cb;
    ( pcbWritten != NULL ? ( * pcbWritten = cb ) : 0 );
    return S_OK;
}

下面我們開始往一個包含ntext欄位的表中添加記錄。假設這個表（News）的結構為：ID int NOT NULL IDENTITY、Title nchar(80)、 Contents ntext。

// 先將記錄添加進去，ntext欄位留空。我們稍後再更新ntext的內容。
HRESULT hResult = ExecuteSQL( pICommand, pICommandText, _T("INSERT INTO News VALUES(''TEST'','''')") );

DBPROP dbProp;
dbPropSet.guidPropertySet = DBPROPSET_ROWSET;
dbPropSet.cProperties        = 1;
dbPropSet.rgProperties       = &dbProp;

DBPROPSET dbPropSet;
dbPropSet.rgProperties[0].dwPropertyID     = DBPROP_UPDATABILITY;
dbPropSet.rgProperties[0].dwOptions         = DBPROPOPTIONS_REQUIRED;
dbPropSet.rgProperties[0].dwStatus           = DBPROPSTATUS_OK;
dbPropSet.rgProperties[0].colid                  = DB_NULLID;
dbPropSet.rgProperties[0].vValue.vt           = VT_I4;
V_I4( &dbPropSet.rgProperties[0].vValue ) = DBPROPVAL_UP_CHANGE;
   
EAutoReleasePtr<ICommandProperties> pICommandProperties;
hResult = pICommandText->QueryInterface( IID_ICommandProperties, ( void** )&pICommandProperties );

// 設定 Rowset 屬性為“可以更新某欄位的值”
hResult = pICommandProperties->SetProperties( 1, &dbPropSet );

hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )L"SELECT Contents FROM News WHERE ID = @@IDENTITY" );

LONG lAffected;
EAutoReleasePtr<IRowsetChange> pIRowsetChange;
hResult = pICommand->Execute( NULL, IID_IRowsetChange, NULL, &lAffected, ( IUnknown** )&pIRowsetChange );

EAutoReleasePtr<IAccessor> pIAccessor;
hResult = pIRowsetChange->QueryInterface( IID_IAccessor, ( void** )&pIAccessor );

struct BLOBDATA
{
    DBSTATUS           dwStatus;
    DWORD              dwLength;
    ISequentialStream* pISeqStream;
};

// 有關DBOBJECT、DBBINDING的設定，建議參考MSDN，很容易懂。
DBOBJECT dbObj;
dbObj.dwFlags = STGM_READ;
dbObj.iid         = IID_ISequentialStream;

DBBINDING dbBinding;
dbBinding.iOrdinal   = 1;                              // BLOB 欄位的位置，從 1 開始
dbBinding.obValue    = offsetof( BLOBDATA, pISeqStream );
dbBinding.obLength   = offsetof( BLOBDATA, dwLength );
dbBinding.obStatus   = offsetof( BLOBDATA, dwStatus );
dbBinding.pTypeInfo  = NULL;
dbBinding.pObject    = &dbObj;
dbBinding.pBindExt   = NULL;
dbBinding.dwPart     =  DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
dbBinding.dwMemOwner = DBMEMOWNER_CLIENTOWNED;
dbBinding.eParamIO   = DBPARAMIO_NOTPARAM;
dbBinding.cbMaxLen   = 0;
dbBinding.dwFlags    = 0;
dbBinding.wType      = DBTYPE_IUNKNOWN;
dbBinding.bPrecision = 0;
dbBinding.bScale     = 0;

HACCESSOR hAccessor = DB_NULL_HACCESSOR;
DBBINDSTATUS dbs;
hResult = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, &dbBinding, sizeof( BLOBDATA ), &hAccessor, &dbs );

EAutoReleasePtr<IRowset> pIRowset;
hResult = pIRowsetChange->QueryInterface( IID_IRowset, ( void** )&pIRowset );

ULONG uRowsObtained = 0;
HROW* phRows = NULL;
hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows );

CSequentialStream* pss = new CSequentialStream;
pss->PreAllocBuffer( 1024 );                           // 預先分配好記憶體，並讀入資料
pss->Write( pszSomebuffer, 512, NULL );        // pss->Write可以連續調用
pss->Write( pszSomebuffer+512, 512, NULL );
pss->ResetPosition( );

BLOBDATA bd;
bd.pISeqStream = ( ISequentialStream* )pss;
bd.dwStatus    = DBSTATUS_S_OK;
bd.dwLength    = pss->GetLength( );

// 將 BLOB 資料寫入到資料庫
hResult = pIRowsetChange->SetData( phRows[0], hAccessor, &bd );

pIAccessor->ReleaseAccessor( hAccessor, NULL );
pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL );

// pss was released by pIRowsetChange->SetData.

這樣，我們就完成了一條記錄的添加。讀取BLOB欄位的代碼跟上面的完全類似，只要把
hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows );
後面的那些改成下面的代碼即可。

BLOBDATA bd;
hResult = pIRowset->GetData( phRows[0], hAccessor, &bd );
if( bd.dwStatus == DBSTATUS_S_ISNULL )
{
    // 此欄位為空白
}
else if( bd.dwStatus != DBSTATUS_S_OK || bd.pISeqStream == NULL )
{
    // 失敗
}
else
{
    // 從系統分配的 ISequentialStream 介面讀入 BLOB 資料
    BYTE szReadBuffer[1024];
    for( ULONG uRead = 0U; ; )
    {
        if( FAILED( bd.pISeqStream->Read( szReadBuffer, 1024, &uRead ) ) )
        {
            break;
        }
        //szReadBuffer中就包含了BLOB欄位的資料
        if( uRead != 1024 )
        {
            break;
        }
    }
    bd.pISeqStream->Release( );
}
pIAccessor->ReleaseAccessor( hAccessor, NULL );
pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL );

至此，要講的已全部講完，希望對你能有所協助。文中貼出的代碼都是可以複製使用的，只是某些地方需要加入傳回值判斷、錯誤處理代碼。