xamarin.forms: retrieving existing data from .db file

Question

In Xamarin Forms 4.3, I'm following this "Local Databases" doc: https://learn.microsoft.com/en-us/xamarin/xamarin-forms/data-cloud/data/databases

This doc explains how to create and use a new sqlite db, but how do I go about in loading and displaying data in an existing .db file with the end purpose of transferring it into a new local sqlite database?

using SQLite;

namespace TruckExample.Models
{
    public class Truck
    {
        [PrimaryKey, AutoIncrement]
        public int TruckNo { get; set; }
        public string TruckName { get; set; }
    }
}
//////////////////////////////////////////////
using System.Collections.Generic;
using System.Threading.Tasks;
using SQLite;
using TruckExample.Models;

namespace TruckExample.Data
{
    public class TruckDatabase
    {
        readonly SQLiteAsyncConnection _database;

        public TruckDatabase(string dbPath)
        {
            _database = new SQLiteAsyncConnection(dbPath);
            _database.CreateTableAsync<Truck>().Wait();
        }

        public Task<List<Truck>> GetTrucksAsync()
        {
            return _database.Table<Truck>().ToListAsync();
        }
    }
}
////////////////////////////////////////////////
using System;
using Xamarin.Forms;
using Xamarin.Forms.Xaml;
using TruckExample.Data;
using System.IO;

[assembly: XamlCompilation(XamlCompilationOptions.Compile)]
namespace TruckExample
{
    public partial class App : Application
    {
        static TruckDatabase database;

        public static TruckDatabase Database
        {
            get
            {
                if(database == null)
                {
                    database = new TruckDatabase(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Truck.db"));  
                }

                return database;
            }
        }

        public App()
        {
            InitializeComponent();
            MainPage = new NavigationPage(new MainPage());
        }
   }
}
/////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Xamarin.Forms;
using Xamarin.Forms.Xaml;

namespace TruckExample
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class MainPage : ContentPage
    {
        public MainPage ()
        {
            InitializeComponent ();
        }

        protected override async void OnAppearing()
        {
            base.OnAppearing();
            listView.ItemsSource = await App.Database.GetTrucksAsync();
            Debug.Write(App.Database.GetTrucksAsync());

        }
    }
}

include it as an Asset (Android) or Content (iOS) in your platform project, and then on app startup copy it to a writable folder — Jason, Dec 24 '19 at 16:42
@Jason thanks, it makes sense to put the .db file in the TruckExample.Android Asset folder. When you say "on app startup copy it", do you mean to use TruckExample's OnStart() in App class file to get the .db file in the Android Asset folder and then you'll be able to write/copy to a file/sqlite db? — Joey, Dec 24 '19 at 16:54
Yes, use the OnStart and copy from Assets to a writable folder — Jason, Dec 24 '19 at 16:58
You can refer to this thread If you want to transfer the data between two db https://www.syncfusion.com/kb/9633/how-to-import-and-export-image-from-sql-db, But I suggest you to read to the data from the on db, then store data to another DB, I tried to copy the db file, it cannot not used. — Leon, Dec 25 '19 at 08:24

score 1 · Accepted Answer · answered Dec 26 '19 at 16:06

For others, helpful resources:

Implementation by Hameed Kunkanoor: "Creating a sqlite databse and storing your data in your android and ios application in Xamarin Forms" https://medium.com/@hameedkunkanoor/creating-a-sqlite-databse-and-storing-your-data-in-your-android-and-ios-application-in-xamarin-2ebaa79cdff0 (Refer to his git if needed)

Explanation/response by Matthewrdev: Use a local database in Xamarin

To improve the implementation -- Brandon Minnick: "Xamarin: Efficiently Using a SQLite Database" https://codetraveler.io/2019/11/26/efficiently-initializing-sqlite-database/

xamarin.forms: retrieving existing data from .db file

1 Answers1