Sohag Developer PostgreSQL Database Applications

Build Powerful Robust Database Applications

Simple Template Part 2

Sat, 08/11/2018 - 12:09 - amr

Simple Generator (Continue).
Simple 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 Simple 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 with .h extention at the end..
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 laid out in a QGridLayout.
f- A set of control buttons added to the form to control the form actions (Query, New, Delete, Save, Close).
g- Control buttons are laid out in QHBoxLayout.
h- 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.


*************************************************************************
**
** GENERATED BY SOHAG DEVELOPER
** Visit Us: http://sohag-developer.com/
**
****************************************************************************/


#ifndef CUSTOMERS_H
#define CUSTOMERS_H

#include <QWidget>
#include <QSqlDatabase>

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();

    enum {CreateRecord,EditRecord,DeleteRecord,ChangedStatus,UnChangedStatus,InsertOperation,
          UpdateOperation,RemoveOperation,QueryOperation,SaveOperation};
    QSqlDatabase db;
    int mode; //
    int status;
    void error(QString code,QString errorText,int operation);
    QVariant toVarinat(QString string);
	QString toString(QVariant value);
private slots:
    void clear();
    void save();
    void newRecord();
    void setStatusChanged();
    bool remove();
    bool query();
};

#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 with .cpp extention at the end.

1-void Customers::newRecord().
Initialize the form to receive a new record data by clearing all widgets data (Items related to database table columns),starting new transaction and setting variables to indicate new record and unchanged record status.

2-void Customers::save().
a- Save method checks the record status if changed it will insert or update the record depinding on the operation mode.
b- Commits changes to the database.
c- Changes the mode to suit the next operation.
If the operation was create or edit the next operation will be edit which allows the user to edit the data and the next save will execute the update methos, If the operation was remove (Deleting record) the new operation will be create new record which prepares the form to receive a new record.
d- If save opeartion succeeded a new database transaction starts otherwise an error will be raised.

3- void Customers::clear().
Clear method clears all widgets to empty values you may use it after delete, cancel and initialization operations.

4- Void Customers::setStatusChanged().
Change the flag variable status to the value ChangedStatus which indicates that the form data has been changed.

5- void Customers::error(QString code, QString errorText, int operation)
Used to show a message box that indicats the error data.

6- 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 .

7- 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 that 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.

8- 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.

9- 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.

10- 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).

11- 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).

12- 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.


/****************************************************************************
**
** GENERATED BY SOHAG DEVELOPER 
** Visit Us: http://sohag-developer.com/
**
****************************************************************************/


#include "customers.h"
#include "ui_customers.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QMessageBox>


Customers::Customers(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Customers)
{
    ui->setupUi(this);
	
    db = QSqlDatabase::database();
    
    newRecord();
    connect(ui->saveButton,SIGNAL(pressed()),this,SLOT(save()));
    connect(ui->newButton,SIGNAL(pressed()),this,SLOT(newRecord()));
    connect(ui->deleteButton,SIGNAL(pressed()),this,SLOT(remove()));
    connect(ui->queryButton,SIGNAL(pressed()),this,SLOT(query()));
    connect(ui->closeButton,SIGNAL(pressed()),this,SLOT(close()));
    
	connect(ui->cust_id,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
	connect(ui->customer,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
	connect(ui->address,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
	connect(ui->phone,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
	connect(ui->mobile,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
	connect(ui->email,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
	connect(ui->fax,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
	connect(ui->notes,SIGNAL(textEdited(QString)),this,SLOT(setStatusChanged()));
    
	
}

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




void Customers::newRecord()
{
    clear();
    mode=CreateRecord;
    status=UnChangedStatus;
    db.transaction();
}


void Customers::save()
{
    
    if(status==UnChangedStatus){
        // Unchanged Record Action
        return;
    }
    if(mode==CreateRecord){
        if(insert()){
            // Insert success Action
        }else{
            // Insert Fail Action
            return;
        }
    }else if(mode==EditRecord){
        if(update()){
            // Edit success Action
            
        }else{
            // Edit Fail Action
            return;
        }
    }
    if(db.commit()){
        if(mode==DeleteRecord){
            mode=CreateRecord;
        }else{
            mode=EditRecord;
        }
        status=UnChangedStatus;
        db.transaction();
        // Save success Action
    }else{
        // Save Fail Action
        error(db.lastError().databaseText(),"Save Failed\n"+db.lastError().text(),SaveOperation);
    }
}



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();
	
}


void Customers::setStatusChanged()
{
    status=ChangedStatus;
}


void Customers::error(QString code, QString errorText, int operation)
{
    QMessageBox::information(this,"error",code+"\n"+errorText+QString::number(operation));
}

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()){
        error(db.lastError().databaseText(),query.lastError().text(),InsertOperation);

        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()){
        error(db.lastError().databaseText(),query.lastError().text(),UpdateOperation);

        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()){
        error(db.lastError().databaseText(),query.lastError().text(),RemoveOperation);

        return false;
    }
    status=ChangedStatus;
    clear();
    mode=DeleteRecord;
    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()){
        error(db.lastError().databaseText(),query.lastError().text(),QueryOperation);

        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)));
    mode=EditRecord;
    status=UnChangedStatus;
    return true;
}

Simple 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.