C++、Qt操作Access数据库
工作需要，使用C++、Qt封装了几个操作Access数据库的接口，分享给大家，
代码是经过反复测试，可以直接使用的：
一些宏定义，ado.h：
/*File: ado.h 关于ADO的一些定义*/
#ifndef ADO_H
#define ADO_H
#include <QAxObject>
#include <QFile>
#include <QTextStream>
#define adConnectUnspecified -1
#define adStateClosed 0
#define adOpenStatic 3
#define adOpenDynamic 2
#define adLockOptimistic 3
#define adCmdText 1
#define adSchemaTables 20
typedef long HRESULT;
#define SUCCEEDED(hr) ((HRESULT)(hr) >= 0)
#define FAILED(hr) ((HRESULT)(hr) < 0)
#define ADO_DELETE(p) do{if(p) delete (p); (p)=0;}while(0)
class ADO {
public:
static void outPutDocument(QString doc, QString fileName)
{
QFile outFile(fileName);
outFile.open(QIODevice::WriteOnly|QIODevice::Truncate);
QTextStream ts(&outFile);
ts<<doc<<endl;
outFile.close();
}
};
#endif // ADO
###1、获取指定数据库的表：
/**
* @brief 获取Access数据库中所有的表
* @param mdbPath 数据库文件路径
* @param tableList 输出参数 表列表
*/
void getAccessTableList(QString mdbPath, QStringList &tableList);
###2、获取Access数据库中某个表所有的字段
/**
* @brief 获取Access数据库中某个表所有的字段
* @param mdbPath 数据库文件路径
* @param targetTable 目标表名称
* @param filedList 输出参数 字段列表
*/
void getAccessFieldList(QString mdbPath, QString targetTable, QStringList &filedList);
###3、获取Access数据库中某个表中的所有数据
/**
* @brief 获取Access数据库中某个表中的所有数据
* @param mdbPath 数据库文件路径
* @param targetTable 目标表名称
* @param data 输出参数 key：字段名称 value：该字段下所有的值
*/
void getAccessData(QString mdbPath, QString targetTable, QMap<QString, QVector<QString> > &data, QString sortField=QString(), bool asc=true);
###以下是完整代码：
1. h
#ifndef NTACCESSMANAGER_H
#define NTACCESSMANAGER_H
#include <QString>
class NtAccessManager
{
public:
NtAccessManager();
/**
* @brief 获取Access数据库中所有的表
* @param mdbPath 数据库文件路径
* @param tableList 输出参数 表列表
*/
void getAccessTableList(QString mdbPath, QStringList &tableList);
/**
* @brief 获取Access数据库中某个表所有的字段
* @param mdbPath 数据库文件路径
* @param targetTable 目标表名称
* @param filedList 输出参数 字段列表
*/
void getAccessFieldList(QString mdbPath, QString targetTable, QStringList &filedList);
/**
* @brief 获取Access数据库中某个表中的所有数据
* @param mdbPath 数据库文件路径
* @param targetTable 目标表名称
* @param data 输出参数 key：字段名称 value：该字段下所有的值
*/
void getAccessData(QString mdbPath, QString targetTable, QMap<QString, QVector<QString> > &data, QString sortField=QString(), bool asc=true);
/**
* @brief delTabelDataByID : 从指定表格删除指定编号的电路
* @param mdbPath : mdb文件所在的路径
* @param targetTable ： 目标表名
* @param id : 电路ID
*/
void delTabelDataByID(QString mdbPath , QString targetTable , QString condition);
private:
QStringList filedList;
};
#endif // NTACCESSMANAGER_H
2.cpp
#include <QAxObject>
#include <QVector>
#include "qt_windows.h"
#include "ado.h"
#include "ntaccessmanager.h"
NtAccessManager::NtAccessManager()
{
}
void NtAccessManager::getAccessTableList(QString mdbPath, QStringList &tableList)
{
HRESULT r = OleInitialize(0);
if (r != S_OK && r != S_FALSE) {
qWarning("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
}
QAxObject *connection = new QAxObject;
connection->setControl("ADODB.Connection");/*创建ADODB.Connection对象*/
connection->setProperty("ConnectionTimeout",300);/*设置超时时间确保连接成功*/
QString connectString = QString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%1;").arg(mdbPath);
//打开连接
HRESULT hr =
connection->dynamicCall("Open(QString,QString,QString,int)",connectString,"","",adConnectUnspecified).toInt(); /*连接到数据库*/
if(SUCCEEDED(hr))
{
//查询有关数据源的schema信息
QAxObject *recordSet = connection->querySubObject("OpenSchema(SchemaEnum)", adSchemaTables);
if(recordSet)
{
while(!recordSet->property("EOF").toBool())
{
QAxObject * adoFields = recordSet->querySubObject("Fields");
if(adoFields)
{
int count = adoFields->property("Count").toInt();
QString tableName = "";
QString tableType = "";
//从字段列表中获取表名和表类型
for(int i = 0; i < count; i++)
{
QAxObject * adoField = adoFields->querySubObject("Item(int)",i);
if(adoField)
{
QString name = adoField->property("Name").toString();
QVariant value = adoField->property("Value");
if(name == "TABLE_NAME")
{
tableName = value.toString();
}
else if(name == "TABLE_TYPE")
{
tableType = value.toString();
}
ADO_DELETE(adoField);
}
}
//判断表类型为TABLE时，该表为用户添加的表，将该表名插入到表列表中
if(tableType == "TABLE")
tableList.append(tableName);
ADO_DELETE(adoFields);
}
//移动至下一条记录
recordSet->dynamicCall("MoveNext");
}
ADO_DELETE(recordSet);
}
//关闭连接
connection->dynamicCall("Close");
delete connection;
connection = NULL;
OleUninitialize();
}
}
void NtAccessManager::getAccessFieldList(QString mdbPath, QString targetTable, QStringList &filedList)
{
HRESULT r = OleInitialize(0);
if (r != S_OK && r != S_FALSE) {
qWarning("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
}
QAxObject *connection = new QAxObject;
connection->setControl("ADODB.Connection");/*创建ADODB.Connection对象*/
connection->setProperty("ConnectionTimeout",300);/*设置超时时间确保连接成功*/
QString connectString = QString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%1;")
.arg(mdbPath);
HRESULT hr =
connection->dynamicCall("Open(QString,QString,QString,int)",connectString,"","",adConnectUnspecified).toInt(); /*连接到数据库*/
if(SUCCEEDED(hr))
{
QString sqlStr = QString("select * from %1").arg(targetTable);
QAxObject *recordSet = connection->querySubObject("Execute(QString, QVariant&, int)", sqlStr);
if(recordSet && !recordSet->property("EOF").toBool())
{
QAxObject * adoFields = recordSet->querySubObject("Fields");
if(adoFields)
{
int count = adoFields->property("Count").toInt();
for(int i = 0; i < count; i++)
{
QAxObject * adoField = adoFields->querySubObject("Item(int)",i);
if(adoField)
{
QString name = adoField->property("Name").toString();
filedList.append(name);
ADO_DELETE(adoField);
}
}
ADO_DELETE(adoFields);
}
ADO_DELETE(recordSet);
}
connection->dynamicCall("Close");
delete connection;
connection = NULL;
OleUninitialize();
}
}
void NtAccessManager::getAccessData(QString mdbPath, QString targetTable, QMap<QString, QVector<QString> > &data, QString sortField, bool asc)
{
HRESULT r = OleInitialize(0);
if (r != S_OK && r != S_FALSE) {
qWarning("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
}
QAxObject *connection = new QAxObject;
connection->setControl("ADODB.Connection");/*创建ADODB.Connection对象*/
connection->setProperty("ConnectionTimeout",300);/*设置超时时间确保连接成功*/
QString connectString = QString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%1;")
.arg(mdbPath);
HRESULT hr =
connection->dynamicCall("Open(QString,QString,QString,int)",connectString,"","",adConnectUnspecified).toInt(); /*连接到数据库*/
if(SUCCEEDED(hr))
{
QString orderStr = "";
if(sortField.length() != 0)
orderStr = QString(" order by %1 %2").arg(sortField).arg(asc ? "asc" : "desc");
QString sqlStr = QString("select * from %1 %2").arg(targetTable).arg(orderStr);
QAxObject *recordSet = connection->querySubObject("Execute(QString, QVariant&, int)", sqlStr);
if(recordSet)
{
while(!recordSet->property("EOF").toBool())
{
QAxObject * adoFields = recordSet->querySubObject("Fields");
if(adoFields)
{
int count = adoFields->property("Count").toInt();
for(int i = 0; i < count; i++)
{
QAxObject * adoField = adoFields->querySubObject("Item(int)",i);
if(adoField)
{
QString fieldName = adoField->property("Name").toString();
QVariant fieldValueVar = adoField->property("Value");
QString fieldValue = fieldValueVar.isValid() ? fieldValueVar.toString() : "";
QVector<QString> values;
if(data.contains(fieldName))
values = data.value(fieldName);
values.append(fieldValue);
data.insert(fieldName, values);
ADO_DELETE(adoField);
}
}
ADO_DELETE(adoFields);
}
//移动至下一条记录
recordSet->dynamicCall("MoveNext");
}
ADO_DELETE(recordSet);
}
connection->dynamicCall("Close");
delete connection;
connection = NULL;
OleUninitialize();
}
}
void NtAccessManager::delTabelDataByID(QString mdbPath , QString targetTable, QString condition)
{
HRESULT r = OleInitialize(0);
if (r != S_OK && r != S_FALSE) {
qWarning("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
}
QAxObject *connection = new QAxObject;
connection->setControl("ADODB.Connection");/*创建ADODB.Connection对象*/
connection->setProperty("ConnectionTimeout",300);/*设置超时时间确保连接成功*/
QString connectString = QString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%1;")
.arg(mdbPath);
HRESULT hr =
connection->dynamicCall("Open(QString,QString,QString,int)",connectString,"","",adConnectUnspecified).toInt(); /*连接到数据库*/
if(SUCCEEDED(hr))
{
//QString sqlStr = QString("select * from %1").arg(targetTable);
QString sqlStr = QString("DELETE FROM %1 where 样品编号 in (%2)").arg(targetTable).arg(condition);
QAxObject *recordSet = connection->querySubObject("Execute(QString, QVariant&, int)", sqlStr);
if(recordSet && !recordSet->property("EOF").toBool())
{
ADO_DELETE(recordSet);
}
connection->dynamicCall("Close");
delete connection;
connection = NULL;
OleUninitialize();
}
}
##下面是我用以上接口写的一个小程序：
####Access中的表：
####使用以上代码读取之后：
