LiteDAC

Deleting Data

This tutorial describes how to delete data from tables using the TLiteQuery and TLiteTable components.

Requirements

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQLite Database"), how to create necessary objects in a database (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").

General information

Data in the database can be deleted using Data Manipulation Language (DML), which is a part of SQL. There are two ways to manipulate a database. You can build DML statements manually and run them within some component like TLiteQuery. Another way is to use the dataset functionality (the Delete method) of the TLiteQuery and TLiteTable components. We will discuss both ways. The goal of this tutorial is to delete a record in the table dept.

Using DataSet Functionality

The Delete method of the TLiteQuery and TLiteTable components allows deleting data without using DML statements. DML statements are generated by LiteDAC components internally. The code below demonstrates using this method:

[Delphi]

var
  LiteQuery: TLiteQuery;
begin
  LiteQuery := TLiteQuery.Create(nil);
  try
    // LiteConnection is either TLiteConnection already set up
    LiteQuery.Connection := LiteConnection; 

    // retrieve data
    LiteQuery.SQL.Text := 'SELECT * FROM dept';
    LiteQuery.Open;

    // delete the current record
    LiteQuery.Delete; 
  finally
    LiteQuery.Free;
  end;
end;

[C++Builder]

{
  TLiteQuery* LiteQuery = new TLiteQuery(NULL);
  try
  {
    // LiteConnection is either TLiteConnection already set up
    LiteQuery->Connection = LiteConnection; 

    // retrieve data
    LiteQuery->SQL->Text = "SELECT * FROM dept";
    LiteQuery->Open();

    // delete the current record
    LiteQuery->Delete();
  }
  __finally
  {
    LiteQuery->Free();
  }
}

Building DML Statements Manually

DML Statements can contain plain text and text with parameters. This section describes both ways.

DML Statements With Parameters

[Delphi]

var
  LiteQuery: TLiteQuery;
begin
  LiteQuery := TLiteQuery.Create(nil);
  try
    // LiteConnection is either TLiteConnection already set up
    LiteQuery.Connection := LiteConnection; 
    
    // set SQL query for delete record
    LiteQuery.SQL.Clear;
    LiteQuery.SQL.Add('DELETE FROM dept WHERE deptno = :deptno;');

    // set parameters
    LiteQuery.ParamByName('deptno').AsInteger := 10;

    // execute query
    LiteQuery.Execute;
  finally
    LiteQuery.Free;
  end;
end;

[C++Builder]

{
  TLiteQuery* LiteQuery = new TLiteQuery(NULL);
  try
  {
    // LiteConnection is either TLiteConnection already set up
    LiteQuery->Connection = LiteConnection;

    // set SQL query for delete record
    LiteQuery->SQL->Clear();
    LiteQuery->SQL->Add("DELETE FROM dept WHERE deptno = :deptno;");

    // set parameters
    LiteQuery->ParamByName("deptno")->AsInteger = 10;

    // execute query
    LiteQuery->Execute();
  }
  __finally
  {
    LiteQuery->Free();
  }
}

DML Statements As Plain Text

[Delphi]

var
  LiteQuery: TLiteQuery;
begin
  LiteQuery := TLiteQuery.Create(nil);
  try
    // LiteConnection is either TLiteConnection already set up
    LiteQuery.Connection := LiteConnection;

    // set SQL query for delete record
    LiteQuery.SQL.Clear;
    LiteQuery.SQL.Add('DELETE FROM dept WHERE deptno = 10;');

    // execute query 
    LiteQuery.Execute;
  finally
    LiteQuery.Free;
  end;
end;

[C++Builder]

{
  TLiteQuery* LiteQuery = new TLiteQuery(NULL);
  try
  {
    // con is either TLiteConnection already set up
    LiteQuery->Connection = con;

    // set SQL query for delete record
    LiteQuery->SQL->Clear();    
    LiteQuery->SQL->Add("DELETE FROM dept WHERE deptno = 10;");

    // execute query 
    LiteQuery->Execute();
  }
  __finally
  {
    LiteQuery->Free();
  }
}
© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback