Gridview and the Data Handling 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:

' Gambas class file
PRIVATE $hConn AS NEW Connection
PRIVATE $res AS Result
'-------------------------------------------------
PUBLIC SUB Form_Open()
  DIM iCount AS Integer
  DIM hTable AS Table
  DIM rTest AS result
  DIM sql AS String
  
  'define the gridview layout
  GridView1.header = GridView.Horizontal
  GridView1.grid = TRUE
  GridView1.Rows.count = 0
  GridView1.Columns.count = 2
  GridView1.Columns[0].text = "ID"
  GridView1.Columns[1].text = "Value"
  GridView1.Columns[0].width = 55
  GridView1.Columns[1].width = 55
  
  
  WITH $hConn
    .Type = "sqlite"
    .host = User.home
    .name = ""
  END WITH 
  
  'delete an existing test.sqlite  
  IF Exist(User.home & "/test.sqlite") THEN 
    KILL User.home & "/test.sqlite"
  ENDIF 
  
  'create test.sqlite
  $hConn.Open
    $hConn.Databases.Add("test.sqlite")
  $hconn.Close
  
  'define the table sampleTable
  $hconn.name = "test.sqlite"
  $hConn.Open
    hTable = $hConn.Tables.Add("sampleTable")
    hTable.Fields.Add("s_seq", db.Integer)
    hTable.Fields.Add("s_rndm", db.Integer)
    hTable.PrimaryKey = ["s_seq"]
    hTable.Update
    
  'fill the table with generated data
  $hconn.Begin
    rTest = $hConn.Create("sampleTable")
    FOR iCount = 1 TO 10000
      rTest!s_seq = iCount
      rTest!s_rndm = Int(Rnd(0, 100))
      rTest.Update
    NEXT
  $hConn.Commit
  
  'read the database
  sql = "select s_seq as ID, s_rndm as Value from sampleTable"
  $res = $hconn.Exec(sql)
  
  CATCH 
    $hConn.Rollback
    Message.Error(DConv(Error.Text))
    
END
'-------------------------------------------------
PUBLIC SUB Form_Activate()
  'change the rowcount of the gridview from 0 to the number of records. 
  'This triggers the data handling event
  GridView1.Rows.Count = $res.Count  
END
'-------------------------------------------------
PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer)
  $res.moveTo(row)
  GridView1.Data.text = Str($res[GridView1.Columns[column].text])
END
'-------------------------------------------------
PUBLIC SUB Form_Close()
  $hconn.Close
END
'-------------------------------------------------

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: