When I did a search on "Silverlight Database" there weren't many results, and none of them actually mentioned a small database that you could embed into your Silverlight application. So, I thought I'd share a solution that I've used to store application data on the client using a combination of LINQ and Isolated Storage. This actually gives you a small "SQL"-like database since you can use LINQ to interact with it.
Basically, this technique constist of:
- Saving Objects in Isolated Storage as XML
- Keeping a copy of the data in memory to reduce disk access.
- Data is returned as a generic List<> object so you can use LINQ.
Also, if you use this in conjunction with web services, it allows you to cache data on the client and persist it across user session to give you a nice performance increase in certain situations.
Here's some sample code to access a table named "PersonTable" using this method:
// Load the table from the database
var people = ClientDB.PersonTable.Load();
// Select only specific data
var newPeople = (from p in people
where p.FirstName == "Steve"
select p).ToList();
// Save new data over the old
ClientDB.PersonTable.Save(newPeople);
// Here's an example of basically selecting directly
// from the table
var newPeople = (from p in ClientDB.PersonTable.Load()
where p.FirstName == "Steve"
select p).ToList();
// Remove a specific person from the table
ClientDB.PersonTable.Remove(new Guid("7687ebfc-31e6-464c-a70d-fd3bb9ecbdb4"));
Here's the code for the "PersonTable" class used above:
using System;
using System.Collections.Generic;
using System.IO.IsolatedStorage;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
// Make sure to add a reference to the following in your project:
// System.Xml.Linq
namespace SimpleSilverlightDatabaseWithLINQ.Data.ClientDB
{
public class PersonTable
{
private const string _filename = "PersonTable.xml";
private static List<Person> _cache = null;
private static object _cache_lock = new object();
public static List<Person> Load()
{
// Check if data has already been loaded from
// isolated storage, if not, then load it to memory.
if (PersonTable._cache == null)
{
lock (PersonTable._cache_lock)
{
if (PersonTable._cache == null)
{
var isf = IsolatedStorageFile.GetUserStoreForApplication();
if (!isf.FileExists(PersonTable._filename))
{
// no data has been previously saved, so set the
// in memory list to an empty list.
PersonTable._cache = new List<Person>();
}
else
{
// Data has bee previously saved, so load it.
var isfs = new IsolatedStorageFileStream(PersonTable._filename, System.IO.FileMode.Open, isf);
var reader = XmlReader.Create(isfs);
var doc = XDocument.Load(reader);
PersonTable._cache = (from e in doc.Root.Elements("Person")
select new Person
{
ID = new Guid(e.Attribute("ID").Value),
FirstName = e.Attribute("FirstName").Value,
LastName = e.Attribute("LastName").Value
}
).ToList();
isfs.Close();
isfs.Dispose();
}
isf.Dispose();
}
}
}
// Return the in memory list
return PersonTable._cache;
}
public static void Save(List<Person> col)
{
lock (PersonTable._cache_lock)
{
// Overwrite the cache with the new list
PersonTable._cache = col;
// Save the list to Isolated Storage
XElement xml = new XElement("People",
from p in col
select new XElement("Person",
new XAttribute("ID", p.ID.ToString()),
new XAttribute("FirstName", p.FirstName),
new XAttribute("LastName", p.LastName)
)
);
var isf = IsolatedStorageFile.GetUserStoreForApplication();
var isfs = new IsolatedStorageFileStream(PersonTable._filename, System.IO.FileMode.Create, isf);
var writer = XmlWriter.Create(isfs);
xml.Save(writer);
writer.Close();
isfs.Close();
isfs.Dispose();
isf.Dispose();
}
}
public static void Remove(Guid id)
{
// Remove a specific Person by ID
var people = PersonTable.Load();
people.Remove(
people.First(m => m.ID.ToString() == id.ToString())
);
PersonTable.Save(people);
}
}
}
And, here's the code for the Person class that is used in this example:
namespace SimpleSilverlightDatabaseWithLINQ.Data
{
public class Person
{
public Person()
{
this.ID = Guid.NewGuid();
}
public Guid ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
}
Conclusion
You can actually store more data using this method than you probably think, before you begin to see performance issues. And, it could probably be optimized fairly easily to store much more data by storing the equivalent of "table index" data in memory only, and then reading from disk to retrieve the necessary records when needed. You'd just have to optimize the caching so it doesn't eat up too much memory, and you could probably store a couple hundred MB pretty easily. Anyway, "AS IS" it is perfect for storing most data that you'll need to keep track of on the client.