VC_数据库ADO开发

VC???????? ADO? ??? 2007-8-30??????? ??ADO?? ADO(ActiveX Data Object)? Microsoft???????? ?????????? OLE DB????????????? ???????????? OLE DB?COM???????? ? ADO,???????????????????? ODBC API?DAO?RDO????????????????? ?????? VC????? ADO??????????? ?????????? ?????? ?????? ?????????????????????????? ????????????????? ADO??????? ????? (1)??? COM???? ADO????? (2)? Connection??????? (3)??????????? Connection?Command??? ? SQL?????? Recordset???????????? ????? (4)?????????????? ????: ???????????????????? Access?? ??????????????????????? test.mdb? ????????????????????? ???COM????? ?????? AfxOleInit()???? COM???????? ? CWinApp::InitInstance()????????????? ??: BOOL CADOTest1App::InitInstance() { AfxOleInit(); ...... ????#import???? ADO??? ??? stdafx.h????????(stdafx.h?????? ????????? FileView?? Header Files???) #import "c:\program files\common files\system\ado\m sado15.dll" no_namespace rename("EOF","adoEOF") ?????????????????????#include ???????????????? msado15.tlh,ado15.tli?? C++?????? ADO?? ????: (1) ????? msado15.dll????????????? ????? (2) ????????????????????? MSDN ???????????????????? msado15.tlh(405) : warning C4146: unary minus opera tor applied to unsigned type, result still unsigned ????? Connection???????? ???????????? Connection?????: _ConnectionPtr m_pConnection; ???????????? Connection???????? ???????????? BOOL CADOTest1Dlg::OnInitDialog() { CDialog::OnInitDialog(); HRESULT hr; try { hr = m_pConnection.CreateInstance("ADODB.Connection ");///?? Connection?? if(SUCCEEDED(hr)) { hr = m_pConnection->Open("Provider=Microsoft.Jet.OL EDB.4.0;Data Source=test.mdb","","",adModeUnknown); ///????? ///??????????? Provider??? ACCESS2000 ?????? ACCESS97,??? ?:Provider=Microsoft.Jet.OLEDB.3.51; } } catch(_com_error e)///???? { CString errormessage; errormessage.Format("???????!\r\n??? ?:%s",e.ErrorMessage()); AfxMessageBox(errormessage);///?????? } ??????????? Connection??? Open???? ????????????????? HRESULT Connection15::Open ( _bstr_t ConnectionStri ng, _bstr_t UserID, _bstr_t Password, long Options ) ConnectionString?????,UserID????, Password ?????,Options??????????Connection?? ?????????, Options?????????: adModeUnknown:???????????? adModeRead:?? adModeWrite:?? adModeReadWrite:???? adModeShareDenyRead:???? Connection?????? ???? adModeShareDenyWrite:???? Connection?????? ???? adModeShareExclusive:???? Connection?????? adModeShareDenyNone:??????????????? ??? ??????????????????: (1)?? JET?????? ACCESS2000?????? m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4 .0;Data Source=C:\\test.mdb","","",adModeUnknown); (2)?? DSN???????? ODBC????????: m_pConnection->Open("Data Source=adotest;UID=sa;PWD =;","","",adModeUnknown); (3)??? DSN? SQL SERVER?????? ?? m_pConnection->Open("driver={SQL Server};Serve r=127.0.0.1;DATABASE=vckbase;UID=sa;PWD=139","","", adModeUnknown); ?? Server? SQL???????DATABASE????? Connection??? Open??????????????? Connection?????????? ConnectionTimeOut? State ConnectionTimeOut??????????????? Open ?????? ?: m_pConnection->ConnectionTimeout = 5;///??? ?????? m_pConnection->Open("Data Source=adotest;","","",ad ModeUnknown); State?????? Connection???????????? ?????????????????????????? ????: if(m_pConnection->State) m_pConnection->Close(); ///???????????? ? ????? SQL?????????? ?????????????????? Recordset??? ??:_RecordsetPtr m_pRecordset; ????? Recordset???? ?: m_pRecordset.CreateInstance("ADODB.Recordset"); SQL???????????????????????? (1)?? Connection??? Execute???? SQL?? Execute?????????: _RecordsetPtr Connection15::Execute ( _bstr_t Comma ndText, VARIANT * RecordsAffected, long Options ) ?? CommandText????????? SQL????? RecordsAffected????????????, ?? Options?? CommandText???????Options??? ?????? adCmdText:?? CommandText????? adCmdTable:?? CommandText????? adCmdProc:?? CommandText??????? adCmdUnknown:?? Execute?????????????????????? ?????????? _variant_t RecordsAffected; ///?? SQL???CREATE TABLE???? users,users? ???????? ID,??? username,?? old,??? birthday m_pConnection->Execute("CREATE TABLE users(ID INTEG ER,username TEXT,old INTEGER,birthday DATETIME)",&R ecordsAffected,adCmdText); ///????????? m_pConnection->Execute("INSERT INTO users(ID,userna me,old,birthday) valueS (1, ''''''''Washington''''' ''',25,''''''''1970/1/1'''''''')",&RecordsAffected, adCmdText); ///????? old?????? m_pConnection->Execute("UPDATE users SET old = old+ 1",&RecordsAffected,adCmdText); ///?? SQL???????????????? m_pRecordset = m_pConnection->Execute("SELECT COUNT (*) FROM users",&RecordsAffected,adCmdText); _variant_t vIndex = (long)0; _variant_t vCount = m_pRecordset->GetCollect(vIndex );///??????????? vCount?? m_pRecordset->Close();///????? CString message; message.Format("??%d???",vCount.lVal); AfxMessageBox(message);///???????? (2)?? Command????? SQL?? _CommandPtr m_pCommand; m_pCommand.CreateInstance("ADODB.Command"); _variant_t vNULL; vNULL.vt = VT_ERROR; vNULL.scode = DISP_E_PARAMNOTFOUND;///?????? m_pCommand->ActiveConnection = m_pConnection;///? ????????????????? m_pCommand->CommandText = "SELECT * FROM users";/// ???? m_pRecordset = m_pCommand->Execute(&vNULL,&vNULL,ad CmdText);///?????????? ??????????? Command?????? SELECT? ????Command?????????????????? ??????????????? (3)??? Recordset??????????? ?? m_pRecordset->Open("SELECT * FROM users",_variant_t ((IDispatch *)m_pConnection,true),adOpenStatic,adLo ckOptimistic,adCmdText); Open?????????: HRESULT Recordset15::Open ( const _variant_t & Sour ce, const _variant_t & ActiveConnection, enum Curso rTypeEnum CursorType, enum LockTypeEnum LockType, l ong Options ) ??? ?Source???????? ?ActiveConnection??????????????? Connection?????????_variant_t??) ?CursorType???????????????????? ???: enum CursorTypeEnum { adOpenUnspecified = -1,///?????? adOpenForwardOnly = 0,///????????????? ??????????? MoveNext??????????? ?????????? BookMark,RecordCount,AbsolutePosition,AbsolutePage ????? adOpenKeyset = 1,///??????????????? ?????????????????????????? ???? adOpenDynamic = 2,///??????????????? ??????????????? adOpenStatic = 3///???????????????? ?????????????????????????? ????????? }; ?LockType????????????????????? ??? enum LockTypeEnum { adLockUnspecified = -1,///??? adLockReadOnly = 1,///????? adLockPessimistic = 2,??????????????? ????????????????? adLockOptimistic = 3,????????????? Update?????????????????????? ?????????? adLockBatchOptimistic = 4????????????? ???????????????????????? }; ?Options??????? Connection??? Execute?? ??? ???????????? ?????????? SQL?????? users????? ????:ID,username,old,birthday ?????????????????????????? ??????????????????????????? ??????? _variant_t vUsername,vBirthday,vID,vOld; _RecordsetPtr m_pRecordset; m_pRecordset.CreateInstance("ADODB.Recordset"); m_pRecordset->Open("SELECT * FROM users",_variant_t ((IDispatch*)m_pConnection,true),adOpenStatic,adLoc kOptimistic,adCmdText); while(!m_pRecordset->adoEOF)///?????? adoEOF? ?? EOF????? rename("EOF","adoEOF")????? { vID = m_pRecordset->GetCollect(_variant_t((long)0)) ;///????????? ??????????????? ???????? vUsername = m_pRecordset->GetCollect("username");// ??? username???? vOld = m_pRecordset->GetCollect("old"); vBirthday = m_pRecordset->GetCollect("birthday"); ///? DEBUG???? OUTPUT??????????? if(vID.vt != VT_NULL && vUsername.vt != VT_NULL && vOld.vt != VT_NULL && vBirthday.vt != VT_NULL) TRACE("id:%d,??:%s,??:%d,? ?:%s\r\n",vID.lVal,(LPCTSTR)(_bstr_t)vUsername,vOl d.lVal,(LPCTSTR)(_bstr_t)vBirthday); m_pRecordset->MoveNext();///??????? } m_pRecordset->MoveFirst();///?????? m_pRecordset->Delete(adAffectCurrent);///????? ? ///?????????? for(int i=0;i<3;i++) { m_pRecordset->AddNew();///????? m_pRecordset->PutCollect("ID",_variant_t((long)(i+1 0))); m_pRecordset->PutCollect("username",_variant_t("? ??")); m_pRecordset->PutCollect("old",_variant_t((long)71) ); m_pRecordset->PutCollect("birthday",_variant_t("193 0-3-15")); } m_pRecordset->Move(1,_variant_t((long)adBookmarkFir st));///?????????????????????? ??? m_pRecordset->PutCollect(_variant_t("old"),_variant _t((long)45));///????? m_pRecordset->Update();///????? ??????????? ?????????? Close????? m_pRecordset->Close();///????? m_pConnection->Close();///???? ??????????? ADO????????????? ?????????????????????????? ?????????????? ADO,?????????? ??????????????????????????? ???????? ????????? ??????? ADO????????????????? ???? Recordset????????????????? ??????????????????????????? ????????????????????????? //------------------------------------------------- ------------------------------------- ?????? ? vc????? ADO?????? ?? #import "msado15.dll" no_namespace rename("EOF","AD OEOF") ??"msado15.dll"???? ???????? _ConnectionPtr m_AdoCon?????? ???????????? ??????? _bstr_t connString; //????? try { connString = _T("Provider=MSDASQL.1;Data Source=kon g;Persist Security Info=False"); m_AdoCon.CreateInstance(__uuidof(Connection));//??? m_AdoCon->Open(connString, "", "", -1); } catch (_com_error& comerr) { IErrorInfo* pErrorInfo = comerr.ErrorInfo(); HRESULT hr = comerr.Error(); if (pErrorInfo) { BSTR bsDesc = NULL; pErrorInfo->GetDescription( &bsDesc ); _bstr_t sDesc( bsDesc, false ); AfxMessageBox(sDesc.operator LPCTSTR()); pErrorInfo->Release(); } } ???? CString strIp, SQL_STR, strTemp, strResult; UINT port; _RecordsetPtr AdoRst; //RecordSet? ?? _StreamPtr AdoStream; //Ado??? SQL_STR = m_strText; //SQL?? if (m_AdoCon->State == adStateClosed) //????? ????? { strTemp = "Ado connection hasn't set up."; m_pWnd->SendMessage(WM_FRESHWATCHLIST, (WPARAM)(&st rTemp)); SendMsg(strTemp, FALSE); return; } try { //Create Recordset Interface AdoRst.CreateInstance(__uuidof(Recordset)); AdoRst->Open((LPCTSTR)SQL_STR, m_AdoCon.GetInterfac ePtr(), adOpenDynamic,adLockOptimistic,adCmdUnknown); //?????? SQL?SQL_STR??? if (strTemp != "SELECT") strResult =_T("OK!"); else { //Save the xmlResult to strResult AdoStream.CreateInstance(__uuidof(Stream)); // AdoRst->Save(AdoStream.GetInterfacePtr(), adPersist XML); strResult = ((BSTR)AdoStream->ReadText(adReadAll)); //??? XML?? } } catch (_com_error& comerr) { //catch the COM exception IErrorInfo* pErrorInfo = comerr.ErrorInfo(); HRESULT hr = comerr.Error(); if (pErrorInfo) { BSTR bsDesc = NULL; pErrorInfo->GetDescription( &bsDesc ); _bstr_t sDesc( bsDesc, false); strResult = sDesc.operator LPCTSTR(); pErrorInfo->Release(); } } 5.??????? GetCollect?? int nItem; _variant_t vUsername,vBirthday,vID,vOld; try { m_pRecordset.CreateInstance("ADODB.Recordset"); m_pRecordset->Open("SELECT * FROM users",_variant_t ((IDispatch*)theApp.m_pConnection,true),adOpenStati c,adLockOptimistic,adCmdText); m_bSuccess = TRUE; while(!m_pRecordset->adoEOF) { vID = m_pRecordset->GetCollect("ID"); vUsername = m_pRecordset->GetCollect("username"); vOld = m_pRecordset->GetCollect("old"); vBirthday = m_pRecordset->GetCollect("birthday"); nItem=m_userlist.InsertItem(0xffff,(_bstr_t)vID); m_userlist.SetItem(nItem,1,1,(_bstr_t)vUsername,NUL L,0,0,0); m_userlist.SetItem(nItem,2,1,(_bstr_t)vOld,NULL,0,0 ,0); m_userlist.SetItem(nItem,3,1,(_bstr_t)vBirthday,NUL L,0,0,0); m_pRecordset->MoveNext(); } } 6.???? PutCollect() if(!m_pRecordset->adoEOF && m_nCurrentSel >= 0 && m _bAutoSave) { vUserID = (long)m_nUserID; vUsername = m_sUsername; vOld = (long)m_nOld; vBirthday = m_tBirthday; m_pRecordset->PutCollect("ID",vUserID); m_pRecordset->PutCollect("username",vUsername); m_pRecordset->PutCollect("old",vOld); m_pRecordset->PutCollect("birthday",vBirthday) } //------------------------------------------------- ------------------------------------- ? ado??? access????? void OpenSchemaX(TCHAR *TableName) { HRESULT hr = S_OK; IADORecordBinding *picRs = NULL; _RecordsetPtr pRstSchema("ADODB.Recordset"); _ConnectionPtr pConnection("ADODB.Connection" ); pConnection->ConnectionString = TableName; pConnection->Provider = "Microsoft.Jet.OLEDB.4.0"; try { pConnection->Open(pConnection->ConnectionString, "" , "", adModeUnknown); pRstSchema->QueryInterface( __uuidof(IADORecordBinding), (LPVOID*)&pi cRs); pRstSchema = pConnection->OpenSchema(adSche maTables);//???????? while(!(pRstSchema->EndOfFile)) { CString strTableType; _bstr_t table_name = pRstSchema->Fields -> GetItem("TABLE_NAME")->Value;//?? ???? _bstr_t table_type = pRstSchema->Fields -> GetItem("TABLE_TYPE")->Value;//?? ???? strTableType.Format("%s",(LPCSTR) table _type); if(!lstrcmp(strTableType,_T("TABLE"))) { m_cbTeam.AddString((LPCSTR) table_name);//???? ?? } pRstSchema->MoveNext(); } // Clean up objects before exit. pRstSchema->Close(); pConnection->Close(); } catch (_com_error &e) { // Notify the user of errors if any. // Pass a connection pointer accessed from the Connection. PrintProviderError(pConnection); PrintComError(e);