Sohag Developer PostgreSQL Database Applications

Build Powerful Robust Database Applications

Basic Template Part 2

Sat, 08/11/2018 - 11:50 - amr

Basic Generator (Continue).
Basic Template Part 1
3- Qt UI file (.ui file) for every class or database table or view.In this topic we will explain
The Qt UI file is an XML formatted file that Qt Designer UI uses to represent the widget components.
Sohag Developer's Basic generator generate this file for every class (Database table or view) in the project with following specifications
a- The file name is the same as class name but with all lower case letters.
b- A label is generated corresponding to every column in the database table or view (the label name is the same as table column name with additional (L) at the end).
c- A widget is generated according to the user selection in the class wizard with the same name generated by the class wizard .
d- The Basic generator adds the class name, widget name and window title the same as class name.
e- All widgets and corresponding labels are put in a QGroupBox container and layout in a QGridLayout.
f- Finally all the form is layout in a QVBoxLayout.

4- C++ header file (.h) for every class or database table or view.
This is the class header file & it contains the definitions.


#ifndef CUSTOMERS_H
#define CUSTOMERS_H

#include 

namespace Ui {
class Customers;
}

class Customers : public QWidget
{
    Q_OBJECT

public:
    explicit Customers(QWidget *parent = 0);
    ~Customers();

private:
    Ui::Customers *ui;
    bool insert();
	bool update();
	bool remove();
	bool query();
    QVariant toVarinat(QString string);
    QString toString(QVariant value);
private slots:
    void clear();
};

#endif // CUSTOMERS_H

5- C++ implementation file (.cpp file) for every class or database table or view.
This file contains the implementation of the class code
The file name is the same as class name but all letters are in lower case.
1- void Customers::clear().
Clear method clears all widgets to empty values you may use it after delete, cancel and initialization operations.

2- QVariant Customers::toVarinat(QString string).
This method converts empty Qstring value to null to store it as null in the database since empty QString is a different value than null .

3- QString Customers::toString(QVariant value)
Qt uses the Qvariant class as a container that receives all database data types and then these data types can be casted according to the widgets that will receive them
, Widgets like Qlabel or QlinEdit displays Qstring (text data) but the returned value from database can be of numeric data types and if this value is null the QString value may be 0 Zero (This problem does'nt exist on all Qt versions)
so using toString method returns an empty QString if the database value is null otherwise it will return QString the represents the numeric database value with its corresponding format in Qt.

The coming section include the methods that are responsable of executing the DML SQL operations (Insert, Update, Delete and Query)
And all the methods uses prepared quires achieve both best database performance and ensure security by preventing sql injections.

4- bool Customers::insert()
Insert method uses prepared query to insert a new record in the database table or view (Note that inserting data into view depends on a several parameters related to the view and the database).
Binding values using positional binding then executing the query and finally the method returns true if SQL query executed without errors otherwise it will return false.

4- bool Customers::insert()
Insert method uses prepared query to insert a new record in the database table or view (Note that inserting data into view depends on a several parameters related to the view and the database).
Binding values using positional binding then executing the query and finally the method returns true if SQL query executed without errors otherwise it will return false.

5- bool Customers::update()
Update method uses prepared query to update record in the database table or view (Note that updating data in a view depends on a several aspects related to the view and the database).
Binding values using positional binding then executing the query and finally the method returns true if SQL query executed without errors otherwise it will return false.
Note that Sohag Developer will detect the primary key column automatically and add a condition in the update statement according to the primary key to ensure that only the required record will be updated, If the table doesn't have a primary key you have to edit the update statement to add the condition (It's always good practice to add a primary key in you database table design).

5- bool Customers::remove()
Remove method uses prepared query to delete a record from the database table or view (Note that deleting data from a view depends on a several aspects related to the view and the database).
Binding primary key value using positional binding then executing the query and finally the method returns true if SQL query executed without errors otherwise it will return false.
Note that Sohag Developer will detect the primary key column automatically and add a condition in the delete statement according to the primary key to ensure that only the required record will be removed, If the table doesn't have a primary key you have to edit the delete statement to add the condition (It's always good practice to add a primary key in you database table design).

6- bool Customers::query()
Query method is used to retrieve a database table or view record and displays the returned values to the widgets corresponding to database columns.
Also this method uses the primary key to retrieve the required record if the condition is omitted or Sohag Developer didn't find a primary key the first record only of returned data result will be displayed
This function returns true if SQL query executed without errors otherwise it will return false.


#include "customers.h"
#include "ui_customers.h"
#include 
#include 

Customers::Customers(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Customers)
{
    ui->setupUi(this);
}

Customers::~Customers()
{
    delete ui;
}


void Customers::clear()
{
	ui->cust_id->clear();
	ui->customer->clear();
	ui->address->clear();
	ui->phone->clear();
	ui->mobile->clear();
	ui->email->clear();
	ui->fax->clear();
	ui->notes->clear();
	
}

QVariant Customers::toVarinat(QString string)
{
    if (string.isEmpty()){
        return QVariant();
    }
    return QVariant(string);
}

QString Customers::toString(QVariant value)
{
	int valueType=value.type();
    if(value.isNull()){
        return QString();
    }else if(valueType==QMetaType::Int){
        return QString::number(value.toInt());
    }else if(valueType==QMetaType::UInt){
        return QString::number(value.toUInt());
    }else if(valueType==QMetaType::Double){
        return QString::number(value.toDouble());
    }else if(valueType==QMetaType::Long){
        return QString::number(value.toLongLong());
    }else if(valueType==QMetaType::LongLong){
        return QString::number(value.toLongLong());
    }else if(valueType==QMetaType::Short){
        return QString::number(value.toInt());
    }else if(valueType==QMetaType::ULong){
        return QString::number(value.toULongLong());
    }else if(valueType==QMetaType::ULongLong){
        return QString::number(value.toULongLong());
    }else if(valueType==QMetaType::UShort){
        return QString::number(value.toUInt());
    }else if(valueType==QMetaType::Float){
        return QString::number(value.toFloat());
    }
    return value.toString();
}

bool Customers::insert()
{
    QSqlQuery query;
    query.prepare("insert into cust.customers(cust_id,customer,address,phone,mobile,email,fax,notes) values (?,?,?,?,?,?,?,?)");
	query.addBindValue(toVarinat(ui->cust_id->text()));
	query.addBindValue(toVarinat(ui->customer->text()));
	query.addBindValue(toVarinat(ui->address->text()));
	query.addBindValue(toVarinat(ui->phone->text()));
	query.addBindValue(toVarinat(ui->mobile->text()));
	query.addBindValue(toVarinat(ui->email->text()));
	query.addBindValue(toVarinat(ui->fax->text()));
	query.addBindValue(toVarinat(ui->notes->text()));


    query.exec();
    if(query.lastError().isValid()){

        return false;
    }
    return true;
}


bool Customers::update()
{
    QSqlQuery query;
    query.prepare("UPDATE cust.customers SET cust_id=?,customer=?,address=?,phone=?,mobile=?,email=?,fax=?,notes=? WHERE cust_id=?");
	query.addBindValue(toVarinat(ui->cust_id->text()));
	query.addBindValue(toVarinat(ui->customer->text()));
	query.addBindValue(toVarinat(ui->address->text()));
	query.addBindValue(toVarinat(ui->phone->text()));
	query.addBindValue(toVarinat(ui->mobile->text()));
	query.addBindValue(toVarinat(ui->email->text()));
	query.addBindValue(toVarinat(ui->fax->text()));
	query.addBindValue(toVarinat(ui->notes->text()));


	query.addBindValue(ui->cust_id->text());


    query.exec();
    if(query.lastError().isValid()){

        return false;
    }
    return true;
}




bool Customers::remove()
{
    QSqlQuery query;
    query.prepare("delete from cust.customers WHERE cust_id=?");

	query.addBindValue(ui->cust_id->text());


    query.exec();
    if(query.lastError().isValid()){

        return false;
    }
    return true;
}


bool Customers::query()
{
    QSqlQuery query;
    query.prepare("select cust_id,customer,address,phone,mobile,email,fax,notes from cust.customers WHERE cust_id=?");

	query.addBindValue(ui->cust_id->text());


    query.exec();
    if(query.lastError().isValid()){

        return false;
    }
    query.first();
	ui->cust_id->setText(toString(query.value(0)));
	ui->customer->setText(toString(query.value(1)));
	ui->address->setText(toString(query.value(2)));
	ui->phone->setText(toString(query.value(3)));
	ui->mobile->setText(toString(query.value(4)));
	ui->email->setText(toString(query.value(5)));
	ui->fax->setText(toString(query.value(6)));
	ui->notes->setText(toString(query.value(7)));
    return true;
}

Basic Template Part 1

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.