Problem with SQLite in iOS App with Xamarin forms

Question

I have created my app in Visual Studio 2019 for Android and iOS.

If I DEBUG my iOS app it is working perfectly as it should. It is debugged with iPhoneSimulator.

But if I rollout my RELEASE and test in on for example iPad, it seems that it can not find the SQLite DB whitch is stored like this:

string dbpath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "MyDatabase.db");

Is there a problem maybe with the SpecialFolder 'Personal' on iOS?

This code will be processes during start up:

    if (!File.Exists(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "MyDatabase.db")))
    {
        DataHelper.Database db = new DataHelper.Database();
        if (!db.CreateDatabase())
        {
            DisplayAlert("Error", "DB could not be loaded !", "OK");
        }
    }

this is my DatabaseHelper class:

    namespace VoTa.DataHelper
    {
        public class Database
        {
                    readonly string dbpath = Data.GetPath();
        private static readonly string ftpurl = "myURL";
    
            public bool CreateDatabase()
            {
                try
                {
                    var connection = new SQLiteConnection(dbpath);            
                        connection.CreateTable<Belohnungen>();
                        connection.CreateTable<Vokabeln>();
                        connection.CreateTable<EigeneVokabel>();
                        connection.Close();
                        return true;               
                }
                catch (SQLiteException)
                {
                    //Log.Info("SQLite Fehler!", ex.Message);
                    return false;
                }
            }

   public void InsertIntoVokabeln(string dateiname)
        {
            

            String voakbelURL = ftpurl + dateiname;
            string id;
            string fremdsprache;
            string deutsch;
            string info1;
            string info2;

            var connection = new SQLiteConnection(dbpath);

            connection.Query<Vokabeln>("Delete from Vokabeln");

            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(voakbelURL);
            var vList = xmldoc.SelectNodes("/Vokabel/VokabelModel");

            try
            {
                foreach (XmlNode node in vList)
                {
                    id = node["Id"].InnerText;
                    fremdsprache = node["fremdsprache"].InnerText;
                    deutsch = node["deutsch"].InnerText;
                    info1 = "";
                    info2 = "";

                    if (node["info1"] != null)
                    {
                        info1 = node["info1"].InnerText;
                    }

                    if (node["info2"] != null)
                    {
                        info1 = node["info2"].InnerText;
                    }

                    Vokabeln vokabel = new Vokabeln
                    {
                        Fremdsprache = fremdsprache,
                        Deutsch = deutsch,
                        Info1 = info1,
                        Info2 = info2
                    };

                    connection.Insert(vokabel);
                }
            }
            catch (Exception)
            {
                return;
            }
            finally
            {
                connection.Close();
            }
        }

        public void InsertIntoBelohnungen(string dateiname)
        {
            String belohunngURL = ftpurl + dateiname;
            string id;
            string beloh;
            string info1;
            string info2;

            var connection = new SQLiteConnection(dbpath);
            connection.Query<Belohnungen>("Delete from Belohnungen");

            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(belohunngURL);
            var vList = xmldoc.SelectNodes("/Belohnung/BelohnungModel");

            try
            {
                foreach (XmlNode node in vList)
                {
                    id = node["Id"].InnerText;
                    beloh = node["Belohnung"].InnerText;
                    info1 = "";
                    info2 = "";

                    if (node["info1"] != null)
                    {
                        info1 = node["info1"].InnerText;
                    }

                    if (node["info2"] != null)
                    {
                        info1 = node["info2"].InnerText;
                    }

                    Belohnungen belohnung = new Belohnungen
                    {
                        Belohnung = beloh,
                        Info1 = info1,
                        Info2 = info2
                    };

                    connection.Insert(belohnung);
                }
            }
            catch (Exception)
            {
                return;
            }
            finally
            {
                connection.Close();
            }
        }

It is working on iPhoneSimulator but not in "real".

Add DB file in the AppDelegate.cs file like:

string sqliteFilename = "MyDatabase.db";
            string folderPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal),"..","Library");
            string dbpath = Path.Combine(folderPath, sqliteFilename);
            LoadApplication(new App(dbpath));

My App.xaml.cs:

   public App(string dbpath)
    {
        InitializeComponent();

        Data.SetPath(dbpath);
        MainPage = new NavigationPage(new MainPage());         
    }

My MainView:

switch (Device.RuntimePlatform)
            {
                case Device.iOS:
                    if (!File.Exists(Data.GetPath()))
                    {
                        DataHelper.Database db = new DataHelper.Database();
                        if (!db.CreateDatabase())
                        {
                            DisplayAlert("Fehler", "Datenbank konnte nicht erstellt werden !", "OK");
                        }
                    }
                    break;
                case Device.Android:
                    if (!File.Exists(Data.GetPath()))
                    {
                        DataHelper.Database db = new DataHelper.Database();
                        if (!db.CreateDatabase())
                        {
                            DisplayAlert("Fehler", "Datenbank konnte nicht erstellt werden !", "OK");
                        }
                    }
                    break;
            }

This does not gave me exception.

I get this exception:

   private void Starten()
    {
        DataHelper.Database db = new DataHelper.Database();

        try
        {
            List<Vokabeln> myvokabel = db.SelectTableVokabeln(anzahlVokabel).ToList();

            frage.Clear();
            antwort.Clear();
            info1.Clear();
            info2.Clear();
            belohnung.Clear();

            int test = myvokabel.Count();

            foreach (var item in myvokabel)
            {
                if (richtung == 1)
                {
                    frage.Add(item.Deutsch);
                    antwort.Add(item.Fremdsprache);
                }
                else
                {
                    frage.Add(item.Fremdsprache);
                    antwort.Add(item.Deutsch);
                    info1.Add(item.Info1);
                    info2.Add(item.Info2);
                }
            }

            List<Belohnungen> mybelohnung = db.SelectTableBelohnungen().ToList();

            foreach (var bel in mybelohnung)
            {
                belohnung.Add(bel.Belohnung);
            }

            //DisplayAlert("Info", "Vokabeln und Belohnungen wurden geladen!", "OK");
        }
        catch (Exception)
        {
            //Log.Info("interner Fehler!", ex.Message);
            DisplayAlert("Fehler", "Es ist ein Fehler aufgetreten", "OK");
        }
    }

Database.cs

 public List<Vokabeln> SelectTableVokabeln(int anzahl)
        {
            try
            {
                var connection = new SQLiteConnection(dbpath);
                var abfrage = connection.Query<Vokabeln>(string.Format("SELECT ID, Fremdsprache, Deutsch, Info1, Info2 FROM Vokabeln ORDER BY RANDOM() LIMIT {0}", anzahl));
                //return connection.Table<Vokabeln>().ToList();
                return abfrage;
            }
            catch (SQLiteException)
            {
                //Log.Info("SQLite Fehler!", ex.Message);
                return null;
            }
        }

        public List<Belohnungen> SelectTableBelohnungen()
        {
            try
            {
                var connection = new SQLiteConnection(dbpath);

                return connection.Table<Belohnungen>().ToList();                
            }
            catch (SQLiteException)
            {
                //Log.Info("SQLite Fehler!", ex.Message);
                return null;
            }
        }

Any help?

Thank you.

Did you get any exception? Does it work if you change a dbpath? — nevermore
– nevermore, Commented Jul 3, 2020 at 5:16
I think I have already solved the directory issue, but I face another problem with accesing the database. I will retrive data from database but always get a displayalert on iOS. with the android solution it is working fine without any error. so it has to be related with the database or tables.....I added to my question the new infos. — BiSaM
– BiSaM, Commented Jul 3, 2020 at 10:06
I found the problem, the Model was not correct, therefore it was not working. All solved now. — BiSaM
– BiSaM, Commented Jul 3, 2020 at 12:21
Ok, you can share your solution in the answer and mark it so that we can help more people with same problem. — nevermore
– nevermore, Commented Jul 6, 2020 at 6:08

Markus Michel · Accepted Answer · 2020-07-02 08:38:49Z

1

I remember having had similar issues when trying to handle the file in shared code.

Strangely enough, creating an interface in my shared code with a native implementation on both iOS and Android resolved the issue.

Here is my code to retrieve the connection from within my iOS project in a native implementation of an interface from the shared project called IFileService:

    public SQLite.SQLiteConnection GetDbConnection()
    {
        try
        {
            string sqliteFilename = "MyAppDb.db3";
            string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal) + sqliteFilename);
            SQLiteConnection conn = new SQLite.SQLiteConnection(path, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.NoMutex);

            return conn;
        }
        catch (Exception ex)
        {
            Logger.Fatal(ex, "An exception occurred at GetConnection");
            Logger.Trace("Stack Trace: {0}", ex.StackTrace);
        }

        return null;
    }

Also I would suggest that you don't access your connection the way you are doing it right now, as

var connection = new SQLiteConnection(dbpath);

will keep your connection open, which will most probably cause problems, if you want to use another connection. In order to ensure that your connection is properly closed, I would highly recommend to manage it in a using block:

using(SQLiteConnection connection = iFileService.GetDbConnection())
{
    connection.CreateTable<Belohnungen>();
    connection.CreateTable<Vokabeln>();
    connection.CreateTable<EigeneVokabel>();
}

This way the connection will be properly closed and disposed when your database access is finished. Also ensure that you always access your database connections that way whenever you get or store data in SQLite.

P.S.: if you are using a newer version of sqlite, you might need to construct your database with

SQLiteConnection connection = new SQLiteConnection(dbpath);

instead of the way I am creating it.

edited Jul 2, 2020 at 8:38

answered Jul 2, 2020 at 8:32

Markus Michel

2,29913 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BiSaM Over a year ago

Thanks for your reply, I added my question can you take a look... I get an error retreaving data from the database..

BiSaM Over a year ago

I found the problem, the Model was not correct, therefore it was not working. All solved now.

Collectives™ on Stack Overflow

Problem with SQLite in iOS App with Xamarin forms

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related