Progress Bar while executing a StoredProcedure

Hi all,

I am planning a project which needs sometimes to do long process on data stored in database. I faced a problem that I cannot show a progress bar while the process is going on. I am using MS-SQL2000 stored-procedures to do my processes on the data. I want to reflect this by showing a progress bar in Delphi.

The idea comes. I have to execute the stored procedure in it's own thread. The proceudre is going to update some temporary table with the total count and the current loop index. my application should read from that temporary table and shows the progress as changes are done to it.

My problem is that I never worked with threads. and I need some help regarding them. I hope someone is going to show me how to create a thread that will execute the procedure. and my application will be able to show a progress bar reflects the loop counter stored and updated in the temporary table.

Regards,
Hamid
HamidHossainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

illusion_chaserCommented:
Try this code sample (includes Unit and Form files):

----- Unit file (Unit1.pas) ----

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls;

const
  iLoopMinVal = 1;
  iLoopMaxVal = 1000;
  iProgressUpdateThreadCheckInterval = 200; //Increase to do less checks (mSec)

type
  TStoredProcThread = class(TThread) //Stored procedure thread
    private
      procedure UpdateMemo;
    protected
      procedure Execute; override;
    //public
      //constructor Create;
      //destructor Destroy;
  end;

  //This thread will update the progress bar according to the progress counter (updated by the stored procedure thread)
  TProgressUpdateThread = class(TThread)
    private
      procedure UpdateProgressBar;
    protected
      procedure Execute; override;
  end;

  TForm1 = class(TForm)
    Memo1: TMemo;
    Edit1: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    ProgressBar1: TProgressBar;
    Button1: TButton;
    Label3: TLabel;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

var
  oStoredProcThread: TStoredProcThread;
  oProgressUpdateThread: TProgressUpdateThread;

{ TSPThread }

procedure TStoredProcThread.Execute;
begin
  //Execute Stored procedure here
  //RunStoreProc;

  //I am using Memo1 to simulate stored procedure process...
  Synchronize(UpdateMemo); //Need to use Synchronize for updating VCL (Memo1)
end;

procedure TStoredProcThread.UpdateMemo;
var
  i: Integer;
  sTemp: string;
begin
  i := iLoopMinVal;
  while (not Terminated) and (i <= iLoopMaxVal) do
  begin
    sTemp := IntToStr(i);
    Form1.Memo1.Lines.Add(sTemp);
    Sleep(300); //Wait for 300 mSec

    Form1.Edit1.Text := sTemp; //Simulate completion of one iteration (updated loop counter)
    Application.ProcessMessages; //Let Edit1 get updated and keep responsive GUI

    Inc(i);
  end;
end;

{ TProgressUpdateThread }

procedure TProgressUpdateThread.Execute;
begin
  while (not Terminated) do
  begin
    Synchronize(UpdateProgressBar);
    Sleep(iProgressUpdateThreadCheckInterval);

    if (Form1.ProgressBar1.Position = iLoopMaxVal) then
      Self.Terminate; //will exit the thread
  end;
end;

procedure TProgressUpdateThread.UpdateProgressBar;
var
  iLoopCounter: Integer;
begin
  //Get loop counter (you will get from a table)
  iLoopCounter := StrToIntDef(Form1.Edit1.Text, 0);

  //Check the progress counter
  if (iLoopCounter > Form1.ProgressBar1.Position) then
    Form1.ProgressBar1.Position := iLoopCounter;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  ProgressBar1.Min := iLoopMinVal;
  ProgressBar1.Max := iLoopMaxVal;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  //Init
  ProgressBar1.Position := 1;
  Memo1.Clear;
  Edit1.Clear;

  //Create suspended threads
  oStoredProcThread := TStoredProcThread.Create(True);
  oProgressUpdateThread := TProgressUpdateThread.Create(True);

  //Threads will free themselves when they are terminated
  oStoredProcThread.FreeOnTerminate := True;
  oProgressUpdateThread.FreeOnTerminate := True;

  //Resume threads
  oStoredProcThread.Resume;
  oProgressUpdateThread.Resume;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  oProgressUpdateThread.Terminate;
  oStoredProcThread.Terminate;
end;

end.

----- Form File (Unit1.dfm) -----

object Form1: TForm1
  Left = 192
  Top = 107
  Width = 333
  Height = 290
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 8
    Top = 16
    Width = 123
    Height = 13
    Caption = 'Stored Procedure prgress:'
  end
  object Label2: TLabel
    Left = 176
    Top = 16
    Width = 83
    Height = 13
    Caption = 'Progress counter:'
  end
  object Label3: TLabel
    Left = 8
    Top = 224
    Width = 62
    Height = 13
    Caption = 'Progress bar:'
  end
  object Memo1: TMemo
    Left = 8
    Top = 32
    Width = 161
    Height = 177
    TabOrder = 0
  end
  object Edit1: TEdit
    Left = 176
    Top = 32
    Width = 145
    Height = 21
    TabOrder = 1
  end
  object ProgressBar1: TProgressBar
    Left = 8
    Top = 240
    Width = 313
    Height = 17
    Min = 1
    Max = 1000
    Position = 1
    TabOrder = 2
  end
  object Button1: TButton
    Left = 176
    Top = 96
    Width = 145
    Height = 25
    Caption = 'Run Stored Procedure'
    TabOrder = 3
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 176
    Top = 128
    Width = 145
    Height = 25
    Caption = 'Terminate'
    TabOrder = 4
    OnClick = Button2Click
  end
end

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_Katka_Commented:
pcsentinelCommented:
You dont need threads to do this, you just need to have the ADO set to async mode and then check the progress of your query.


************UNIT CODE********************
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, ADODB, Grids, DBGrids, StdCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Button1: TButton;
    Label1: TLabel;
    Button2: TButton;
    ADOCommand2: TADOCommand;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    fCancel: boolean;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
  fCancel:=false;
  adocommand2.Execute;
  while stExecuting in adocommand2.States do
  begin
        label1.caption:=IntToStr(StrToInt(label1.caption)+1);
    label1.Repaint;
    Application.ProcessMessages;
  end;
  label1.caption:='0';
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
      fCancel:=true;
      adocommand2.cancel;
end;

end.

***********************FORM CODE**********************************
object Form1: TForm1
  Left = 128
  Top = 160
  Width = 696
  Height = 480
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 36
    Top = 72
    Width = 6
    Height = 13
    Caption = '0'
  end
  object Button1: TButton
    Left = 88
    Top = 8
    Width = 75
    Height = 25
    Caption = 'Run'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 88
    Top = 36
    Width = 75
    Height = 25
    Caption = 'Cancel'
    TabOrder = 1
    OnClick = Button2Click
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initi' +
      'al Catalog=paultemp;Data Source=YourServer;Use Procedure for Prepare=1;' +
      'Auto Translate=True;Packet Size=4096;Use ' +
      'Encryption for Data=False;Tag with column collation when possibl' +
      'e=False'
    LoginPrompt = False
    Provider = 'SQLOLEDB.1'
    Left = 12
    Top = 8
  end
  object ADOCommand2: TADOCommand
    CommandText = 'TESTDelay'
    CommandType = cmdStoredProc
    Connection = ADOConnection1
    ExecuteOptions = [eoAsyncExecute]
    Parameters = <>
    Left = 48
    Top = 8
  end
end


******************SQL SP******************

CREATE PROCEDURE TESTDelay
As

WAITFOR DELAY '00:00:10'
GO


****************************************
the above is just demo stuff i.e. it constantly increases the number in a label while the progress is running.


pcsentinelCommented:
HamidHossain, did this solve your problem? if so please close the question


regards
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.