I am hoping someone can point me in the right direction on this. There are numerous questions raised about this topic here and on Google but I still can't find an answer the works for me :-(
I am writing a Xamarin Forms app for use with iOS and Android. I want that app to access a large read-only SQLite database that should be downloaded with the installation files. This database is around 160mb so clearly it is important that multiple copies are not produced / held and also it should ideally not have to be copied into different locations at runtime. Just as an aside, I also create and access a small local SQLite database to hold configuration data.
I have created an abstract class for creating a database connection (with thanks to an article by Brandon Minnick on efficiently doing this). I have got this method to work (with read/write flags rather than the readonly flag I show below) for the locally written SQLite configuration database, so I know that in principle this method works. However the small config database is located in the AppDataDirectory so has read/write access.
I am trying to load the large prebuilt SQLite database as an embedded resource and open it as a readonly database. This is failing with a SQLite.SQLiteException of "Could not open database file : .... (CannotOpen)" .
I am attempting to use the database path of "SLFinder.SFMain.db" (where SLFinder is the namespace and SFMain.db is the name of the file). I have also tried this with SLFinder.iOS.SFMain.db as I am trying to debug on an iPhone device.
The code I'm using is as follows
using System;
using Xamarin.Essentials;
using SQLite;
using System.Threading.Tasks;
using System.IO;
using System.Linq;
using Polly;
namespace SLFinder
{
public abstract class MainDbBase
{
static readonly string _databasePath = "SLFinder.SFMain.db";
static readonly Lazy<SQLiteAsyncConnection> _databaseConnectionHolder = new Lazy<SQLiteAsyncConnection>(() => new SQLiteAsyncConnection(_databasePath, SQLiteOpenFlags.ReadOnly ));
static SQLiteAsyncConnection DatabaseConnection => _databaseConnectionHolder.Value;
protected static async ValueTask<SQLiteAsyncConnection> GetDatabaseConnection<T>()
{
if (!DatabaseConnection.TableMappings.Any(x => x.MappedType == typeof(T)))
{
await DatabaseConnection.CreateTablesAsync(CreateFlags.None, typeof(T)).ConfigureAwait(false);
}
return DatabaseConnection;
}
protected static Task<T> AttemptAndRetry<T>(Func<Task<T>> action, int numRetries = 10)
{
return Policy.Handle<SQLite.SQLiteException>().WaitAndRetryAsync(numRetries, pollyRetryAttempt).ExecuteAsync(action);
static TimeSpan pollyRetryAttempt(int attemptNumber) => TimeSpan.FromMilliseconds(Math.Pow(2, attemptNumber));
}
}
}
//plus the following class
using System.Collections.Generic;
using System.Threading.Tasks;
using SQLite;
namespace SLFinder
{
public class MainDB : MainDbBase
{
public MainDB()
{
//Do something when initialising
DoSomething();
}
private async void DoSomething()
{
var mydata = new SfGridResult();
mydata = await ReadSomething();
return;
}
public async Task<SfGridResult> ReadSomething()
{
var databaseConnection = await GetDatabaseConnection<SfGridResult>().ConfigureAwait(false);
return await AttemptAndRetry(() => databaseConnection.GetAsync<SfGridResult>(1));
}
}
[Table("SFGrid")]
public class SfGridResult
{
[PrimaryKey]
public byte[] GridID { get; set; }
//...
I guess that I must be doing something fundamentally wrong unfrotunately. This is the first time I have tried to do this. In principle , should this be possible?
Thanks
Huw