Android - Could not open database in Xamarin.forms (SQLite exceptions)

Question

Sorry if the question is asked before, but i searched very well to handle this problem and i didn't find an answer.

I am handling local database with SQLite in my Xamarin forms project (PCL).

1- The connection is working well in iOS but in android i got this problem (Could not open database file )

• I also set "ReadExternalStoarage" and "WriteExternalStoarage" permissions.

2- I used another method of creating the connection path which is :

string documentsPath = System.Environment.GetFolderPath (System.Environment.SpecialFolder.Personal);
string path = Path.Combine(documentsPath, "pbcare.db");

by this way the exception happened when dealing with the database ...

        public bool checkLogin (string email, string password)
        {

            if (DB.Table<User> ().Where (user => user.Email == email && user.Password == password)
                            .FirstOrDefault () != null) { // exception caught here
                return true;
            } else {
                return false;
            }
        }

but the table is there in the database.

Note: this second way create the connection even if the database file doesn't exist !

Answer 1

Android won't be able to use a SQLite database with a file from your local system. The path variable has to come from the Android system. The second approach you used to create the path was correct:

var path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
path = Path.combine(path, 'pbcare.db3');

Will create an appropriate file path for the db3 file on Android.

The next issue you reference: no such table: User is caused by not creating the table. Before you use the database, you need to create all of the necessary tables.

var conn = new SQLiteConnection(path);
conn.CreateTable<User>();

If you do this and create the User table first, then it should work as expected. There is a more in depth tutorial from Xamarin here: https://developer.xamarin.com/guides/cross-platform/application_fundamentals/data/part_3_using_sqlite_orm/