The Road to Delphi – a Blog about programming

Delphi – Free Pascal – Oxygene


5 Comments

Returning multiple datasets with ADO and Delphi

Maybe when you’ve used the SQL Server Management studio ‘ve noticed that you can run multiple queries at once.

SQL Managemnt Studio , Multiple=ADO supports this feature, and you can include it in your applications. the key is to use the function NextRecordset and then assign it to any TCustomADODataSet descendent.

see this simple example.


program MultiDataSetsADO;

{$APPTYPE CONSOLE}

uses
  ActiveX,
  ADODB,
  SysUtils;

const
//the connection string
StrConnection='Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Application Name=MyApp;' +
              'Data Source=%s;Use method for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False';

var
AdoConnection : TADOConnection;

procedure SetupConnection;//Open a connection
begin
  Writeln('Connecting to SQL Server');
  AdoConnection:=TADOConnection.Create(nil);
  AdoConnection.LoginPrompt:=False;//dont ask for the login parameters
  AdoConnection.ConnectionString:=Format(StrConnection,['pass','user','DataBase','Server']);
  AdoConnection.Connected:=True; //open the connection
  Writeln('Connected');
end;

procedure CloseConnection;//Close an open connection
begin
  Writeln('Closing connection to Sql Server');
  if AdoConnection.Connected then
  AdoConnection.Close;
  AdoConnection.Free;
  Writeln('Connection closed');
end;

Procedure RunMutilplesQuerysatOnce(SqlQuerys : array of string);
var
  AdoDataSet      : TADODataSet;
  AdoDataSetChild : TADODataSet;
  i               : integer;
  j               : integer;
  RecCount        : OleVariant;
begin
   AdoDataSet:=TADODataSet.Create(nil);
   try
    AdoDataSet.Connection :=AdoConnection;//set the connection
    AdoDataSet.CommandType:=cmdText;
    AdoDataSet.LockType   :=ltReadOnly;
    for i:=Low(SqlQuerys)  to High(SqlQuerys) do
    AdoDataSet.CommandText:=AdoDataSet.CommandText+SqlQuerys[i]+' '; //assign the querys
    AdoDataSet.Open;//Execute all the querys at once.

    for i:=Low(SqlQuerys)  to High(SqlQuerys) do
    begin
        AdoDataSetChild:=TADODataSet.Create(nil);//Create a Dummy dataset to fetch the data
        try
           Writeln('Loading Dataset #'+IntToStr(i+1));
            if i=0 then
            AdoDataSetChild.Recordset:=AdoDataSet.Recordset //Assign the first dataset returned
            else
            AdoDataSetChild.Recordset:=AdoDataSet.Recordset.NextRecordset(RecCount); //Assign the next dataset  in the buffer

            for j:=0 to AdoDataSetChild.FieldCount-1 do
            Write(format('%-15s',[AdoDataSetChild.Fields[j].FieldName])); //Show the fields names
            Writeln;
            while not AdoDataSetChild.eof do
            begin
                //do your stuff here
                for j:=0 to AdoDataSetChild.FieldCount-1 do
                Write(format('%-15s',[AdoDataSetChild.Fields[j].asString])); // Show the data
                Writeln;

              AdoDataSetChild.Next;
            end;
        finally
        AdoDataSetChild.Free;
        end;
   end;
   finally
   AdoDataSet.Free;
   end;
end;

begin
  CoInitialize(nil); // call CoInitialize()
  try
       Writeln('Init');
       try
         SetupConnection;
         RunMutilplesQuerysatOnce(
         [
         'select top 10 transnum,transtype,ItemCode from oinm',
         'select top 10 CardCode,CardType,Country from ocrd',
         'select top 10 ItemCode,ItemType,ManBtchNum,OnHand,OnOrder from oitm']
         );

         CloseConnection; //close the connection
       except
         on E : Exception do
           Writeln(E.Classname, ': ', E.Message);
       end;
      Readln;
  finally
   CoUnInitialize; // free memory
  end;
end.

if you run the Sql Profiler you can check wich all the querys are executed at once

and the final result is

console dataset

Console Output

program MultiDataSetsADO;

{$APPTYPE CONSOLE}

uses
ActiveX,
ADODB,
SysUtils;

const
//the connection string
StrConnection=’Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Application Name=MyApp;’ +
‘Data Source=%s;Use method for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False’;

var
AdoConnection : TADOConnection;

procedure SetupConnection;//Open a connection
begin
Writeln(‘Connecting to SQL Server’);
AdoConnection:=TADOConnection.Create(nil);
AdoConnection.LoginPrompt:=False;//dont ask for the login parameters
AdoConnection.ConnectionString:=Format(StrConnection,['us8j329','sa','CMMSDEMO_ORICA','localhost']);
AdoConnection.Connected:=True; //open the connection
Writeln(‘Connected’);
end;

procedure CloseConnection;//Close an open connection
begin
Writeln(‘Closing connection to Sql Server’);
if AdoConnection.Connected then
AdoConnection.Close;
AdoConnection.Free;
Writeln(‘Connection closed’);
end;

Procedure RunMutilplesQuerysatOnce(SqlQuerys : array of string);
var
AdoDataSet      : TADODataSet;
AdoDataSetChild : TADODataSet;
i               : integer;
j               : integer;
RecCount        : OleVariant;
begin
AdoDataSet:=TADODataSet.Create(nil);
try
AdoDataSet.Connection :=AdoConnection;
AdoDataSet.CommandType:=cmdText;
AdoDataSet.LockType   :=ltReadOnly;
for i:=Low(SqlQuerys)  to High(SqlQuerys) do
AdoDataSet.CommandText:=AdoDataSet.CommandText+SqlQuerys[i]+’ ‘;
AdoDataSet.Open;

for i:=Low(SqlQuerys)  to High(SqlQuerys) do
begin
AdoDataSetChild:=TADODataSet.Create(nil);
try
Writeln(‘Loading Dataset #’+IntToStr(i+1));
if i=0 then
AdoDataSetChild.Recordset:=AdoDataSet.Recordset
else
AdoDataSetChild.Recordset:=AdoDataSet.Recordset.NextRecordset(RecCount);

for j:=0 to AdoDataSetChild.FieldCount-1 do
Write(format(‘%-15s’,[AdoDataSetChild.Fields[j].FieldName]));
Writeln;
while not AdoDataSetChild.eof do
begin
for j:=0 to AdoDataSetChild.FieldCount-1 do
Write(format(‘%-15s’,[AdoDataSetChild.Fields[j].asString]));
Writeln;

AdoDataSetChild.Next;
end;
finally
AdoDataSetChild.Free;
end;
end;
finally
AdoDataSet.Free;
end;
end;

begin
CoInitialize(nil); // call CoInitialize()
try
Writeln(‘Init’);
try
SetupConnection;
RunMutilplesQuerysatOnce(
[
'select top 10 transnum,transtype,ItemCode from oinm',
'select top 10 CardCode,CardType,Country from ocrd',
'select top 10 ItemCode,ItemType,ManBtchNum,OnHand,OnOrder from oitm']
);

CloseConnection; //close the connection
except
on E : Exception do
Writeln(E.Classname, ‘: ‘, E.Message);
end;
Readln;
finally
CoUnInitialize; // free memory
end;
end.


17 Comments

Build your own profiler using ADO

You can construct your own SQL profiler for yours apps wich use ADO, the TAdoConnection Object has two events TADOConnection.OnWillExecute and TADOConnection.OnExecuteComplete to accomplish this task.

TWillExecuteEvent = procedure (const 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) of object;
TExecuteCompleteEvent = procedure (const Connection: TADOConnection; RecordsAffected: Integer; const Error: Error; var EventStatus: TEventStatus; const Command: _Command; const Recordset: _Recordset) of object;

1) Create a New Form with a TListview and a TMemo (in this example i am use a TSynEdit for format the SQL Command)

2) Create a public procedure in your form called AddLog

procedure AddLog(const Command,CommandType,Status,CursorType,LockType:String;RecordsAffected:Integer);

and implement the procedure like this

procedure TFrmLogSql.AddLog(const Command,CommandType,Status,CursorType,LockType:String;RecordsAffected:Integer);
var
  item : TListItem;
begin
    ListViewSQL.Items.BeginUpdate;
  try
    item:=ListViewSQL.Items.Add;
    item.Caption:=FormatDateTime('DD/MM/YYYY HH:NN:SS.ZZZ',Now);
    item.SubItems.Add(CommandType);
    item.SubItems.Add(Command);
    item.SubItems.Add(Status);
    item.SubItems.Add(IntToStr(RecordsAffected));
    item.SubItems.Add(CursorType);
    item.SubItems.Add(LockType);
  finally
    ListViewSQL.Items.EndUpdate;
  end;
  ListViewSQL.Items.Item[ListViewSQL.Items.Count-1].MakeVisible(false); //Scroll to the last line
end;

3) Assign the OnChange Event of the TListView

procedure TFrmLogSql.ListViewSQLChange(Sender: TObject; Item: TListItem;
  Change: TItemChange);
begin
    if ListViewSQL.Selected<>nil then
    SynEdit1.Lines.Text:=ListViewSQL.Selected.SubItems[1];
end;

4) Assign the events OnWillExecute and OnExecuteComplete for you AdoConnection object.

uses
  TypInfo;

procedure TDataModule1.ADOConnection1WillExecute(
  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);
begin
   FrmLogSql.AddLog(
   CommandText,
   'Before '+GetEnumName(TypeInfo(TCommandType),Integer(CommandType)),
   GetEnumName(TypeInfo(TEventStatus),Integer(EventStatus)),
   GetEnumName(TypeInfo(TCursorType),Integer(CursorType)),
   GetEnumName(TypeInfo(TADOLockType),Integer(LockType)),
   0);
end;

procedure TDataModule1.ADOConnection1ExecuteComplete(
  Connection: TADOConnection; RecordsAffected: Integer; const Error: ADODB.Error;
  var EventStatus: TEventStatus; const Command: _Command;
  const Recordset: _Recordset);
begin
  FrmLogSql.AddLog(
  Command.CommandText,
  'After '+GetEnumName(TypeInfo(TCommandType),Integer(Command.CommandType)),
  GetEnumName(TypeInfo(TEventStatus),Integer(EventStatus)),
  GetEnumName(TypeInfo(TCursorType),Integer(Recordset.CursorType)),
  GetEnumName(TypeInfo(TADOLockType),Integer(Recordset.LockType)),
  RecordsAffected);
end;

5) and the final result

Follow

Get every new post delivered to your Inbox.

Join 588 other followers