Cannot read SQLite file in Xamarin Android project

Question

I have a file called Lego_Parts.db3 that is a SQLite file already populated with data. I have it in the assets folder of the Android Xamarin project. The code I have to set the database path is:

static PieceDB database;

public static PieceDB PieceDatabase
{
        get
        {
            if (database == null)
            {
                database = new PieceDB(Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "Lego_Parts.db3"));
            }
            return database;
        }
}

When I try to display the data from the database (in DatabaseTest.xaml) no data shows

Here is any applicable code:

Piece.cs

using SQLite;

namespace TestApp1
{
    public class Piece
    {
        public int PartNum { get; set; }
        public string PartName { get; set; }
        public string Category { get; set; }
        public string Url { get; set; }
    }
}

PieceDB.cs

using SQLite;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace TestApp1
{
    public class PieceDB
    {
        readonly SQLiteAsyncConnection _database;

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

        public Task<List<Piece>> GetAllPieces()
        {
            return _database.Table<Piece>().ToListAsync();
        }

        public Task<Piece> GetPiece(int partNum)
        {
            return _database.Table<Piece>().Where(i => i.PartNum == partNum).FirstOrDefaultAsync();
        }

        public Task<int> SavePieceAsync(Piece temp)
        {
            return _database.InsertAsync(temp);
        }
    }
}

DatabaseTest.xaml.cs

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

namespace TestApp1
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class DatabaseTest : ContentPage
    {

        protected override async void OnAppearing()
        {
            base.OnAppearing();

            listView.ItemsSource = await App.PieceDatabase.GetAllPieces();
        }

        public DatabaseTest()
        {
            InitializeComponent();
        }

        async void Handle_ItemTapped(object sender, ItemTappedEventArgs e)
        {
            if (e.Item == null)
                return;

            await DisplayAlert("Item Tapped", "An item was tapped.", "OK");

            //Deselect Item
            ((ListView)sender).SelectedItem = null;
        }
    }
}

DatabaseTest.xaml

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:d="http://xamarin.com/schemas/2014/forms/design"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             mc:Ignorable="d"
             x:Class="TestApp1.DatabaseTest">
    <StackLayout Margin="20,35,20,20">
        <ListView x:Name="listView">
            <ListView.ItemTemplate>
                <DataTemplate>
                    <TextCell Text="{Binding PartNum}"
                              Detail="{Binding Url}"
                              />
                </DataTemplate>
            </ListView.ItemTemplate>

        </ListView>
    </StackLayout>
</ContentPage>

Answer 1

I was able to copy the database file from the android assets folder by creating this class in the android project:

CreateConnection.cs

namespace TestApp1.Droid
{
    public class CreateConnection
    {
        public void Open()
        {
            var sqliteFilename = "lego_parts.db3";
            string documentsDirectoryPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsDirectoryPath, sqliteFilename);

            if(!File.Exists(path))
            {
                using (var binaryReader = new BinaryReader(Android.App.Application.Context.Assets.Open(sqliteFilename)))
                {
                    using (var binaryWriter = new BinaryWriter(new FileStream(path, FileMode.Create)))
                    {
                        byte[] buffer = new byte[2048];
                        int length = 0;
                        while((length = binaryReader.Read(buffer, 0, buffer.Length)) > 0)
                        {
                            binaryWriter.Write(buffer, 0, length);
                        }
                    }
                }
            } else
            {
                Console.WriteLine("Database already saved on device");
            }


        }
    }
}

and calling the Open() method from the Android project's OnCreate() method

MainActivity.cs

protected override async void OnCreate(Bundle savedInstanceState)
        {
            TabLayoutResource = Resource.Layout.Tabbar;
            ToolbarResource = Resource.Layout.Toolbar;

            base.OnCreate(savedInstanceState);

            await CrossMedia.Current.Initialize(); 

            CrossCurrentActivity.Current.Init(this, savedInstanceState);
            Xamarin.Essentials.Platform.Init(this, savedInstanceState);
            global::Xamarin.Forms.Forms.Init(this, savedInstanceState);

            CreateConnection connection = new CreateConnection();
            connection.Open();
            LoadApplication(new App());
        }

Answer 2

You will have to copy your database to a file location.

First, I recommend putting your db3 file in the Resources/Raw folder as it makes the copying a slight bit easier. Also Android resources should only use lower case letters, digits, and underscores, and must start with a letter, so change your db file name to lego_parts.db3 for starters.

Then in OnCreate of your MainActivity, do the following:

var dbPath = Path.Combine (System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal), "lego_parts.db3"); // FILE NAME TO USE WHEN COPIED
var s = Resources.OpenRawResource(Resource.Raw.lego_parts);  // DATA FILE RESOURCE ID
if (!System.IO.File.Exists(dbPath)) {
    FileStream writeStream = new FileStream(dbPath, FileMode.OpenOrCreate, FileAccess.Write);
    ReadWriteStream(s, writeStream);
}

and add the following method to MainActivity class:

private void ReadWriteStream(Stream readStream, Stream writeStream)
{
    int Length = 256;
    Byte[] buffer = new Byte[Length];
    int bytesRead = readStream.Read(buffer, 0, Length);
    // write the required bytes
    while (bytesRead > 0)
    {
        writeStream.Write(buffer, 0, bytesRead);
        bytesRead = readStream.Read(buffer, 0, Length);
    }
    readStream.Close();
    writeStream.Close();
}

Then you can connect to your database using dbPath for the file path.

on iOS, you can put the lego_parts.db3 in the iOS project's root folder and then use the following code in AppDelegate.FinishedLaunching method to copy it:

var dbPath = Path.Combine (System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal), "lego_parts.db3");
var appDir = NSBundle.MainBundle.ResourcePath;
var seedFile = Path.Combine(appDir, "lego_parts.db3");
if (!File.Exists(dbPath) && File.Exists(seedFile))
    File.Copy(seedFile, dbPath);