UWP Show a Sqlite table with Gridview

Question

I am translating my app from Windows Forms to UWP but i am stuck with this, my app has a database in sqlite with some tables, and I need show each table in a grid, in Windows Forms I used Sqlite connection and Datagridview.DataSource to get this, but in UWP isnt Datagridview.DataSource anymore, I think use to DO this: Binding each table and show in a gridview, then the client can easily modify cells and rows, but I am relatively new with binding then I am trying bind my table but it show errors in the building, I hope you can help me.

There is some files I am using to bind:

Database.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace App1
{
    public class Database
    {
        string path;
        SQLite.Net.SQLiteConnection conn;
        public Database()
        {
            path = Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path, "Roaming", "database.sqlite");

            conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), path);
        }
    }        
}

BindableBase.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Runtime.CompilerServices;


namespace System.ComponentModel
{
    public class BindableBase : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        public bool SetProperty<T>(ref T propertyBackStore, T newValue, [CallerMemberName] string propertyName = "")
        {
            if (Equals(propertyBackStore, newValue))
                return false;


            propertyBackStore = newValue;
            if (PropertyChanged != null)
                PropertyChanged(this,
                                new PropertyChangedEventArgs(propertyName)
                                );
            return true;
        }
        public BindableBase()
        {

        }

        public BindableBase(PropertyChangedEventHandler propertyChanged)
        {
            PropertyChanged = propertyChanged;
        }
    }
}

Answer 1

You have only posted code of your old winForm app, it's hard to know what the problem you have now. Are you asking a tutorial for using SQLite in UWP? Or Is there any problem with your data Binding?

If you are asking for a tutorial, you may refer to Windows 10 Development - SQLite Database.

but I am relatively new with binding then I am trying bind my table but it show errors in the building

I assume that you have only problems with data binding here, and I saw that in your code you used BindableBase, so here I wrote a sample using Prism.Core for MVVM:

<Page.DataContext>
    <local:MainPageViewModel x:Name="VM" />
</Page.DataContext>

<Grid Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">
    <Grid.RowDefinitions>
        <RowDefinition Height="5*" />
        <RowDefinition Height="*" />
    </Grid.RowDefinitions>
    <GridView ItemsSource="{x:Bind VM.dbList}" Grid.Row="0" SelectionChanged="{x:Bind VM.GridView_SelectionChanged}">
        <GridView.ItemTemplate>
            <DataTemplate>
                <StackPanel Width="100">
                    <TextBlock Text="{Binding ID}" HorizontalAlignment="Center" />
                    <TextBlock Text="{Binding Content}" HorizontalAlignment="Center" />
                </StackPanel>
            </DataTemplate>
        </GridView.ItemTemplate>
        <FlyoutBase.AttachedFlyout>
            <Flyout>
                <StackPanel>
                    <Button Content="Delete" Command="{x:Bind VM.Delete}" />
                    <TextBox />
                    <Button Content="Edit" Command="{x:Bind VM.Edit}" />
                    <Button Content="Add after this item" Command="{x:Bind VM.AddAfterThisItem}" />
                </StackPanel>
            </Flyout>
        </FlyoutBase.AttachedFlyout>
    </GridView>

    <Button Content="Insert data for test" VerticalAlignment="Bottom" Command="{x:Bind VM.InsertDataForTest}" Grid.Row="1" />
</Grid>

code of my MainPageViewModel:

public class MainPageViewModel : BindableBase
{
    public MainPageViewModel()
    {
        dbList = new ObservableCollection<DBList>();
        Delete = new DelegateCommand(DeleteClicked);
        Edit = new DelegateCommand(EditClicked);
        AddAfterThisItem = new DelegateCommand(AddClicked);
        InsertDataForTest = new DelegateCommand(InsertClicked);

        //load data from db
        if (db.Table<DBList>().Count() != 0)
        {
            foreach (var entry in db.Table<DBList>())
            {
                dbList.Add(new DBList { ID = entry.ID, Content = entry.Content });
            }
        }
    }

    public readonly ICommand Delete;
    public readonly ICommand Edit;
    public readonly ICommand AddAfterThisItem;
    public readonly ICommand InsertDataForTest;

    public ObservableCollection<DBList> dbList;
    public static string DB_PATH = Path.Combine(ApplicationData.Current.LocalFolder.Path, "database.sqlite");
    public static SQLite.Net.Platform.WinRT.SQLitePlatformWinRT SQLITE_PLATFORM = new SQLitePlatformWinRT();
    public SQLite.Net.SQLiteConnection db = new SQLite.Net.SQLiteConnection(SQLITE_PLATFORM, DB_PATH);

    private DBList item;

    public void GridView_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        var gridview = sender as GridView;
        FlyoutBase.ShowAttachedFlyout(gridview);
        item = gridview.SelectedItem as DBList;
    }

    private void DeleteClicked()
    {
    }

    private void EditClicked()
    {
    }

    private void AddClicked()
    {
    }

    private void InsertClicked()
    {
        db.DeleteAll<DBList>();
        for (int i = 0; i < 300; i++)
        {
            DBList list = new DBList();
            list.ID = i;
            list.Content = "Item " + i;
            db.Insert(list);
            if (i == 299)
            {
                foreach (var entry in db.Table<DBList>())
                {
                    dbList.Add(new DBList { ID = entry.ID, Content = entry.Content });
                }
            }
        }
    }
}

And I created this database in the App.xaml.cs file like this:

public static string DB_PATH = Path.Combine(ApplicationData.Current.LocalFolder.Path, "database.sqlite");
public static SQLite.Net.Platform.WinRT.SQLitePlatformWinRT SQLITE_PLATFORM = new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT();

public App()
{
    Microsoft.ApplicationInsights.WindowsAppInitializer.InitializeAsync(
        Microsoft.ApplicationInsights.WindowsCollectors.Metadata |
        Microsoft.ApplicationInsights.WindowsCollectors.Session);
    this.InitializeComponent();
    this.Suspending += OnSuspending;

    if (!CheckFileExists("database.sqlite").Result)
    {
        using (var db = new SQLite.Net.SQLiteConnection(SQLITE_PLATFORM, DB_PATH))
        {
            db.CreateTable<DBList>();
        }
    }
}

private async Task<bool> CheckFileExists(string fileName)
{
    try
    {
        var store = await Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(fileName);
        return true;
    }
    catch
    {
    }
    return false;
}

The DBList class contains only two properties ID and Content:

public class DBList
{
    public int ID { get; set; }
    public string Content { get; set; }
}

I din't complete the part of deleting, editing, inserting, I believe you can do this work. By the way, there are many samples, you can search for it. And for the supported controls in a UWP app, you can refer to UI basics (XAML) sample. For more information about data binding, you may refer to Data binding.