Mango Sample: Database Part 1:2

Storing Windows Phone application data is a pretty normal requirement. If you want it to persist across sessions, or even phone reboots, you really have three options:

1. Save to the Cloud
2. Save to Isolated Storage
3. Save to Local Database

Each is correct for certain scenarios. But in this article, I will ONLY walk through the Local Database. This is an enhanced version of SQL CE – meaning your SQL skills are an asset. And, if you don’t have them, they come quickly.

Note: I like things simple. But a database in a Windows Phone application isn’t super simple. So, please resist the urge to skip to the samples. I’m including some valuable learning you should read. Sorry, mates.

Okay, technically we are creating an SDF file in your app’s Isolated Storage. Mango makes the CE assemblies native. Visual Studio, on the other hand, doesn’t make this obvious (yet). So, creating your database will require some tactics.

About SQL CE:

1. SQL Server Compact is file based; the connection string is a path to the SDF.
2. SQL Server Compact is not a service like SQL Server. It is file-based.
3. SQL Server Compact supports up to 256 connections.
4. SQL Server Compact supports database files up to 512 MB.

MSDN: The maximum size of a local database, in megabytes, if not specified is 32. The maximum value is 512MB.

About Linq to SQL

Yes, you can use Linq to SQL on Windows Phone. In fact, you must! New features have been added specifically for Windows Phone. However, there are limitations:

1. ExecuteCommand (raw t-SQL) is not supported.
2. ADO.NET Objects (such as DataReader) are not supported.
3. Only SQL Server Compact data types are supported.
4. Table.IListSource.GetList is not supported. You must bind to a query.
5. BinaryFormatter is not supported. For VarBinary use byte[].
6. Take() requires a constant: For a variable, calculate it in a different query.
7. Skip() and Take() require an ordered list to return results consistently.

Option 1: Code-First

Basically, you are allowed to decorate your classes and when you interact with a Data Context, the schema of the database will be made to accommodate your classes.

We need System.Data.Linq for this.

The Entity Framework refers to this approach as a code-first approach. It supports the domain-driven design pattern (which focuses on user activities, not data schemas).

The DTO (simple version):

Above, we decorate DTO classes and properties System.Data.Linq attributes. The framework will map these classes and properties to database tables and columns.

The Data Context:

Above, we write our data context. When created, it tests if the database exists. If not, it generates it. The database schema reflects the DTO decorations we setup.

The View-Model implementation:

Above, our sample View-Model interacts with the Linq to SQL data context. It does not and should not know if the database exists, our data context handles everything.

Do better: An introduction to Memory Management

LINQ to SQL change tracking works by maintaining two copies of each object. One copy remains as the original. The other is changed by the application. When an update is submitted, LINQ to SQL can determine which properties have been updated.

The INotifyPropertyChanging interface notifies the DataContext when it is modifying a property. The DataContext can use that notification as a trigger to create the copy. This way, only the items that are actually changing need to be duplicated.

Above, we implement both INotifyPropertyChanging (which helps reduce your memory footprint) and INotifyPropertyChanged (which enables XAML data binding).

Do better: An introduction to Version Columns

One of the easiest ways to optimize the performance of an update operation on a table is to add a version column. This optimization is specific to LINQ to SQL for Windows Phone. This can yield a significant performance improvement for large updates. More here.

Do better: An introduction to Compiled Queries

By default, LINQ to SQL will translate the Transact-SQL statement every time the query is executed. For frequent queries (like find record with ID), the overhead is wasteful. To avoid this compile your queries. More here.

Do better: Even More Hints

1. Is your data read only? Set ObjectTrackingEnabled to false to save memory.
2. Save frequently so your change sets are small and fast.
3. Save frequently so tombstoning doesn’t take long.
4. Use indexes on common column filters.

You have Questions?

1. You might be wondering about the connection string syntax? Look here.
2. You might be wondering how you update schemas after creation? Look here.
3. You might be wondering how you create referential integrity? Look here.
4. You might be wondering about using BLOB data in your DB. Look here.

Option 2: Use the Visual Designer

It seems most intuitive to use the database visual designer at design-time. It is most unfortunate that this method is the least supported. Moreover, it seems just messy that we must drop to the Visual Studio DOS prompt and use SQLMetal to get it fully working.

This isn’t trivial, so I will put it in a separate post here.