ado方式操作数据库类

      VC/MFC数据库新任版主zyq5945（竹叶青）在数据库版开设帖子整理的一些ADO数据库入门与例子，为了表示支持我在其中回复了一个自己平时用的ado操作数据库的类。怎奈论坛对回帖字数有限制，结果只贴了头文件......不过竹叶青版主邀请我把这个类整理到博客里以便分享，我觉得这个办法不错。不过既然要整理到博客里就应该更正式一些，所以花些时间规范了一下这个类，并且制作了一个简单的使用范例，也算是对竹版的支持，对数据库版的一点小小的贡献吧。

      好久没写博客了，其实应该多说两句介绍一下这个类和其简单的使用。不过自己在数据库方面确实知之甚少，这个类其实也是抄来的，只不过在自己的使用过程中“本土化”了一下，呵呵。所以就不班门弄斧了，直接上代码吧，希望能对有志于涉足于数据库应用技术的同僚们有所帮助，按竹版的话说算是“入门实例”吧。

使用范例

本例中操作的数据库为ACCESS2003，操作方式为本地连接，数据库带密码

 

类实现代码

头文件

#pragma once#include "math.h"#import "MSADO15.DLL"rename_namespace("ADOWE") rename("EOF","EndOfFile")using namespace ADOWE;class CDBAdo{public:_CommandPtrm_ptrCommand;//命令对象_RecordsetPtrm_ptrRecordset;//记录集对象_ConnectionPtrm_ptrConnection;//数据库对象CStringm_strConnect,//连接字符串m_strErrorMsg;//错误信息public:CDBAdo(void);~CDBAdo(void);voidDetectResult(HRESULT hResult);voidRecordErrorMsg(_com_error comError);CStringGetLastError(){return m_strErrorMsg;}boolCreateInstance();boolSetConnectionString(CString strDriver, CString strIP, WORD wPort, CString strCatalog, CString strUserID, CString strPassword);boolSetConnectionString(CString strDriver, CString strDataSrc, CString strPassword);boolOpenConnection();boolCloseConnection();boolIsConnecting();voidClearAllParameters();voidAddParamter(LPCTSTR lpcsrName, ADOWE::ParameterDirectionEnum Direction, ADOWE::DataTypeEnum Type, long lSize, _variant_t & vtValue);voidSetSPName(LPCTSTR lpcsrSPName);boolExecuteCommand(bool bIsRecordset);boolExecute(LPCTSTR lpcsrCommand);longGetReturnValue();boolOpenRecordset(char* szSQL);boolCloseRecordset();boolIsRecordsetOpened();boolIsEndRecordset();voidMoveToNext();voidMoveToFirst();voidMoveToLast();longGetRecordCount();boolGetFieldValue(LPCTSTR lpcsrFieldName, WORD& wValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, CString& strValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, INT& nValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, BYTE& bValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, LONG& lValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, DWORD& dwValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, UINT& ulValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, DOUBLE& dbValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, __int64& llValue);boolGetFieldValue(LPCTSTR lpcsrFieldName, COleDateTime& Time);boolGetFieldValue(LPCTSTR lpcsrFieldName, bool& bValue);};

实现文件

#include "StdAfx.h"#include "DBAdo.h"CDBAdo::CDBAdo(void){m_strConnect= _T("");m_strErrorMsg= _T("");//初始化 COMCoInitialize(NULL);}CDBAdo::~CDBAdo(void){//关闭连接CloseConnection();//释放对象if(m_ptrCommand!=NULL)m_ptrCommand.Release();if(m_ptrRecordset!=NULL)m_ptrRecordset.Release();if(m_ptrConnection!=NULL)m_ptrConnection.Release();}bool CDBAdo::CreateInstance(){//创建对象m_ptrCommand.CreateInstance(__uuidof(Command));m_ptrRecordset.CreateInstance(__uuidof(Recordset));m_ptrConnection.CreateInstance(__uuidof(Connection));if(m_ptrCommand==NULL){m_strErrorMsg= _T("数据库命令对象创建失败");returnfalse;}if(m_ptrRecordset==NULL){m_strErrorMsg= _T("数据库记录集对象创建失败");returnfalse;}if(m_ptrConnection==NULL){m_strErrorMsg= _T("数据库连接对象创建失败");returnfalse;}//设置变量m_ptrCommand->CommandType= adCmdStoredProc;returntrue;}void CDBAdo::DetectResult(HRESULT hResult){if(FAILED(hResult))_com_issue_error(hResult);}void CDBAdo::RecordErrorMsg(_com_error comError){_bstr_tbstrDescribe(comError.Description());m_strErrorMsg.Format(TEXT("ADO 错误：0x%8x，%s"), comError.Error(), (LPCTSTR)bstrDescribe);}bool CDBAdo::SetConnectionString(CString strDriver, CString strIP, WORD wPort, CString strCatalog, CString strUserID, CString strPassword){CStringstrProvider, strPWD, strUID, strData, strDataSrc;strProvider.Format(_T("Provider=%s;"), strProvider);strPWD.Format(_T("Password=%s;"), strPassword);strUID.Format(_T("User ID=%s;"), strUserID);strData.Format(_T("Initial Catalog=%s;"), strCatalog);strDataSrc.Format(_T("Data Source=%s,%ld;"), strIP, wPort);//构造连接字符串m_strConnect= strProvider+strPWD+_T("Persist Security Info=True;")+strUID+strData+strDataSrc;return true;}bool CDBAdo::SetConnectionString(CString strDriver, CString strDataSrc, CString strPassword){CStringstrProvider, strDataSource, strPWD;strProvider.Format(_T("Provider=%s;"), strDriver);strDataSource.Format(_T("Data Source=%s;"), strDataSrc);strPWD.Format(_T("Jet OLEDB:DataBase Password=%s;"), strPassword);//构造连接字符串m_strConnect= strProvider+_T("User ID=Admin;")+strDataSource+strPWD;return true;}bool CDBAdo::OpenConnection(){try{//关闭连接CloseConnection();//连接数据库DetectResult(m_ptrConnection->Open(_bstr_t(m_strConnect), "", "", adModeUnknown));m_ptrConnection->CursorLocation= adUseClient;m_ptrCommand->ActiveConnection= m_ptrConnection;return true;}catch(_com_error& comError) {RecordErrorMsg(comError);}return false;}bool CDBAdo::CloseConnection(){try{CloseRecordset();if((m_ptrConnection!=NULL)&&(m_ptrConnection->GetState()!=adStateClosed))DetectResult(m_ptrConnection->Close());return true;}catch(_com_error& comError){RecordErrorMsg(comError);}return false;}bool CDBAdo::IsConnecting(){try {//状态判断if(m_ptrConnection==NULL)returnfalse;if(m_ptrConnection->GetState()==adStateClosed)returnfalse;//参数判断longlErrorCount= m_ptrConnection->Errors->Count;if(lErrorCount>0L){ ErrorPtrpError = NULL;for(long i=0; i<lErrorCount; i++){pError= m_ptrConnection->Errors->GetItem(i);if(pError->Number==0x80004005)returnfalse;}}return true;}catch(_com_error& comError){RecordErrorMsg(comError);}return false;}bool CDBAdo::OpenRecordset(char* szSQL){try{//关闭记录集CloseRecordset();m_ptrRecordset->Open(szSQL, m_ptrConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);return true;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::CloseRecordset(){try{if(IsRecordsetOpened())DetectResult(m_ptrRecordset->Close());return true;}catch(_com_error& comError){RecordErrorMsg(comError);}return false;}bool CDBAdo::IsRecordsetOpened(){if(m_ptrRecordset==NULL)returnfalse;if(m_ptrRecordset->GetState()==adStateClosed)returnfalse;return true;}bool CDBAdo::IsEndRecordset(){try {return (m_ptrRecordset->EndOfFile==VARIANT_TRUE);}catch(_com_error& comError){RecordErrorMsg(comError);}return true;}void CDBAdo::MoveToNext(){try { m_ptrRecordset->MoveNext(); }catch(_com_error& comError){RecordErrorMsg(comError);}}void CDBAdo::MoveToFirst(){try { m_ptrRecordset->MoveFirst(); }catch(_com_error& comError){RecordErrorMsg(comError);}}void CDBAdo::MoveToLast(){try { m_ptrRecordset->MoveLast(); }catch(_com_error& comError){RecordErrorMsg(comError);}}long CDBAdo::GetRecordCount(){try{if(m_ptrRecordset==NULL)return0;returnm_ptrRecordset->GetRecordCount();}catch(_com_error& comError){RecordErrorMsg(comError);}return 0;}void CDBAdo::ClearAllParameters(){try {longlParamCount= m_ptrCommand->Parameters->Count;if(lParamCount>0L){for(long i=lParamCount; i>0; i--){_variant_tvtIndex;vtIndex.intVal= i-1;m_ptrCommand->Parameters->Delete(vtIndex);}}}catch(_com_error& comError){RecordErrorMsg(comError);}}void CDBAdo::AddParamter(LPCTSTR lpcsrName, ADOWE::ParameterDirectionEnum Direction, ADOWE::DataTypeEnum Type, long lSize, _variant_t & vtValue){ASSERT(lpcsrName!=NULL);try { _ParameterPtrParameter= m_ptrCommand->CreateParameter(lpcsrName, Type, Direction, lSize, vtValue);m_ptrCommand->Parameters->Append(Parameter);}catch(_com_error& comError){RecordErrorMsg(comError);}}void CDBAdo::SetSPName(LPCTSTR lpcsrSPName){try { m_ptrCommand->CommandText= lpcsrSPName; }catch(_com_error& comError){RecordErrorMsg(comError);}}bool CDBAdo::ExecuteCommand(bool bIsRecordset){try {//关闭记录集CloseRecordset();//执行命令if(bIsRecordset){m_ptrRecordset->PutRefSource(m_ptrCommand);m_ptrRecordset->CursorLocation= adUseClient;DetectResult(m_ptrRecordset->Open((IDispatch*)m_ptrCommand, vtMissing, adOpenForwardOnly, adLockReadOnly, adOptionUnspecified));}else {m_ptrConnection->CursorLocation= adUseClient;DetectResult(m_ptrCommand->Execute(NULL, NULL, adExecuteNoRecords));}returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::Execute(LPCTSTR lpcsrCommand){try{m_ptrConnection->CursorLocation= adUseClient;m_ptrConnection->Execute(lpcsrCommand, NULL, adExecuteNoRecords);returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}long CDBAdo::GetReturnValue(){try { _ParameterPtrptrParam;longlParameterCount= m_ptrCommand->Parameters->Count;for(long i=0; i<lParameterCount; i++){ptrParam= m_ptrCommand->Parameters->Item[i];if(ptrParam->Direction==adParamReturnValue)returnptrParam->Value.lVal;}}catch(_com_error& comError){RecordErrorMsg(comError);}return0;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, WORD& wValue){wValue= 0L;try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))wValue= (WORD)vtFld.ulVal;return true;}catch(_com_error& comError){RecordErrorMsg(comError);}return false;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, CString& strValue){try{_variant_t vtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;if(vtFld.vt==VT_BSTR){strValue= (char*)_bstr_t(vtFld);strValue.TrimLeft();returntrue;}returnfalse;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, INT& nValue){nValue= 0;try{_variant_t vtFld = m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;switch(vtFld.vt){case VT_BOOL:{nValue= vtFld.boolVal;break;}case VT_I2:case VT_UI1:{nValue= vtFld.iVal;break;}case VT_NULL:case VT_EMPTY:{nValue= 0;break;}default: nValue= vtFld.iVal;}return true;}catch(_com_error& comError){RecordErrorMsg(comError);}return false;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, BYTE& bValue){bValue= 0;try{_variant_t vtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;switch(vtFld.vt){case VT_BOOL:{bValue= (vtFld.boolVal!=0)?1:0;break;}case VT_I2:case VT_UI1:{bValue= (vtFld.iVal>0)?1:0;break;}case VT_NULL:case VT_EMPTY:{bValue= 0;break;}default: bValue= (BYTE)vtFld.iVal;}return true;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, UINT& ulValue){ulValue= 0L;try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))ulValue= vtFld.lVal;returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, DOUBLE& dbValue){dbValue=0.0L;try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;switch(vtFld.vt){case VT_R4:dbValue= vtFld.fltVal;break;case VT_R8:dbValue= vtFld.dblVal;break;case VT_DECIMAL:{dbValue = vtFld.decVal.Lo32;dbValue*= (vtFld.decVal.sign==128)?-1:1;dbValue/= pow((float)10,vtFld.decVal.scale);}break;case VT_UI1:dbValue= vtFld.iVal;break;case VT_I2:case VT_I4:dbValue= vtFld.lVal;break;case VT_NULL:case VT_EMPTY:dbValue= 0.0L;break;default:dbValue= vtFld.dblVal;}return true;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, LONG& lValue){lValue= 0L;try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))lValue= vtFld.lVal;returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, DWORD& dwValue){dwValue= 0L;try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))dwValue= vtFld.ulVal;returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, __int64& llValue){llValue= 0L;try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))llValue=vtFld.lVal;returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, COleDateTime& Time){try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;switch(vtFld.vt) {case VT_DATE:{COleDateTimeTempTime(vtFld);Time= TempTime;}break;case VT_EMPTY:case VT_NULL:Time.SetStatus(COleDateTime::null);break;default: return false;}returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, bool& bValue){try{_variant_tvtFld= m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;switch(vtFld.vt) {case VT_BOOL:bValue=(vtFld.boolVal==0)?false:true;break;case VT_EMPTY:case VT_NULL:bValue = false;break;default:return false;}returntrue;}catch(_com_error& comError){RecordErrorMsg(comError);}returnfalse;}