QtDbfHelper.h文件
#pragma once
#include <QObject>
#include <qsql.h>
#include <qsqldatabase.h>
#include <qsqlquerymodel.h>
#include <qsqlrecord.h>
#include <qsqlquery.h>
#include <qsqlfield.h>
class QtDbfHelper : public QObject
{
Q_OBJECT
private:
QSqlDatabase m_dbconn;
public:
QtDbfHelper(QObject *parent = nullptr);
~QtDbfHelper();
public:
bool ConnectDB(const QString& dbFilePath);//dbFilePath指的是dbf所在路径,而不是.dbf文件
QSqlQueryModel* QueryModel(const QString& tabelName);//获取整个table数据模型
QSqlQueryModel* ExecuteScalar(const QString& sql); //sql 查询 数据操作
bool ExecuteNonQuery(const QString& sql); //sql 增,删,改 数据操作
bool ExistsTable(const QString& tableName);
};
QtDbfHelper.cpp文件
#include "QtDbfHelper.h"
QtDbfHelper::QtDbfHelper(QObject *parent): QObject(parent)
{
}
QtDbfHelper::~QtDbfHelper()
{
if (m_dbconn.isValid() && m_dbconn.isOpen())
{
m_dbconn.close();
}
}
bool QtDbfHelper::ConnectDB(const QString& dbFilePath)
{
bool bResult = false;
//获取是否存在有数据库连接
m_dbconn = QSqlDatabase::database(dbFilePath);
if (m_dbconn.isValid())//存在连接直接退出
{
bResult = true;
}
else
{
//创建dbFilePath路径数据库连接
m_dbconn = QSqlDatabase::addDatabase("QODBC", dbFilePath);
QString connStr = QString("Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=%1;Exclusive=No;").arg(dbFilePath);
m_dbconn.setDatabaseName(connStr);
bResult = m_dbconn.open();
}
return bResult;
}
QSqlQueryModel* QtDbfHelper::QueryModel(const QString& tabelName)
{
QSqlQueryModel* pQueryModel = nullptr;
if (m_dbconn.isOpen())
{
pQueryModel = new QSqlQueryModel;
pQueryModel->setQuery(QString("SELECT * FROM %1;").arg(tabelName), m_dbconn);
}
return pQueryModel;
}
QSqlQueryModel* QtDbfHelper::ExecuteScalar(const QString& sql)
{
QSqlQueryModel* pQueryModel = nullptr;
if (m_dbconn.isOpen())
{
QSqlQuery* query = new QSqlQuery(m_dbconn);
if (query)
{
bool bResult = query->exec(sql);
if(bResult)
{
pQueryModel = new QSqlQueryModel;
pQueryModel->setQuery(*query);
}
query->clear();
}
}
return pQueryModel;
}
bool QtDbfHelper::ExecuteNonQuery(const QString& sql)
{
bool bResult = false;
if (m_dbconn.isOpen())
{
QSqlQuery* query = new QSqlQuery(m_dbconn);
if (query)
{
bResult = query->exec(sql);
query->clear();
}
}
return bResult;
}
bool QtDbfHelper::ExistsTable(const QString& tableName)
{
bool bResult = false;
if (m_dbconn.isOpen())
{
bResult = m_dbconn.tables().contains(tableName);
}
return bResult;
}
测试代码
#include "QtDbfHelper.h"
//备注:*.dbf文件放到"E:/QtDbfTest"路径下(程序生成的.exe执行文件路径)
QString szDirPath = QCoreApplication::applicationDirPath();; //dbf文件目录
QtDbfHelper dbfHelper;
bool res = dbfHelper.ConnectDB(szDirPath);
//查询操作测试
void QtPro::on_btnDbfQuery_clicked()
{
//对E:/QtDbfTest/config.dbf数据库操作
QString sql = QString("SELECT * FROM config WHERE NO < 5");
QSqlQueryModel* pQueryModel = dbfHelper.ExecuteScalar(sql);
if (pQueryModel == nullptr)return;
QTableView* tableView = new QTableView;
tableView->setWindowTitle(sql);
tableView->setModel(pQueryModel);
tableView->resize(800, 300);
tableView->show();
return;
//以下是具体值获取
QString strQuery;
for (int row = 0; row < pQueryModel->rowCount(); row++)
{
QSqlRecord record = pQueryModel->record(row);
for (int col = 0; col < record.count(); col++)
{
strQuery += record.value(col).toString() + " ";
}
strQuery += "\r\n";
}
QMessageBox::information(this, "Query Data", strQuery);
}
//插入操作测试
void QtPro::on_btnDbfInsert_clicked()
{
dbfHelper.ExecuteNonQuery(QString("create table %1/student(id num(6), name char(20));").arg(szDirPath));
dbfHelper.ExecuteNonQuery(QString("insert into student values(0,'LiMing')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(1,'LiuTao')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(2,'WangHong')"));
QString sql = QString("select * from student");
QSqlQueryModel* pQueryModel = dbfHelper.ExecuteScalar(sql);
if (pQueryModel == nullptr)return;
QString strQuery;
for (int row = 0; row < pQueryModel->rowCount(); row++)
{
QSqlRecord record = pQueryModel->record(row);
for (int col = 0; col < record.count(); col++)
{
strQuery += record.value(col).toString() + " ";
}
strQuery += "\r\n";
}
QMessageBox::information(this, "Query Data", strQuery);
pQueryModel->clear(); //注意:不进行clear操作 会导致下一次的某些操作失败,比如删除表格
}
//删除操作测试
void QtPro::on_btnDbfDelete_clicked()
{
if (dbfHelper.ExistsTable("student"))
{
dbfHelper.ExecuteNonQuery(QString("drop table %1/student").arg(szDirPath));
}
dbfHelper.ExecuteNonQuery(QString("create table %1/student(id num(6), name char(20));").arg(szDirPath));
dbfHelper.ExecuteNonQuery(QString("insert into student values(0,'LiMing')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(1,'LiuTao')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(2,'WangHong')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(3,'ADFASFASDF')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(4,'ASDFDS')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(5,'SADFDSF')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(6,'ASDFDASFWE')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(7,'EFEFEWAF')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(8,'ASDFASDFDSAF')"));
//删除
dbfHelper.ExecuteNonQuery(QString("delete from student where id >5"));
QString sql = QString("select * from student");
QSqlQueryModel* pQueryModel = dbfHelper.ExecuteScalar(sql);
if (pQueryModel == nullptr)return;
QString strQuery;
for (int row = 0; row < pQueryModel->rowCount(); row++)
{
QSqlRecord record = pQueryModel->record(row);
for (int col = 0; col < record.count(); col++)
{
strQuery += record.value(col).toString() + " ";
}
strQuery += "\r\n";
}
QMessageBox::information(this, "Query Data", strQuery);
pQueryModel->clear(); //注意:不进行clear操作 会导致下一次的某些操作失败,比如删除表格
}
//修改操作测试
void QtPro::on_btnDbfModify_clicked()
{
if (dbfHelper.ExistsTable("student"))
{
dbfHelper.ExecuteNonQuery(QString("drop table %1/student").arg(szDirPath));
}
dbfHelper.ExecuteNonQuery(QString("create table %1/student(id num(6), name char(20));").arg(szDirPath));
dbfHelper.ExecuteNonQuery(QString("insert into student values(0,'LiMing')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(1,'LiuTao')"));
dbfHelper.ExecuteNonQuery(QString("insert into student values(2,'WangHong')"));
//改
dbfHelper.ExecuteNonQuery(QString("update student set name = 'aaaaaaaaaaa' where id = 0"));
dbfHelper.ExecuteNonQuery(QString("update student set name = 'bbbbbbbbbbb' where id = 1"));
dbfHelper.ExecuteNonQuery(QString("update student set name = 'ccccccccccc' where id = 2"));
QString sql = QString("select * from student");
QSqlQueryModel* pQueryModel = dbfHelper.ExecuteScalar(sql);
if (pQueryModel == nullptr)return;
QString strQuery;
for (int row = 0; row < pQueryModel->rowCount(); row++)
{
QSqlRecord record = pQueryModel->record(row);
for (int col = 0; col < record.count(); col++)
{
strQuery += record.value(col).toString() + " ";
}
strQuery += "\r\n";
}
QMessageBox::information(this, "Query Data", strQuery);
pQueryModel->clear(); //注意:不进行clear操作 会导致下一次的某些操作失败,比如删除表格
}