{"id":707,"date":"2017-07-10T09:00:00","date_gmt":"2017-07-10T09:00:00","guid":{"rendered":"https:\/\/staging.infragistics.com\/blogs\/?p=707"},"modified":"2025-02-26T12:21:10","modified_gmt":"2025-02-26T12:21:10","slug":"scrolling-sqlite-tables-xamarin-forms","status":"publish","type":"post","link":"https:\/\/www.infragistics.com\/blogs\/scrolling-sqlite-tables-xamarin-forms","title":{"rendered":"Scroll Seamlessly Through Large SQLite Tables in Xamarin.Forms with Low Memory Overhead"},"content":{"rendered":"\n

\"<\/a>You can bind it to a remote OData service and scroll through the rows, and it will use the velocity of your movement to predict when it needs to fetch data and seamlessly load it before you get there. If you haven\u2019t seen this trick yet, definitely check out our samples browser for Ultimate UI for Xamarin<\/a>, which shows this off well.<\/p>\n\n\n\n

Running the Sample<\/h2>\n\n\n\n

You can get the sample we’ll be building in this article here<\/a>. Once you open the sample, you’ll need to make sure you have our Trial or RTM Nuget packages in your local repository and restore the Nuget packages<\/a>.<\/p>\n\n\n\n

Virtualizing Access to Other Data Sources<\/h2>\n\n\n\n

Our remote data samples in our samples browser and documentation give you lots of details on how to load remote OData services into the grid. But we didn’t stop there, additionally, you can also create your own custom versions of VirtualDataSource<\/code> to virtualize access to other types of remote, or even local, data. In fact, recently, customers were asking us about whether it was possible to use our data grid with a SQLite database, without first loading all the data for a table into memory. This would be required if you wanted to provide some collection directly to the ItemsSource property on the grid, but there\u2019s a better way if you extend VirtualDataSource<\/code>. Lucky for you, though, I already did it<\/a>.<\/p>\n\n\n\n

If you build that project you\u2019ll wind up with a SQLite specific version of our VirtualDataSource<\/code>. This allows for linking to a table, or a joined set of tables, and then allowing for you to seamlessly page over it as if you were scrolling through a large, unbroken contiguous collection. Better yet, you can limit the amount of data pages the data source will keep in memory at one time, so you can put an upper bound on the memory usage in your mobile application.<\/p>\n\n\n\n

SQLite Database Setup<\/h2>\n\n\n\n

Ok, so let\u2019s put it into practice. Given you have a Xamarin.Forms project set up using the XamDataGrid<\/code>, you first need to add a SQLite database to the Android app and the iOS app. For the Android App, this goes in the assets:<\/p>\n\n\n\n

\"<\/figure>\n\n\n\n

The Build Action<\/code> for the database should be marked as AndroidAsset<\/code>:<\/p>\n\n\n\n

\"<\/figure>\n\n\n\n

Given that, this logic, when placed in MainActivity.cs<\/code>, right before Xamarin.Forms is initialized and before the main app is created, will make sure that the SQLite database is accessible to the application at runtime:<\/p>\n\n\n\n

string targetPath = \n    System.Environment.GetFolderPath(\n        System.Environment.SpecialFolder.Personal\n    );\nvar path = Path.Combine(\n    targetPath, \"chinook.db\");\n\nif (!File.Exists(path))\n{\n    using (Stream input = \n        Assets.Open(\"chinook.db\"))\n    {\n        using (var fs = new FileStream(\n            path, \n            FileMode.Create))\n        {\n            input.CopyTo(fs);\n        }\n    }\n}<\/pre>\n\n\n\n
For iOS, you should place the database file in the Resources<\/code> for the application:<\/p>\n\n\n\n
\"<\/figure>\n\n\n\n
And make sure that the Build Action<\/code> is set to BundleResource<\/code>:<\/p>\n\n\n\n
\"<\/figure>\n\n\n\n
Given the database file being properly included, this logic, when placed in AppDelegate.cs<\/code>, right before Xamarin.Forms is initialized and before the main app is created, would ensure that it is accessible to the iOS application at runtime:<\/p>\n\n\n\n
var targetPath = Environment.GetFolderPath(\n    Environment.SpecialFolder.Personal);\ntargetPath = Path.Combine(targetPath, \"..\", \"Library\");\n\nvar path = Path.Combine(targetPath, \"chinook.db\");\nif (!File.Exists(path))\n{\n    var bundlePath = NSBundle.MainBundle.PathForResource(\n        \"chinook\", \n        \"db\"\n    );\n    File.Copy(bundlePath, path);\n} <\/pre>\n\n\n\n
For both platforms, the file path to the SQLite database can now be passed into the Xamarin.Forms App<\/code> when it is created:<\/p>\n\n\n\n
LoadApplication(new App(path));<\/pre>\n\n\n\n
The app will then just make sure that path is available to the page we will be using:<\/p>\n\n\n\n
public App(string dbPath)\n{\n    InitializeComponent();\n    MainPage = new SQLDemo.MainPage(dbPath);\n}<\/pre>\n\n\n\n
Live Virtual Scrolling Through SQLite Tables<\/h2>\n\n\n\n
To read data from a SQLite database, first you need a SQLite client that is compatible with a PCL (portable class library) and\/or Xamarin.Android\/Xamarin.iOS, so we’ll install the sqlite-net-pcl<\/code> Nuget package<\/a>.<\/p>\n\n\n\n
\"<\/figure>\n\n\n\n
The SQLite.NET library includes a lightweight ORM tool<\/a> that it will use to hydrate data being read into POCO<\/a> types, so we first need to create a POCO type for the table we are interested in:<\/p>\n\n\n\n
using SQLite;\nusing System;\nusing System.Collections.Generic;\nusing System.Linq;\nusing System.Text;\nusing System.Threading.Tasks;\n\nnamespace SQLDemo.Data\n{\n    [Table(\"tracks\")]\n    public class Track\n    {\n        [PrimaryKey, AutoIncrement]\n        public int TrackId { get; set; }\n\n        [MaxLength(200)]\n        public string Name { get; set; }\n\n        public int AlbumId { get; set; }\n\n        [Column(\"Title\")]\n        public string AlbumTitle { get; set; }\n\n        public int MediaTypeId { get; set; }\n\n        public int GenreId { get; set; }\n\n        [MaxLength(220)]\n        public string Composer { get; set; }\n\n        public int Milliseconds { get; set; }\n\n        public int Bytes { get; set; }\n\n        public decimal UnitPrice { get; set; }\n    }\n}<\/pre>\n\n\n\n
This type maps to the tracks<\/code> table in the Chinook SQLite sample database, which stores sample data about various tracks off popular music albums. We’ve indicated here, via attributes, various meta information about the table, such as the primary key, and maximum lengths of some of the string columns.<\/p>\n\n\n\n
Now that data can be loaded from the tracks<\/code> table, we are all set up to scroll over that table in the XamDataGrid<\/code>.<\/p>\n\n\n\n
First, we can lay out the grid in XAML:<\/p>\n\n\n\n
<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n<ContentPage xmlns=\"http:\/\/xamarin.com\/schemas\/2014\/forms\"\n             xmlns:x=\"http:\/\/schemas.microsoft.com\/winfx\/2009\/xaml\"\n             xmlns:local=\"clr-namespace:SQLDemo\"\n             x:Class=\"SQLDemo.MainPage\"\n             xmlns:igGrid=\"clr-namespace:Infragistics.XamarinForms.Controls.Grids;assembly=Infragistics.XF.DataGrid\">\n\n    <Grid>\n        <Grid.RowDefinitions>\n            <RowDefinition \/>\n            <RowDefinition Height=\"Auto\" \/>\n        <\/Grid.RowDefinitions>\n\n        <igGrid:XamDataGrid x:Name=\"grid\" RowHeight=\"90\"\n                        SelectionMode=\"MultipleRow\"\n                        HeaderClickAction=\"SortByMultipleColumnsTriState\"\n                        AutoGenerateColumns=\"False\">\n            <igGrid:XamDataGrid.Columns>\n                <igGrid:TextColumn PropertyPath=\"Name\" \n                                LineBreakMode=\"WordWrap\" \n                                Width=\"1*\"\n                                \/>\n                <igGrid:TextColumn PropertyPath=\"Composer\" \n                                LineBreakMode=\"Ellipsis\"\n                                Width=\"1.25*\"\/>\n                <igGrid:TextColumn PropertyPath=\"AlbumTitle\" \n                        HeaderText=\"Album Title\" \n                        LineBreakMode=\"WordWrap\" \n                        Width=\"1*\"\/>\n                <igGrid:NumericColumn PropertyPath=\"UnitPrice\"\n                        HeaderText=\"Unit Price\"\n                        MinFractionDigits=\"2\"\n                        Width=\"1*\"\/>\n            <\/igGrid:XamDataGrid.Columns>\n        <\/igGrid:XamDataGrid>\n    <\/Grid>\n\n<\/ContentPage><\/pre>\n\n\n\n
In the XAML, we’ve defined a XamDataGrid<\/code> and configured some columns, just as if we were about to bind some in memory data to the grid. We could have skipped defining the columns and allowed them to auto generate, but there are sufficient number of columns on the tracks<\/code> table that this would get pretty crowded.<\/p>\n\n\n\n
Ok then, so how do we bind the grid against the SQLite table? First we need to create a connection to talk to the SQLite database:<\/p>\n\n\n\n
_connection = new SQLiteAsyncConnection(dbPath);<\/pre>\n\n\n\n
Where dbPath is the file path to the SQLite database that we passed around earlier. Then, we just need to create a SQLiteVirtualDataSource, configure it and assign it to the grid:<\/p>\n\n\n\n
var dataSource = new SQLiteVirtualDataSource();\ndataSource.Connection = _connection;\ndataSource.TableExpression = \n    \"tracks left outer join albums on tracks.AlbumId = albums.AlbumId\";\ndataSource.ProjectionType = typeof(Track);\n\ngrid.ItemsSource = dataSource;<\/pre>\n\n\n\n
Here we:<\/p>\n\n\n\n