Thread and select from Database MSSQL2005 Express with ADO

Hello Experts,
Can you help me with such a thing:
I have a select statement from MSSQL 2005 Express here it is:

procedure TForm1.Button3Click(Sender: TObject);
var
czas,czas1,temp1 : String;
temp : Double;
d1,d2,c1,c2 : String;
zlicz_czas,zlicz_czase,zlicz_czasa : Integer;
begin
  DBGrid1.DataSource:=DataSource2;
  czas := TimeToStr(DateTimePicker3.Time);
  czas1 := TimeToStr(DateTimePicker4.Time);
  raportodczas:=czas;
  raportdoczas:=czas1;
  raportoddata:= DateToStr(DateTimePicker1.Date);
  raportdodata:= DateToStr(DateTimePicker2.Date);
  if ComboBox1.Text = 'Wybierz wag...' then
      ComboBox1.Text := '2-8';
  if ComboBox2.Text ='Waga od...' then
      ComboBox2.Text:='-1000';
  if ComboBox3.Text ='Waga do...' then
      ComboBox3.Text:='2000';
  d1:= FormatDateTime('yyyy-MM-dd',DateTimePicker1.DateTime)+' '+'00:00:00';
  d2:= FormatDateTime('yyyy-MM-dd',DateTimePicker2.DateTime)+' '+'00:00:00';
  c1:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker3.Time);
  c2:= '1900-01-01'+' '+FormatDateTime('HH:mm:ss',DateTimePicker4.Time);
  waga_nr:= ComboBox1.Text;

//-------------------------------Base query-------------------------------------
     with ADOQuery1, SQL do
      begin
        Close;
        Clear;
        Add('SELECT ID,WAGA,MASA,RATE,DATA,CZAS,RAPORT FROM PRODUKCJA WHERE WAGA=:waga AND MASA >= :masa_od AND MASA <= :masa_do AND DATA >= Convert(DateTime, :data_od) AND DATA <= Convert(DateTime, :data_do) '+
        'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do) ORDER BY DATA ASC,CZAS ASC');
        Parameters.ParamByName('masa_od').Value := StrToFloat(ComboBox2.Text);
        Parameters.ParamByName('masa_do').Value := StrToFloat(ComboBox3.Text);
        Parameters.ParamByName('waga').Value := ComboBox1.Text;
        Parameters.ParamByName('data_od').Value := d1;
        Parameters.ParamByName('data_do').Value := d2;
        Parameters.ParamByName('czas_od').Value := c1;
        Parameters.ParamByName('czas_do').Value := c2;
        Open;
      end;

//-------------------Sum of the highest values-------------------
     with ADOQuery3, SQL do
      begin
        Close;
        Clear;
        Add('SELECT SUM(MASY_DZIEN.MASA_MAX)FROM (SELECT MAX(MASA) AS MASA_MAX FROM PRODUKCJA WHERE WAGA = :waga AND MASA BETWEEN :masa_od AND :masa_do AND DATA BETWEEN Convert(DateTime, :data_od) AND Convert(DateTime, :data_do)'+
        'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do) GROUP BY convert(varchar(10), DATA, 105 )) masy_dzien');
        Parameters.ParamByName('masa_od').Value := StrToFloat(ComboBox2.Text);
        Parameters.ParamByName('masa_do').Value := StrToFloat(ComboBox3.Text);
        Parameters.ParamByName('waga').Value := ComboBox1.Text;
        Parameters.ParamByName('data_od').Value := d1;
        Parameters.ParamByName('data_do').Value := d2;
        Parameters.ParamByName('czas_od').Value := c1;
        Parameters.ParamByName('czas_do').Value := c2;
        Open;
        if (ADOQuery3.Fields[0].AsString) <> ''  then
        begin
        temp := StrToFloat(ADOQuery3.Fields[0].AsString);
        temp1:=FloatToStrF(temp,ffNumber,10,3);
        Edit2.Text := temp1;
        raport:= temp1;
        end;
      end;
end;

When I select the data from database the whole app is blocked - I think it`s connected with the huge number of records in database, so I think that i should use threads.
The problem is that i have never made such think.
I think that the best option is to separate the base querry and the highest value querry. If I get such examples I`ll be able to change my app to use the threads as base.
VaalarAsked:
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.

Geert GOracle dbaCommented:
there are some things to keep in mind about using threads and databases:

when you don't want your threads to block your app,
you must get the data in the thread using a different connection
it can be the same connectionstring, except the name of the connection
after getting the data, you need to copy it from the thread to the main thread
to a memory dataset

updatable queries will not work as the query is in memory and not from the same connection

first thing you need is an in memory dataset:
www.devarc.com
they have a lot of commercial products, but the TVirtualTable is a free in memory dataset

as I don't have Delphi here i couldn't compile it,
but this is what it should look like.
Tomorrow, i'll try give a working sample

type
  TForm1 = class(TForm)
    VTDataSet: TVirtualTable;
  private
    fDataThread: TDataThread;
    procedure DataLoaded(aMessage: string; aMessageInfo: integer = 0);
  end;

procedure TForm.ButtonLoadDataClick(Sender: TObject);
var aSelect: string;
begin
  aSelect := 'SELECT ID,WAGA,MASA,RATE,DATA,CZAS,RAPORT FROM PRODUKCJA WHERE WAGA=:waga AND MASA >= :masa_od AND MASA <= :masa_do AND DATA >= Convert(DateTime, :data_od) AND DATA <= Convert(DateTime, :data_do) '+
        'AND CZAS >= Convert(DateTime, :czas_od) AND CZAS <= Convert(DateTime, :czas_do) ORDER BY DATA ASC,CZAS ASC';
  fDataThread := TDataThread.Create(aConnectionString, aSelect,
    VarrArrayCreate([ComboBox2.Text, ComboBox3.Text, ComboBox1.Text, d1, d2, c1, c2]), DataLoaded);
end;

procedure TForm1.DataLoaded(aMessage: string; aMessageInfo: integer = 0);
begin
  VTDataset.Active := True;
  VTDataset.Assign(fDataThread.Dataset);
end;
type
 
  TCallbackProc = procedure (aMessage: string; aMessageInfo: Integer = 0) of object;
 
  TCallbackThread = class(TThread)
  private
    FCallBack: TCallbackProc;
    FCallbackMsg: string;
    FCallbackMsgInfo: integer;
    procedure SynchedCallback;
  protected
    procedure DoCallback(aMsg: string; aMsgInfo: integer = 0); virtual;
    property Callback: TCallbackProc read FCallback;
  public
    constructor Create(aCallback: TCallbackProc; CreateSuspended: Boolean = False); reintroduce; virtual;
  end;
 
  TDataThread = class(TCallbackThread)
  private
    fConnection: TADOConnection;
    fQuery: TADOQuery;
  protected
    procedure Execute; override;
  public
    constructor Create(aConnectionString: string; aQuery: string; aParams: Variant; aCallback: TCallbackProc);
    destructor Destroy; override;
    property Dataset: TDataset read GetDataset;
  end;
 
{ TCallbackThread }
 
constructor TCallbackThread.Create(aCallback: TCallbackProc; CreateSuspended: Boolean = False);
begin
  inherited Create(CreateSuspended);
  FreeOnTerminate := True;
  FCallback := aCallback;
end;
 
procedure TCallbackThread.DoCallback(aMsg: string; aMsgInfo: Integer = 0);
begin
  FCallbackMsg := aMsg;
  FCallbackMsgInfo := aMsgInfo;
  Synchronize(SynchedCallback);
end;
 
procedure TCallbackThread.SynchedCallback;
begin
  if Assigned(FCallback) then
    FCallBack(FCallbackMsg, FCallbackMsgInfo);
end;
 
constructor TDataThread.Create(aConnectionString: string; aQuery: string; Params: Variant; aCallback: TCallbackProc);
var I: Integer;
begin
  inherited Create(aCallback);
  CoInitialize(nil, 0);
  fConnection := TAdoConnection.Create(nil, 'thread_connection', aConnectionString);  
  fQuery := TADOQuery.Create(nil);
  fQuery.Connection := fConnection;
  fQuery.SQL.Text := aQuery;
  for I := VarArrayLowBound(Params, 1) to  VarrArrayHighBound(Params, 1) do
    fQuery.Parameters[I].Value := Params[I];
end;
 
destructor TDataThread.Destroy; 
begin
  FreeAndNil(fQuery);
  FreeAndNil(fConnection);
  inherited Destroy;
end;
 
procedure TDataThread.Execute;
begin
  try
    fConnection.Connect;
    try
      fQuery.Open;
      DoCallback('Data loaded', 0);
      fQuery.Close;
    finally
      fConnection.Disconnect;
    end;
  except
    on E: Exception do 
      DoCallback(E.Message, 1);
  end;
end;
 
function TDataThread.GetDataset: TDataset;
begin
  Result := fQuery;
end;

Open in new window

0

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.