`
缥缈孤鸿
  • 浏览: 40191 次
  • 性别: Icon_minigender_1
  • 来自: 大连
最近访客 更多访客>>
社区版块
存档分类
最新评论

MFC中ADO方式操作数据库实例(转)

    博客分类:
  • C++
阅读更多
连接ACCESS为例:
//头文件
#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);  
};  
#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);
}; 

//实现文件

[cpp] view plaincopyprint?#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;  
}  

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics