第一接触数据库是2年前在研究生院上课的时候,北京理工大学的一个老太太讲的。不是太好!选课失败!今天做的东西中有根数据库相关的部分,于是便学了一下,最终决定采用ADO方式防卫ACCESS。 感谢W.C.Y, Z.W.J, W.S.F在这个过程中给与的指导和讨论!
(1) 动态建立数据库
bool createDatabase(std::string &databasefile,std::string &strcnn)
{
//生成Database路径
strcnn="Provider=Microsoft.JET.OLEDB.4.0;Data source=";
strcnn+=databasefile;
//试图创建该Database,如果该Database不存在的话,不进行创建
CFileFind fFind;
BOOL bSuccess;
bSuccess=fFind.FindFile(databasefile.c_str());
if(bSuccess)
return true;
else
{
HRESULT hr = S_OK;
try
{
_CatalogPtr m_pCatalog = NULL;
hr = m_pCatalog.CreateInstance(__uuidof (Catalog));
if(FAILED(hr))
{
_com_issue_error(hr);
}
else
{
m_pCatalog->Create(_bstr_t((strcnn.c_str()))); //Create MDB
}
}
catch(_com_error &e)
{
AfxMessageBox("创建数据库文件失败!");
return false;
}
}
return true;
}
(2)准备工作
#import "D:\Program Files\Common Files\System\ADO\msado15.dll" \
rename("EOF", "EndOfFile")
#import "msadox.dll" no_namespace
::CoInitialize(NULL);
::CoUninitialize();
(3)打开数据库
ADODB::_ConnectionPtr m_pConnection;
m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
try
{
// 打开本地Access库Demo.mdb
m_pConnection->Open(strOpen.c_str(),
"","",ADODB::adModeUnknown);
}
catch(_com_error e)
{
AfxMessageBox("数据库连接失败,确认数据库是否存在!");
return;
}
(4)数据库信息添加
variant_t RecordsAffected;
//执行SQL命令:CREATE TABLE创建表格
m_pConnection->Execute("CREATE TABLE users(ID INTEGER,Histogram INTEGER)",&RecordsAffected,ADODB::adCmdText);
//往表格里面添加记录
// unsigned int i=2;
// unsigned int val=2001;
// m_pConnection->Execute("INSERT INTO users(ID,Histogram)VALUES (1, 200)",&RecordsAffected,ADODB::adCmdText);
// m_pConnection->Execute("INSERT INTO users(ID,Histogram)VALUES (2, 2002)",&RecordsAffected,ADODB::adCmdText);
//向数据库中添加数据项
ADODB::_RecordsetPtr m_pRecordset;
m_pRecordset.CreateInstance (__uuidof (ADODB::Recordset));
try
{
m_pRecordset->Open("SELECT * FROM users", // 查询DemoTable表中所有字段
m_pConnection.GetInterfacePtr(), // 获取库接库的IDispatch指针
ADODB::adOpenDynamic,
ADODB::adLockOptimistic,
ADODB::adCmdText);
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
return;
}
_variant_t var;
for(int i=0; i<20; i++)
{
m_pRecordset->AddNew();
var.intVal=i;
var.vt=VT_INT;
m_pRecordset->PutCollect("ID",var);
var.intVal=i*i;
m_pRecordset->PutCollect("Histogram",var);
}
m_pRecordset->Update();
m_pRecordset->Close();
m_pRecordset = NULL;
(5)查询数据库
可以采用两种方式,第一种:
ADODB::_CommandPtr m_pCommand;
m_pCommand.CreateInstance(__uuidof(ADODB::Command));
m_pCommand->ActiveConnection = m_pConnection;
m_pCommand->CommandText = "SELECT * FROM users WHERE ID=9";
m_pRecordset = m_pCommand->Execute(NULL, NULL,ADODB::adCmdText);
_variant_t vSum;
vSum = m_pRecordset->GetCollect("Histogram");// m_pRecordset->Fields->GetItem("Histogram")->Value;
CString mystr;
mystr.Format("%d",vSum.intVal);
AfxMessageBox(mystr);
第二种
m_pRecordset=m_pConnection->Execute("SELECT * FROM users WHERE ID=9",&vSum,ADODB::adCmdText);
vSum = m_pRecordset->GetCollect("Histogram");// m_pRecordset->Fields->GetItem("Histogram")->Value; _variant_t vSum;
CString mystr;
mystr.Format("%d",vSum.intVal);
AfxMessageBox(mystr);
std::string strCmd="SELECT COUNT(ID) AS IDCount FROM users";
_variant_t vSum;
m_ppara->m_pRecordset = m_ppara->m_pConnection->Execute(strCmd.c_str(),&vSum,ADODB::adCmdText);
vSum = m_ppara->m_pRecordset->GetCollect("IDCount");
if(vSum.vt == NULL)
return false;
else
{
int nLookup = vSum.intVal;
}
(5)遍历数据库
while(!m_pRecordset->adoEOF)
{
var = m_pRecordset->GetCollect("Name");
if(var.vt != VT_NULL)
strName = (LPCSTR)_bstr_t(var);
var = m_pRecordset->GetCollect("Age");
if(var.vt != VT_NULL)
strAge = (LPCSTR)_bstr_t(var);
m_AccessList.AddString( strName + " --> "+strAge );
m_pRecordset->MoveNext();
}
参考文献:
(1)数据库查询方面
http://www.vckbase.com/document/viewdoc/?id=1215
http://www.codeguru.com/cpp/data/mfc_database/ado/article.php/c6729__2/
(2)数据库建立方面
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/adproprimarykeyxvc.asp
http://www.51cto.com/html/2005/0922/3585.htm
(3) 数据库升级部分
http://www.codeproject.com/database/ADOImageDB.asp
遇到的典型问题
(1) error C2011: 'DataTypeEnum' : 'enum' type redefinition
解决方案:
#import "msado15.dll" rename("EOF","adoEOF") rename("DataTypeEnum","adoDataTypeEnum")
#import "msadox.dll" rename_namespace("MAJIANGLIN") rename("EOF","adoXEOF") rename("DataTypeEnum","adoXDataTypeEnum")
我试从http://www.blogcn.com/user3/jiangsheng/index.html 伯克商找到的答案, TKS google