MS2010 VC++使用ADO访问数据库

下文介绍了使用MS2010开发坏境中的VC++和Microsoft.Jet.OLEDB.4.0数据库引擎操作ACCESS的通用类

1.头文件OperatorADO.h

#pragma once#include <atlbase.h>#include <afxoledb.h>#include <atlplus.h>#include <atldbsch.h>#include <atlconv.h>#include <atlcoll.h>typedef struct CSchemaTableInfo{CString csSchema;CString csName;CSchemaTableInfo(){}CSchemaTableInfo( const CSchemaTableInfo& other ){csSchema = other.csSchema;csName = other.csName;}CSchemaTableInfo& operator=( const CSchemaTableInfo& other ){if( &other != this ){csSchema = other.csSchema;csName = other.csName;}return *this;}};class OperatorADO{public:OperatorADO(void);~OperatorADO(void);// 根据连接字符串获得数据源类实例BOOL OpenDataSource(CString strConn,CDataSource* pDS,CSession* pSession);////////////////////////////////////////////////////////////////////////////////////访问数据集的方式////////////////////////////////////////////////////////////////////////////////////CDynamicAccessor形式访问数据// 通过SQL语句获得记录集BOOL GetRecordsFromSQL(CSession* pSession,CCommand<CDynamicAccessor>* pRs, CString strSQL);// 从记录集中获得数据//template<class T>  //CCommand<CDynamicAccessor>void* GetDataFromRecords(CCommand<CDynamicAccessor>* pRs,int nRow,int nCol,DBTYPE &type);//CDynamicStringAccessor形式访问数据// 通过SQL语句获得记录集BOOL GetRecordsFromSQL(CSession* pSession,CCommand<CDynamicStringAccessor>* pRs, CString strSQL);// 从记录集中获得数据//template<class T>  //CCommand<CDynamicAccessor>CHAR* GetDataFromRecords(CCommand<CDynamicStringAccessor>* pRs,int nRow,int nCol,DBTYPE &type);//CXMLAccessor形式访问数据// 通过SQL语句获得记录集BOOL GetRecordsFromSQL(CSession* pSession,CCommand<CXMLAccessor>* pRs, CString strSQL);// 从记录集中获得数据//template<class T>  //CCommand<CDynamicAccessor>CString GetDataFromRecords(CCommand<CXMLAccessor>* pRs,int nRow,int nCol,DBTYPE &type);////////////////////////////////////////////////////////////////////////////////////16进制数转为ASCII码static void d2h( BYTE n, char* pszHex );void* GetValue(void);void* GetSubValue(void);// 得到数据库中数据表的信息BOOL GetTableInfo(CSession* pSession, CAtlArray<CSchemaTableInfo>* pArray);};

2.实现文件OperatorADO.cpp

#include "StdAfx.h"#include "OperatorADO.h"#define MAX_DATALEN1000000OperatorADO::OperatorADO(void){::CoInitialize(NULL);}OperatorADO::~OperatorADO(void){::CoUninitialize();}// 根据连接字符串获得数据源类实例BOOL OperatorADO::OpenDataSource(CString strConn,CDataSource* pDS,CSession* pSession){if(pDS==NULL||pSession==NULL)return FALSE;HRESULT hr=pDS->OpenFromInitializationString(CT2COLE(strConn));if(FAILED(hr))return FALSE;hr = pSession->Open( *pDS );if(FAILED(hr)){pDS->Close();return FALSE;}return TRUE;}// 通过SQL语句获得记录集BOOL OperatorADO::GetRecordsFromSQL(CSession* pSession,CCommand<CDynamicAccessor>* pRs, CString strSQL){if(pSession==NULL||pRs==NULL)return FALSE;int nBlobDeal=1;switch( nBlobDeal ){case 1:pRs->SetBlobHandling( DBBLOBHANDLING_DEFAULT );break;case 2:pRs->SetBlobHandling( DBBLOBHANDLING_NOSTREAMS );break;case 3:pRs->SetBlobHandling( DBBLOBHANDLING_SKIP );break;}CDBPropSet pset( DBPROPSET_ROWSET );pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );HRESULT hr = pRs->Open(*pSession, strSQL, &pset );if( FAILED( hr ) ){return FALSE;}return TRUE;}// 从记录集中获得数据//template<class T>  //CCommand<CDynamicAccessor>void* OperatorADO::GetDataFromRecords(CCommand<CDynamicAccessor>* pRs,int nRow,int nCol,DBTYPE &type){if(pRs==NULL)return NULL;HRESULT hr = pRs->MoveFirst();if(FAILED(hr))return NULL;for(int i=0;i<nRow;i++){hr=pRs->MoveNext();if(FAILED(hr))return NULL;}if(nCol>=pRs->GetColumnCount())return NULL;DBTYPE dbtype;DBSTATUS status;nCol+=1;pRs->GetColumnType( nCol, &dbtype );type=dbtype;pRs->GetStatus( nCol, &status );if( FAILED( status ) )return NULL;if( status == DBSTATUS_S_ISNULL ){return NULL;}else if( status == DBSTATUS_S_OK || status == DBSTATUS_S_TRUNCATED ){// the following case will handle BLOBs binded as ISequentialStream/IStream pointerif( dbtype == DBTYPE_IUNKNOWN ){// first we have to determine what was the column's type originally reported by the providerCComHeapPtr<DBCOLUMNINFO> spColumnInfo;CComHeapPtr<OLECHAR> spStringsBuffer;DBORDINAL nColumns = 0;hr = pRs->CDynamicAccessor::GetColumnInfo( pRs->m_spRowset, &nColumns, &spColumnInfo, &spStringsBuffer );if(FAILED(hr))return NULL;if( nCol > nColumns )return NULL;DBTYPE wType = spColumnInfo[nCol].wType;IUnknown* pUnknown = *(IUnknown**)pRs->GetValue( nCol );if( pUnknown == NULL )return NULL;// First, try to obtain the ISequentialStream pointerCComPtr<ISequentialStream> spSequentialStream;hr = pUnknown->QueryInterface( __uuidof(ISequentialStream), (void**)&spSequentialStream );if( SUCCEEDED(hr) && spSequentialStream ){type=wType;switch( wType ){case DBTYPE_STR:{CHAR* buffer=new CHAR[MAX_DATALEN+1];ULONG cbRead = 0;hr = spSequentialStream->Read( (void*)buffer, MAX_DATALEN, &cbRead );if( SUCCEEDED(hr) && cbRead > 0 ){buffer[cbRead] = 0;return buffer;}break;}case DBTYPE_WSTR:{WCHAR* buffer=new WCHAR[MAX_DATALEN+1];ULONG cbRead = 0;hr = spSequentialStream->Read( (void*)buffer, 2*MAX_DATALEN, &cbRead );if( SUCCEEDED(hr) && cbRead > 0 ){buffer[cbRead/2] = 0;return buffer;}break;}case DBTYPE_BYTES:{BYTE* buffer=new BYTE[MAX_DATALEN+4];ULONG cbRead = 0;hr = spSequentialStream->Read( (void*)&buffer[4], MAX_DATALEN, &cbRead );if( SUCCEEDED(hr) && cbRead > 0 ){buffer[0]=cbRead&0x0ff;buffer[1]=(cbRead>>8)&0x0ff;buffer[2]=(cbRead>>16)&0x0ff;buffer[3]=(cbRead>>24)&0x0ff;return buffer;}break;}}}else{// try to obtain the IStream pointerCComPtr<IStream> spStream;hr = pUnknown->QueryInterface( __uuidof(IStream), (void**)&spStream );if( FAILED( hr ) )return FALSE;type=wType;switch( wType ){case DBTYPE_STR:{CHAR* buffer=new CHAR[MAX_DATALEN+1];ULONG cbRead = 0;hr = spStream->Read( (void*)buffer, MAX_DATALEN, &cbRead );if( SUCCEEDED(hr) && cbRead > 0 ){buffer[cbRead] = 0;return buffer;}break;}case DBTYPE_WSTR:{WCHAR* buffer=new WCHAR[MAX_DATALEN+1];ULONG cbRead = 0;hr = spStream->Read( (void*)buffer, 2*MAX_DATALEN, &cbRead );if( SUCCEEDED(hr) && cbRead > 0 ){buffer[cbRead/2] = 0;return buffer;}break;}case DBTYPE_BYTES:{BYTE* buffer=new BYTE[MAX_DATALEN+4];ULONG cbRead = 0;hr = spStream->Read( (void*)&buffer[4], MAX_DATALEN, &cbRead );if( SUCCEEDED(hr) && cbRead > 0 ){buffer[0]=cbRead&0x0ff;buffer[1]=(cbRead>>8)&0x0ff;buffer[2]=(cbRead>>16)&0x0ff;buffer[3]=(cbRead>>24)&0x0ff;return buffer;}break;}}}}// the following three cases will handle BLOBs binded by reference in provider allocated, user owned memoryelse if( dbtype == (DBTYPE_WSTR & DBTYPE_BYREF) ){WCHAR** ppData = (WCHAR**)pRs->GetValue( nCol );return *ppData;}else if( dbtype == (DBTYPE_STR & DBTYPE_BYREF) ){CHAR** ppData = (CHAR**)pRs->GetValue( nCol );return *ppData;}else if( dbtype == (DBTYPE_BYTES & DBTYPE_BYREF) ){BYTE** ppData = (BYTE**)pRs->GetValue( nCol );return *ppData;}else{switch( dbtype ){case DBTYPE_STR:{CHAR* pData = (CHAR*)pRs->GetValue( nCol );return pData;}break;case DBTYPE_WSTR:{WCHAR* pData = (WCHAR*)pRs->GetValue( nCol );return pData;}break;case DBTYPE_BYTES:{BYTE* pData = (BYTE*)pRs->GetValue( nCol );return pData;}break;case DBTYPE_I2:return (SHORT*)pRs->GetValue( nCol );case DBTYPE_I4:return (LONG*)pRs->GetValue( nCol );case DBTYPE_I1:return (signed char*)pRs->GetValue( nCol );case DBTYPE_UI2:return (USHORT*)pRs->GetValue( nCol );case DBTYPE_UI4:return (ULONG*)pRs->GetValue( nCol );case DBTYPE_I8:return (__int64*)pRs->GetValue( nCol );case DBTYPE_UI8:return (unsigned __int64*)pRs->GetValue( nCol );case DBTYPE_R4:return (float*)pRs->GetValue( nCol );case DBTYPE_R8:return (double*)pRs->GetValue( nCol );case DBTYPE_CY:{CURRENCY* pCurrency = (CURRENCY*)pRs->GetValue( nCol );return pCurrency;}break;case DBTYPE_BSTR:return (BSTR*)pRs->GetValue(nCol);case DBTYPE_IDISPATCH:return (void**)pRs->GetValue(nCol);case DBTYPE_BOOL:return (BOOL*)pRs->GetValue(nCol);case DBTYPE_VARIANT:{VARIANT var;if (S_OK == VariantChangeType(&var, (VARIANT*)pRs->GetValue(nCol), 0, VT_BSTR)) {return &var;//printf( "%S", var.bstrVal );}else {return NULL;}}case DBTYPE_DECIMAL:{VARIANT var1, var2;var1.decVal = *(DECIMAL*)pRs->GetValue(nCol);var1.vt = VT_DECIMAL;if (S_OK == VariantChangeType(&var2, &var1, 0, VT_BSTR)) {return &var2;//printf( "%S", var2.bstrVal );} else {return NULL;}}case DBTYPE_UI1:return (BYTE*)pRs->GetValue(nCol);case DBTYPE_DBDATE:{return (DBDATE*)pRs->GetValue(nCol);}case DBTYPE_DBTIME:{return (DBTIME*)pRs->GetValue(nCol);}case DBTYPE_DBTIMESTAMP:{return (DBTIMESTAMP*)pRs->GetValue(nCol);}// the following case will handle BLOBs binded as STREAMdefault:return NULL;} // switch} // if} // ifreturn NULL;}// 通过SQL语句获得记录集BOOL OperatorADO::GetRecordsFromSQL(CSession* pSession,CCommand<CDynamicStringAccessor>* pRs, CString strSQL){if(pSession==NULL||pRs==NULL)return FALSE;CDBPropSet pset( DBPROPSET_ROWSET );pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );HRESULT hr = pRs->Open(*pSession, strSQL, &pset );if( FAILED( hr ) ){return FALSE;}return TRUE;}CHAR* OperatorADO::GetDataFromRecords(CCommand<CDynamicStringAccessor>* pRs,int nRow,int nCol,DBTYPE &type){if(pRs==NULL)return NULL;HRESULT hr = pRs->MoveFirst();if(FAILED(hr))return NULL;for(int i=0;i<nRow;i++){hr=pRs->MoveNext();if(FAILED(hr))return NULL;}if(nCol>=pRs->GetColumnCount())return NULL;DBTYPE dbtype;DBSTATUS status;nCol+=1;pRs->GetColumnType( nCol, &dbtype );type=dbtype;pRs->GetStatus( nCol, &status );if( FAILED( status ) )return NULL;if( status == DBSTATUS_S_ISNULL )return NULL;CHAR* pData = pRs->GetString( nCol );return pData;}// 通过SQL语句获得记录集BOOL OperatorADO::GetRecordsFromSQL(CSession* pSession,CCommand<CXMLAccessor>* pRs, CString strSQL){if(pSession==NULL||pRs==NULL)return FALSE;CDBPropSet pset( DBPROPSET_ROWSET );pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );HRESULT hr = pRs->Open(*pSession, strSQL, &pset );if( FAILED( hr ) ){return FALSE;}return TRUE;}CString OperatorADO::GetDataFromRecords(CCommand<CXMLAccessor>* pRs,int nRow,int nCol,DBTYPE &type){if(pRs==NULL)return NULL;HRESULT hr = pRs->MoveFirst();if(FAILED(hr))return NULL;for(int i=0;i<nRow;i++){hr=pRs->MoveNext();if(FAILED(hr))return NULL;}//if(nCol>=pRs->GetColumnCount())//return NULL;//DBTYPE dbtype;//DBSTATUS status;//nCol+=1;//pRs->GetColumnType( nCol, &dbtype );//type=dbtype;//pRs->GetStatus( nCol, &status );//if( FAILED( status ) )//return NULL;//if( status == DBSTATUS_S_ISNULL )//return NULL;CStringW csBuffer;if(!SUCCEEDED( pRs->GetXMLColumnData( csBuffer ) ) )return NULL;if(SUCCEEDED( pRs->GetXMLRowData( csBuffer ) ) ){return (CString)(COLE2CT(csBuffer));}elsereturn NULL;}//16进制数转为ASCII码void OperatorADO::d2h( BYTE n, char* pszHex ){ATLENSURE( pszHex != NULL );pszHex[0] = n / 16;pszHex[1] = n % 16;if( pszHex[0] > 9 )pszHex[0] += 'a' - 10;elsepszHex[0] += '0';if( pszHex[1] > 9 )pszHex[1] += 'a' - 10;elsepszHex[1] += '0';}void* OperatorADO::GetValue(void){//int* a=new int[4];int a=10;//a[0]=1;a[1]=2;return &a;}void* OperatorADO::GetSubValue(void){return GetValue();}// 得到数据库中数据表的信息BOOL OperatorADO::GetTableInfo(CSession* pSession, CAtlArray<CSchemaTableInfo>* pArray){CTables rs;HRESULT hr = rs.Open(*pSession, NULL, NULL, NULL, "TABLE" );if( FAILED(hr) ){return FALSE;}hr = rs.MoveFirst();while( SUCCEEDED( hr ) && hr != DB_S_ENDOFROWSET ){CSchemaTableInfo info;info.csSchema = rs.m_szSchema;info.csName = rs.m_szName;pArray->Add( info );hr = rs.MoveNext();}rs.Close();return TRUE;}

3.在CMainFrame中使用OperatorADO类实现数据库连接，点击ID_FILE_OPEN选择Access数据库

在CMainFrame头文件中包含OperatorADO.h并声明变量

CDataSource m_ds;

CSessoin m_session;

void CMainFrame::OnFileOpen(){// TODO: 在此添加命令处理程序代码CFileDialog dlg(TRUE , "mdb",NULL,OFN_EXPLORER|OFN_FILEMUSTEXIST|OFN_PATHMUSTEXIST|OFN_READONLY,"Database Files(*.mdb)|*.mdb||",this);if(dlg.DoModal()==IDOK){m_ds.Close();m_session.Close();OperatorADO op;HRESULT hResult;CString szConnectString="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=";szConnectString+=dlg.GetPathName();m_szDBName=dlg.GetPathName();GetActiveDocument()->SetTitle(m_szDBName);szConnectString+=";Mode=Share Deny None;";if(!op.OpenDataSource(szConnectString,&m_ds,&m_session)){m_wndStatusBar.SetPaneText(1,"打开数据源失败");return;}CCommand<CDynamicStringAccessor> rs;if(!op.GetRecordsFromSQL(&m_session,&rs,"select * from main order by [no]"))//"select [no],speed from main order by [no]")){m_wndStatusBar.SetPaneText(1,"打开记录集失败");return;}m_wndStatusBar.SetPaneText(1,"打开记录集成功");((CBrowseView*)GetActiveView())->FillListView(rs);rs.Close();//m_tableArray.RemoveAll();}}

4.FillListView函数实现

int CBrowseView::FillListView(CCommand<CDynamicStringAccessor>& rs){CListCtrl& list=GetListCtrl();list.DeleteAllItems();while(list.DeleteColumn(0));LVCOLUMN col;col.mask = LVCF_FMT | LVCF_TEXT|LVCF_WIDTH;//col.pszText = _T("No.");col.fmt = LVCFMT_CENTER;col.cx=100;//rs中的记录索引都是从1开始的DBSTATUS dStatus;int i=0;for(i=1;i<=rs.GetColumnCount();i++){rs.GetStatus(i, &dStatus );if(FAILED(dStatus))break;else if(dStatus==DBSTATUS_S_ISNULL)break;{CString szData=COLE2CT(rs.GetColumnName(i));if(szData=="")break;else if(szData=="no")szData="No.";else if(szData=="date")szData="Test Date";else if(szData=="company")szData="Company";else if(szData=="man")szData="Operator";else if(szData=="groupnum")szData="BitNo.";else if(szData=="matirial")szData="Material";else if(szData=="humidity")szData="Twist";else if(szData=="temp")szData="T/RH";col.pszText=szData.GetBuffer(0);list.InsertColumn(i-1,&col);szData.ReleaseBuffer();}}int nColumns=i;HRESULT hr=rs.MoveFirst();int nRowIndex=0;while(SUCCEEDED(hr)&&hr!=DB_S_ENDOFROWSET){CHAR *p=rs.GetString((DBORDINAL)1);list.InsertItem(nRowIndex,p);list.SetItemData(nRowIndex,atoi(p));for(int j=2;j<=nColumns;j++){list.SetItemText(nRowIndex,j-1,rs.GetString(j));}nRowIndex++;hr=rs.MoveNext();}return 0;}