DBGrid alternative which allows scrolling without moving Cursor
lazarus / 16-Databases
----------------------------------------------------------------------
Maris · 2018-03-02 14:50
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 · 2018-03-02 15:00
try the virtualdbgrid package from OPM requires the virtualtreview
package
----------------------------------------------------------------------
Maris · 2018-03-05 19:19
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 · 2018-03-06 09:49
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 · 2018-03-12 15:37
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Timer1Timer(Sender: TObject);begin SQLQueryDetail.DataSource
:= DataSourceMaster; {Looks like no need on linux}
SQLQueryDetail.EnableControls; Timer1.Enabled := False;end; procedure
TForm1.VirtualDBGrid1Scroll(Sender: TBaseVirtualTree; DeltaX, DeltaY:
Integer);begin if not SQLQueryDetail.ControlsDisabled then begin
SQLQueryDetail.DataSource := nil; {Looks like no need on linux}
SQLQueryDetail.DisableControls; end; Timer1.Enabled := False;
Timer1.Enabled := True;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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.VirtualDBGrid1Click(Sender: TObject);begin
SQLQueryDetail.Active := False;
SQLQueryDetail.ParamByName('id').AsString :=
SQLQueryMaster.FieldByName('id').AsString; SQLQueryDetail.Active :=
True;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.
----------------------------------------------------------------------
tonyw · 2018-03-13 09:33
Quote from: Maris on March 12, 2018, 04:37:02 pmTonyw, 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.
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 · 2018-03-13 10:54
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
.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 .LookupCache!
----------------------------------------------------------------------
tonyw · 2018-03-13 14:24
Quote from: mse on March 13, 2018, 11:54:57 amIn 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
.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 .LookupCache!
I probably should work on something similar for IBX.