**Course Title:** Qt 6 Application Development with C++ **Section Title:** Integrating Databases with Qt **Topic:** Building a CRUD-based application with SQLite and displaying data in a table view.(Lab topic) **Overview** In this lab topic, you will learn how to create a CRUD (Create, Read, Update, Delete) application using SQLite as the database management system and display the data in a QTableView. You will apply the concepts and techniques learned in the previous topics to design and develop a fully functional application. **Step 1: Setting up the Project** Create a new Qt6 project in Qt Creator by following these steps: * Open Qt Creator and click on "File" -> "New File or Project" * Select "Application" under "Projects" and click "Next" * Choose "Qt Widgets Application" and click "Next" * Enter a project name, project location, and kit, and click "Finish" **Step 2: Creating the Database** Create a new SQLite database file named "db.sqlite" in the project directory. You can use a tool like DB Browser for SQLite (https://www.sqlitebrowser.org) to create and manage the database. Create a new table named "employees" with the following columns: | Column Name | Data Type | | --- | --- | | id | integer | | name | text | | email | text | | department | text | Insert some sample data into the employees table. **Step 3: Connecting to the Database** In Qt Creator, add the SQLite driver to the project by following these steps: * Open the project file (.pro) and add the following line: ``` QT += sql ``` * Run qmake by clicking on "Build" -> "Run qmake" Create a new class named "DatabaseManager" that inherits from QObject. This class will handle all database operations. ```cpp // databaseManager.h #ifndef DATABASEMANAGER_H #define DATABASEMANAGER_H #include <QObject> #include <QSqlDatabase> #include <QSqlQuery> class DatabaseManager : public QObject { Q_OBJECT public: explicit DatabaseManager(QObject *parent = nullptr); ~DatabaseManager(); void openDatabase(); void closeDatabase(); QSqlQueryModel* getEmployeesModel(); signals: public slots: private: QSqlDatabase m_db; QSqlQueryModel* m_employeesModel; }; #endif // DATABASEMANAGER_H ``` ```cpp // databaseManager.cpp #include "databaseManager.h" DatabaseManager::DatabaseManager(QObject *parent) : QObject(parent) { m_employeesModel = new QSqlQueryModel(this); } DatabaseManager::~DatabaseManager() { delete m_employeesModel; } void DatabaseManager::openDatabase() { m_db = QSqlDatabase::addDatabase("QSQLITE"); m_db.setDatabaseName("db.sqlite"); m_db.open(); } void DatabaseManager::closeDatabase() { m_db.close(); } QSqlQueryModel* DatabaseManager::getEmployeesModel() { QSqlQuery query(m_db); query.prepare("SELECT * FROM employees"); query.exec(); m_employeesModel->setQuery(query); return m_employeesModel; } ``` **Step 4: Displaying Data in QTableView** Create a new class named "MainWindow" that inherits from QMainWindow. This class will handle the UI and display the data in a QTableView. ```cpp // mainWindow.h #ifndef MAINWINDOW_H #define MAINWINDOW_H #include <QMainWindow> #include <QTableView> #include <DatabaseManager> namespace Ui { class MainWindow; } class MainWindow : public QMainWindow { Q_OBJECT public: explicit MainWindow(QWidget *parent = nullptr); ~MainWindow(); public slots: void displayData(); private: Ui::MainWindow *ui; DatabaseManager* m_dbManager; QTableView* m_tableView; }; #endif // MAINWINDOW_H ``` ```cpp // mainWindow.cpp #include "mainWindow.h" MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow) { ui->setupUi(this); m_dbManager = new DatabaseManager(this); m_tableView = new QTableView(ui->centralWidget); ui->horizontalLayout->addWidget(m_tableView); } MainWindow::~MainWindow() { delete ui; } void MainWindow::displayData() { m_dbManager->openDatabase(); QSqlQueryModel* model = m_dbManager->getEmployeesModel(); m_tableView->setModel(model); m_dbManager->closeDatabase(); } ``` **Step 5: Creating CRUD Operations** Add CRUD operations to the DatabaseManager class. ```cpp // databaseManager.h #ifndef DATABASEMANAGER_H #define DATABASEMANAGER_H #include <QObject> #include <QSqlDatabase> #include <QSqlQuery> class DatabaseManager : public QObject { Q_OBJECT public: explicit DatabaseManager(QObject *parent = nullptr); ~DatabaseManager(); void openDatabase(); void closeDatabase(); QSqlQueryModel* getEmployeesModel(); void addEmployee(const QString& name, const QString& email, const QString& department); void deleteEmployee(int id); void updateEmployee(int id, const QString& name, const QString& email, const QString& department); signals: public slots: private: QSqlDatabase m_db; QSqlQueryModel* m_employeesModel; }; #endif // DATABASEMANAGER_H ``` ```cpp // databaseManager.cpp #include "databaseManager.h" // ... void DatabaseManager::addEmployee(const QString& name, const QString& email, const QString& department) { QSqlQuery query(m_db); query.prepare("INSERT INTO employees (name, email, department) VALUES (:name, :email, :department)"); query.bindValue(":name", name); query.bindValue(":email", email); query.bindValue(":department", department); query.exec(); } void DatabaseManager::deleteEmployee(int id) { QSqlQuery query(m_db); query.prepare("DELETE FROM employees WHERE id = :id"); query.bindValue(":id", id); query.exec(); } void DatabaseManager::updateEmployee(int id, const QString& name, const QString& email, const QString& department) { QSqlQuery query(m_db); query.prepare("UPDATE employees SET name = :name, email = :email, department = :department WHERE id = :id"); query.bindValue(":id", id); query.bindValue(":name", name); query.bindValue(":email", email); query.bindValue(":department", department); query.exec(); } ``` **Conclusion** In this lab topic, you have created a CRUD-based application using SQLite as the database management system and displayed the data in a QTableView. You have applied the concepts and techniques learned in the previous topics to design and develop a fully functional application. **What's Next?** In the next topic, we will introduce multithreading in Qt with QThread. **Do you have any questions or need further clarification?** Please leave a comment below or ask for help if you have any questions or need further clarification on any of the topics covered in this lab topic.