If you are using Mono for Android and need to use SQLite you are in luck since the platform fully supports this scenario. In fact you don’t even need to do anything special, and this post also applies if you are developing for Android in Java using Eclipse. What’s different however is Eclipse will let you copy files from your Android device, in Mono you are using Visual Studio which at this time does not have that capability.
The preferred method (but not the only one) of creating, and managing a SQLite DB in Android is to create a class and inherit from SQLiteOpenHelper, which is in the namespace ‘Android.Database.Sqlite’, you will also need to add the references to assemblies: ‘Mono.Android’, ‘Mono.Data.Sqlite’
Below is an example of where you inherit SQLiteOpenHelper and then override two methods: ‘OnCreate’ and ‘OnUpgrade’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
public class Database : Android.Database.Sqlite.SQLiteOpenHelper, IDatabase
{
private const string DATABASE_NAME = "MyDatabase";
private const int DATABASE_VERSION = 1;
public Database(Android.Content.Context context)
: base(context, DATABASE_NAME, null, DATABASE_VERSION)
{
}
public override void OnCreate(SQLiteDatabase db)
{
db.ExecSQL(@"CREATE TABLE MyTable (id INT PRIMARY KEY, comments TEXT NOT NULL");
}
public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.ExecSQL("DROP TABLE IF EXISTS MyTable");
OnCreate(db);
}
} |
It is important to note that OnCreate is ONLY called when the database is accessed for the very first time, subsequent calls are ignored. If the DB already exists, it is ignored, OnUpgrade is called only if the version number has been incremented, the database already exists and when the database is accessed for the first time per that instance. Also to note is that I am taking an extremely lazy approach to upgrade where I simply drop the tables and re-create them, obviously not ideal for real world scenario because you would lose all your data.
At the top of the file I am defining two constants, DATABASE_NAME and DATABASE_VERSION which I mentioned above briefly is used for upgrade scenarios. The name is the actual name the DB file will end up being names on the Android file system. In order to browse the file system you will need to have your Android device rooted or you will need to have a developer Android device that you obtained directly from Google.
These instructions pertain to (Windows users only, the commands will be similar however on OSX)
- Open cmd
- Type ‘adb shell’, if you get an error type ‘adb kill-server’ and try again.
- Type ‘su’ (for super user access, your phone must be rooted or be dev phone)
- Once you are in the shell, type ls (you will notice its a linux shell)
- Type ‘cd data/data’, it will list out all of the directories
- Type ‘ls data’, and find your applications directory
- Type cd <com.company.appname>
- Type cd ‘databases’
- Type ‘ls’ to look at your databases
- Note the name of your database matches your constant defined above (if everything worked…)
- Type ‘pwd’ (print working directory)
- Copy the path of this directory to Notepad or somewhere safe
To browse the database you can download SQLite Database Browser for free, and do the following.
- In order to browse the database your must copy the db file from your Android device onto your computer, I created a batch file for this and I run it when I need to take a look at something.
- Open ‘cmd’ prompts
- Type ‘adb pull <path of db file> <local path>’ for example ‘adb pull /data/data/com.elucidsoft.myapp/databases/MyDatabase D:\MyDatabase
- Open up SQLite Database Browser
- File -> Open -> D:\MyDatabase
- Whenever you need to see the new version you need to File -> Close Database, and repeat step 3, otherwise it can not copy over an in-use database file
I hope this helps you when working with SQLite on Android, it definitely helps me!
