The Road to Delphi

Delphi – Free Pascal – Oxygene

Returning multiple datasets with ADO and Delphi

5 Comments

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.

Author: Rodrigo

Just another Delphi guy.

5 thoughts on “Returning multiple datasets with ADO and Delphi

  1. Hello!
    Great post!
    Just a real minor, let’s say cosmetic thing:
    In the second loop “for i:=Low(SqlQuerys) to High(SqlQuerys) do” you have a condition “if i=0”. Shouldn’t this be “if i = low(SqlQuerys)”?
    Regards,
    Fritz

  2. Interesting piece of well-hidden functionality. Thanks!

  3. Hi Rodrigo,

    I am deseperatly trying to connect to PostGreSQL with ADO, but don’t manage to do so.
    I got a message telling me that i haven’t got any provider.
    How to do please ??? thanks …

  4. Much help Much appreciated

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s