18 October, 2010

How to: Database for WP7

Update (27.04.2011): Native support for SQL CE is coming in May 2011 to the WP7 platform. Therefore, the below database will become more and more obsolete…

There is no support for databases (e.g. SQL Server, CE) on WP7. It will come, I’m sure – but in the initial phase you need to find other means of storing data on the phone for your application needs.

A number of different options exists; but the database I’ve been using for a while is the JubbaDB. It is a simple object database (manifested in a single DLL) that is stored in your applications Isolated Storage. It supports all your existing classes and allows for querying the database tables using LINQ.

image 

The structure is simple; it is a fa├žade (IDatabase) that holds a number of ITables<T> underneath it. It is exposed via a DBFactory class that handles the creation and retrieval of data from IsolatedStorage. The database stores your entire business  objects, and does not split it into different tables with rows and columns etc. It is just plain and simple objects stored (serialized) in the database.

image

Using the JubbaDB is darn simple too:

//create database
IDatabase db = DBFactory.CreateDatabase("myDB");
Debug.Assert(db != null, "null");


//if no support for “myclass” -> add support
if(!db.IsStorageSupported<MyClass>())
db.AddTableSupport<MyClass>();


//load table
var tbl = db.LoadTable<MyClass>();

//add data
tbl.Data.Add(new MyClass() { Id = 1, Name = "d1" });
tbl.Data.Add(new MyClass() { Id = 2, Name = "d2" });
tbl.Data.Add(new MyClass() { Id = 3, Name = "d3" });
tbl.Data.Add(new MyClass() { Id = 4, Name = "d4" });

//save table data
db.SaveTable<MyClass>(tbl);


The custom classes this table supports is a simple one:

/// <summary>
///
A custom class
/// </summary>
public class MyClass
{
public int Id { get; set; }

public string Name { get; set; }
}



When you want to retrieve data from the database, load the table into memory and query using standard LINQ syntax:

//reload;
ITable<MyClass> dbTbl = db.LoadTable<MyClass>();
Debug.Assert(dbTbl != null, "null");


//query for data with id = 3
MyClass cls = tdbTblbl.Data.Where(t => t.Id == 3).FirstOrDefault();


Pretty simple and clean!



Download: http://www.clauskonrad.net/download.ashx?id=5



Update history:
- 18.12.2010:
Version 2.0 :
Recently experienced some unfortunate exceptions when using the database in a multithreaded environment. It has now been made thread safe.
 
- 02.11.2010:
Version 1.0 :
Initial version.



Technorati Tags:

8 comments:

Ing. Marco Rinaldi said...

Hi Claus, where could I download JubbaDB project source ?

Thanks you

Claus Konrad said...

Sorry - I've updated the post with a download link ;-)

Ing. Marco Rinaldi said...

Hi Claus, I tried your class very good work, but I've a question: is there a way to delete item from the "database" ?

I tried this code, but this seems to remove only the instance from the List not from the storage:

IDatabase db = GetDatabase();
ITable dbTbl = db.LoadTable();
bTbl.Data.Remove(item); db.SaveTable(dbTbl);

If you send me the source code I could help you to improve your framework.

Thanks for help

Ing. Marco Rinaldi said...

I found my error in code:

db.SaveTable(dbTbl);

and not

db.SaveTable(dbTbl);

But no exception was raised

Claus Konrad said...

You are correct; the proposed removal only removes the item from the in-memory collection (List). You need to persist to disk to see it removed from the database as well. When you persist a table - the database actually removes the table file on disk and replaces this with your new version. In this way - you will always see the correct table when reloading from disk.

Exceptions? I agree that they are usefull in some scenarios, but what should provoke the exception here and what type of exception did you expect?

Claus Konrad said...

I've updated the code with a shortcut that allows you to load the table directly from the DBFactory. No need to first load the database object and then the table.

Download from the source above.

Ing. Marco Rinaldi said...

Hi Claus, probably you've forgotten to public the download link.

>Download from the source above.

Thanks

Claus Konrad said...

Hi

Well - I was referring to the original source:
Download files here: http://www.clauskonrad.net/download.ashx?id=5