Fetch query name during TAdoConnection.OnWillExecute

Question

I am planning to log all the SQL Queries that were passed from ADOConnection to SQL Server.

I found this How can I monitor the SQL commands send over my ADO connection? and able to log all the queries.

But I also want to log query components (dataset's) name. I tried below code but Command.Name is returning blank.

const cXmlDBOPStart       = '<DBOP>';
const cXmlDBOPClose       = '</DBOP>';
const cXmlStartTimeStart  = '<StartTime>';
const cXmlStartTimeClose  = '</StartTime>';
const cXmlQueryStart      = '<Query>';
const cXmlQueryClose      = '</Query>';

procedure TdtmAdo.adoRover_DataWillExecute(Connection: TADOConnection;
  var CommandText: WideString; var CursorType: TCursorType;
  var LockType: TADOLockType; var CommandType: TCommandType;
  var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus;
  const Command: _Command; const Recordset: _Recordset);
var
   myFile : TextFile;
begin
  try
    AssignFile(myFile, 'E:\SQLLog.txt');
    Append(myFile);
    WriteLn(myFile, cXmlDBOPStart);
    WriteLn(myFile, Command.Name);
    WriteLn(myFile, cXmlStartTimeStart
        + FormatDateTime('DD/MM/YYYY HH:NN:SS.ZZZ',Now)
        + cXmlStartTimeClose);
    WriteLn(myFile, cXmlQueryStart + CommandText + cXmlQueryClose);
    WriteLn(myFile, cXmlDBOPClose);        
  finally
    CloseFile(myFile);
  end;
end;

Is there any way to fetch the query name.

Answer 1

TLama's is a good suggestion. In a similar vein, this is a little more direct:

Add some variables to your event handler:

 I : Integer;
 ADataSet : TDataSet;
 ADataSetName : String;

Then, add this before your AssignFile()

  ADataSet := Nil;
  for I := 0 to Connection.DataSetCount - 1 do begin
    if Connection.DataSets[I] is TCustomAdoDataSet then
      if TCustomAdoDataset(Connection.DataSets[I]).Recordset = RecordSet then
        ADataSet := Connection.DataSets[I];
  end;

  if ADataSet <> Nil then
    ADataSetName := ADataSet.Name
  else
    ADataSetName := '';

It works because TAdoConnection calls

procedure TCustomConnection.RegisterClient

when a TDataSet with a connection property has the property's value set to the connection.

(Tested with D7, Win7 64-bit and TAdoQuery and TAdoDatasets)