Silverlight: Client-Side Database via LINQ and Isolated Storage

Oct 10, 2008  • Silverlight

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:

  1. Saving Objects in Isolated Storage as XML
  2. Keeping a copy of the data in memory to reduce disk access.
  3. 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.