[VC] ADO访问本地数据库应用

1、初始化

if(!AfxOleInit()){AfxMessageBox(_T("COM库初始化失败！"));return FALSE;}

初始化方法二：

CoInitialize()
CoUninitialize()

2、导入msado15.dll（我是放在stdafx.h下）

#import "msado15.dll"no_namespace rename("EOF","ADOEOF"),rename("BOF","ADOBOF")

3、变量申明

_ConnectionPtr m_pConnection;//_RecordsetPtr m_pRecordset;CString m_strMdbName;//数据库名称CString m_strMdbPath;//数据库所在路径CString m_strSQL;//sql语句CXmlDialog m_xmlDlg;//执行select语句时，将记录集保存

4、连接数据库

void CMyAdoDlg::OnBnClickedLink(){// TODO: 在此添加控件通知处理程序代码if(m_strMdbName.IsEmpty()){AfxMessageBox(_T("请选择数据库！"));return;}//if(m_pConnection->GetState()) //error:如果对象都没创建，不可以GetStateif(m_pConnection != NULL){if(m_pConnection->GetState())AfxMessageBox(_T("数据库已连接！"));elseAfxMessageBox(_T("数据连接对象已创建，但未成功连接数据库！"));return ;}try{HRESULT hr;//hr = m_pConnection.CreateInstance("ADODB.Connection");//okhr = m_pConnection.CreateInstance(__uuidof(Connection));if(SUCCEEDED(hr)){CString strConnection = _T("Provider='Microsoft.Jet.OLEDB.4.0';Data Source='");strConnection += m_strMdbPath;strConnection += "'";_bstr_t bstrConnection = (LPTSTR)(LPCTSTR)strConnection;m_pConnection->ConnectionTimeout = 10;//设置连接时间m_pConnection->Open(bstrConnection,"","",adModeUnknown);AfxMessageBox(_T("连接数据库成功！"));}}catch(_com_error& e){AfxMessageBox(e.Description());}}

备注：
1、m_pConnection.CreateInstance(__uuidof(Connection));为什么用.操作符，而不是->。虽然m_pConnection是指针，但CreateInstance是指针本身的函数，而不是指针所指向的对象的函数。
2、m_pConnection != null 不代表连接了数据库

5、断开数据库

void CMyAdoDlg::OnBnClickedDisconnect(){// TODO: 在此添加控件通知处理程序代码if(m_pConnection == NULL){AfxMessageBox(_T("未创建数据库对象实例！"));return;}else if(!m_pConnection->GetState()){AfxMessageBox(_T("已创建数据库对象实例，但未连接数据库！"));return;}try{m_pConnection->Close();/*m_pConnection->Release();*/m_pConnection = NULL;//上面的release不调用，因为重载的operator=会调用Release：/*template<> _com_ptr_t& operator=(Interface* pInterface) throw(){if (m_pInterface != pInterface) {Interface* pOldInterface = m_pInterface;m_pInterface = pInterface;_AddRef();if (pOldInterface != NULL) {pOldInterface->Release();}}return *this;}*/AfxMessageBox(_T("成功断开数据库！"));}catch(_com_error& e){AfxMessageBox(e.Description());}}

备注：
1、
/*m_pConnection->Release();*/
m_pConnection = NULL;
让这两条语句同事调用，调试的时候，老是产生中断，原因是重载了=，重载函数中有调用Release()

6、SQL语句执行

 

/*SQL:create table employee (name nvarchar(20),sex text(1),age int);drop table employee;insert into employee values ('xinlang','男',24);insert into employee values ('ximei','女',25);select * from employee;*/void CMyAdoDlg::OnBnClickedExcute(){// TODO: 在此添加控件通知处理程序代码UpdateData();if(m_strSQL.IsEmpty()){AfxMessageBox(_T("请填写SQL语句！"));return;}if(m_pConnection == NULL || !m_pConnection->GetState()){AfxMessageBox(_T("请连接数据库！"));return;}if(m_pRecordset != NULL){if(m_pRecordset->GetState())m_pRecordset->Close();/*m_pRecordset->Release();*/m_pRecordset = NULL;}try{HRESULT hr;hr = m_pRecordset.CreateInstance("adodb.recordset");if(SUCCEEDED(hr)){m_pRecordset->Open(_bstr_t(m_strSQL),_variant_t((IDispatch*)m_pConnection),adOpenDynamic,adLockOptimistic,adCmdText);//如果SQL语句执行的是insert into、delete等不返回记录集的操作，那么m_pRecordset执行完open后是关闭的，//此时再用m_pRecordset执行操作，将会提示“对象关闭时，不允许操作！”，如果m_pRecordset执行的是select，//执行完后m_pRecordset未关闭，因此可以再执行save操作//m_strSQL.MakeUpper();//if(-1 != m_strSQL.Find(_T("SELECT")))if(m_pRecordset->GetState()){_variant_t vtSave;_bstr_t bstr; CString strSaveFilePath = GetSavefilePath();SavefileHandle(strSaveFilePath);m_xmlDlg.SetXmlFilePath(strSaveFilePath);bstr = strSaveFilePath;vtSave.vt = VT_BSTR;vtSave.bstrVal  = bstr.copy();//  //void PutCollect (const _variant_t & Index,const _variant_t & pvar );// m_pRecordset->PutCollect("name",_bstr_t("asani"));//备注：用来修改当前记录的信息// m_pRecordset->PutCollect("sex",_bstr_t("中"));// //m_pRecordset->MoveLast();// m_pRecordset->PutCollect("name",_bstr_t("nihao"));//备注：用来修改当前记录的信息// m_pRecordset->PutCollect("age",_bstr_t("100"));//inline HRESULT _Recordset::Save ( const _variant_t & Destination, enum PersistFormatEnum PersistFormat )m_pRecordset->Save(vtSave,adPersistXML);//将所取的信息保存至vtSaveint state = m_pRecordset->GetState();while(!m_pRecordset->ADOEOF){_variant_t vr;//vr.vt = VT_LPSTR;//error 不能赋值，赋值则出错//_variant_t GetCollect (const _variant_t & Index );//vr = m_pRecordset->GetCollect("name");//ok//vr = m_pRecordset->GetCollect("sex");//okvr = m_pRecordset->GetCollect("age");m_pRecordset->MoveNext();}}else{AfxMessageBox(_T("执行SQL成功！"));}}}catch(_com_error& e){AfxMessageBox(e.Description());}}

7、保存文件的处理（保存为XML）

//获取保存文件的路径CString CMyAdoDlg::GetSavefilePath(){CString strSaveFile;if(!m_strMdbPath.IsEmpty()){strSaveFile = m_strMdbPath;strSaveFile = strSaveFile.Left(1 + strSaveFile.ReverseFind('\\'));strSaveFile += DEFAULT_SAVE_XML_NAME;}return strSaveFile;}//保存文件的处理BOOL CMyAdoDlg::SavefileHandle(CString& strSaveFilePath){if(PathFileExists(strSaveFilePath)){CString strNewFile = strSaveFilePath;strNewFile += _T(".bak");if(PathFileExists(strNewFile))DeleteFile(strNewFile);CFile::Rename(strSaveFilePath,strNewFile);}return TRUE;}

#define DEFAULT_MDB_NAME _T("shujuku.mdb")#define DEFAULT_SAVE_XML_NAME _T("SaveData.xml")