|
|
数据库技术戏称是程序里面的体力劳动，实际上它也有个技术坎，你突破了这个坎，它就是体力活，没有掌握就是脑力活。
这里示范是MDB数据库的常用操作，建库，建表，插入数据，修改数据，显示数据。没有示范删除，那只是一句语句而已。
有些东西常久不用，就会遗忘，好记性不如烂笔头，这里也可备用，实在想不起就来查一查。
操作本地数据库，这里没用SQL语句，SQL在网络数据库里弄吧。仅仅只是示范Jet4.0引擎。
方法来自MSDN，只不过我把集中体现在下面一个示例中。实际运用中，看到都能回忆起来，哦，原来是这样操作地。
引入库：
ado 用于数据操作
adox用于数据管理
关于这两库的资料已经多的不能再多了，不明白就上网找，满大街都是。
例程：
#include "stdafx.h"
#include <iostream>
#include <iomanip>
#include <string>
#include <sstream>
#include <Ole2.h>
#include <icrsint.h>
#include<oledberr.h>
//指针和对象释放
#define SAFE_RELEASE_P(p){ if(p) { (p)->Release();(p)=NULL;}}
#define SAFE_RELEASE_O(p){ if(p) { (p).Release();}}
//如果#import显示无法打开源文件msadox.tlh，选菜单生成，无论对错，重新生成解决方案即可。
#import "C:\Program Files (x86)\Common Files\system\ado\msado15.dll" rename("EOF", "EndOfFile")
#import "C:\Program Files (x86)\Common Files\system\ado\msadox.dll" no_namespace
using namespace std;
bool createdatabase(string dbname) {
bool result = false;
stringstream o;
o << "Provider = 'Microsoft.JET.OLEDB.4.0';Data source = " << dbname;
string connstr = o.str();
_CatalogPtr pCatalog = NULL;
_bstr_t strcnn(connstr.c_str());
try {
HRESULT hr = pCatalog.CreateInstance(__uuidof(Catalog));
if (FAILED(hr)) _com_issue_error(hr);
pCatalog->Create(strcnn);
cout << "本地数据库:"<< dbname << " 已建立！" << endl;
result = true;
}
catch (_com_error &e) {
//错误号包含在 #include<oledberr.h>中
cout << "异常返回码=0x" << hex << e.Error() << dec << endl;
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
cout << "源 : " << (LPCSTR)bstrSource << endl;
cout << "描述: " << (LPCSTR)bstrDescription << endl;
}
catch (...) {
cout << " CreateDatabaseX 出错！！." << endl;
}
SAFE_RELEASE_O(pCatalog);
return result;
}
bool buildtable(string dbname,string tabname)
{
bool result = false;
stringstream o;
o << "Provider = 'Microsoft.JET.OLEDB.4.0';Data source = " << dbname;
string connstr = o.str();
_bstr_t strcnn(connstr.c_str());
_TablePtr pTable = NULL;
_IndexPtr pIndex = NULL;
_CatalogPtr pCatalog = NULL;
_KeyPtr pKeyPrimary = NULL;
HRESULT hr = S_OK;
try {
hr = pTable.CreateInstance(__uuidof(Table));
if (FAILED(hr)) _com_issue_error(hr);
hr = pIndex.CreateInstance(__uuidof(Index));
if (FAILED(hr)) _com_issue_error(hr);
hr = pCatalog.CreateInstance(__uuidof (Catalog));
if (FAILED(hr)) _com_issue_error(hr);
pCatalog->PutActiveConnection(strcnn);
// 定义表，追加到目录.
pTable->Name = _bstr_t(tabname.c_str());
hr = pTable->Columns->Append("编号", adInteger, 0);
hr = pTable->Columns->Append("姓名", adVarWChar, 30);
hr = pTable->Columns->Append("性别", adInteger, 0);
hr = pTable->Columns->Append("年龄", adInteger, 0);
hr = pTable->Columns->Append("班级", adInteger, 0);
hr = pTable->Columns->Append("住址", adVarWChar, 50);
hr = pCatalog->Tables->Append(_variant_t((IDispatch *)pTable));
if (FAILED(hr)) _com_issue_error(hr);
cout << "表" << pTable->Name << "被追加!" << endl;
// 定义辅索引.
pIndex->Name = _bstr_t("按名索引");
pIndex->Columns->Append("姓名", adInteger, 0);
// 添加索引到表中.
hr = pCatalog->Tables->GetItem(tabname.c_str())->Indexes->Append(_variant_t((IDispatch *)pIndex));
if (FAILED(hr)) _com_issue_error(hr);
cout << "索引[" << pIndex->Name << "]被添加!" << endl;
//定义主索引.
hr = pKeyPrimary.CreateInstance(__uuidof(Key));
pKeyPrimary->Name = "主键";
pKeyPrimary->Type = adKeyPrimary;
pKeyPrimary->RelatedTable = _bstr_t(tabname.c_str());;
pKeyPrimary->Columns->Append("编号", adVarWChar, 0);
pKeyPrimary->Columns->Append("班级", adVarWChar, 0);
pKeyPrimary->UpdateRule = adRICascade;
_variant_t vOptional;
vOptional.vt = VT_ERROR;
vOptional.scode = DISP_E_PARAMNOTFOUND;
hr=pCatalog->Tables->GetItem(tabname.c_str())->Keys->
Append(_variant_t((IDispatch *)pKeyPrimary),adKeyPrimary, vOptional, L"", L"");
if (FAILED(hr)) _com_issue_error(hr);
cout << "索引[" << pKeyPrimary->Name << "]被添加!" << endl;
result = true;
}
catch (_com_error &e) {
//错误号包含在 #include<oledberr.h>中
cout << "异常返回码=0x" << hex << e.Error() << dec << endl;
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
cout << "源 : " << (LPCSTR)bstrSource << endl;
cout << "描述: " << (LPCSTR)bstrDescription << endl;
}
catch (...) {
cout << "建立表和索引产生未知错误！...." << endl;
}
SAFE_RELEASE_O(pKeyPrimary);
SAFE_RELEASE_O(pIndex);
SAFE_RELEASE_O(pTable);
SAFE_RELEASE_O(pCatalog);
return result;
}
void appenddata(string dbname,string tabname) {
struct _data{
int no;
wstring name;
int sex;
int howold;
int classgroup;
wstring address;
};
_data data[] = {
{ 1,L"张三",1,8,301,L"城中村1" },
{ 2,L"李四",1,7,301,L"城中村1" },
{ 3,L"王五",1,8,301,L"城中村2" },
{ 4,L"赵六",1,8,301,L"城中村2" },
{ 5,L"钱七",1,9,301,L"城中村2" },
{ 6,L"宋八",1,8,301,L"城中村3" },
};
class CDemoRs : public CADORecordBinding {
BEGIN_ADO_BINDING(CDemoRs)
ADO_NUMERIC_ENTRY(1, adInteger, m_bh, 0,0, m_bhStatus,TRUE)
ADO_VARIABLE_LENGTH_ENTRY2(2, adVarWChar, m_name,sizeof(m_name), m_nameStatus,TRUE)
ADO_NUMERIC_ENTRY(3, adInteger, m_sex, 0,0, m_sexStatus, TRUE)
ADO_NUMERIC_ENTRY(4, adInteger, m_howold, 0,0, m_howoldStatus, TRUE)
ADO_NUMERIC_ENTRY(5, adInteger, m_class, 0,0, m_classStatus, TRUE)
ADO_VARIABLE_LENGTH_ENTRY2(6, adVarWChar, m_address,sizeof(m_address),m_addressStatus,TRUE)
END_ADO_BINDING()
public:
int m_bh;
ULONG m_bhStatus;
TCHAR m_name[30];
ULONG m_nameStatus;
int m_sex;
ULONG m_sexStatus;
int m_howold;
ULONG m_howoldStatus;
int m_class;
ULONG m_classStatus;
TCHAR m_address[50];
ULONG m_addressStatus;
};
IADORecordBinding *pIrb = NULL;
CDemoRs rs;
stringstream o;
o << "Provider = 'Microsoft.JET.OLEDB.4.0';Data source = " << dbname;
string connstr = o.str();
ADODB::_RecordsetPtr pRst = NULL;
ADODB::_ConnectionPtr pConnection = NULL;
HRESULT hr = S_OK;
try {
hr = pConnection.CreateInstance(__uuidof(ADODB::Connection));
if (FAILED(hr)) _com_issue_error(hr);
hr = pConnection->Open(connstr.c_str(), "", "", ADODB::adConnectUnspecified);
if (FAILED(hr)) _com_issue_error(hr);
hr = pRst.CreateInstance(__uuidof(ADODB::Recordset));
if (FAILED(hr)) _com_issue_error(hr);
hr = pRst->QueryInterface(__uuidof(IADORecordBinding), (LPVOID*)&pIrb);
if (FAILED(hr)) _com_issue_error(hr);
hr = pRst->Open(tabname.c_str(), _variant_t((IDispatch *)pConnection, true),
ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);
if (FAILED(hr)) _com_issue_error(hr);
//数据绑定
hr = pIrb->BindToRecordset(&rs);
for (auto x : data) {
rs.m_bh = x.no;
wcscpy_s(rs.m_name, 30,x.name.c_str());
rs.m_sex = x.sex;
rs.m_howold = x.howold;
rs.m_class = x.classgroup;
wcscpy_s(rs.m_address,50,x.address.c_str());
rs.m_bhStatus = 0;
rs.m_nameStatus = 0;
rs.m_sexStatus = 0;
rs.m_howoldStatus = 0;
rs.m_classStatus = 0;
rs.m_addressStatus = 0;
hr= pIrb->AddNew(&rs);
if (hr != S_OK) {
wcout.imbue(locale("chs"));
cout << "添加记录错误！记录重请检查数据" << endl;
cout << "记录域转换情况：" << endl;
cout << "状态:记录域 " << rs.m_bh << (rs.m_bhStatus == adFldOK ? "-- ok" : "-- Null") << endl;
wcout << L"状态:记录域 " << rs.m_name << (rs.m_bhStatus == adFldOK ? L"-- ok" : L"-- Null") << endl;
cout << "状态:记录域 " << rs.m_sex << (rs.m_bhStatus == adFldOK ? "-- ok" : "-- Null") << endl;
cout << "状态:记录域 " << rs.m_howold << (rs.m_bhStatus == adFldOK ? "-- ok" : "-- Null") << endl;
cout << "状态:记录域 " << rs.m_class << (rs.m_bhStatus == adFldOK ? "-- ok" : "-- Null") << endl;
wcout << L"状态:记录域 " << rs.m_address << (rs.m_bhStatus == adFldOK ? L"-- ok" : L"-- Null") << endl;
}
if (FAILED(hr)) _com_issue_error(hr);
}
//记录集直接增加,不过也很变态，要转几道才行
//这里只弄一条
_variant_t Fields[] = {
"编号",
"姓名",
"性别",
"年龄",
"班级",
"住址"
};
_variant_t Value[] = {
99,
L"班主任",
1,
24,
301,
L"城中村农家乐"
};
long len = sizeof Fields / sizeof Fields[0];
//弄两个安全数组
SAFEARRAYBOUND rgsaName[1], rgsaValue[1];
rgsaName[0].lLbound = 0;
rgsaName[0].cElements = len;
SAFEARRAY *psaName = SafeArrayCreate(VT_VARIANT, 1, rgsaName);
rgsaValue[0].lLbound = 0;
rgsaValue[0].cElements = len;
SAFEARRAY *psaValue = SafeArrayCreate(VT_VARIANT, 1, rgsaValue);
//把值传进去
for (long i = 0;i < 6;i++)
{
SafeArrayPutElement(psaName, &i, &Fields[ i ]);
SafeArrayPutElement(psaValue,&i, &Value[ i ]);
}
//再构成变量数组
VARIANT vsaName, vsaValue;
vsaName.vt = VT_VARIANT | VT_ARRAY;
vsaValue.vt = VT_VARIANT | VT_ARRAY;
V_ARRAY(&vsaName) = psaName;
V_ARRAY(&vsaValue) = psaValue;
//这样再给它用
hr = pRst->AddNew(vsaName, vsaValue);
if (hr != S_OK) {
cout << "添加记录 99 错误！可能记录重请检查数据" << endl;
}
if (FAILED(hr)) _com_issue_error(hr);
cout << "数据添加完毕!" << endl;
//下面是修改动作
//修改后刷新获取的结果,不然pRst->GetRecordCount() 会不对。
pRst->Requery(ADODB::adCmdUnknown);
//合法移动范围 0- pRst->GetRecordCount() 之间,这里等于MoveLast;
int pos = pRst->GetRecordCount();
pRst->Move(pos-1);
//if (pRst->Fields->GetItem("编号")->GetValue() ==variant_t(99))
{
wcout.imbue(locale("chs"));
cout << "更改的当前记录:" << endl;
cout << "编号:" << pRst->Fields->GetItem("编号")->GetValue().intVal << endl;
wcout<< L"姓名:" << pRst->Fields->GetItem("姓名")->GetValue().bstrVal << endl;
cout << "性别:" << pRst->Fields->GetItem("性别")->GetValue().intVal << endl;
cout << "年龄:" << pRst->Fields->GetItem("年龄")->GetValue().intVal << endl;
cout << "班级:" << pRst->Fields->GetItem("班级")->GetValue().intVal << endl;
wcout<< L"住址:" << pRst->Fields->GetItem("住址")->GetValue().bstrVal << endl;
pRst->Fields->GetItem("编号")->Value = 88;
pRst->Fields->GetItem("姓名")->Value= L"女班主任";
pRst->Fields->GetItem("性别")->Value = 0;
pRst->Fields->GetItem("年龄")->Value = 54;
pRst->Fields->GetItem("班级")->Value = 301;
pRst->Fields->GetItem("住址")->Value = L"城中村农家乐";
pRst->Update();
//修改后刷新获取的结果
pRst->Requery(ADODB::adCmdUnknown);
}
pRst->Close();
pConnection->Close();
}
catch (_com_error &e) {
//错误号包含在 #include<oledberr.h>中
cout << "异常返回码=0x" << hex<< e.Error() <<dec<< endl;
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
cout << "源 : " << (LPCSTR)bstrSource << endl;
cout << "描述: " << (LPCSTR)bstrDescription << endl;
}
catch (...) {
cout << "追加记录产生未知错误！...." << endl;
}
SAFE_RELEASE_P(pIrb);
SAFE_RELEASE_O(pRst);
SAFE_RELEASE_O(pConnection);
}
void showtable(string dbname,string tblname) {
HRESULT hr = S_OK;
stringstream o;
o << "Provider = 'Microsoft.JET.OLEDB.4.0';Data source = " << dbname;
string connstr = o.str();
_bstr_t strcnn(connstr.c_str());
_CatalogPtr pCatalog = NULL;
ADODB::_RecordsetPtr pRecordset = NULL;
try {
hr = pRecordset.CreateInstance(__uuidof(ADODB::Recordset));
if (FAILED(hr)) _com_issue_error(hr);
hr = pCatalog.CreateInstance(__uuidof (Catalog));
if (FAILED(hr)) _com_issue_error(hr);
cout << "记录集\n=========" << endl;
hr = pRecordset->Open(tblname.c_str(), strcnn, ADODB::adOpenKeyset, ADODB::adLockReadOnly, ADODB::adCmdTable);
if (FAILED(hr)) _com_issue_error(hr);
//保存关联连接
pCatalog->PutActiveConnection(pRecordset->GetActiveConnection());
if (pRecordset->RecordCount < 1) {
cout << "无记录行！" << endl;
}
//获取显示列名
ADODB::FieldPtr pFld = NULL;
cout << "[adox]";
for (short Idx = 0; Idx < pRecordset->Fields->GetCount(); Idx++) {
pFld = pRecordset->Fields->GetItem(Idx);
cout << (LPCSTR)pFld->GetName();
switch (pFld->Type)
{
case adEmpty:cout << "(adEmpty)";break;
case adTinyInt:cout << "(adTinyInt)";break;
case adSmallInt:cout << "(adSmallInt)";break;
case adInteger:cout << "(adInteger)";break;
case adUnsignedTinyInt:cout << "(adUnsignedTinyInt)";break;
case adUnsignedSmallInt:cout << "(adUnsignedSmallInt)";break;
case adUnsignedInt:cout << "(adUnsignedInt)";break;
case adUnsignedBigInt:cout << "(adUnsignedBigInt)";break;
case adSingle:cout << "(adSingle)";break;
case adDouble:cout << "(adDouble)";break;
case adCurrency:cout << "(adCurrency)";break;
case adDecimal:cout << "(adDecimal)";break;
case adNumeric:cout << "(adNumeric)";break;
case adBoolean:cout << "(adBoolean)";break;
case adError:cout << "(adError)";break;
case adUserDefined:cout << "(adUserDefined)";break;
case adVariant:cout << "(adVariant)";break;
case adIDispatch:cout << "(adIDispatch)";break;
case adIUnknown:cout << "(adIUnknown)";break;
case adGUID:cout << "(adGUID)";break;
case adDate:cout << "(adDate)";break;
case adDBDate:cout << "(adDBDate)";break;
case adDBTime:cout << "(adDBTime)";break;
case adDBTimeStamp:cout << "(adDBTimeStamp)";break;
case adBSTR:cout << "(adBSTR)";break;
case adChar:cout << "(adChar)";break;
case adVarChar:cout << "(adVarChar)";break;
case adLongVarChar:cout << "(adLongVarChar)";break;
case adWChar:cout << "(adWChar)";break;
case adVarWChar:cout << "(adVarWChar)";break;
case adLongVarWChar:cout << "(adLongVarWChar)";break;
case adBinary:cout << "(adBinary)";break;
case adVarBinary:cout << "(adVarBinary)";break;
case adLongVarBinary:cout << "(adLongVarBinary)";break;
case adChapter:cout << "(adChapter)";break;
case adFileTime:cout << "(adFileTime)";break;
case adPropVariant:cout << "(adPropVariant)";break;
case adVarNumeric:cout << "(adVarNumeric)";break;
default:
cout << "(未知" << hex << pFld->Type << ")";
}
cout << "\t";
}
cout << endl;
pRecordset->MoveFirst();
int iCount = 0;
while (!(pRecordset->EndOfFile)) {
cout << "\t";
for (long i = 0; i < pRecordset->Fields->GetCount(); ++i)
{
_variant_t FldVal = pRecordset->Fields->GetItem(i)->Value;
switch (FldVal.vt) {
case (VT_NULL): cout << "NULL";break;
case (VT_BOOL): cout << (FldVal.boolVal) ? "True " : "False";break;
case (VT_BSTR): cout << (LPCSTR)(_bstr_t)FldVal.bstrVal;break;
case (VT_I1): cout << (LPCSTR)(_bstr_t)FldVal.cVal;break;
case (VT_I2): cout << (LPCSTR)(_bstr_t)FldVal.iVal;break;
case (VT_I4): cout << (LPCSTR)(_bstr_t)FldVal.lVal;break;
case (VT_I8): cout << (LPCSTR)(_bstr_t)FldVal.llVal;break;
case (VT_UI1): cout << (LPCSTR)(_bstr_t)FldVal.bVal;break;
case (VT_UI2): cout << (LPCSTR)(_bstr_t)FldVal.uiVal;break;
case (VT_UI4): cout << (LPCSTR)(_bstr_t)FldVal.ulVal;break;
case (VT_UI8): cout << (LPCSTR)(_bstr_t)FldVal.ullVal;break;
case (VT_INT): cout << (LPCSTR)(_bstr_t)FldVal.intVal;break;
case (VT_UINT): cout << (LPCSTR)(_bstr_t)FldVal.uintVal;break;
case (VT_EMPTY): cout << (LPCSTR)(_bstr_t)FldVal.lVal;break;
case (VT_R4): cout << (LPCSTR)(_bstr_t)FldVal.fltVal;break;
case (VT_R8): cout << (LPCSTR)(_bstr_t)FldVal.dblVal;break;
case (VT_LPWSTR):cout << (LPCSTR)(_bstr_t)FldVal.bstrVal;break;
//注意类型还有，这里只列举常用地。
default:cout << " "; break;
}
cout << "\t\t";
}
cout << endl;
pRecordset->MoveNext();
}
}
catch (_com_error &e) {
cout << "异常返回码=0x" << hex << e.Error() << dec << endl;
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
cout << "源 = " << (LPCSTR)bstrSource << endl;
cout << "描述 = " << (LPCSTR)bstrDescription << endl;
}
catch (...) {
cout << "流览记录集出错！." << endl;
}
SAFE_RELEASE_O(pCatalog);
if (pRecordset)
{
if (pRecordset->State == 1)
pRecordset->Close();
SAFE_RELEASE_O(pRecordset);
}
}
int main()
{
CoInitialize(NULL);
string dbname = "demo.mdb";
string tabname = "花名册";
HANDLE hStdin = GetStdHandle(STD_INPUT_HANDLE);
DWORD OldMode,NewMode;
OldMode = GetConsoleMode(hStdin, &OldMode);
NewMode = OldMode & ~ENABLE_LINE_INPUT;
NewMode = NewMode | ENABLE_ECHO_INPUT;
SetConsoleMode(hStdin, NewMode);
char ch;
while (1)
{
cout << "\n数据库demo.mdb存在，是否删除 Y/N/Q退出 ";
cin.get(ch);
int t = toupper(ch);
if (t=='Q') return 0;
if (t=='Y') {
DeleteFileA(dbname.c_str());
break;
}
if (t=='N') break;
Beep(660, 5);
}
cout << endl;
SetConsoleMode(hStdin, OldMode);
struct _stat64 stat;
int FileExist = _stat64(dbname.c_str(), &stat);
if (FileExist == -1) {
cout << "建库..." << endl;
bool b=createdatabase(dbname);
if (!b) return -1;
cout << "建表-花名册...." << endl;
b=buildtable(dbname, tabname);
if (!b) return -2;
cout << "添加测试数据...." << endl;
appenddata(dbname, tabname);
}
//显示
showtable(dbname, tabname);
system("pause");
CoUninitialize();
return 0;
}
运行结果:
注意：
数据库demo.mdb存在，是否删除 Y/N/Q退出 是为了处理异常，重复运行的情况
其中 Y 为删数据库重新建库，建表，添加数据，然后显示数据。
N 为不删数据库，也不建表，添加数据。只显示。
Q 为直接退出，什么都不做。
createdatabase，buildtable 不是通用，绑的死表。
showtable是通用地，自动判定，绑定数据库字段。
xx_player 2019.5
不知道中途字体是怎么变歪地。多包涵。
|
评分
-
查看全部评分
上一篇： 现代C++的技巧-大浪淘金下一篇： 数据库之网络数据库
|
/2 
发表于 2019-5-10 10:33:40
发表于 2019-5-10 22:56:28
