转自http://blog.csdn.net/xianglitian/article/details/5617173
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:
- _CommandPtr m_ptrCommand; //命令对象
- _RecordsetPtr m_ptrRecordset; //记录集对象
- _ConnectionPtr m_ptrConnection; //数据库对象
- CString m_strConnect, //连接字符串
- m_strErrorMsg; //错误信息
- public:
- CDBAdo(void);
- ~CDBAdo(void);
- void DetectResult(HRESULT hResult);
- void RecordErrorMsg(_com_error comError);
- CString GetLastError(){return m_strErrorMsg;}
- bool CreateInstance();
- bool SetConnectionString(CString strDriver, CString strIP, WORD wPort, CString strCatalog, CString strUserID, CString strPassword);
- bool SetConnectionString(CString strDriver, CString strDataSrc, CString strPassword);
- bool OpenConnection();
- bool CloseConnection();
- bool IsConnecting();
- void ClearAllParameters();
- void AddParamter(LPCTSTR lpcsrName, ADOWE::ParameterDirectionEnum Direction, ADOWE::DataTypeEnum Type, long lSize, _variant_t & vtValue);
- void SetSPName(LPCTSTR lpcsrSPName);
- bool ExecuteCommand(bool bIsRecordset);
- bool Execute(LPCTSTR lpcsrCommand);
- long GetReturnValue();
- bool OpenRecordset(char* szSQL);
- bool CloseRecordset();
- bool IsRecordsetOpened();
- bool IsEndRecordset();
- void MoveToNext();
- void MoveToFirst();
- void MoveToLast();
- long GetRecordCount();
- bool GetFieldValue(LPCTSTR lpcsrFieldName, WORD& wValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, CString& strValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, INT& nValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, BYTE& bValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, LONG& lValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, DWORD& dwValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, UINT& ulValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, DOUBLE& dbValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, __int64& llValue);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, COleDateTime& Time);
- bool GetFieldValue(LPCTSTR lpcsrFieldName, bool& bValue);
- };
实现文件
- #include "StdAfx.h"
- #include "DBAdo.h"
- CDBAdo::CDBAdo(void)
- {
- m_strConnect = _T("");
- m_strErrorMsg = _T("");
- //初始化 COM
- CoInitialize(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("数据库命令对象创建失败");
- return false;
- }
- if(m_ptrRecordset==NULL)
- {
- m_strErrorMsg = _T("数据库记录集对象创建失败");
- return false;
- }
- if(m_ptrConnection==NULL)
- {
- m_strErrorMsg = _T("数据库连接对象创建失败");
- return false;
- }
- //设置变量
- m_ptrCommand->CommandType = adCmdStoredProc;
- return true;
- }
- void CDBAdo::DetectResult(HRESULT hResult)
- {
- if(FAILED(hResult))
- _com_issue_error(hResult);
- }
- void CDBAdo::RecordErrorMsg(_com_error comError)
- {
- _bstr_t bstrDescribe(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)
- {
- CString strProvider, 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)
- {
- CString strProvider, 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)
- return false;
- if(m_ptrConnection->GetState()==adStateClosed)
- return false;
- //参数判断
- long lErrorCount = m_ptrConnection->Errors->Count;
- if(lErrorCount>0L)
- {
- ErrorPtr pError = NULL;
- for(long i=0; i<lErrorCount; i++)
- {
- pError = m_ptrConnection->Errors->GetItem(i);
- if(pError->Number==0x80004005)
- return false;
- }
- }
- 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);
- }
- return false;
- }
- 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)
- return false;
- if(m_ptrRecordset->GetState()==adStateClosed)
- return false;
- 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)
- return 0;
- return m_ptrRecordset->GetRecordCount();
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return 0;
- }
- void CDBAdo::ClearAllParameters()
- {
- try
- {
- long lParamCount = m_ptrCommand->Parameters->Count;
- if(lParamCount>0L)
- {
- for(long i=lParamCount; i>0; i--)
- {
- _variant_t vtIndex;
- 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
- {
- _ParameterPtr Parameter = 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));
- }
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- bool CDBAdo::Execute(LPCTSTR lpcsrCommand)
- {
- try
- {
- m_ptrConnection->CursorLocation = adUseClient;
- m_ptrConnection->Execute(lpcsrCommand, NULL, adExecuteNoRecords);
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- long CDBAdo::GetReturnValue()
- {
- try
- {
- _ParameterPtr ptrParam;
- long lParameterCount = m_ptrCommand->Parameters->Count;
- for(long i=0; i<lParameterCount; i++)
- {
- ptrParam = m_ptrCommand->Parameters->Item[i];
- if(ptrParam->Direction==adParamReturnValue)
- return ptrParam->Value.lVal;
- }
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return 0;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, WORD& wValue)
- {
- wValue = 0L;
- try
- {
- _variant_t vtFld = 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();
- return true;
- }
- return false;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- 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);
- }
- return false;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, UINT& ulValue)
- {
- ulValue = 0L;
- try
- {
- _variant_t vtFld = m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;
- if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))
- ulValue = vtFld.lVal;
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, DOUBLE& dbValue)
- {
- dbValue=0.0L;
- try
- {
- _variant_t vtFld = 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);
- }
- return false;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, LONG& lValue)
- {
- lValue = 0L;
- try
- {
- _variant_t vtFld = m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;
- if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))
- lValue = vtFld.lVal;
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, DWORD& dwValue)
- {
- dwValue = 0L;
- try
- {
- _variant_t vtFld = m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;
- if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))
- dwValue = vtFld.ulVal;
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, __int64& llValue)
- {
- llValue = 0L;
- try
- {
- _variant_t vtFld = m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;
- if((vtFld.vt!=VT_NULL)&&(vtFld.vt!=VT_EMPTY))
- llValue=vtFld.lVal;
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, COleDateTime& Time)
- {
- try
- {
- _variant_t vtFld = m_ptrRecordset->Fields->GetItem(lpcsrFieldName)->Value;
- switch(vtFld.vt)
- {
- case VT_DATE:
- {
- COleDateTime TempTime(vtFld);
- Time = TempTime;
- }break;
- case VT_EMPTY:
- case VT_NULL:Time.SetStatus(COleDateTime::null);break;
- default: return false;
- }
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }
- bool CDBAdo::GetFieldValue(LPCTSTR lpcsrFieldName, bool& bValue)
- {
- try
- {
- _variant_t vtFld = 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;
- }
- return true;
- }
- catch(_com_error& comError)
- {
- RecordErrorMsg(comError);
- }
- return false;
- }