SDAC

Deleting Data From Tables

This tutorial describes how to delete data from tables using the TMSQuery and TMSTable components.

  1. Requirements
  2. General information
  3. Using DataSet Functionality
  4. Building DML Statements Manually
  5. Additional Information

Requirements

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQL Server" and "Connecting To SQL Server Compact"), how to create necessary objects on the server (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").

General information

Data on server can be deleted using Data Manipulation Language (DML), which is a part of SQL. DML statements can be executed on server by an account that has necessary privileges. There are two ways to manipulate a database. You can build DML statements manually and run them within some component like TMSQuery. Another way is to use the dataset functionality (the Delete method) of the TMSQuery and TMSTable 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 TMSQuery and TMSTable components allows deleting data without using DML statements. DML statements are generated by SDAC components internally. The code below demonstrates using this method:

[Delphi]

var
  q: TMSQuery;
begin
  q := TMSQuery.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    q.Connection := con; 

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

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

[C++Builder]

{
  TMSQuery* q = new TMSQuery(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    q->Connection = con; 

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

    // delete the current record
    q->Delete();
  }
  __finally
  {
    q->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
  q: TMSQuery;
begin
  q := TMSQuery.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    q.Connection := con; 
    
    // set SQL query for delete record
    q.SQL.Clear;
    q.SQL.Add('DELETE FROM dept WHERE deptno = :deptno;');

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

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

[C++Builder]

{
  TMSQuery* q = new TMSQuery(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    q->Connection = con;

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

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

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

DML Statements As Plain Text

[Delphi]

var
  q: TMSQuery;
begin
  q := TMSQuery.Create(nil);
  try
    // con is either TMSConnection or TMSCompactConnection already set up
    q.Connection := con;

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

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

[C++Builder]

{
  TMSQuery* q = new TMSQuery(NULL);
  try
  {
    // con is either TMSConnection or TMSCompactConnection already set up
    q->Connection = con;

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

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

Additional Information

It is also possible to use stored procedures for deleting data. In this case, all data manipulation logic is defined on server. You can find more about using stored procedures in the tutorial "Stored Procedures".

© 1997-2019 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback