Home / Questions / 2336
Accepted
Edwin V
Asked: 2020-01-11 19:25:53 +0800 CST 2020-01-11 19:25:53 +0800 CST

How to bind to an SQLite query

  • 772

I want to bind data from an SQLite table to a listView but I want to do it from my viewModel, how can I do it? Thanks.

c#

1 Answers

  1. Best Answer

    There are many ways to do it, this is my preferred way, since you have not put any code (you should do it in the future to make things easier for those of us who want to help you) I have created this small solution.

    You should add this Nuget package (there are many SQLite options) - SQLite.Net-PCL

    And for convenience I recommend adding this VS extension

    model

    You can Get BindableBase here if you don't have it yet

    public class User : BindableBase
{
    private int _id;

    public int Id
    {
        get { return _id; }
        set { SetProperty(ref _id, value); }
    }

    private string _name;

    public string Name
    {
        get { return _name; }
        set { SetProperty(ref _name, value); }
    }
}

    view model

    I have chosen to do lazy initialization of the viewmodel, so that I can instantiate the viewmodel from xaml and still make sure the viewmodel is initialized. The Initialize method handles initialization (a bit of an obvious one). To save you trouble I have additionally used a deadlock to avoid concurrency errors.

    public class MainAppVM : BindableBase
{

    private bool _initialized = false;
    private object _lockject = new object();
    private User _firstUser;


    public User FirstUser
    {
        get
        {
            Initialize();

            return _firstUser;
        }
        set
        {SetProperty(ref _firstUser, value);}
    }

    private List<User> _userList;

    public List<User> UserList
    {
        get
        {
            Initialize();
            return _userList;
        }
        set
        {SetProperty(ref _userList, value);}
    }

    public void Initialize()
    {

        lock (_lockject)
        {
            if (!_initialized)
            {
                _initialized = true;
                var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "db.sqlite");

                using (SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), path))
                {
                    var list = from user in conn.Table<User>()
                               select user;

                    this._firstUser = list.FirstOrDefault();
                    this._userList = list.ToList();
                }
            }
        }
    }
}

    xaml

    look at line #6 that starts with xmlns:vmthere I have referenced the namespace where I have the viewmodel.

    Then I instantiate the viewmodel as datacontext

    <Page.DataContext>
    <vm:MainAppVM></vm:MainAppVM>
</Page.DataContext>

    And then I solve everything with the binding.

    <Page
    x:Class="App3.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="using:App3"
    xmlns:vm="using:App3.ViewModels"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d"
    Loaded="Page_Loaded"
    >
    <Page.DataContext>
        <vm:MainAppVM></vm:MainAppVM>
    </Page.DataContext>
    <Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
        <Grid.RowDefinitions>
            <RowDefinition Height="1*"  />
            <RowDefinition  Height="4*" />
        </Grid.RowDefinitions>
        <StackPanel VerticalAlignment="Center" 
                    Grid.Row="0" Orientation="Horizontal">
            <TextBlock>First Result:</TextBlock>
            <TextBlock Text="{Binding FirstUser.Name}"></TextBlock>
        </StackPanel>
        <ListView  Grid.Row="1" 
                   ItemsSource="{Binding UserList, Mode=TwoWay}">
            <ListView.ItemTemplate>
                <DataTemplate>
                    <TextBlock Text="{Binding Name}"></TextBlock>
                </DataTemplate>
            </ListView.ItemTemplate>
        </ListView>
    </Grid>
</Page>

    And ready! that's all, in my case I have added something in the MainPage.xaml.cs to seed test data after each execution:

        public MainPage()
    {
        this.InitializeComponent();
        this.Loading += MainPage_Loading;
    }

    private void MainPage_Loading(FrameworkElement sender, object args)
    {
        var path = Path.Combine(ApplicationData.Current.LocalFolder.Path, "db.sqlite");

        using (SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), path))
        {
            conn.CreateTable<User>();

            conn.Insert(new User() { Name = "Random " + Guid.NewGuid().ToString() });
        }
    }

    Download here the complete example

    • 4