vs使用ado连接oracle,在VS环境下以ADO方式操作Oracle数据库

利用ADO引擎方式访问Oracle数据库的实现方法：

定义数据库头文件为CDBOperation.h

#pragma once

#import "C:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF","adoEOF"),rename("LockTypeEnum","AdoLockTypeEnum"),rename("DataTypeEnum","AdoDataTypeEnum"),\

rename("FieldAttributeEnum","AdoFieldAttributeEnum"),rename("EditModeEnum","AdoEditModeEnum"),rename("RecordStatusEnum","AdoRecordStatusEnum"),rename("ParameterDirectionEnum","AdoParameterDirectionEnum")

class CDBOperation

{

public:

//构造对象方法

CDBOperation(void);

~CDBOperation(void);

//连接数据库

bool ConnToDB();

//数据库操作函数(增、删、改、查)

_RecordsetPtr ExecuteWithResSQL(CString);

private:

void PrintErrorInfo(_com_error &);

private:

//初始化数据库连接、命令、记录集

_ConnectionPtr CreateConnPtr();

_CommandPtr CreateCommPtr();

_RecordsetPtr CreateRecsetPtr();

private:

//数据库连接需要的连接、命令、记录集对象

_ConnectionPtr m_pConnection;

_CommandPtr m_pCommand;

_RecordsetPtr m_pRecordset;

};

定义程序源文件CDBOperation.cpp

#include "CDBOperation.h"

CDBOperation::CDBOperation(void)

{

CoInitialize(NULL);

m_pConnection = CreateConnPtr();

m_pCommand = CreateCommPtr();

m_pRecordset = CreateRecsetPtr();

}

CDBOperation::~CDBOperation(void)

{

m_pConnection->Close();

}

bool CDBOperation::ConnToDB()

{

if (NULL == m_pConnection)

{

printf("Failed to create connection\n");

return false;

}

try

{

_bstr_t strConnect="Provider=OraOLEDB.Oracle;User ID=scott;Password=19900624;Persist Security Info=True;Data Source=\"(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.108.85.137)(PORT = 1521)) )(CONNECT_DATA = (SID = ORCL)(SERVER=DEDICATED)))\"";

HRESULT hr=m_pConnection->Open(strConnect,"","",NULL);

if (TRUE == FAILED(hr))

{

return false;

}

m_pCommand->ActiveConnection = m_pConnection;

return true;

}

catch(_com_error &e)

{

PrintErrorInfo(e);

return false;

}

}

_RecordsetPtr CDBOperation::ExecuteWithResSQL(const char *sql)

{

try

{

m_pCommand->CommandText = _bstr_t(sql);

m_pRecordset = m_pCommand->Execute(NULL, NULL, adCmdText);

return m_pRecordset;

}

catch(_com_error &e)

{

PrintErrorInfo(e);

return NULL;

}

}

void CDBOperation::PrintErrorInfo(_com_error &e)

{

printf("Error infomation are as follows\n");

printf("ErrorNo: %d\nError Message:%s\nError Source:%s\nError Description:%s\n", e.Error(), e.ErrorMessage(), (LPCTSTR)e.Source(), (LPCTSTR)e.Description());

}

_ConnectionPtr CDBOperation::CreateConnPtr()

{

HRESULT hr;

_ConnectionPtr connPtr;

hr = connPtr.CreateInstance(__uuidof(Connection));

if (FAILED(hr) == TRUE)

{

return NULL;

}

return connPtr;

}

_CommandPtr CDBOperation::CreateCommPtr()

{

HRESULT hr;

_CommandPtr commPtr;

hr = commPtr.CreateInstance(__uuidof(Command));

if (FAILED(hr) == TRUE)

{

return NULL;

}

return commPtr;

}

_RecordsetPtr CDBOperation::CreateRecsetPtr()

{

HRESULT hr;

_RecordsetPtr recsetPtr;

hr = recsetPtr.CreateInstance(__uuidof(Recordset));

if (FAILED(hr) ==TRUE)

{

return NULL;

}

return recsetPtr;

}

Oracle数据库的实现main()：

#include

#include "CDBOperation.h"

#include

#include

#include

using namespace std;

void main()

{

CDBOperation dbObject;

CString sql;

bool bConn = dbObject.ConnToDB();

if (true == bConn)

{

//AfxMessageBox("connection success \0");

// ::MessageBox(NULL, "abc!",_T("attention"), MB_OKCANCEL == IDOK);

// MessageBox(NULL,"连接数据库出现错误\0",0,0);

cout<

}

_RecordsetPtr pRst=NULL; //创建一个数据集智能指针

pRst.CreateInstance(__uuidof(Recordset));//初始化Recordset指针

char type;

while(true)

{

cout<

cin>>type;

switch(type)

{

case 's':{

//查询

sql="select * from RECV_TABLE";

pRst = dbObject.ExecuteWithResSQL(sql);

if (NULL == pRst)

{

//MessageBox(NULL,_T("查询数据出现错误！\0"),0,0);

printf("查询数据出现错误！\n");

break;

}

if (pRst->adoEOF)

{

pRst->Close();

//AfxMessageBox(_T("There is no records in this table\0"));

//MessageBox(NULL,"There is no records in this table\0",0,0);

printf("There is no records in this table...\n");

break;

}

_variant_t vSno, vName;

if(!pRst->BOF)

{

pRst->MoveFirst();//记录集指针移动到查询结果集的前面

}

while (!pRst->adoEOF)

{

vSno = pRst->GetCollect(_variant_t("SID"));

vName = pRst->GetCollect(_variant_t("CONTENT"));

pRst->MoveNext();

if(vSno.vt != VT_NULL)

{

cout<

}

}

}

break;

case 'a':{

CTime tm = CTime::GetCurrentTime();

CString str=tm.Format("%Y-%m-%d %H-%M-%S");

char arr[]="任何问题都可以帮你解决";

char id[] = "S10011";

CString SID= id;

CString content = arr;

sql="insert into RECV_TABLE (SID,CONTENT,TIME) values ('"+SID+"','"+content+"',to_date('"+str+"','yyyy-mm-dd hh24:mi:ss'))";

pRst = dbObject.ExecuteWithResSQL(sql);

if (NULL != pRst)

{

//AfxMessageBox(_T("插入数据成功\n"));

printf("插入数据成功！\n");

break;

}

}

break;

case 'd':{

//执行删除语句

//sprintf(sql, "delete from RECV_TABLE where SID='%s', "S1003");

sql="delete from RECV_TABLE where SID='S1004'";

pRst = dbObject.ExecuteWithResSQL(sql);

if (NULL != pRst)

{

MessageBox(NULL,_T("删除数据成功\0"),0,0);

break;

}

}

break;

case 'u':{

sql="update RECV_TABLE set CONTENT='cupt5634@126.com' where SID='S1003'";

pRst = dbObject.ExecuteWithResSQL(sql);

if (NULL != pRst)

{

//MessageBox(NULL,_T("更新数据成功\0"),0,0);

printf("更新数据成功！\n");

break;

}

}

break;

}

}

system("PAUSE");

}