VC++ OLE DB 读写数据库
在需要對(duì)數(shù)據(jù)庫進(jìn)行操作時(shí),OLE DB總是被認(rèn)為是一種效率最高但最難的方法。但是以我最近使用OLE DB的經(jīng)驗(yàn)看來,OLE DB的效率高則高矣,但卻一點(diǎn)都不難。說它難恐怕主要是因?yàn)榭蓞⒖嫉闹形馁Y料太少,為了幫助以后需要接觸OLE DB的同行,我撰寫了這篇文章。本文包含如下內(nèi)容:
首先來看看對(duì)SQL Server進(jìn)行寫操作的代碼,有一定VC基礎(chǔ)的讀者應(yīng)該可以很順利地看懂。OLE DB寫數(shù)據(jù)庫,就是這么簡單!
注:
1.以下代碼中使用的模板類EAutoReleasePtr與ATL中的CComPtr類似,是一個(gè)在析構(gòu)時(shí)自動(dòng)調(diào)用Release的類。CComPtr的代碼在ATLBASE.H中定義。
2.以下代碼均在UNICODE環(huán)境下編譯,因?yàn)閳?zhí)行的SQL語句必須是UNICODE的。設(shè)置工程為UNICODE的方法是:首先在project->settings->C/C++的屬性頁中的Preprocessor中,刪除_MBCS寫入U(xiǎn)NICODE,_UNICODE。然后在link屬性頁中Category中選擇output,在Entry-Point symbol 中添加wWinMainCRTStartup。
EAutoReleasePtr pIDBInitialize;
HRESULT hResult = ConnectDatabase( &pIDBInitialize, _T(“127.0.0.1”), _T(“sa”), _T(“password”) );
if( FAILED( hResult ) )
{
//失敗,可能是因?yàn)閿?shù)據(jù)庫沒有啟動(dòng)、用戶名密碼錯(cuò)等等
return;
}
EAutoReleasePtr pIOpenRowset;
hResult = CreateSession( pIDBInitialize, &pIOpenRowset );
if( FAILED( hResult ) )
{
//出錯(cuò)
return;
}
EAutoReleasePtr pICommand;
EAutoReleasePtr pICommandText;
hResult = CreateCommand( pIOpenRowset, &pICommand, &pICommandText );
if( FAILED( hResult ) )
{
//出錯(cuò)
return;
}
hResult = ExecuteSQL( pICommand, pICommandText, _T(“USE PBDATA”) );
if( FAILED( hResult ) )
{
//如果這里失敗,那就是SQL語句執(zhí)行失敗。在此處,就是PBDATA還未創(chuàng)建
return;
}
// 創(chuàng)建表
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)”) );
//…
其中幾個(gè)函數(shù)的代碼如下:
HRESULT ConnectDatabase( IDBInitialize** ppIDBInitialize, LPCTSTR pszDataSource, LPCTSTR pszUserID, LPCTSTR pszPassword )
{
ASSERT( ppIDBInitialize != NULL && pszDataSource != NULL && pszUserID != NULL && pszPassword != NULL );
}
HRESULT CreateSession( IDBInitialize* pIDBInitialize, IOpenRowset** ppIOpenRowset )
{
ASSERT( pIDBInitialize != NULL && ppIOpenRowset != NULL );
EAutoReleasePtr pSession;
HRESULT hResult = pIDBInitialize->QueryInterface( IID_IDBCreateSession, ( void** )&pSession );
if( FAILED( hResult ) )
{
return hResult;
}
EAutoReleasePtr 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 pICommand;
{
EAutoReleasePtr pICreateCommand;
hResult = pIOpenRowset->QueryInterface( IID_IDBCreateCommand, ( void** )&pICreateCommand );
if( FAILED( hResult ) )
{
return hResult;
}
}
HRESULT ExecuteSQL( ICommand* pICommand, ICommandText* pICommandText, LPCTSTR pszCommand, LONG* plRowsAffected )
{
ASSERT( pICommand != NULL && pICommandText != NULL && pszCommand != NULL && pszCommand[0] != 0 );
}
以上就是寫數(shù)據(jù)庫的全部代碼了,是不是很簡單呢?下面再來讀的。
// 先用與上面代碼中一樣的步驟獲取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 pIRowset;
hResult = pICommand->Execute( NULL, IID_IRowset, NULL, &lAffected, ( IUnknown** )&pIRowset );
if( FAILED( hResult ) )
{
return;
}
EAutoReleasePtr pIAccessor;
hResult = pIRowset->QueryInterface( IID_IAccessor, ( void** )&pIAccessor );
if( FAILED( hResult ) )
{
return;
}
// 一個(gè)根據(jù)表中各字段的數(shù)值類型而定義的結(jié)構(gòu),用于存儲(chǔ)返回的各字段的值
struct CLoadLastFromDB
{
DBSTATUS dwdsVolume;
DWORD dwLenVolume;
float fVolume;
};
// 此處我們只查詢了一個(gè)字段。如果要查詢多個(gè)字段,CLoadLastFromDB中要添加相應(yīng)的字段定義,下面的dbBinding也要相應(yīng)擴(kuò)充。dbBinding[].iOrdinal要分別指向各個(gè)字段,dbBinding[].wType要根據(jù)字段類型賦合適的值。
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( );
讀操作也完成了,是不是仍然很簡單呢?下面我們?cè)賮砜纯醋盥闊┑亩M(jìn)制數(shù)據(jù)(text、ntext、image等)的讀寫。要實(shí)現(xiàn)BLOB數(shù)據(jù)的讀寫,我們需要一個(gè)輔助的類,定義如下:
class CSequentialStream : public ISequentialStream // BLOB 數(shù)據(jù)訪問類
{
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
};
實(shí)現(xiàn)如下:
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; }
}
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; }
}
下面我們開始往一個(gè)包含ntext字段的表中添加記錄。假設(shè)這個(gè)表(News)的結(jié)構(gòu)為:ID int NOT NULL IDENTITY、Title nchar(80)、 Contents ntext。
// 先將記錄添加進(jìn)去,ntext字段留空。我們稍后再更新ntext的內(nèi)容。
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 pICommandProperties;
hResult = pICommandText->QueryInterface( IID_ICommandProperties, ( void** )&pICommandProperties );
// 設(shè)置 Rowset 屬性為“可以更新某字段的值”
hResult = pICommandProperties->SetProperties( 1, &dbPropSet );
hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )L"SELECT Contents FROM News WHERE ID = @@IDENTITY" );
LONG lAffected;
EAutoReleasePtr pIRowsetChange;
hResult = pICommand->Execute( NULL, IID_IRowsetChange, NULL, &lAffected, ( IUnknown** )&pIRowsetChange );
EAutoReleasePtr pIAccessor;
hResult = pIRowsetChange->QueryInterface( IID_IAccessor, ( void** )&pIAccessor );
struct BLOBDATA
{
DBSTATUS dwStatus;
DWORD dwLength;
ISequentialStream* pISeqStream;
};
// 有關(guān)DBOBJECT、DBBINDING的設(shè)置,建議參考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 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 ); // 預(yù)先分配好內(nèi)存,并讀入數(shù)據(jù)
pss->Write( pszSomebuffer, 512, NULL ); // pss->Write可以連續(xù)調(diào)用
pss->Write( pszSomebuffer+512, 512, NULL );
pss->ResetPosition( );
BLOBDATA bd;
bd.pISeqStream = ( ISequentialStream* )pss;
bd.dwStatus = DBSTATUS_S_OK;
bd.dwLength = pss->GetLength( );
// 將 BLOB 數(shù)據(jù)寫入到數(shù)據(jù)庫
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
{
// 從系統(tǒng)分配的 ISequentialStream 接口讀入 BLOB 數(shù)據(jù)
BYTE szReadBuffer[1024];
for( ULONG uRead = 0U; ; )
{
if( FAILED( bd.pISeqStream->Read( szReadBuffer, 1024, &uRead ) ) )
{
break;
}
//szReadBuffer中就包含了BLOB字段的數(shù)據(jù)
if( uRead != 1024 )
{
break;
}
}
bd.pISeqStream->Release( );
}
pIAccessor->ReleaseAccessor( hAccessor, NULL );
pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL );
總結(jié)
以上是生活随笔為你收集整理的VC++ OLE DB 读写数据库的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Scrapy爬取知乎用户信息以及人际拓扑
- 下一篇: CDN的加速原理