Using a WebDataSource to Display Millions of Rows in a WebListBox – Xojo Programming Blog (2024)

If you are wondering why you would use a WebDataSource for your WebListBox, this post gives you some arguments and tips for making that decision and implementing it.

Why would you need a WebDataSource, isn’t AddRow enough?

Let’s prepare a million records database to play with.

In this example we will use a humble SQLite database, but keep in mind a WebDataSource can be anything. For example you could display the result of calling an external API, or your hard drive filesystem.

The following SQL creates a table called “things”, with three columns: “foo”, “bar” and “baz” with one million rows:

CREATE TABLE things ( id INTEGER PRIMARY KEY AUTOINCREMENT, foo TEXT, bar TEXT, baz TEXT);-- Time to populate that tableWITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM cnt LIMIT 1000000)INSERT INTO things (foo, bar, baz)SELECT 'foo_' || x AS foo, 'bar_' || x AS bar, 'baz_' || x AS bazFROM cnt;-- Finally, let's create some indexes, so we can sort the columns quicklyCREATE INDEX "idx_foo" ON things ( foo, id, bar, baz);CREATE INDEX "idx_bar" ON things ( bar, id, foo, baz);CREATE INDEX "idx_baz" ON things ( baz, id, foo, bar);VACUUM;

We just need to store this SQL as a string constant, for convenience. Let’s create one, called kDatabaseSetupSQLite in your Application class, and paste it above the SQL code.

First Time Setup

The first time we launch the application, we need to make sure our database file has been created and populated. It won’t take too much to generate the database, but we’ll try to do it just once anyway.

Create a new method in your App class, called DBFile, that returns a FolderItem, pointing to the place we want to store the DataBase. Make it Public, as we will use this method later from the Session. The contents:

Return SpecialFolder.Desktop.Child("test-db.sqlite")

Nothing fancy. Use another path if you want. It will be around ~200MB, just remember to delete it when you don’t need it anymore.

Let’s create another App method, called SetupDatabase. It will be in charge of creating and populating the database just once, so if you restart the server, the data will still be there:

Var db As New SQLiteDatabasedb.DatabaseFile = DBFiledb.WriteAheadLogging = True// The file is already there, no need to build it againIf db.DatabaseFile.Exists Then ReturnEnd Ifdb.CreateDatabasedb.Connectdb.ExecuteSQL(kDatabaseSetupSQLite)

Lastly, implement the App.Opening event and add a method call to SetupDatabase:

SetupDatabase

That should do the trick.

Preparing the Session

It’s recommended to have a Database instance for each Session. Add a new public Property to your Session class called Database of type SQLiteDatabase.

Then, add a handler for the Opening event with the following code:

Database = New SQLiteDatabaseDatabase.DatabaseFile = App.DBFileDatabase.WriteAheadLogging = TrueDatabase.Connect

Every time a user arrives to the web application, a new isolated SQLiteDatabase connection will be created.

Implementing the WebDataSource Interface

Since Xojo 2024r2, the amount of methods you need in order to implement WebDataSource has been reduced.

Create a new class called DatabaseDataSource. Then, in the inspector panel, select the WebDataSource interface:

This includes the three required methods: ColumnData, RowCount and RowData.

ColumnData
In this method, you need to return an array of WebListBoxColumnData. We have four columns in this example, here is the code:

Var result() As WebListBoxColumnDataresult.Add(New WebListBoxColumnData("ID", "id"))result.Add(New WebListBoxColumnData("Foo", "foo"))result.Add(New WebListBoxColumnData("Bar", "bar"))result.Add(New WebListBoxColumnData("Baz", "baz"))Return result

RowCount
Xojo needs to know the amount of data your data source has. Returning an Integer is enough, but we will query our database.

Try Var rows As RowSet = Session.Database.SelectSQL("SELECT COUNT(*) AS counter FROM things") Return rows.Column("counter").IntegerValueCatch DatabaseException Return 0End Try

RowData
This is the place where you need to return the row contents. As you can see, there is a rowCount and a rowOffset parameter, meaning that we won’t need to return 1 million records. The control just loads a subset. The amount is dynamically calculated, and varies depending on the height of your WebListBox and the height of the rows.

A sortColumns parameter is also provided. If you allow your users to sort the columns, you need to use it to know the column and direction. Fortunately, this example has sortable columns. Here is the code to comply with the WebDataSource Interface:

Var sql As String = "SELECT id, foo, bar, baz FROM things"If sortColumns <> "" Then sql = sql + " ORDER BY " + sortColumnsEnd Ifsql = sql + " LIMIT " + rowOffset.ToString + ", " + rowCount.ToStringVar result() As WebListBoxRowDataVar rows As RowSet = Session.Database.SelectSQL(sql)// This isn't needed, it's just to demonstrate how to use cell renderersVar style As New WebStylestyle.Bold = Truestyle.BackgroundColor = Color.Tealstyle.ForegroundColor = Color.WhiteFor Each row As DatabaseRow In rows Var newRowData As New WebListBoxRowData newRowData.PrimaryKey = row.Column("id").IntegerValue newRowData.Value("id") = row.Column("id") newRowData.Value("foo") = row.Column("foo") newRowData.Value("bar") = New WebListBoxStyleRenderer(style, row.Column("bar")) newRowData.Value("baz") = row.Column("baz") result.Add(newRowData)NextReturn result

Preparing the User Interface

You’ve reached the easiest part, building the interface takes less than a minute.

1. Drop a DatabaseDataSource control into your WebPage
2. Drop a WebListBox control into your WebPage
3. In the WebListBox Opening event, configure the DataSource

If you name your DataSource instance “MyDataSource”, this is the line of code required in the WebListBox Opening event:

Me.DataSource = MyDataSource

Here is a short video:

That’s It!

The WebListBox control, when combined with a WebDataSource, is a very robust and performant solution for displaying a large set of data.

Download the project:

Happy coding!

Ricardo has always been curious about how things work. Growing up surrounded by computershe became interested inweb technologies in the dial-up connections era. Xojo has been his secret weapon and language of preference since 2018. When he’s not online, chances are he will be scuba diving … or crocheting amigurumis. Find Ricardo on Twitter@piradoiv.

• Facebook
• Instagram
• LinkedIn
• Twitter
• YouTube
• GitHub