Recent

Author Topic: DBGrid alternative which allows scrolling without moving Cursor  (Read 2292 times)

Maris

  • New member
  • *
  • Posts: 10
Hello!

Is there any alternative for DBGrid, which allows scrolling records without changing active record (Cursor)?

I found TKDBGrid (part of KControls) and looks like it has such behavior, but it is very buggy, and based on comments author of this components does not use database at all now, which probably means work on DBGrid is not active.

My skills are not good enough yet to improve such components.

Thank you in advance!

taazz

  • Hero Member
  • *****
  • Posts: 5362
Re: DBGrid alternative which allows scrolling without moving Cursor
« Reply #1 on: March 02, 2018, 04:00:28 pm »
try the virtualdbgrid package from OPM requires the virtualtreview package
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Maris

  • New member
  • *
  • Posts: 10
Re: DBGrid alternative which allows scrolling without moving Cursor
« Reply #2 on: March 05, 2018, 08:19:14 pm »
Thanks Taazz, VirtualDBGrid looks better. It really does not change cursor, but looks like active record is refreshing when scrolling records or something similar.

I noticed it has some slowdowns when scrolling. Things got clear when Master/Detail relation was made. I didn't use order by in Detail query and when started to scroll Master grid, order of records in Detail grid was changing, which means Detail query was refreshing, and random order of records allowed to notice that.

Order of records itself is easy to solve by adding Order By in query, but point is that VirtualDBGrid is still refreshing something in DataSet or somewhere, what makes barely noticeable slowdowns when scrolling, and Detail Query is refreshing when active record in Master is not changed yet.

Thanks again anyway!

tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: DBGrid alternative which allows scrolling without moving Cursor
« Reply #3 on: March 06, 2018, 10:49:42 am »
I am wondering what you are trying to achieve here. Are you trying to scroll a dataset while editing a row (and hence avoid posting the row's changes) or is performance the issue you are worried about?

TDataset and TDBGrid should work together to display and edit the rows of a dataset. I don't know which DB driver you are using or even the database, but most will use a buffered dataset model where rows are read into local buffers on demand and then cached there for future use. If you edit a row then the updates may be written to database as soon as you post them - although you may be able to cache updates locally to be applied as a batch later, The above is, for example, the way IBX works with a buffered dataset and cached updates as an option.

When you read a row from a TDataset, you can only read the "active record" and likewise you can only update the active record. Changes to the active record must be posted before you can change the active record.

A TDBGrid displays a row by setting the active record to the one it wants to display and the reading the fields. When displaying multiple rows, it sets the active record for each one in turn. When you edit a row, it has to set the active record to this row and, if you then scroll the dataset, it must post the changes before displaying the rows that come into view.

The bottom line is that if you do not want to post changes while scrolling then you need a different model from that offered by TDataset/TDBGrid. It's not just an issue of changing the grid component. If you are worried about performance, then check to make sure that your DB driver is buffering the dataset. If it is being buffered then you may see some delays when you scroll down for the first time, but that's it. It the problem is the update time then trying using cached updates.

Maris

  • New member
  • *
  • Posts: 10
Re: DBGrid alternative which allows scrolling without moving Cursor
« Reply #4 on: March 12, 2018, 04:37:02 pm »
Tonyw, it's about performance, no editing at all.

Especially performance is not good when you have Master/Detail concept and Detail query is relatively slow.

I found some solutions though. As VirtualDBGrid does not change active record (Cursor), it's better to use it instead of DBGrid. Master/Detail concept is still not very fast, even when using VirtualDBGrid, because looks like Detail DataSource still scrolling or refreshing (not sure) when scrolling Master grid and that makes delays and scrolling is not smooth.

Solution - disable controls of Detail DataSet when scrolling Master grid, and enable controls with timer (interval 500) after scrolling is finished (didn't find better solution with using any combination of Grid and DataSet events):

Code: Pascal  [Select]
  1. procedure TForm1.Timer1Timer(Sender: TObject);
  2. begin
  3.   SQLQueryDetail.DataSource := DataSourceMaster; {Looks like no need on linux}
  4.   SQLQueryDetail.EnableControls;
  5.   Timer1.Enabled := False;
  6. end;
  7.  
  8. procedure TForm1.VirtualDBGrid1Scroll(Sender: TBaseVirtualTree; DeltaX,
  9.   DeltaY: Integer);
  10. begin
  11.   if not SQLQueryDetail.ControlsDisabled then
  12.   begin
  13.     SQLQueryDetail.DataSource := nil; {Looks like no need on linux}
  14.     SQLQueryDetail.DisableControls;
  15.   end;
  16.   Timer1.Enabled := False;
  17.   Timer1.Enabled := True;
  18. end;

This works, but there is disadvantage. Active record in Detail grid will get back to first record after scrolling Master.

Another solution is using DataSet without Master/Detail concept. So both data sets are separate and Detail data set is reloaded when Master active record is changed (Master grid OnClick). DataSource property of Detail Dataset is None (no Master/Detail concept).

Code: Pascal  [Select]
  1. procedure TForm1.VirtualDBGrid1Click(Sender: TObject);
  2. begin
  3.   SQLQueryDetail.Active := False;
  4.   SQLQueryDetail.ParamByName('id').AsString := SQLQueryMaster.FieldByName('id').AsString;
  5.   SQLQueryDetail.Active := True;
  6. end;

This solution is better if performance is main thing.

It is quite usual thing to scroll grids when working with databases and searching for record you need, so it's quite strange that scrolling of grids and data sets are made this way. If I'm not mistaken the same is in Delphi and it was always like this. Maybe there is even reason for this, though I don't know it yet.

Also it looks like VirtualDBGrid does not work with Zeos components, but I din't test different versions of Zeos, so probably it'a an issue only in trunk version. But that's another question.
« Last Edit: March 12, 2018, 04:41:19 pm by Maris »

tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: DBGrid alternative which allows scrolling without moving Cursor
« Reply #5 on: March 13, 2018, 10:33:43 am »
Tonyw, it's about performance, no editing at all.

Especially performance is not good when you have Master/Detail concept and Detail query is relatively slow.

<snip/>

What you are pointing to is a basic problem with the Master/Detail concept. The Master dataset is cached in memory but every time you change the row selected in a Master Dataset, a new SQL select query is run on the detail. If the query is anything less than very quick, the user experience is poor.

The easiest way to avoid this is to use a timer to delay executing the query on the detail dataset until the user has settle on a specific row in the master dataset. To do this, you must remove the "datasource" from the detail dataset and manage the link programmatically e.g.

Create some timer (TTimer), with its interval set to 0 so that it normally does nothing.

In the Master dataset's "AfterScroll" event, set the timer interval to say 500ms. if you are scrolling faster than this, the timer will thus keep getting reset and will not fire.

In the Timer's OnTimer event, toggle the Detail Dataset's active property to false and then back to true i.e. force a refresh.

In the Detail's OnBeforeOpen event, set the parameter that link's the query to the master, to the value of its corresponding field in the Master Dataset's current row.

In the Detail's OnBeforeClose handler, set the Timer interval to zero.

The effect of the above is the same as linking a Datasource from the Detail to the Master - it just avoids a query per row and restricts Detail Dataset queries to when the user has settled on a specific row in the master. You may have to play around with the timer delay to get it just right.

mse

  • Sr. Member
  • ****
  • Posts: 286
Re: DBGrid alternative which allows scrolling without moving Cursor
« Reply #6 on: March 13, 2018, 11:54:57 am »
In order to synchronize a lookup datatset with a possible delay MSEgui provides TFieldParamLink. The property TFieldParamlink.Delayus defines a time in microseconds where changes in master dataset are not forwarded to the params of the detail dataset, the detail dataset will be refreshed at "Delayus" delay after the last change in master dataset.
MSEgui TBufdataset also provides the possibility by the "CurrentAs*[]" properties to access arbitrary record data in the TDataset cache without scrolling the dataset.
The MSEgui DB-dropdownlists use "CurrentAs*[]" in order to fill the list and to select the wanted item without scrolling the dataset(s) if <dropdownlistedit>.datalist.optionsdb.odb_directdata is set. It also works for lookupfields if there are local indexes for the key fields in the detail dataset(s). Do not set <lookupfield>.LookupCache!

tonyw

  • Full Member
  • ***
  • Posts: 124
    • MWA Software
Re: DBGrid alternative which allows scrolling without moving Cursor
« Reply #7 on: March 13, 2018, 03:24:10 pm »
In order to synchronize a lookup datatset with a possible delay MSEgui provides TFieldParamLink. The property TFieldParamlink.Delayus defines a time in microseconds where changes in master dataset are not forwarded to the params of the detail dataset, the detail dataset will be refreshed at "Delayus" delay after the last change in master dataset.
MSEgui TBufdataset also provides the possibility by the "CurrentAs*[]" properties to access arbitrary record data in the TDataset cache without scrolling the dataset.
The MSEgui DB-dropdownlists use "CurrentAs*[]" in order to fill the list and to select the wanted item without scrolling the dataset(s) if <dropdownlistedit>.datalist.optionsdb.odb_directdata is set. It also works for lookupfields if there are local indexes for the key fields in the detail dataset(s). Do not set <lookupfield>.LookupCache!
I probably should work on something similar for IBX.