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:如果对象都没创建,不可以GetState
if(m_pConnection != NULL)
{
if(m_pConnection->GetState())
AfxMessageBox(_T("数据库已连接!"));
else
AfxMessageBox(_T("数据连接对象已创建,但未成功连接数据库!"));
return ;
}
try{
HRESULT hr;
//hr = m_pConnection.CreateInstance("ADODB.Connection"); //ok
hr = 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); //将所取的信息保存至vtSave
int 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"); //ok
vr = 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")