Search code examples
xamarin.formssqlite-net

How to overwrite sqlite database for existing users in a Xamarin Forms app?

We have a Xamarin Forms app.

We have a few existing users which had been using the app since last year where the database file name is MyAppName.db

Our new app strategy requires us to include the .db (Sqlite) along with the app to ensure we can include persistent information and does not require internet when you install the app, meaning we hope to overwrite the db file for existing users.

After we publish this new change where the database file is now MyNewDbFile.db, our users complain that they do not see any data, the app does not crash tough.

We capture error reports and we can see a common error in the our tool stating about "row value missused", a quick search indicates the value are not present and so the "SELECT * FROM TABLE WHERE COLUMNAME" query does not work causing the exception.

We can confirm we are using

Xamarin.Forms version 4.5.x

sqlite-net-sqlcipher version 1.6.292

We do not have any complex logic and cannot see a very specific reason causing this as this is not produced when we test our apps from the Alpha channel or the TestFlight.

Solution

  • We investigated the heart of the problem and can confirm that the troublemaker is the device Culture information.

    We have been testing with English as the device language, however the minute the app is used by folks with any other language except English the query causes exception.

    We make use of the Xamarin Essentials library to get device location (which is optional).

    The latitude and longitude returned are culture specific.

    So if you are using a Spanish language on device, the format for the Latitude and Longitude information is a , (comma) and NOT a . (dot).

    That is, a Latitude value is something similar to 77.1234 where it is separated by a . (dot)

    However for users with different region settings on device, the format would change to 77,1234 as value where it is separated by a , (comma).

    The query requires us to have the values as string and that when not separated by a . (dot) fails to execute:

    Query:

    if (deviceLocation != null)
   queryBuilder.Append($" ORDER BY((Latitude - {deviceLocation.Latitude})*(Latitude - {deviceLocation.Latitude})) +((Longitude - {deviceLocation.Longitude})*(Longitude - {deviceLocation.Longitude})) ASC");

    Where the deviceLocation object is of type Xamarin.Essentials.Location which has double Latitude and Longitude values but when used as a string or even if you explicitly do deviceLocation.Latitude.ToString(), it would return the formatted values as per the device CultureInfo.

    We can confirm that the problem is due to a mismatch of the format type and causing the query to throw an exception and in return making the experience as if there is no data.