With Xamarin Forms, how can I create my SQLite connections in shared code?

Question

I recently noticed a couple of articles that mentioned creating SQLite connections all in common code. Is this something new as I have always done it this way with an interface:

Is there a way this could all be accomplished in common code rather than in the implementation below that requires code in Common, iOS and Android?

Common code:

namespace Memorise
{
    public interface ISQLiteDB1
    {
        (SQLiteConnection, bool) GetConnection();
    }
}

iOS code:

[assembly: Dependency(typeof(ISQLiteDB_iOS))]
namespace Memorise.iOS
{
    public class ISQLiteDB_iOS : ISQLiteDB
    {
        public (SQLite.SQLiteConnection, bool) GetConnection(string _dbName)
        {
            bool newDb = false ;
            var sqliteFilename = _dbName;
            string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            string libraryPath = Path.Combine(documentsPath, "..", "Library");

            Debug.WriteLine(documentsPath);
            Debug.WriteLine(libraryPath);

            var path = Path.Combine(libraryPath, sqliteFilename);
            switch (_dbName)
            {
                case CONST.DB1Name:
                    if (File.Exists(path))
                    {
                        newDb = false;
                        File.Delete(path);
                    }
                    else
                    {
                        newDb = true;
                    }
                    File.Copy(sqliteFilename, path);

                    break;
                case CONST.DB2Name:
                    if (File.Exists(path))
                    {
                        newDb = false;
                    }
                    else
                    {
                        newDb = true;
                        File.Create(path);
                    }

                    break;
                case CONST.DB3Name:
                    if (File.Exists(path))
                    {
                        newDb = false;
                    }
                    else
                    {
                        newDb = true;
                        File.Copy(sqliteFilename, path);
                    }
                    break;
            }
           
            return (new SQLite.SQLiteConnection(path), newDb);
        }
    }
}

Android Code:

[assembly: Dependency(typeof(SQLiteDB1_Android))]

namespace Memorise.Droid
{
    public class SQLiteDB1_Android : ISQLiteDB1
    {
        public (SQLite.SQLiteConnection, bool) GetConnection()
        {
            bool newDb;
            var sqliteFilename = "db1.db3";
            string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
            var path = Path.Combine(documentsPath, sqliteFilename);
            if (File.Exists(path))
            {
                newDb = false;
                File.Delete(path);
            }
            else
            {
                newDb = true;
            }
            FileStream writeStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
            ReadWriteStream(Android.App.Application.Context.Assets.Open(sqliteFilename), writeStream);
            return (new SQLite.SQLiteConnection(path), newDb);
        }

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

Answer 1

We can leverage the FileSystem API in the Xamarin.Essentials NuGet Package to locate the platform-specific App Data Directory and create our database connection in shared code.

var databasePath = Path.Combine(FileSystem.AppDataDirectory, "Database.db3");
var databaseConnection = new SQLiteConnection(databasePath);

I go into more depth in this blog post on Xamarin + SQLite: https://codetraveler.io/2019/11/26/efficiently-initializing-sqlite-database/

Answer 2

As I can see your code you have two database files, db1 and db2. Also, I can see that db1 is copied from Project Assests and pasted into the local folder. So you need to put the file db1.db3 file in Resources folder and Mark it as EmbeddedResource. This step is important.

Rest of your code can be simplified and changed to :-

    public DataManager()
    {
        var db1Path = Path.Combine(BasePath, CONST.DB1Name);
        newDb1 = !File.Exists(db1Path);
        if (newDb1)
        {
            var assembly = IntrospectionExtensions.GetTypeInfo(typeof(DataManager)).Assembly;
            Stream stream = assembly.GetManifestResourceStream("YourProjectName.Resources.db1.db3");
            using (var db1SourceStream = new FileStream(db1Path, FileMode.Create, FileAccess.ReadWrite))
            {
                stream.CopyTo(db1SourceStream);
                db1SourceStream.Close();
            }
        }
        db1 = new SQLiteConnection(db1Path);

        var db2Path = Path.Combine(BasePath, CONST.DB2Name);
        newDb2 = !File.Exists(db2Path);
        db2 = new SQLiteConnection(db2Path);
        db2.TimeExecution = true;
    }