Delphi / ADO : Which components? TADODataSet and TADOCommand or TADOQuery?

Question

According to http://www.delphigroups.info/2/3/181838.html ^(archive)

The preferred approach with ADO components is to use TADODataSet and TADOCommand. TADOQuery (and TADOTable and TADOStoredProc) are provided for compatibility.

Use TADODataSet for SQL that returns result-sets and TADOCommand for SQL that does not.

I am a clueless beginner, who is about to code a lot of ADO stuff. Is the above statement correct?

---

p.s. Is there any good open source Windows program that will let me visualize & explore the contents of my databases?

Which components should I use for something which does/does not return a result?

Answer 1

That statement is correct. TADODataset and TADOCommand are direct interfaces to the native ADO objects, and can perform all the task made by the other three, which exist to ease porting application written for the BDE (Borland Database Engine), implmenting a similar interface - they end up calling the first two.

Answer 2

And I will go the half-oppositie! ;-)

There might be some cases when TADOQuery fits nicely for both of the jobs. If your query will result in data use TADOQuery.Acvite := True, If you need to perform update\insert\delete use TADOQuery.ExecSQL.

For example, you could write a query to UPDATE\ INSERT and SELECT a record and do it in one component instead of introducing two.

DECLARE @ID int, @Mode int, @SomeValue varchar(20)

SET @ID = :ID
SET @Mode = :Mode
SET @SomeValue = :SomeValue 

IF (@Mode = 1) //INSERT
BEGIN
  INSERT INTO dbo.YourTable(ID, SomeColumn) VALUES(@ID, @SomeValue)
END ELSE
IF (@Mode = 2) //UPDATE
BEGIN
  UPDATE dbo.YourTable SET SomeValue = @SomeValue WHERE ID = @ID
END ELSE
IF (@Mode = 3) //DELETE
BEGIN
  DELETE FROM dbo.YourTable WHERE ID = @ID
END ELSE
IF (@Mode = 4) //SELECT
BEGIN
  IF (@ID = -1) //SELECT ALL
  BEGIN
    SELECT * FROM dbo.YourTable
  END ELSE
  BEGIN
    SELECT * FROM dbo.YourTable WHERE ID = @ID
  END
END

Just an example, written now. I hope you get the idea.

Answer 3

Which database do you use. SqlBuddy is open source IDE to explore database.

Answer 4

You have 2 different classifications here either depending on the nature of the SQL object (TADOTable, TADOQuery and TADOStoredProc) or the action/result (TADODataSet and TADOCommand).
The historical Delphi approach is more of the 1st while ADO is by nature more of the 2nd.

Both can be useful depending on what you want to do.

I recommand you read the Delphi help on the ADO components.
For instance you'll find useful notes like: "ADOdb.TADODataSet and SQLExpr.TSQLDataSet have a CommandType property that lets you specify whether they represent a table, query, or stored procedure. Property and method names are most similar to query-type datasets, although TADODataSet lets you specify an index like a table type dataset."

If you are sure to stick with ADO and never need to change and port to other Data Layers, then go the "ADO route" with TADODataSet and TADOCommand.
You'll get the most of ADO with it and it will be easier to use MS docs and examples.

Answer 5

SELECT statements

For issuing DQL statements that return a data set (e.g. SELECT)

• TADOQuery

  qry.Sql.Text := 'SELECT * FROM Users WHERE Name = :username';
  qry.Parameters.ParamByName('username').Value := 'ian';
  qry.Open;
  

• TADODataSet

  ds.CommandText := 'SELECT * FROM Users WHERE Name = :username';
  ds.Parameters.ParamByName('username').Value := 'ian';
  ds.Open;
  

• TADOCommand

  cmd.CommandText := 'SELECT * FROM Users WHERE Name = :username';
  cmd.Parameters.ParamByName('username').Value := 'ian';
  rs: _Recordset;
  rs := cmd.Execute;
  

  The ADOCommand will return a native ADO IRecordset. You can use the Recordset interface directly (it's not that hard), or you can wrap it in a friendly Delphi wrapper class:

  ds.Recordset := rs;
  

  or

  qry.Recordset := rs;
  

INSERT, UPDATE, DELETE statements

For issuing DML statements that do not return a data set (e.g. INSERT, UPDATE, DELETE)

• TADOQuery

  qry.Sql.Text := 'DELETE FROM Users WHERE Name = :username';
  qry.Parameters.ParamByName('username').Value := 'ian';
  qry.ExecuteOptions := [eoExecuteNoRecords];
  qry.ExecSql;
  

• TADOCommand

  cmd.CommandText := 'DELETE FROM Users WHERE Name = :username';
  cmd.Parameters.ParamByName('username').Value := 'ian';
  cmd.ExecuteOptions := [eoExecuteNoRecords];
  cmd.Execute;
  

• TADODataSet: Cannot be done. TADODataSet will throw an exception if no dataset is returned by the statement

  ds.CommandText := 'DELETE FROM Users WHERE Name = :username';
  ds.Parameters.ParamByName('username').Value := 'ian';
  ds.ExecuteOptions := [eoExecuteNoRecords];
  ds.Open; // <-- Exception: "CommandText does not return a result set"
  

Chart form

| Component   | Issue command | Return rows |
|-------------|---------------|-------------| 
| TADODataSet |  No           |  Yes        |
| TADOCommand |  Yes          |  Yes¹       |
| TADOQuery   |  Yes          |  Yes        |

¹ Recordset interface

Inheritance hierarchy

• TComponent
  • TADOCommand (near native ADO access)
  • TDataSet (Delphi's base data set model)
    • TCustomADODataSet (exposing ADO as DataSet)
      • TADODataSet (cannot issue DML)
      • TADOQuery (can issue DML and DQL)
    • TCustomClientDataSet (exposing in-memory tables as a DataSet)
    • TBDEDataSet (exposing BDE as DataSet)
    • TCustomSQLDataSet (exposing dbExpress as DataSet)

TADOCommand is the closest to the metal for issuing raw queries.

TADODataSet and TADOQuery use Delphi's existing database object model to expose ADO data sources

TADODataSet can only be used to represent data sets

TADOQuery is the jack-of-all-trades that can do everything.