Qt操作dbf数据库

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操作 会导致下一次的某些操作失败,比如删除表格
}