C++、Qt操作Access数据库

最新推荐文章于 2024-07-18 15:07:10 发布

好儿郎-志在四方

最新推荐文章于 2024-07-18 15:07:10 发布

阅读量8k

点赞数 5

分类专栏：编程技巧 Qt编程文章标签： Qt C++ Access 数据库代码

本文链接：https://blog.csdn.net/rl529014/article/details/79305372

版权

Qt编程同时被 2 个专栏收录

109 篇文章 93 订阅

订阅专栏

编程技巧

59 篇文章 11 订阅

订阅专栏

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中的表：
这里写图片描述