Record the whole process of connecting to SQL Server database in detail
environment
- 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 ) GO ALTER TABLE [dbo].[LoginHistory_TB] SET (LOCK_ESCALATION = TABLE) GO -- ---------------------------- -- 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') GO 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') GO 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') GO 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') GO 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') GO 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') GO 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') GO 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') GO 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') GO 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') GO 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') GO 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') GO
2, Qt C + + code
1. Project catalogue
2. Engineering documents
QT -= gui
QT += sql core
CONFIG += c++11 console
CONFIG -= app_bundle
DEFINES += QT_DEPRECATED_WARNINGS
SOURCES += \
main.cpp
# 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
//main.cpp
#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();
if(ok)
{
qDebug()<<"DataBase open sucess!";
}
else {
qDebug()<<db.lastError();
}
//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")
.arg(ID).arg(LoginName).arg(LoginTime).arg(LogoutTime).arg(OPeration);
}
//Database shutdown
db.close();
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"