Vc调用ado访问数据库

Vc主要提供了两个类型来实现这一功能：

_ConnectionPtr m_Connection; //实现数据库的连接

_RecordsetPtr m_pRs;          //访问记录集

访问数据库，主要包含以下几项功能：

1、  打开数据库连接

_bstr_t strCnn = strCon;

try

{

    m_Connection.CreateInstance(__uuidof(Connection));

    m_Connection->Open(strCnn,"","",NULL);

}catch(_com_error)

{

        return;

}

_variant_t vDispatch((IDispatch*)m_Connection);

m_Connection->AddRef();

 

2、  向记录添加记录

CString strCmdText = "Select * from tblMapHeader";

try{

        m_pRs1.CreateInstance(__uuidof(Recordset));

        m_pRs1->CursorLocation = adUseClient;

        m_pRs1->Open((LPCTSTR)strCmdText, vDispatch, adOpenKeyset,

               adLockOptimistic, adCmdUnknown);

}catch(_com_error &e)

{

  

}

long rgIndices[1];

VARIANT rgf;

VARIANT rgv;

COleVariant fld[6], val[6];

SAFEARRAYBOUND bound;

int num = 6;

try

{    

        fld[0]="nFlag";      

        val[0].vt = VT_I8;

        val[0].lVal =nFlag;

 

        fld[1]="nScale";

        val[1].vt = VT_I8;

        val[1].lVal = nScale;

 

        fld[2]="nTime";     

        val[2].vt = VT_I8;

        val[2].lVal = nTime;

 

        fld[3]="nProj";

        val[3].vt = VT_I1;

        val[3].intVal = nProj;

 

        fld[4]="nReserved";

        val[4].vt = VT_I1;

        val[4].intVal = nReserved;

       

        fld[5]="nHeight";

        val[5].vt=VT_I8;

        val[5].lVal = nHeight;

 

        rgf.vt=VT_ARRAY|VT_VARIANT;

        rgv.vt=VT_ARRAY|VT_VARIANT;

       

        bound.cElements=num;

        bound.lLbound=0;

 

       rgv.parray=::SafeArrayCreate(VT_VARIANT,1,&bound);

        if(rgv.parray==NULL)

               AfxThrowMemoryException();

        rgf.parray=::SafeArrayCreate(VT_VARIANT,1,&bound);

        if(rgf.parray==NULL)

               AfxThrowMemoryException();

 

        for ( int i = 0; i < num; i++)

        {

               rgIndices[0]=i;

               ::SafeArrayPutElement(rgf.parray, rgIndices, &fld[i]);

               ::SafeArrayPutElement(rgv.parray, rgIndices, &val[i]);

        }

       

        m_pRs1->AddNew(&rgf,&rgv);         

        booChanged = true;

}

catch (_com_error &e)

{

        AfxMessageBox(e.Description());

        m_pRs1->CancelUpdate();

        booChanged = false;

}

m_pRs1->Close();

注：也可写成下面的格式，只是sql数据变为insert即可。

3、  修改记录

strSql="Update tblGroundBaseInfo       set   TranType=''    where GroundNo='"+m_strGroundNo+"'";

tmpRs.CreateInstance(__uuidof(Recordset));

tmpRs->CursorLocation = adUseClient;

tmpRs->Open((LPCTSTR)strSql,vDispatch,adOpenKeyset,adLockOptimistic,adCmdUnkown);

注：或写成m_pRs->Update(&rgf,&rgv);同添加记录相同

4、  删除记录

CString strIns;

        strIns="delete from tmpStatByGround";

        m_pRs.CreateInstance(__uuidof(Recordset));

        m_pRs->CursorLocation = adUseClient;

        m_pRs->Open((LPCTSTR)strIns, vDispatch, adOpenKeyset,

               adLockOptimistic, adCmdUnknown);

5、  检索记录集

CString strCmdText = "Select * from tblMapHeader where szNum='"+sNum+"'";

        _RecordsetPtr pRs;

        try{

               pRs.CreateInstance(__uuidof(Recordset));

               pRs->CursorLocation = adUseClient;

               pRs->Open((LPCTSTR)strCmdText, vDispatch,adOpenKeyset,

                      adLockOptimistic, adCmdText);

        }catch(_com_error &e)

        {

          

        }           

        if(pRs->RecordCount > 0)

        {

               m_Header.nFlag = (long)pRs->Fields->GetItem("nFlag")->Value;

               m_Header.nScale = (long)pRs->Fields->GetItem("nScale")->Value;

               m_Header.nTime = (long)pRs->Fields->GetItem("nTime")->Value;

               m_Header.nProj = (byte)pRs->Fields->GetItem("nProj")->Value;

               m_Header.nReserved = (byte)pRs->Fields->GetItem("nReserved")->Value;

               m_Header.nHeight = (long)pRs->Fields->GetItem("nHeight")->Value;

               m_Header.ptOrg.x = (long)pRs->Fields->GetItem("ptOrgX")->Value;

               m_Header.ptOrg.y = (long)pRs->Fields->GetItem("ptOrgY")->Value;

               m_Header.fBaseLat = (double)pRs->Fields->GetItem("fBaseLat")->Value;

               m_Header.left = (double)pRs->Fields->GetItem("left1")->Value;

               m_Header.top = (double)pRs->Fields->GetItem("top1")->Value;

               m_Header.right = (double)pRs->Fields->GetItem("right1")->Value;

               m_Header.bottom = (double)pRs->Fields->GetItem("bottom1")->Value;

              

        }

        pRs->Close();

6、  关闭数据库连接    

m_Connection.Release();

关于blob类型字段的使用

long lDataSize = pRecordRead->GetFields()->GetItem(pRec->sRecordName)->ActualSize;///得到数据的长度
        _variant_t varBLOB;
        varBLOB = pRecordRead->GetFields()->GetItem(pRec->sRecordName)->GetChunk(lDataSize);
        pRecordWrite->GetFields()->GetItem(pRec->sRecordName)->AppendChunk(varBLOB);