In this tutorial, I will show you how to populate a UWP ListView control by loading data from an SQLite database.
First, start the Visual Studio IDE (I am using Visual Studio 2019) and click on the button [Create a new project].
In the [Create a new project window] select [C# Blank App (Universal Windows)] and click the [Next] button.
In the [Configure your new project] window give your project a name (I called it ListViewSQLite), select the location for the project, and click on the button [Create].
Finally, select a Target Windows 10 version and a Minimum Windows 10 version, press the [OK] button and wait until the Visual Studio creates the project.
Next, we are going to create an SQLite database and fill it with some data. For that, we will use the [DB Browser for SQLite] application, which you can download here [https://sqlitebrowser.org/].
Start the [DB Browser for SQLite] application and press the [New Database] button.
In the [Save File] dialog, select a location for the database and give it a name (I called it uwp.db).
In the [Edit table definition] window give a name to the Table (I called it Data) and add two fields: [Name] and [Description]. Both fields should be of type [TEXT]. Finally, press the [OK] button to create the table.
Next, select the [Browse Data] panel and click on the [New Record] button to add a new entry to the database. Type a name in the [Name] field and some description in the [Description] field.
Repeat the process so many times as many items you want to have in the database. Press the [Write Changes] button to write the records to the database. Now, close the [DB Browser for SQLite] application and switch back to the Visual Studio IDE.
To store the items from the database, we have to create a container class first. In the [Solution Explorer], right-click on the project name and select [Add -> Class…].
In the [Add new Item] window, type in the name of the class (I called it Item) and click on the [Add] button.
Next, in the [Solution Explorer], double-click on the [Item.cs] file to open it and replace the content of the file with the following code:
namespace ListViewSQLite
{
public class Item
{
public string Name { get; set; }
public string Description { get; set; }
}
}
As you can see, the [Item] class has two string properties [Name] and [Description] mimicking the structure of the database’s [Data] table.
Next, we are going to add a ListView control to the main page and define how the content of each item should be shown. In the [Solution Explorer] double-click on the [MainPage.xaml] file to open it. Replace the content of the file with the following XAML code:
<Page
x:Class="ListViewSQLite.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="using:ListViewSQLite"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
Background="{ThemeResource ApplicationPageBackgroundThemeBrush}"
Loaded="Page_Loaded">
<Grid>
<ListView x:Name="ItemsList" ItemsSource="{Binding Items}">
<ListView.ItemTemplate>
<DataTemplate x:DataType="local:Item">
<StackPanel>
<TextBlock Text="{Binding Name}" FontSize="18" FontWeight="Black"/>
<TextBlock Text="{Binding Description}"/>
</StackPanel>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</Grid>
</Page>
In the code, we added a new [ListView] control with the name [ItemsList]. We bound the [ItemsSource] attribute to [Items] property, which we will define later in the file [MainPage.xaml.cs]. Next, we defined the [ItemTeplate] using the [DataTemplate] element. We set the value of the [DataType] attribute of the [DataTemplate] to [Item], which is the name of the container class we have just created. That allows us proper binding to the class properties. Finally, we defined two [TextBlock] elements and bound their [Text] attributes to the [Name] and [Description] properties of the [Item] class. Additionally, we defined a handler for the [Loaded] event that we will use to load the records from the database.
Now, we are going to create a routine for loading records from the database. For that, however, we first need to add an SQLite package to our project. In the [Solution Explorer] right-click on the [References] node and select [Manage NuGet Packages…].
In the opened window, select the [Browse] panel and type in [System.Data.SQLite.Core] in the search field. Next, select the [System.Data.SQLite.Core] package in the results list and click [Install] to install it. If needed, confirm the changes in the [Preview changes] window.
We also have to add the created database to the project. In the [Solution Explorer], right-click on the [Assets] and select [Add -> Existing item…].
In the [Open File] dialog, navigate to the location with the database file, select the database file, and click [Add] to add the file to the project. Next, select the database filename in the [Solution Explorer] and the value of the [Copy to Output Directory] property in the [Properties] panel to [Copy always].
Double-click on the project name in the [Solution Explorer] and select [Add -> Class…]. Name the class [DatabaseConnector] and click the [Add] button. Open the DatabaseConnector.cs file and replace its content with the following code:
using System.Collections.ObjectModel;
using System.Threading.Tasks;
using System.Data.SQLite;
namespace ListViewSQLite
{
public static class DatabaseConnector
{
public static async Task LoadRecordsAsync(ObservableCollection<Item> items)
{
using(SQLiteConnection connection = new SQLiteConnection("Data Source=Assets/uwp.db;Version=3"))
{
await connection.OpenAsync();
SQLiteCommand command = new SQLiteCommand("SELECT * FROM Data", connection);
using(var reader = await command.ExecuteReaderAsync())
{
var nameOrdinal = reader.GetOrdinal("Name");
var descriptionOrdinal = reader.GetOrdinal("Description");
while (await reader.ReadAsync())
{
items.Add(new Item() { Name = reader.GetString(nameOrdinal), Description = reader.GetString(descriptionOrdinal) });
}
}
}
}
}
}
As you can see, we defined the [DatabaseConnector] class as static, so we can access its methods without creating an instance. The [DatabaseConnector] class contains one async method, namely [LoadRecordsAsync] that takes a reference to an [ObservableConnection] and populates this collection with records loaded from the database. We used async methods of the SQLite package to keep GUI responsive while loading data.
Finally, we need to edit the [MainPage.xaml.cs] file to bring all parts together. Open the file and replace its content with the following code:
using System.Collections.ObjectModel;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
namespace ListViewSQLite
{
public sealed partial class MainPage : Page
{
public ObservableCollection<Item> Items { get; set; }
public MainPage()
{
Items = new ObservableCollection<Item>();
DataContext = this;
this.InitializeComponent();
}
private async void Page_Loaded(object sender, RoutedEventArgs e)
{
await DatabaseConnector.LoadRecordsAsync(Items);
}
}
}
Here, we first declare an [ObservableCollection] of [Item]s called [Items]. We use the constructor to initialize the collection and set the DataContext property of the page to the instance of the page. This allows us to bind the [ItemsSource] attribute of the [ListView] control to the [Items] property of the page. Finally, we define the [Page_Loaded] method, which will be called on the [Loaded] event of the page. In that method, we call the [LoadRecordsAsync] method of the [DatabaseConnector] class and pass the [Items] collection to it.
That is all! If you start the application now, you will see the data loaded in the [ListView] control.
