Debugging SQLite Databases in Xamarin-based Android Apps
We recently got an email asking if it is possible to use SQLScout to debug SQLite databases in Xamarin-based Android applications. I was happy to find out that it is actually possible, and better yet, fairly straightforward.
What is SQLScout?
- Room support, including automatic migration to Room and ability to run queries specified in @Query annotations.
- A schema explorer for SQLite databases stored in Android devices and the file system.
- A powerful SQL editor that supports SQLite’s SQL dialect and includes code completion for SQLite-specific functions and symbol navigation.
- A data console to visualize and update database records without the use of SQL statements.
- Database diagrams!
When working with databases in Android applications, SQLScout offers 2 options:
- Download the database to your computer, for offline use. Any record changes are local to the downloaded copy, but can be easily uploaded back to the device.
- “Live Device Connection” where you can manage and update database records directly from the Android device, without downloading any files.
To debug and manage databases in Xamarin-based Android application using SQLScout, we only need to follow a few steps:
1. Build and import the APK into Android Studio
We first need to build the Android application and its APK in Visual Studio. Once we have the APK file, we can import it in Android Studio.
SQLScout is a plugin for Android Studio and it doesn’t work with Visual Studio (the IDE of choice when working with Xamarin). We can work around this limitation by using Android Studio’s ability to import pre-built APKs for debugging.
Importing the APK into Android Studio is as simple as clicking the “Profile or import APK” action from the IDE’s “Welcome” page, and then selecting the APK file from the file system.
Android Studio will create a project for the imported APK. The created project, like any Android project, will have a Run Configuration to run or debug the application in an Android device or emulator.
2. Configure the project’s Android SDK
Before we run the application, we will need to set the Android SDK to use in the project. This seems like a bug in Android Studio, since the APK’s manifest already specifies the platform level to use, and the IDE should be able to pick this up to automatically set up the project’s SDK.
To me, the easiest way to set up the project’s SDK is to open the file AndroidManifest.xml and select the “Open Project Structure” link on the editor’s notification header (figure 2). Then, in the “Project Structure” dialog, select an Android SDK from the drop-down or create a new one(figure 3).
3. Run the application
Now we can run our application (for debugging the app’s database we don’t need to execute the app in debug mode).
4. Locate the application’s database file in the Device File Explorer
Once the application is up and running, we will locate the application’s database file in Android Studio’s Device File Explorer. In Android applications, database files have the extension “db” and are under the path /data/data/<package>/databases (where <package> is the value in the “package” attribute in AndroidManifest.xml). In the case of Xamarin-based applications, the path for database files is /data/data/<package>/files (at least this is the path in the samples I found).
5. Open the selected database file
SQLScout’s tight integration with Android Studio allows us to connect to a database file by simply double-clicking it or selected the “Open” action in the context menu.
SQLScout will automatically download the database file to the local file system and connect to it. After the connection to the database is established, we will be able to browse the database’s schema in SQLScout’s SQLite Explorer.
6. Viewing and updating database records
From this point on, we can view and update records in the database. SQLScout provides 2 ways to do this:
- SQLite Console. The console lets us visualize all the records in a table. It also supports adding, updating and removing records through its UI, without using SQL statements (figure 6).
- SQL Editor. SQLScout’s SQL editor supports SQLite’s SQL dialect, and provides all the functionality we would expect from an IDE editor: syntax highlighting, code completion (including SQLite-specific functions) and symbol navigation. We can also execute SQL statements from the editor with a single click (figure 7).
To update our application’s database with the changes we made to the local copy, we simply need to click the “Upload” action in SQLScout’s SQLite Explorer.
In this article, we have explored how to easy it is to use SQLScout to debug SQLite databases in Xamarin-based Android applications:
- Build the APK using your favorite IDE/build system (Visual Studio, in the case of Xamarin).
- Import the built the APK into Android Studio using its “Profile or import APK” feature.
- Set the project’s Android SDK (hopefully future versions of Android Studio will do this automatically).
- Run the application in an Android device or emulator.
- Locate the database file in Android Studio’s Device File Explorer and open it.
- From this point you can use SQLScout to view and update the records in the database.
SQLScout for Visual Studio?
SQLScout is a plugin for Android Studio and it doesn’t work with Visual Studio. The person who asked us whether it is possible to use SQLScout to debug databases in Xamarin also mentioned that he would like to use SQLScout in Visual Studio, instead of using two IDEs to develop applications and debug databases.
I’d like to kindly ask you, dear reader, to please leave a comment or send us an email to contact at idescout.com if you would consider using a Visual Studio-compatible version of SQLScout (you can see all its features in action here).