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

user_1422604 0
Viewed 394

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();
        }
    }
}
xamarin.forms xamarin c#
3 Answers
user_59536430

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;
    }
user_119274150

By using the sqlite-net-pcl I succeeded to have data layer in .NET Standard library and utilize it in Xamarin Forms (iOS) and also WPF (but you can probably extend it also for Android and UWP for example.

The main trick is to specify different path to app local storage for database file, by using DI or platform if/else compilation.

Also one trick for foreign keys and delete cascade ("PRAGMA foreign_keys=ON;"), that for some platforms it was sufficient to call it only once, after the Connection object was created, but for the sake of iOS only I needed to change it to be called before each command needing the cascade functionality of foreign keys.

    protected readonly SQLiteAsyncConnection db;
    protected abstract List<BaseMigration> Migrations { get; }

    public YourDB(string dbPath)
    {
        db = new SQLiteAsyncConnection(dbPath);
        db.ExecuteAsync("PRAGMA foreign_keys=ON;").Wait();
        //ExecuteMigrations(db).Wait();
    }
user_20184150
Related