MFC ADO数据库操作

MFC ADO数据库操作 - 延陵小明 - CSDN博客 http://blog.csdn.net/guoming0000/article/details/7280070/

内容比较乱，作为草稿，对现有的ado数据库操作函数方法进行汇总。

小函数
m_pRecordset->RecordCount//取得记录数量

全局变量
#import "msado15.dll" no_namespace rename("EOF","adoEOF")rename("BOF","adoBOF")
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;

1.连接数据源OnInitADOConn();
//初始化OLE/COM库环境
::CoInitialize(NULL);
try{
//创建Connection对象
m_pCOnnection.CreateInstance("ADODB.Connection");
//设置连接字符串
_bstr_t strConnect="uid=;pwd=;DRIVER={Microsoft Access Driver(*mdb)};DBQ=database.mdb;";
m_pConnection->Open(strConnect,"","",adModeUnknown);
}
catch(_com_error e){
AfxMessageBox(e.Description);
}

2.关闭数据库连接
if(m_pRecordset!=NULL)
m_pRecordset->Close();
m_pConnection->Close();
::CoUninitialize();//释放环境

3.获取记录集数据
传入一个_bstr_t bstrSQL
(1)Open方法
try{
if(m_pConnection==NULL)//如果为空，重新连接
OnInitADOConn();
m_pRecordset.CreateInstance(__uuidof(Recordset));
m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
}//adCmdText：bstrSQL是文本.adCmdTable:bstrSQL是表名
(2)Execute方法
_RecordsetPtr Execute(_bstr_t CommandTex,VARIANT* RecordsAffeced,long Options)
CommandText是SQL命令,RecordsAffeced是操作后影响的行数,Options是CommandText中内容的类型
Options:adCmdText文本,adCmdTable表名,adCmdStoredProc存储过程,adCmdUnknown类型未知
>>_variant_t RecordsAffected;
try{
if(m_pConnection==NULL)//是否连接数据库
OnInitADOConn();//又重新连接
m_pConnection->Execute(bstrSQL,NULL,adCmdText);
}
catch(_com_error e)
{
e.Description();
return false;
}

4.遍历记录集
MoveNext,MoveFirst,MoveLast,MovePrevious;
(char *)(_bstr_t)m_pRecordset->GetCollect("姓名");//方法一
m_sName=(CStringW)(m_pAdoRecordset->Fields->Item[_variant_t("NAME")]->Value);//方法二
while(m_pRecordset->adoEOF==0)
{m_pRecordset->MoveNext();}

5.添加数据
try{
m_pRecordset->AddNew();//开始添加
m_pRecordset->PutCollect("列名",(_bstr_t)m_id);
m_pRecordset-->Update();//更新字段
}

6.修改数据
try{
m_pRecordset->Move((long)pos,vtMissing);
m_pRecordset->PutCollect("姓名",(_bstr_t)m_name);
m_pRecordset-->Update();
}

7.删除数据
m_pRecordset->Move((long)pos,vtMissing);
m_pAdoRecordset->Delete(adAffectCurrent);
m_pRecordset-->Update();
    try
    {
       //假设删除第10条记录
       m_pRecordset->MoveFirst();//注意一定要移到第一个
       m_pRecordset->Move(9);
       m_pRecordset->Delete(adAffectCurrent);
       //参数adAffectCurrent为删除当前记录
       m_pRecordset->Update();
    }
    catch(_com_error *e)
    {
       AfxMessageBox(e->ErrorMessage());
    }

8.保存图片（参考下一篇博客）
char *m_pBuffer;//文件数据
DWORD m_filelen;//文件长度
VARIANT varblob;
SAFEARRAY *psa;
SAFEARRAYBOUND rgsabound[1];
rgsabound[0].lLbound=0;
rgsabound[0].cElements=m_filelen;
psa=SafeArrayCreate(VT_UI1,1,rgsabound);
for(long i=0;i<(long)m_filelen;i++)
{
SafeArrayPutElement(psa,&i,m_pBuffer++);
}
//记录值
varblob.vt=VT_ARRAY|VT_UI1;
varblob.parray=psa;
m_pRecordset->GetFields()->GetItem("PHOTO_DATA")->AppendChunk(varblob);
m_pRecordset->Update();
读取数据库语音数据
long lDataSize=m_pRecordset->GetFields()->GetItem("voice")->ActualSize;//取得数据区域大小
char *m_pBuffer;
if(lDataSize>0)
{
//读取数据到varBLOB中
_variant_t varBLOB;
varBLOB=m_pRecordse->GetFields()->GetItem("voice")->GetChunk(lDataSize);
if(varBLOB.vt==(VT_ARRAY|VT_UI1))
{
if(m_pBuffer=new char[lDataSize+1])
{
char *pBuf=0;
SafeArrayAccessData(varBLOB.parray,(void**)&pBuf);
memcpy(m_pBuffer,pBuf,lDataSize);//赋值数据到m_pBuffer
SafeArrayUnaccessData(varBLOB.parray);
}
}
}

其他连接方法
打开
m_pAdoRecordset=NULL;
m_pAdoConnect=NULL;//初始化
iCurrentOne=-1;
//*******************************************
if(FAILED(::CoInitialize(NULL)))//这句话很重要！
{
  ::AfxMessageBox(_T("fail to CInitialize(NULL)"));
  PostQuitMessage(-8);//?
}
HRESULT hr=m_pAdoConnect.CreateInstance(__uuidof(Connection));
if(FAILED(hr)){
  ::AfxMessageBox(_T("fail to create instance for _ConnectPtr"));
  PostQuitMessage(-8);
}
bstr_t strConnect="DSN=FRDB;
       DBQ=H:\FRDB.accdb;
       DriverID=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;";
try{
  m_pAdoConnect->Open(strConnect,"clc","",NULL);
}
catch(_com_error &e)
{
  ::AfxMessageBox(e.Description());
  PostQuitMessage(-8);
}
m_pAdoRecordset=NULL;
hr=m_pAdoRecordset.CreateInstance(__uuidof(Recordset));
if(FAILED(hr)){
  ::AfxMessageBox(_T("fail to create instance for _RecordsetPtr"));
  PostQuitMessage(-8);
}
m_pAdoRecordset->Open(_variant_t("Person"),_variant_t((IDispatch*)

m_pAdoConnect,true),adOpenKeyset,adLockOptimistic,adCmdTable);
关闭
if(m_pAdoRecordset){ if(m_pAdoRecordset->State==adStateOpen){
   m_pAdoRecordset->Close(); }}
if(m_pAdoConnect)
{ if(m_pAdoConnect->State==adStateOpen){
   m_pAdoConnect->Close();}}
  ::CoUninitialize();//关闭线程下的数据库
添加数据
m_pAdoRecordset->Fields->GetItem(_variant_t("NAME"))->Value=_variant_t(m_sName);
载入数据
m_sName=(CStringW)(m_pAdoRecordset->Fields->Item[_variant_t("NAME")]->Value);