Qt+SqlServer数据库编程

参考网上的许多文章学习Qt数据库编程，不一一给出链接。

在项目.pro中添加"QT+=sql"，VS插件选择Sql模块。

1．数据库驱动

#include <QApplication>
#include "mainwindow.h"
#include <QDebug>
#include <QSqlDatabase>

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    MainWindow w;
    w.show();

    qDebug() << "Available drivers: ";
    QStringList drivers = QSqlDatabase::drivers();
    foreach (QString driver, drivers)
    {
        qDebug() << driver;
    }
    QSqlDatabase dbc = QSqlDatabase::addDatabase("QODBC");
    qDebug() << "ODBC driver valid? " << dbc.isValid();

    return app.exec();
}

如果输出以下内容，表示ODBC驱动可用：

Available drivers: 
"QSQLITE" 
"QODBC3" 
"QODBC" 
ODBC driver valid? True

2．连接数据库

QSqlDatabase类实现数据库连接

bool OpenDatabase()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName(QString("DRIVER={SQL SERVER};"
                               "SERVER=%1;"
                               "DATABASE=%2;"
                               "UID=%3;"
                               "PWD=%4;"
                               
                               //如果用主机名，对于express版本数据库，即是VS自带的数据库，要加\SQLEXPRESS后缀，如MYPC\SQLEXPRESS
                               //如果用ip地址，加上端口号，如192.168.1.1, 1433
                               ).arg("pc")
                       .arg("db")
                       .arg("sa")
                       .arg("sa")
                       );
    if (!db.open())
    {
        qDebug() << "Error: " << db.lastError().text();
        return false;
    }
    return true;
}

3．执行SQL语句

QSqlQuery类执行SQL语句

void ExecQuery()
{
    QSqlQuery query;
    QString str = QString("select * from table");
    
    if (query.exec(str))
    {
        //执行完query.exec()时，query是指向结果集以外的，利用query.next()，query便指向了结果集的第一条记录。
        //也可以在query.exec()前加上query.setForwardOnly(true)，此后只能使用next()和seek()。
        while(query.next())
        {
            //获取每条记录中属性（即列）的个数
            int columnCount = query.record().count();
            //获取name属性所在列的编号，列从左向右编号，最左边的编号为0
            int nameIndex = query.record().indexOf("name");
            //获取id属性的值，并转换为int型
            int id = query.value(0).toInt();
            //获取name属性的值
            QString name = query.value(nameIndex).toString();
            
            qDebug() << " id is : " << id
                     << " name is : " << name
                     << " columnCount is : " << columnCount;
        }
    }
}

addDatabase原型: QSqlDatabase * QSqlDatabase::addDatabase ( const QString & type, const QString & connectionName = defaultConnection ) 

在QSqlDatabase::addDatabase("QODBC")后，query找到defaultConnection ，根据defaultConnection执行SQL语句。

有可能出现QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.错误，就是defaultConnection的原因，可以通过下面的代码使用addDatabase

QSqlDatabase db;
if (QSqlDatabase::contains("qt_sql_default_connection"))
{
    db = QSqlDatabase::database("qt_sql_default_connection");
}
else
{
    db = QSqlDatabase::addDatabase("QODBC");
}

4．执行其它SQL语句

void InsertQuery()
{
    //设置字符编码
    QTextCodec::setCodecForTr(QTextCodec::codecForLocale());

    //单一插入数据
    QSqlQuery query;
    query.prepare("insert into course (cno, cname, credit, semester) values (:cno, :cname, :credit, :semester)");
    query.bindValue(":cno", "c1005");
    query.bindValue(":cname", tr("数学"));
    query.bindValue(":credit", 6);
    query.bindValue(":semester", 9);
    query.exec();

    //批量插入数据
    query.prepare("insert into course values (?, ?, ?, ?)");

    QVariantList cnos;
    cnos << "c1" << "c2" << "c3" << "c4";
    query.addBindValue(cnos);

    QVariantList cnames;
    cnames << tr("物理") << tr("化学") << tr("历史") << tr("地理");
    query.addBindValue(cnames);

    QVariantList credits;
    credits << 9 << 9 << 9 << 9;
    query.addBindValue(credits);

    QVariantList semesters;
    semesters << 12 << 12 << 12 << 12;
    query.addBindValue(semesters);

    if (!query.execBatch())
    {
        qDebug() << query.lastError();
    }
}

void UpdateAndDelete()
{
    //设置字符编码
    QTextCodec::setCodecForTr(QTextCodec::codecForLocale());

    QSqlQuery query;
    query.prepare("update course set credit = ? where cname = ?");
    query.bindValue(0, 10);
    query.bindValue(1, tr("物理"));
    query.exec();

    query.prepare("delete from course where cname = ?");
    query.bindValue(0, tr("化学"));
    if (!query.exec())
    {
        qDebug() << query.lastError();
    }
}

5．QSqlQueryModel 只读模式，基于SQL查询

void ExecQueryMode()
{
    QSqlQueryModel model;
    model.setQuery("SELECT * FROM course");

    for (int i = 0; i < model.rowCount(); ++i)
    {
        QString cno = model.record(i).value("cno").toString();
        QString cname = model.record(i).value("cname").toString();
        qDebug() << cno << cname;
    }
}

6．QSqlTableModel 可进行读写

void ExecTableMode()
{
    //设置字符编码
    QTextCodec::setCodecForTr(QTextCodec::codecForLocale());

    QSqlTableModel model;
    model.setTable("course");
    model.select();
    for (int i = 0; i < model.rowCount(); ++i)
    {
        QString cno = model.record(i).value("cno").toString();
        QString cname = model.record(i).value("cname").toString();
        qDebug() << cname << cno;
    }

    for (int i = 0; i < model.rowCount(); ++i)
    {
        QSqlRecord record = model.record(i);
        int credit = record.value("credit").toInt();
        credit -= 10;
        record.setValue("credit", credit);
        model.setRecord(i, record);
    }
    model.submitAll();

    int row = 3;
    model.insertRows(row, 1);  //第3行数据后插入一条数据
    model.setData(model.index(row, 0), "c1013");
    model.setData(model.index(row, 1), tr("数学"));
    model.setData(model.index(row, 2), 9);
    model.setData(model.index(row, 3), 12);
    model.submitAll();

    model.removeRows(8, 1);  //第8行数据后删除一条数据
    model.submitAll();
}

7．QSqlQueryModel 结合QTableView显示数据

void ShowQueryView()
{
    QSqlQueryModel *model = new QSqlQueryModel;
    model->setQuery("SELECT * FROM course");

    //设置标题头部标签信息
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("Cno"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Cname"));
    model->setHeaderData(2, Qt::Horizontal, QObject::tr("Credit"));
    model->setHeaderData(3, Qt::Horizontal, QObject::tr("Semester"));
    //借助QTableView，把数据信息显示出来,
    ui->tableView->setModel(model);
}

8．获取记录总数

void GetRows()
{
    QSqlQuery query;
    QSqlDatabase db;
    int rows;
    if (query.exec("select * from course"))
    {
        while (query.next())
        {
            if (db.driver()->hasFeature(QSqlDriver::QuerySize))
            {
                rows = query.size();
            }
            else
            {
                query.last();
                rows = query.at() + 1;
            }
        }
    }
    qDebug() << rows;
}