MFC利用ADO连接ACCESS数据库及其操作数据库的方法
MFC利用ADO连接ACCESS数据库及其操作数据库的方法
1， 在FileView中的StdAfx.h文件中添加：
#import “C:\Program Files\Common Files\system\ado\msadox.dll”
#import “c:\program files\common files\system\ado\msado15.dll” no_namespace rename(“EOF”,”adoEOF”)
2， 在ClassView中的应用程序类ＣXXXXApp中的InitInstance()中添加：
if(!AfxOleInit())
{
AfxMessageBox(“OLE初始化出错！”);
return FALSE;
}
3， 在需要连接数据库的类中添加：
_ConnectionPtr m_pConnection;
_variant_t RecordsAffected;
_RecordsetPtr m_pRecordset;
4， 连接ACCESS数据库：
try
{
m_pConnection.CreateInstance(__uuidof(Connection));
m_pConnection->Open(“Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\\datasource.mdb”,””,””,adModeUnknown);
}
catch(_com_error e)
{
CString errormessage;
errormessage.Format(“连接数据库失败!\r错误信息:%s”,e.ErrorMessage());
AfxMessageBox(errormessage);
return;
}
5,操作数据库中的表：
(1)查询操作：
try
{
if (m_tradingname != _T(“”))
{
m_pRecordset.CreateInstance(“ADODB.Recordset”); //为Recordset对象创建实例
CString strselect;
strselect.Format (“SELECT * FROM %s “, m_tradingname);
m_pRecordset=m_pConnection->Execute(_bstr_t(strselect),&RecordsAffected,adCmdText);
////浏览按钮是被按下过，初始化为否
static BOOL bIsCreated = FALSE;
_variant_t tradingday, openprice, highestprice, lowestprice, closeprice, volume;
///当第一次按下浏览按钮时
if ( bIsCreated == FALSE )
{
try
{
while(!m_pRecordset->adoEOF)
{
//vEmployeeID=m_pRecordset->GetCollect(_variant_t((long)0));
tradingday=m_pRecordset->GetCollect(“TradingDay”);
openprice = m_pRecordset->GetCollect(“OpenPrice”);
highestprice = m_pRecordset->GetCollect(“HighestPrice”);
lowestprice = m_pRecordset->GetCollect(“LowestPrice”);
closeprice = m_pRecordset->GetCollect(“ClosePrice”);
volume = m_pRecordset->GetCollect(“Volume”);
CString str;
if(tradingday.vt!=VT_NULL)
{
str+=(LPCTSTR)(_bstr_t)tradingday;
m_list.InsertItem(0, str);
}
if(openprice.vt!=VT_NULL)
{
str =(LPCTSTR)(_bstr_t)openprice;
m_list.SetItemText(0, 1, str);
}
if(highestprice.vt!=VT_NULL)
{
str =(LPCTSTR)(_bstr_t)highestprice;
m_list.SetItemText(0, 2, str);
}
if(lowestprice.vt!=VT_NULL)
{
str =(LPCTSTR)(_bstr_t)lowestprice;
m_list.SetItemText(0, 3, str);
}
if(closeprice.vt!=VT_NULL)
{
str =(LPCTSTR)(_bstr_t)closeprice;
m_list.SetItemText(0, 4, str);
}
if(volume.vt!=VT_NULL)
{
str =(LPCTSTR)(_bstr_t)volume;
m_list.SetItemText(0, 5, str);
}
m_pRecordset->MoveNext();
}
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
//显示要查询的合约名称
m_showtradingname = m_tradingname;
//显示查询结果的数量
m_number = m_list.GetItemCount ();
UpdateData(FALSE);
bIsCreated = TRUE;
}
else
{
m_list.DeleteAllItems();
bIsCreated = FALSE;
}
m_pRecordset->Close();
m_pRecordset=NULL;
m_pConnection->Close();
m_pConnection=NULL;
}
else
{
MessageBox(“请输入合约名称!”);
return ;
}
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
(2)修改
try
{
CString dataToModify;
dataToModify.Format(“update %s set OpenPrice = \’%f\’ where TradingDay = \’%s\'”, m_tradingname, m_openprice, m_tradingday); //需要进一步修改
m_pConnection->Execute(_bstr_t(dataToModify),&RecordsAffected,adCmdText);
}
catch(_com_error e)
{
CString errormessage;
errormessage.Format(“你修改的数据不符合要求，请确定后重新修改!”,e.ErrorMessage());
AfxMessageBox(errormessage);
return;
}
（3）插入
CString strinsert;
strinsert.Format (“insert into %s values(%s, %f, %f, %f, %f, %d)”,m_tradingname, m_tradingday, m_openprice, m_highestprice, m_lowestprice, m_closeprice, m_volume);
m_pRecordset=m_pConnection->Execute(_bstr_t(strinsert),&RecordsAffected,adCmdText);
MessageBox(“输入成功输入!”);
if(m_pConnection->State)
{
m_pConnection->Close();
}
（4）删除
try
{
CString dataToDel;
dataToDel.Format(“delete from %s where TradingDay = \’%s\'”, m_tradingname, m_strday);
m_pConnection->Execute(_bstr_t(dataToDel),&RecordsAffected,adCmdText);
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
（5）查找数据库表中的表名并且建表
CFileDialog fileDlg(TRUE);
fileDlg.m_ofn .lpstrFilter = “Text FIles(*.txt)\0*.txt\0All FIles(*.*)\0*.*\0\0”;
if(IDOK == fileDlg.DoModal ())
{
CString filename = fileDlg.GetFileTitle (); //获取文件的名字，不带后缀名
ADOX::_CatalogPtr m_pCatalog=NULL;
ADOX::_TablePtr m_pTable=NULL;
CString DBName=”Provider=Microsoft.JET.OLEDB.4.0;Data source=C:\\datasource.mdb”;
try
{
m_pCatalog.CreateInstance(__uuidof(ADOX::Catalog));
m_pCatalog->PutActiveConnection(_bstr_t(DBName));
int tableCount=m_pCatalog->Tables->Count;
int i=0;
BOOL nflag = true;
while(i<tableCount)
{
m_pTable=(ADOX::_TablePtr)m_pCatalog->Tables->GetItem((long)i);
CString tableName=(BSTR)m_pTable->Name;
if( tableName==filename )
{
nflag = false;
}
i++;
}
if (nflag)
{
MessageBox(“合约表不存在，建表！”);
try
{
CString strCommand;
strCommand.Format(“CREATE TABLE %s(TradingDay Text(16), OpenPrice Integer, HighestPrice Integer, LowestPrice Integer, ClosePrice Integer, Volume Integer)”,filename);
m_pConnection->Execute(_bstr_t(strCommand),&RecordsAffected,adCmdText);
MessageBox(“创建成功!!”);
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
}
}
catch(_com_error &e)
{
AfxMessageBox(e.Description());
return;
}
}
（6）建立数据库
（一）添加：#include “Shlwapi.h”
#pragma comment(lib,”shlwapi.lib”)
（二）
CString str;
str= “C:\\”+m_dbName+”.mdb”;
if(PathFileExists(str))
{
CString strTemp;
strTemp.Format(“%s已存在!”,str);
AfxMessageBox(strTemp);
return ;
}
ADOX::_CatalogPtr m_pCatalog = NULL;
CString DBName=”Provider=Microsoft.JET.OLEDB.4.0;Data source=”;
DBName=DBName+str;
try
{
m_pCatalog.CreateInstance(__uuidof(ADOX::Catalog));
m_pCatalog->Create(_bstr_t((LPCTSTR)DBName));
MessageBox(“建库成功!!”);
}
catch(_com_error &e)
{
AfxMessageBox(e.ErrorMessage());
return ;
}
(7)