Is there a right way to add an existing SQLite database to a Xamarin project?

Question

Every example I've found creates the database for you and then has you create tables and populate them in code. My problem, though, is that I would like to create and populate the database elsewhere (SQLiteStudio) and then include it in my app.

I sense (through the general feel of ...whatever I've been looking at. We'll call it documentation) that you are supposed to copy the database to the Environment.SpecialFolder.Personal directory. So my workflow is to include the database as a resource and then copy it to the Environment.SpecialFolder.Personal directory. Is that right? Has anyone written any of this down succinctly and authoritatively (as opposed to loose collections of articles)?

I'd prefer not to have two copies of the same database but if that's what everyone else is doing then ...okay.

I have not been able to find an answer on any of the following web pages.

You can create the db externally and add it to your project, but it will be read-only UNLESS you copy it to writable folder at runtime. — Jason, Jul 17 '18 at 18:28

score 3 · Answer 1 · answered Jul 17 '18 at 23:19

Since you tagged pcl, have you tried treating this as an embedded resource? You pretty much just make a folder, drop in the database, and set the build action as an embedded resource. You can access the file through your SQLite library by linking up to the path of where the database is.

https://learn.microsoft.com/en-us/xamarin/xamarin-forms/app-fundamentals/files?tabs=vswin

score 0 · Answer 2 · answered Jul 05 '23 at 11:28

If you're looking for .Net Maui solution:

put your file as MauiAsset into Resources/Raw/Something
consume:

In this example we are checking if app.dp3 is existing, if not then we create it from from prebuilt.db3 shipped with resources.

builder.Services.AddTransient<LocalDatabase>((services) =>
        {
            var filenameDb = Path.Combine(FileSystem.AppDataDirectory, "app.db3");
            if (!File.Exists(filenameDb))
            {
                using var stream = FileSystem.OpenAppPackageFileAsync("Something/prebuilt.db3").GetAwaiter().GetResult();
                using (var memoryStream = new MemoryStream())
                {
                    stream.CopyTo(memoryStream);
                    File.WriteAllBytes(filenameDb, memoryStream.ToArray());
                }
            }
            return new LocalDatabase(filenameDb);
        });

Is there a right way to add an existing SQLite database to a Xamarin project?

2 Answers2