Gridview and the Data Handling in gambas Event
Here is another short tutorial based around database work under Gambas. I think Gambas is a marvelous data manipulation tool. I suspect it has greater facility than the so-called 4th Generation ‘application generators’ I was using not so long ago.
The tutorial highlights a feature of gridview display which saves huge amounts of time when displaying large databases. The feature is actually used in the Gambas database example so is nothing original. I do find with a lot of Gambas examples that the the example author tries too hard to show too many capabilities with the result that the example itself becomes confusing.
The following Gambas2 code:
1 Deletes a file test.sqlite in the users home directory (if it exists).
2 Creates an SQLite database test.sqlite and defines a table sampleTable with two integer fields, one a sequence (yes, I know SQLite does that internally anyway!) and the other a random number.
3 Fills the table with 10000 records.
4 Displays the results in a gridview.
On Gambas you need nothing more than a gridview (gridview1) on an otherwise empty form. The only interesting bit that may need explanation is the GridView1_Data event. This is called to fill an empty gridview cell as it is exposed (by scrolling). It saves having to load all 10000 records into the gridview with the time penalty that would imply.
Here is the code for FMain.class:
We initially set the row count of the gridview to 0. Having read the database into result $res, it will have 10000 records. In other words $res.count=10000. Setting GridView1.Rows.Count = $res.Count will make the gridview (which you have put on the form) a window to this grid of 1000 rows by 2 columns. The fact that empty cells are now displayed in this window to a much bigger grid causes the gridview object to repeatedly call Gridview1_data for each empty cell in view. The event then fills the value of the blank cell from the equivalent value in the result set. In no time at all every blank cell is filled with the corresponding data from the result set.
If you scroll down the gridview, it exposes empty cells in the grid which are automatically filled by the data handling event. And so on.
You could load all the available data into the gridview in one step but it would take an unacceptably long time. This method gives the impression of blinding speed by only loading the gridview cell values which are visible. It’s just so clever.
The tutorial highlights a feature of gridview display which saves huge amounts of time when displaying large databases. The feature is actually used in the Gambas database example so is nothing original. I do find with a lot of Gambas examples that the the example author tries too hard to show too many capabilities with the result that the example itself becomes confusing.
The following Gambas2 code:
1 Deletes a file test.sqlite in the users home directory (if it exists).
2 Creates an SQLite database test.sqlite and defines a table sampleTable with two integer fields, one a sequence (yes, I know SQLite does that internally anyway!) and the other a random number.
3 Fills the table with 10000 records.
4 Displays the results in a gridview.
On Gambas you need nothing more than a gridview (gridview1) on an otherwise empty form. The only interesting bit that may need explanation is the GridView1_Data event. This is called to fill an empty gridview cell as it is exposed (by scrolling). It saves having to load all 10000 records into the gridview with the time penalty that would imply.
Here is the code for FMain.class:
01 | ' Gambas class file |
02 | PRIVATE $hConn AS NEW Connection |
03 | PRIVATE $res AS Result |
04 | '------------------------------------------------- |
05 | PUBLIC SUB Form_Open() |
06 | DIM iCount AS Integer |
07 | DIM hTable AS Table |
08 | DIM rTest AS result |
09 | DIM sql AS String |
10 | |
11 | 'define the gridview layout |
12 | GridView1.header = GridView.Horizontal |
13 | GridView1.grid = TRUE |
14 | GridView1.Rows.count = 0 |
15 | GridView1.Columns.count = 2 |
16 | GridView1.Columns[0].text = "ID" |
17 | GridView1.Columns[1].text = "Value" |
18 | GridView1.Columns[0].width = 55 |
19 | GridView1.Columns[1].width = 55 |
20 | |
21 | |
22 | WITH $hConn |
23 | .Type = "sqlite" |
24 | .host = User.home |
25 | .name = "" |
26 | END WITH |
27 | |
28 | 'delete an existing test.sqlite |
29 | IF Exist(User.home & "/test.sqlite") THEN |
30 | KILL User.home & "/test.sqlite" |
31 | ENDIF |
32 | |
33 | 'create test.sqlite |
34 | $hConn.Open |
35 | $hConn.Databases.Add("test.sqlite") |
36 | $hconn.Close |
37 | |
38 | 'define the table sampleTable |
39 | $hconn.name = "test.sqlite" |
40 | $hConn.Open |
41 | hTable = $hConn.Tables.Add("sampleTable") |
42 | hTable.Fields.Add("s_seq", db.Integer) |
43 | hTable.Fields.Add("s_rndm", db.Integer) |
44 | hTable.PrimaryKey = ["s_seq"] |
45 | hTable.Update |
46 | |
47 | 'fill the table with generated data |
48 | $hconn.Begin |
49 | rTest = $hConn.Create("sampleTable") |
50 | FOR iCount = 1 TO 10000 |
51 | rTest!s_seq = iCount |
52 | rTest!s_rndm = Int(Rnd(0, 100)) |
53 | rTest.Update |
54 | NEXT |
55 | $hConn.Commit |
56 | |
57 | 'read the database |
58 | sql = "select s_seq as ID, s_rndm as Value from sampleTable" |
59 | $res = $hconn.Exec(sql) |
60 | |
61 | CATCH |
62 | $hConn.Rollback |
63 | Message.Error(DConv(Error.Text)) |
64 | |
65 | END |
66 | '------------------------------------------------- |
67 | PUBLIC SUB Form_Activate() |
68 | 'change the rowcount of the gridview from 0 to the number of records. |
69 | 'This triggers the data handling event |
70 | GridView1.Rows.Count = $res.Count |
71 | END |
72 | '------------------------------------------------- |
73 | PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer) |
74 | $res.moveTo(row) |
75 | GridView1.Data.text = Str($res[GridView1.Columns[column].text]) |
76 | END |
77 | '------------------------------------------------- |
78 | PUBLIC SUB Form_Close() |
79 | $hconn.Close |
80 | END |
81 | '------------------------------------------------- |
If you scroll down the gridview, it exposes empty cells in the grid which are automatically filled by the data handling event. And so on.
You could load all the available data into the gridview in one step but it would take an unacceptably long time. This method gives the impression of blinding speed by only loading the gridview cell values which are visible. It’s just so clever.
