EhLib

User's guide

Common understanding of TDBGridEh, TDataLink and TDataSet.

All below text in equal degrees pertains as to TDBGridEh component as to TDBGrid component.
A TDBGridEh control lets you view and edit records in a dataset in a tabular grid format.
TDBGridEh does not story data in in itself, it only show data from dataset via TDataLink object. Every database control have internal TDataLink object to interaction with dataset. You can connect TDBGridEh to dataset using DataSource property. If you already use TStringGrid component you can see that data shows in TStringGrid and in TDBGridEh very similarly, but mechanism that use to show data in  TStringGrid and in TDBGridEh very different. In TStringGrid count of rows is equal of rows in array of strings while in TDBGridEh (and TDBGrid) count of rows always not more then count of visible rows and although vertical scrollbar can display thumb position regarding the count of record in dataset it take info not from grid but directly from dataset. TDataSet don't allows us to work with data as with array of data i.e. we can not quickly get value of the field from certain record, some types of dataset have not even such notion as record number (in such datasets we can only know that we inhere in the begin of dataset or in the end of its or somewhere between, in that case DBGrid shows vertical vertical scrollbar only in three positions). But to have possibility to draw several record simultaneously TDataLink object allows to have buffer of records (record buffer window) with quick readonly access. DBGrid use this possibility of datalink and set size of record buffer window equal of count of visible rows in grid. We can not control what record must be first in this buffer, DataLink itself scroll record buffer window then we navigate through the dataset and it control the scrolling of record buffer window as that the active record as always in record buffer window. It is a reason why the active record change position when users change thumb position of vertical scrollbar using mouse.

TDBGridEh and vertical scrollbar.

If you works with different type of dataset you can notice that for some type of dataset DBGrid show vertical scrollbar validly but for over vertical scrollbar have only three position independently of record count in dataset. To set vertical scrollbar accomodation DBGrid use RecordCount and RecNo property of DataSet component. But some dataset and even same dataset under some condition holds -1 in  RecordCount and RecNo. DataSet function IsSequenced indicates whether the underlying database table uses record numbers to indicate the order of records. When IsSequenced returns True, applications can safely use the RecNo property to navigate to records in the dataset and DBGrid use RecNo property to show thumb position in vertical scrollbar. But when IsSequenced returns False DBGrid can not define current record position and show vertical scrollbar in three positions. DBGridEh component have possibility to show proportional scrollbar for no sequenced dataset. To do it need to activate SumList and create list of record bookmars. Set SumList.Active to True and SumList.VirtualRecords to True. SumList will run through dataset and create list of  record bookmarks, if you use client/sever technology to access database SumList will force dataset to fetch all records, so it operation can take much time. Keep in mind that VirtualRecords will work only for full relationship bookmarks dataset, it means that DataSet.ComapreBookmark function has to return > 0 if bookmark1 > bookmark1 (i.e. record to which indicates bookmark1 have to be after record to which indicates bookmark1), = 0 if bookmark1 = bookmark1, < 0 if bookmark1 = bookmark1. TBDEDataSet in most cases support full relationship bookmarks.

Using DBGridEh for sorting and filtering data in the DataSet.

DBGridEh can not sort or filter data by itself. But it can pass action for sorting or filtering to the specal object that will do it in DataSet. You can adjust grid fot a two type of sorting (local or server), using property SortLocal and two type of filtering (also, local and server), but real possibility to aplly each type of operation depended of the type of DataSet. For instance, TBDEDataSet does not suppot sorting locally (inside DataSet) so you can not sort data locally when grid is linked to TQuery or TTable. Furthermore, EhLib have only special objects for standart type of DataSet (not for a third party DataSet's).
Below you can see a table of special object and DataSet in witch these objects can sort or filter data.

  When grid is adjusted for a local filtering the special object will build exprression and assign it to a property Filter of DataSet. So need also to set Filtered to True manually.
   When grid is adjusted for a server filtering, the special object will build SQL:ORDER BY exprression and replace ORDER BY string in SQL or Command property of DataSet and reopen DataSet.
   When grid is adjusted for a local sorting the special object will do a sorting using specific of the type of DataSet.
  When grid is adjusted for a server sorting the special object will build expression for SQL 'WHERE' clause in SQL string of dataset. It will try to found string that begining from key string '/*Filter*/' (You can change this key string using global variable - SQLFilterMarker) in SQL expression and adds filter expression after this string in same line. So your SQL query have to have a line that begining from '/*Filter*/'. For instance:
' select *
from table1
where
/*Filter*/ 1=1 '

  For automatically filtering/sorting data in dataset need to add one of the units EhLibXXX (EhLibADO, EhLibBDE, EhLibCDS ... dependenly of the dataset that grid is linked to) to 'uses' clause of any unit of your project. This units have code to register TDatasetFeaturesEh based classes (special object) for respective DataSet's. For other third party DataSet's you have to write and register your own special object or you have to wrtie OnApplyFilter or/and OnSortMarkingChanged event for every required grid. More other, you can write OnApplySorting and/or OnApplyFilter for the global variable DBGridEhDataService. These events has TNotifyEvent type where Sender is TCustomDBGridEh.

Adjusting grid for sorting data:
TDBGridEh allows to show special  sortmarking bitmaps (small triangles) in the right part of title cell. In order to automatically marking title by sortmarking bitmaps add dghAutoSortMarking to OptionsEh property. Add dghMultiSortMarking too OptionsEh in order to allow sortmarking several columns simultaneously. Set Column.Title.TitleButton to true for titles which will have possibility to change sortmarkers at run time. Or you can set ColumnDefValues.Title.TitleButton to allow to set sortmarking for all columns which Column.Title.TitleButton is not changed. At runtime clicking on title will change sortmarking. Holding Ctrl key allows to mark several columns simultaneously. After user change sormarking grid call OnSortMarkingChanged event or pass action to special object, if it registered. Special object will use Grid.SortLocal property to determine how to sort data: locally or on the server. If you write OnSortMarkingChanged you can use TDBGrid.SortMarkedColumns property to access to sortmarked columns and TColumnEh.Title.SortMarker property to get state of the sortmarker.

Adjusting grid for filtering data:
TDBGridEh allows to show special row (under title) where user enter expressions in cells for filtering records in dataset. (Expression in the cell can look like: "1", ">=1", ">1 AND <=5", "%TestStr%"). Set STFilter.Visible to True to show this special row. AT Run time, when cursor reside in the filter cell they can press ENTER to apply filter. Grid will parse expression in every not empty filter cell and call TDBGridEh.ApplyFilter, call OnApplyFilter event or pass action to special object, if it registered.

For normally parsinging, the expression in cell have to has next format:

[Operator1] Operand1 [)] [AND|OR Operator2 Operand2 [)] ]

Where OperatorX is one of [ = | <> | != | > | < | >= | <= | ~ | !~ | in ( ]
OperandX is a numeric, date or string value or for the operator 'IN' it is a list of values divided by comma.

For instance:
'1'
'>=1'
'>1 AND <=5'
'~ TestStr%'
'!~ TestStr_'
'in (1,2,3,4,5,6) and >100'
'in (Str1,Str2,Str3)'

Complex titles.

TDBGridEh allows to create title above several columns. See Figure:

To do it set DBGridEh.UseMultiTitle property to True and fill label of the fields or caption of title of the column using next rule: every part of text  in field label or column title must consist of several parts divided by sign "|", where every common part is same for several columns. Other fields or captions must contain same text in corresponding parts. 

For instance:

Field1.DisplayLabel := 'Title1|SubTitle1';
Field2.DisplayLabel := 'Title1|SubTitle2';
    or
DBGridEh.Columns[0].Title.Caption := 'Title1|SubTitle1';
DBGridEh.Columns[1].Title.Caption := 'Title1|SubTitle2'; 

Title acts as button.

To force title cells act as button set Column.Title.TitleButton to True. Write an OnTitleBtnClick event to take specific action when the user clicks on title cell.

Show bitmaps in titles.

To show bitmap in titles instead of caption use TitleImages property of TDBGridEh and ImageIndex property of TColumnTitleEh.

Default values for title properties.

To set default values for title properties use TDBGridEh.ColumnDefValues.Title property.

Footers and total values.

TDBGridEh allows to show special row (footer) or rows at bottom part. Use FooterRowCount property to specify the number of footer rows in the grid. Use Footer or Footers property of TColumnEh object to specify information which need to show in footer cells. Footers property useful then you have more then one footer rows. Footers is a collection of TColumnFooterEh objects where information from i-th aliment of collection will be show in i-th cell of footer column. In footer cell, it is possible to show: Sum value for specified field, record count, value of a specified field or static text. Use property Footer.ValueType or Footers[i].ValueType to specify which type of value will be show in footer cell. If ValueType = fvtStaticText, then set the property Value to specify text which need to show. If ValueType = fvtFieldValue, then you need to set property FieldName to specify field, value of which need to show. To force grid to calculate total values need to activate SumList (DBGridEh.SumList.Active := True). Set ValueType to fvtSum and grid must to show sum value of the column field in the footer cell, you can also specify Column.Footer.FieldName to calculate total value of the other field. Set ValueType to fvtCount to force grid to show count of records in the footer cell.

Show bitmaps in data cells depending on field values.

TDBGridEh allows to show bitmaps from TImageList component depending on field values. To show bitmaps depending on field values need: Fill list of field values to Column.KeyList property (every value in separate line) and set Column.ImageList property to ImageList control that has the bitmap in according index. Set Column.NotInKeyListIndex to index of bitmap that will be shown if field's value does not correspond to any value in KeyList (for instance you can set index of image for Null field value). At run time you are not allowed to edit bitmap in column cell. Use blank key and mouse click to set next value from Column.KeyList to the field; Shift-blank key and Shift-Mouse click to set previous value from Column.KeyList. Set Column.DblClickNextval to True have allows to change value on mouse double click.

Checkboxes for boolean and noboolean field.

Grid automatically shows checkboxes for boolean field. To show checkboxes for non boolean fields fill first line of Column.KeyList property that corresponds to the checked state of the checkbox, second line - non checked state, and set Column.Checkboxes ptoperty to True. Line of KeyList can represent more than one value in a semicolon-delimited list of items.

Data row height.

Use RowHeight and RowLines properties to specify the data row height. Full data row height = height in pixels of RowLines + RowHeight. Set RowSizingAllowed to True to enable ability to change row height using mouse at run time.

To brake a long text in data row to a multiline set Column.WordWrap to True. Text will wrap if row height > one text line.

Draw memo fields.

To draw memo fields as text set DrawMemoText to True.

Customize cells font attributes and color.

TDBGridEh properties Font and Color describes the font and color used to draw the cells in the grid.
TColumnEh properties Font and Color describes the font and color used to draw the cells in the specified column. 

Events to customize cells font attributes and color.

There are several events that you can write to customize cell font attributes and color before a data cell will be drawn.
You can write TDBGridEh.OnDrawColumnCellEvent event handler to provide customized drawing for the data in the cells of the grid. You can draw on the cell using the methods of the Canvas property. But if you want only change attributes of font or color I advise to use below event.
You can write TDBGridEh.OnGetCellParams event to take specific action before a data cell is draw. You can change draw font and background color. This event suitable to use then you want to change attributes of the font or color of whole row.
If you want to change attributes of cell in specified column you can use TColumnEh.OnGetCellParams. Write this event event to take specific action before a column data cell is draw or edit. Before a column data cell is draw you can change draw font, background color, alignment, ImageIndex, Text or State of checkbox. Before a column data cell is edit you can change edit font, background color, text or readonly state.

Default values for column properties.

To set default values for column properties use ColumnDefValues property. New created columns will take property values from ColumnDefValues properties and will hold them till first assign.

Several fields in the dropdown lookup list.

To show several fields in lookup list for lookup fields set Column.LookupDisplayFields to the list of fields.Semicolons should separate multiple field names. Name Column.Field.LookupResultField must be present in LookupDisplayFields list. Lookup list with several fields can be applied only for lookup fields.

Column with simple lookup values.

You can show another text in column depending on field values. Use KeyList and PickList property. KeyList have to contain a values which kept in the field but PickList in according index have to contain a values to show. Set Column.NotInKeyListIndex to index of text from PickList that will be shown if field value do not contain in KeyList (for instance you can set index of text for Null field value). Set Column.DblClickNextval to True to change value on mouse double click.

Dropdown calendar.

For TDateField and TDateTimeField fields inplace editor will show dropdown button to show dropdown calendar. Set Column.ButtonStyle to cbsNone to prevent the display of the dropdown button.

Inplace editor Color and Font.

Inplace editor takes Color and Font from cell color. Cell takes Color and Font from Column Color and Font, and OnGetCellParams event. Inplace editor automatically set multiline mode when row height > height of one line and property WordWrap for column is True.

Set AutoFitColWidths to True for automatic column resizing to set grid width equal client width. MinAutoFitWidth property determining the minimum width of grid for that column widths will be recalculated.

Use OptionsEh property to show/hide 3D frame for fixed, frozen, footer and data rows.
Use Flat property to show grid in flat style.

EhLib have set of functions to export data from DBGridEh to Text, Csv, HTML, RTF, XLS and internal formats. It can write data to stream (TStream object) or to file.

Example
Pascal: SaveDBGridEhToExportFile(TDBGridEhExportAsText,DBGridEh1,'c:\temp\file1.txt',False);
C++: SaveDBGridEhToExportFile(__classid(TDBGridEhExportAsText),DBGridEh1,"c:\\temp\\file1.txt",false);

EhLib have set of functions to import data from Text and internal formats to DBGridEh's dataset. It can read data to stream (TStream object) or from file.

In lookup inplace editor you can clear (set to Null) LookupKeyField value at runtime. Simply select whole text and press Delete key.

Frozen zone.

Frozen zone is a set of columns appear at the left of the grid that cannot be scrolled. Unlike the fixed columns the frozen columns can receive the edit focus. To specify the count of right nonscrolling columns set FrozenCols property.

Increment search

TDBGridEh allows users to accomplish special "increment" search in grid column. When user enter in increment search mode he can type chars and grid will try to locate text in the current column. Use dghIncSearch and dghPreferIncSearch values (OptionsEh property) to manipulate increment search in the grid. dghIncSearch value allows to do increment search in grid. At run time you can use next key for increment searching:
    Ctrl+F - to begin increment searching.
    Ctrl+Enter - to search next matching.
    Ctrl+Shift+Enter - to search prior matching.
If dghIncSearch in OptionsEh and column is read only then grid will set increment search mode automatically on first key press and will return to normal mode after 1.5 sec. dghPreferIncSearch value determine that the grid will set increment search mode automatically on first key press instead of cell editing.

Horizontal or vertical scrollbars.

Use VertSctollbar, HorzSctollbar properties to hide/show and track horizontal or vertical scrollbars.

Multiselect

TDBGridEh allows to select records, columns and rectangle areas for following operations on selected area.
On allowing of multiselect affects next properties:
    dgMultiSelect in Options property - Specifies whether the multiselect is allowed.
    dghClearSelection in OptionsEh property - Specifies whether the selection will be cleared after user move to next cell.
    EditActions property - Specifies actions which user can execute on selection (Copy,Cut,Delete,Paste,SelectAll).
    AllowedSelections - Specifies the types of selection that allowed to do (Rows, Columns, Rectangle area, All).
Selection property specify a current multiselection state, selected records, columns or rectangle area and have properties and functions to access them.

Save and restore grid and columns layout to/from registry or ini file.

TDBGridEh have set of routines to save and restore grid and columns layout to/from registry or ini file:
    RestoreColumnsLayout    - Restore Columns Order , Width , SortMarker from registry.
    RestoreColumnsLayoutIni    - Restore Columns Order , Width , SortMarker from the ini file.
    RestoreGridLayout    - Restore Columns Order, Width, Visibility, SortMarker, Sortmarked index and/or row height from registry.
    RestoreGridLayoutIni    - Restore Columns Order, Width, Visibility, SortMarker, Sortmarked index and/or row height from the ini file.
    SaveColumnsLayout    - Save Columns Order, Width, SortMarker in registry.
    SaveColumnsLayoutIni    - Save Columns Order, Width, SortMarker in the ini file.
    SaveGridLayout    - Save Columns Order, Width, Visibility, SortMarker, Sortmarked index and row height in registry.
    SaveGridLayoutIni    - Save Columns Order, Width, Visibility, SortMarker, Sortmarked index and row height in the ini file.

This version of TDBGridEh does not support next features:
    TDBGridEh can not have individual row height of every data row.
    TDBGridEh can not works as TreeView. It can not have nodes and leafs.
    TDBGridEh can not merge data cells horizontally or vertically.

Although TDBGridEh does not inherited from TCustomDBGrid component, there are many alike properties in TDBGridEh and TDBGrid.
It allows convert existing TDBGrid component to TDBGridEh with minimum losses.
To convert existing TDBGrid component to TDBGridEh follow next instructions:

Open form with TDBGrid component in Delphi IDE.
Set view form as Text (Alt-F12) 
Rename all TDBGrid objects to TDBGridEh  ('object DBGrid1: TDBGrid' -> 'object DBGrid1: TDBGridEh')
Set view form back as Form (Alt-F12)
Rename all TDBGrid objects in form declaration to TDBGridEh ('DBGrid1: TDBGrid;' -> 'DBGrid1: TDBGridEh;' )
Try to recompile project.

---

EhLib Team E-mail: support@ehlib.com