question

HuwBaynham-2468 avatar image
0 Votes"
HuwBaynham-2468 asked HuwBaynham-2468 commented

Accessing readonly SQLite database as embedded resource in Xamarin Forms shared code

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




dotnet-xamarindotnet-sqlite
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

JarvanZhang-MSFT avatar image
0 Votes"
JarvanZhang-MSFT answered HuwBaynham-2468 commented

Hello,​

Welcome to our Microsoft Q&A platform!

This is the first time I have tried to do this. In principle , should this be possible

Sorry, we cannot do that. The embedded file is embedded as a resource in the assembly, it doesn't have a valid path. However, the initialization of 'SQLiteAsyncConnection' requires the file path. We cannot get and read the db file if it's stored as embedded resource. Please save the db file fto the application's local storage on each platform.

This database is around 160mb ...

What do you store in the database? The db file is too large, the apk file may exceed the limit when releasing. Do you store the video file or audio files? For the large files, please load them using url or download the files at runtime instead.

Best Regards,

Jarvan Zhang



If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the response, which makes sense. I think I'll need to consider a one time download on first run - I was just trying to avoid that by bundling with the installation, but if that is going to be problematic then I'll avoid it.

Thanks again.

Huw

0 Votes 0 ·