Is this the perfect way to set up a database connection with Xamarin Forms and SQLite?

Question

I have an application which works for most of the time but which stops working sometimes after 5-10 minutes. I'm trying hard to work out what's wrong and to this end I would like to get some feedback as to if the way I am doing a database connection with the application is ideal. Here's what I have right now:

public partial class App : Application
{
    public static DataManager DB { get; } = new DataManager();

public partial class DataManager 
{
    protected static object l = new object();
    protected SQLiteConnection db1;

    public DataManager()
    {
        db1 = DependencyService.Get<ISQLiteDB1>().GetConnection();
        db2.TimeExecution = true;
    }

public interface ISQLiteDB1
{
    SQLiteConnection GetConnection();
}

[assembly: Dependency(typeof(SQLiteDB1_iOS))]
namespace Ja.iOS
{
  public class SQLiteDB1_iOS : ISQLiteDB1 
  {
    public SQLite.SQLiteConnection GetConnection()
    {
       var sqliteFilename = "db1.db3";
       string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
       string libraryPath = Path.Combine(documentsPath, "..", "Library");
       Debug.WriteLine((libraryPath));
       var path = Path.Combine(libraryPath, sqliteFilename);
       if (!File.Exists(path))
       {
          File.Create(path);
       }
       return new SQLite.SQLiteConnection(path);
    }
  }
}  

[assembly: Dependency(typeof(SQLiteDB1_Android))]
namespace Jap.Droid
{
    public class SQLiteDB1_Android :ISQLiteDB1
    {

        public SQLite.SQLiteConnection GetConnection()
        {
            var sqliteFilename = "db1.db3";
            string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, sqliteFilename);
            if (!File.Exists(path))
            {
                File.Create(path);
            }
            return new SQLite.SQLiteConnection(path);
        }
    }
} 

I use this code to execute queries

public partial class DataManager
{

    public void UpdatePhraseSetViewedFalse()
    {
        sql = string.Format("UPDATE Phrase SET Viewed = 0 WHERE Selected = 1");
        App.DB.RunExecute(sql);
    }

public partial class DataManager
{

    private int RunExecute(String s)
    {
            lock (l)
            {
                try
                {
                    rc1 = db2.Execute(s);
                }
                catch (Exception ex)
                {
                    Crashes.TrackError(ex,
                        new Dictionary<string, string> {
                            {"RunExecute", "Exception"},
                            {"sql", s },
                            {"Device Model", DeviceInfo.Model },
                        });
                    throw;
                }
            }

and this code style for inserts and updates:

            db2.Insert(new QuizHistory()
            {
                QuizId = quiz,
                Cards  = (App.viewablePhrases ?? GetViewablePhrases(MO.Quiz)).Count,
                Score = score,
                UtcNow = (int)Math.Truncate(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1)).TotalSeconds),
                Viewed = 1,
                Deck   = Helpers.Deck.GetTitle()
            });

To reduce the chance of issues, should I be adding lock(l) around all the code that accesses the database? Should I make changes to the way I connect?

Answer 1

It's not clear how the app stops working (crash, hangs, other?), but it seems that the code has some red flags which could cause that:

1. You are using lock when accessing the data from DB. While the syntax is correct, it could lead to deadlocks, and if you call the data access methods such as UpdatePhraseSetViewedFalse from UI thread (and most likely you do), that would be the primary cause of application hangs/crash.

2. You are manually creating SQL queries and executing them. SQLite is known for performance issues when it is accessed on multiple threads so you can try to read about best practices, and better use some ORMs and Wrapper SDK to access the database properly. Such as SQLite.NET for iOSand SQLite.NET for Android

Answer 2

Just some guesses: (Because it is not clear what your problem exactly is)

I had a similar problem a couple of years ago. This problem was caused by querys which were opened but never ended. Like open a Reader but don't read to the end. So this Application was working for some minutes very well and than got slower and slower...

What framework do you use to access Sqlite? I tried some low level libraries and had lots of problems. Now I use Microsoft.Data.Sqlite or Entityframework and had never problems again.

Your GetConnection code is not calling .Open on the connection. Maybe your framework is not requireing it, but if it is, I would call this in the GetConnection function.

I would not write platform dependent versions of GetConnection, I would just generate the filename OS dependent using a FileService Interface.