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

Question

According to http://www.delphigroups.info/2/3/181838.html

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 n00b - 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

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 2

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

Answer 3

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 4

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.