Silverlight Offline Database Storage
In previous article I talked about Offline Web applications and how local database storage is central to make this happen. In this article I will show you how to build a simple local database in Silverlight using Isolated Storage and LINQ.
Modeling the Database
First, we will create a Data Model for a sample "Orders" database as below.
In the above Data Model, we have 5 entity classes: Customer, Product, Category, Order and OrderDetail. The arrows between the entities represent the relationships between the entities. Each class represents a row in table and each property a field in table. Eg: Order entity has an OrderDetails property which points to collection of OrderDetail objects.
Public Class OrderDB
Inherits OfflineDBContext
Private _customers As Table(Of Customer)
Public Property Customers() As Table(Of Customer)
Get
Return _customers
End Get
Set(ByVal value As Table(Of Customer))
_customers = value
End Set
End Property
Private _categories As Table(Of Category)
Public Property Categories() As Table(Of Category)
Get
Return _categories
End Get
Set(ByVal value As Table(Of Category))
_categories = value
End Set
End Property
Private _orders As Table(Of Order)
Public Property Orders() As Table(Of Order)
Get
Return _orders
End Get
Set(ByVal value As Table(Of Order))
_orders = value
End Set
End Property
Private _orderDetails As Table(Of OrderDetail)
Public Property OrderDetails() As Table(Of OrderDetail)
Get
Return _orderDetails
End Get
Set(ByVal value As Table(Of OrderDetail))
_orderDetails = value
End Set
End Property
Private _products As Table(Of Product)
Public Property Products() As Table(Of Product)
Get
Return _products
End Get
Set(ByVal value As Table(Of Product))
_products = value
End Set
End Property
Public Sub New()
MyBase.New()
_customers = New Table(Of Customer)
_categories = New Table(Of Category)
_orderDetails = New Table(Of OrderDetail)
_products = New Table(Of Product)
End Sub
Public Sub Load()
'Load the database from file
Dim _orderDB As OrderDB = MyBase.LoadDBFromFile(DBName, Me.GetType())
End Sub
End Class
Here we are inheriting our local database OrderDB from OfflineDBContext which provides facilities to load the database from Isolated storage and save the changes back to Isolated storage. Each property in OrderDB represents a table in our Local data store.
Interacting with the Local database
Now that we have modeled our Orders database now we can interact with the database. Below are few examples:
1)Insert New Category, Products and Customers to the database
Dim db As OrderDB = New OrderDB()
db.Load()
'Create new categories and products
Dim category1 As New Category
category1.CategoryId = 1
category1.CategoryName = "Games"
db.Categories.Add(category1)
'Link products with the category
Dim product1 As New Product
product1.ProductId = 1
product1.ProductName = "Xbox"
product1.UnitPrice = 299
product1.Category = category1
Dim product2 As New Product
product2.ProductId = 2
product2.ProductName = "Playstation3"
product2.UnitPrice = 500
product2.Category = category1
db.Products.Add(product1)
db.Products.Add(product2)
'Create Customer
Dim customer1 As New Customer
customer1.CustomerId = 1
customer1.CustomerName = "Jerry"
Dim customer2 As New Customer
customer2.CustomerId = 2
customer2.CustomerName = "Tom"
db.Customers.Add(customer1)
db.Customers.Add(customer2)
'Save changes to database
db.SubmitChanges()
db.Close()
2)Querying Products by Category from Database
Dim products = (From p In db.Products Where p.Category.CategoryName = "Games")
3)Update the Unit Price of a Product in the Database
'Update price for a product
Dim prodName As String = "Xbox"
Dim product = (From p In db.Products Where p.ProductName = prodName).Single
If Not product Is Nothing Then
product.UnitPrice = 249
db.SubmitChanges()
End If
4)Delete a Product from the Database
'Remove product
'Using lambda expressions to provide a filter for specifying the products to delete
db.Products.RemoveAll(Function(p As Product) p.Category.CategoryName = "Games" And p.ProductName = "Xbox")
db.SubmitChanges()
Silverlight Local Database: Behind the scenes
OfflineDBContext is key class which provides facilities to load the database from Isolated storage and save the changes back to Isolated storage.
SaveDbToFile function of OfflineDBContext serializes the object model passed to it and saves it in Isolated storage. LoadDBFromFile function loads the database file from Isolated storage and converts it into a Object Model.
Private Sub SaveDBToFile(ByVal dbFileName As String, ByVal o As Object)
Using store As IsolatedStorageFile = IsolatedStorageFile.GetUserStoreForApplication()
'We are overwriting an existing file in case if it exists
Dim fs As IsolatedStorageFileStream = store.CreateFile(dbFileName)
'Serialize to file
Dim xmlser As New XmlSerializer(o.GetType())
xmlser.Serialize(fs, o)
fs.Close()
End Using
End Sub
Protected Function LoadDBFromFile(ByVal dbFileName As String, ByVal type As Type)
Dim o As Object = Nothing
Using store As IsolatedStorageFile = IsolatedStorageFile.GetUserStoreForApplication()
Dim files As String() = store.GetFileNames()
If store.FileExists(dbFileName) Then
'Deserialize back to the Object
Using fs As IsolatedStorageFileStream = store.OpenFile(dbFileName, FileMode.Open)
Dim xmlser As New XmlSerializer(type)
o = xmlser.Deserialize(fs)
End Using
End If
End Using
Return o
End Function
Limitations
• In current approach, we are saving entire Object graph to the database even if we have changed only one row in a table . In future, I am thinking of tracking changes and only updating those changes back to the database.
• We are serializing to XML which is very verbose format and may occupy more disk space. In future, I am thinking of using compression to solve this.
Please share your feedback and suggestions on how to improve this. I am going to update this based on your feedback.
You can download the sample from here.
Comments
Hi Sriram,
Very nice job - thank you very much for sharing this.
It would be really interesting to see if there is a way to download and utilize the SQL Server Compact 3.5 Database.
http://www.microsoft.com/sql/editions/compact/downloads.mspx
Thanks again for sharing your hard work.
Posted by: David Roh | August 9, 2008 08:22 AM
Thanks David for your feedback!
I have posted this project on Codeplex.
http://www.codeplex.com/silverlightdatabase
Posted by: Sriram Balaji | August 11, 2008 05:25 AM
Any chance you could produce a C# version?
Posted by: Fallon Massey | August 15, 2008 12:47 AM
I will post a C# version shortly
Posted by: Sriram Balaji | August 18, 2008 03:20 AM