Qt connect and use SQL Sever database

Posted by drawmack on Tue, 08 Mar 2022 03:11:55 +0100

Record the whole process of connecting to SQL Server database in detail


  • System: Windows 10 professional
  • Qt version: Qt 5.14.2 [compilation kit MinGW 32]
  • SQL Sever: SQL Sever 2019
  • Programming language: C++

1, Configuring ODBC data sources

1, Add a new user in SQL Sever Manger

1. Open the MSS Management Studio provided below

2. Select Windows authentication when logging in

3. Go to security - login name. Right click to create a new login name

4. In general, select SQL server authentication and enter a user-defined user name and password;

[you can select which database you want to use by default]

5. In user mapping, select the database and user name to be mapped

[ensure that db_owner is checked, otherwise the database can be connected, but the operation of adding, deleting, modifying and querying the database cannot be performed]

6. In the status, select the following options to grant the enabled status

7. Test connections in Navicat

In the connection, enter [host name] [initialization database can be ignored] as follows:
[if you don't know the host name, you can see record 8]

Connection successful

  • If the connection fails [you can try to reset czy's password (which can be the original password)]

8. Host name acquisition

Find the following software in the start button

You can see the server host as follows

If not
Refer to [Article 9] for the following configuration

9. Configure host

1, In SQL server network configuration, ensure that TCP/IP is enabled, and the parameters can be set by default

Double click TCP/IP to view the IP address and port of the server in the IP address bar. Of course, you can also configure the port. I choose default

2, Create a new alias in SQL Native Client configuration,

I created an alias for localhost

3, Test connections in Navicat

2, Configuring ODBC data sources

1. Open ODBC data source

2. Add SQL server data source

The configuration is shown in the figure below

3. Configuration data source description

Select the localhost server we just created

4. Select user name to log in

Use the czy account you just created

If there is no change, go to the next step

5. Test data source

Data source test succeeded

6. Browse the created data source [czyTest]

2, Database connection

1, Database sql code [including creating tables and inserting data]

[some codes may need to be deleted, please delete them by yourself]

-- ----------------------------
-- Table structure for LoginHistory_TB
-- ----------------------------
CREATE TABLE [dbo].[LoginHistory_TB] (
  [ID] int  NULL,
  [LoginName] nvarchar(254) COLLATE Chinese_PRC_CI_AS  NULL,
  [LoginTime] nvarchar(254) COLLATE Chinese_PRC_CI_AS  NULL,
  [LogoutTime] nvarchar(254) COLLATE Chinese_PRC_CI_AS  NULL,
  [Operation] int  NULL


-- ----------------------------
-- Records of LoginHistory_TB
-- ----------------------------
INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'1', N'Wei Xue', N'two thousand and twenty-one/1/8-08:32:49', N'two thousand and twenty-one/1/8-12:32:49', N'1111')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'2', N'Jiaguli', N'two thousand and twenty-one/1/8-08:32:50', N'two thousand and twenty-one/1/8-12:32:50', N'11')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'3', N'Mu CAI', N'two thousand and twenty-one/1/8-08:32:61', N'two thousand and twenty-one/1/8-12:32:51', N'1')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'4', N'Zu Yan', N'two thousand and twenty-one/1/8-08:32:62', N'two thousand and twenty-one/1/8-12:32:52', N'111')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'5', N'Fu Zhixiang', N'two thousand and twenty-one/1/8-08:32:53', N'two thousand and twenty-one/1/8-12:32:53', N'111')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'6', N'Jin Xiaobo', N'two thousand and twenty-one/1/8-08:35:54', N'two thousand and twenty-one/1/8-12:32:54', N'1')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'7', N'Guzai Yuyuan', N'2021/1/8-08:35:55', N'2021/1/8-12:32:55', N'1111')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'8', N'Ji Yun', N'2021/1/8-08:32:56', N'2021/1/8-12:32:56', N'1')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'9', N'Jiaguli', N'2021/1/8-08:32:57', N'2021/1/8-12:32:57', N'1')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'10', N'Mu CAI', N'2021/1/8-08:32:58', N'2021/1/8-12:32:58', N'1111')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'11', N'Zu Yan', N'2021/1/8-08:32:59', N'2021/1/8-12:32:59', N'11')

INSERT INTO [dbo].[LoginHistory_TB] ([ID], [LoginName], [LoginTime], [LogoutTime], [Operation]) VALUES (N'12', N'Zu Yan', N'2021/1/8-08:33:00', N'2021/1/8-12:32:00', N'1111')

2, Qt C + + code

1. Project catalogue

2. Engineering documents

QT -= gui
QT += sql core

CONFIG += c++11 console
CONFIG -= app_bundle



# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target

3. Main function file

#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>

int main(int argc, char *argv[])
    //Database configuration
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setHostName("localhost");         // Server name
    db.setDatabaseName("czyTest");       // Data source name
    db.setUserName("czy");               // user name
    db.setPassword("202128you");         // password

    //Database connection
    bool ok = db.open();
        qDebug()<<"DataBase open sucess!";
    else {

    //Database query
    QSqlQuery query("SELECT * FROM LoginHistory_TB;");
    qDebug()<<"ID     LoginName        LoginTime                LogoutTime        OPeration";
    while (query.next()) 
        int ID = query.value(0).toInt();
        QString LoginName = query.value(1).toString();
        QString LoginTime = query.value(2).toString();
        QString LogoutTime = query.value(3).toString();
        int OPeration = query.value(4).toInt();

        qDebug()<<QString("%1       %2       %3         %4      %5")

    //Database shutdown
    return 0;

4. Operation results

DataBase open sucess!
ID     LoginName        LoginTime                LogoutTime        OPeration
"1       Wei Xue       2021/1/8-08:32:49         2021/1/8-12:32:49      1111"
"2       Jiaguli       2021/1/8-08:32:50         2021/1/8-12:32:50      11"
"3       Mu CAI       2021/1/8-08:32:61         2021/1/8-12:32:51      1"
"4       Zu Yan       2021/1/8-08:32:62         2021/1/8-12:32:52      111"
"5       Fu Zhixiang       2021/1/8-08:32:53         2021/1/8-12:32:53      111"
"6       Jin Xiaobo       2021/1/8-08:35:54         2021/1/8-12:32:54      1"
"7       Guzai Yuyuan       2021/1/8-08:35:55         2021/1/8-12:32:55      1111"
"8       Ji Yun       2021/1/8-08:32:56         2021/1/8-12:32:56      1"
"9       Jiaguli       2021/1/8-08:32:57         2021/1/8-12:32:57      1"
"10       Mu CAI       2021/1/8-08:32:58         2021/1/8-12:32:58      1111"
"11       Zu Yan       2021/1/8-08:32:59         2021/1/8-12:32:59      11"
"12       Zu Yan       2021/1/8-08:33:00         2021/1/8-12:32:00      1111"

Topics: Database SQL Server Qt5 SQL