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

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

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

	_ConnectionPtr m_pConnection;	//
	_RecordsetPtr m_pRecordset;

	CString m_strMdbName;	//数据库名称
	CString m_strMdbPath;//数据库所在路径
	CString m_strSQL;//sql语句

	CXmlDialog m_xmlDlg;//执行select语句时，将记录集保存

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());
	}
	
	
}

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());
	}
}

/*
	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());
	}

}

//获取保存文件的路径
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")