Windows Phone Mango – Local Database

With the Mango update for Windows Phone we can now use a local database to store relational data. The local database in Mango is a SQL Compact Edition and you can use object models for CRUD (Create-Read-Update-Delete) operations and you have to use LINQ to SQL to query, filter and sort the data (currently T-SQL queries are not supported).

The local database is stored in the applications isolated storage. This means that the local database is sandboxed from other applications and only your application and background agents have access to the database.

Using the local database in Mango is perfect if you have relational data like Customers and Orders. It will offer you the ability to do quick, efficient and complex queries to the database using LINQ to SQL and it also allows “lazy loading” so that only the data that is necessary at a particular moment is loaded into memory.

Let’s go over the core concepts you need to know to start working with the local database for Windows Phone

Architectural View

As the above image shows we have a DataContext object which acts as a proxy for the local database. The LINQ to SQL runtime is bridging the application objects (the DataContext object) with the local database data.

The process to start using local database for Windows Phone is:

  1. Implement/Define DataContext and entities (model object to database mapping)
  2. Create a new database in Isolated Storage
  3. Use LINQ and the DataContext object to start working with your data

Simple :) Although lets go through each step in more detail

1. DataContext and POCOs

First of all we need to define the DataContext and all the entities (POCO classes – plain old CLR object) that will be used. The DataContext object contains tables and rows, where each row is represented as an entity. These entity classes define the mapping of object model data and database schema.

The following implementation shows the Customer POCO classes with attributes (LINQ to SQL mapping attributes) and also the DataContext definition.


    [Table]
    public class Customer : INotifyPropertyChanged, INotifyPropertyChanging
    {
        // Primary Key
        private int customerID;

        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int CustomerID
        {
            get
            {
                return customerID;
            }
            set
            {
                if (customerID != value)
                {
                    OnPropertyChanging("CustomerID");
                    customerID = value;
                    OnPropertyChanged("CustomerID");
                }
            }
        }

    public class CustomerDataContext : DataContext
    {
        // Specify the connection string as a static, used in main page and app.xaml.
        public static string DBConnectionString = "Data Source=isostore:/FILENAME.sdf";

        // Pass the connection string to the base class.
        public CustomerDataContext(string connectionString) : base(connectionString) { }

        // Specify a single table for the to-do items.
        public Table Customers;
    }

For each entity, mapping details are specified by using LINQ to SQL mapping attributes. These attributes specify database-specific features such as tables, columns, primary keys, and indexes. For more information, see Attribute-based Mapping (LINQ to SQL).

2. Create a new database in Isolated Storage

Now it’s time to create the database in Isolated Storage. (a .sdf file)


    // Create the database if it does not yet exist.
    using (CustomerDataContext db = new CustomerDataContext("isostore:/FILENAME.sdf"))
    {
        if (db.DatabaseExists() == false)
        {
            // Create the database.
            db.CreateDatabase();
        }
    }

As shown in this example, you must first specify the data context and the file location of the database file in order to create the data context. The DataContext constructor value specifies that the database file name. The isostore:/ portion of the value specifies that the file is located in isolated storage. Next, the CreateDatabase method is used to create the database after the DatabaseExists method confirms that the database does not yet exist.

3. Use LINQ and the DataContext object to start working with your data

Now we are ready to perform CRUD operations on the database.

Create
Inserting data into the database is a two-step process. First add an object to the data context, then call the data context SubmitChanges method to persist the data as a row in the database.

    // Create a new customer based on the text box value.
    Customer newCustomer = new Customer{ CustomerName = nameTextBox.Text };

    // Add the to-do item to the local database.
    db.Customers.InsertOnSubmit(newCustomer);

Read
Fetching data from the database is a matter of using LINQ to SQL using the DataContext object.

    // Define query to fetch all customers in database.
    var customersInDB = from Customer todo in db.Customers
                        select todo;

    // Execute query and place results into a collection.
    var customers = new ObservableCollection<Customer>(customersInDB);

Update
To update an object we need to first of all query the database for the object that is to be updated. Then, modify the object as desired. Finally, call the SubmitChanges method to save the changes to the local database

    //Save changes to the database
    db.SubmitChanges();

Delete
Deleting data in the database is also comprised of three steps. First, query the database for the objects that are to be deleted. Then, depending on whether you have one or more objects to delete, call the DeleteOnSubmit or DeleteAllOnSubmit method, respectively, to put those objects in a pending delete state. Finally, call the SubmitChanges method to save the changes to the local database.

    //Get a handle for the customer, either fetch it from DB or get it from the ViewModel
    //All depends on how your application is implemented
    Customer customerToDelete = customers[0];

    //Remove the Customer item from the local database
    db.Customers.DeleteOnSubmit(customerToDelete);

    //Save changes to the database
    db.SubmitChanges();

Takeaways

The following is just a short summary of things to be aware of when working with Compact SQL for Windows Phone.

  • Use object model for CRUD
  • Use LINQ to SQL
  • T-SQL is not supported
  • Uses IsolatedStorage
  • Background agents has access
  • A reference to System.Data.Linq
  • No other assemblies needed that increase application size, it’s all provided by the Mango framework
  • Need to use a special connection string, “Data Source=isostore:/FILENAME.sdf”
  • There is currently no “helper UI” to build the database and classes so a “Code first” approach is needed

Read more at MSDN:
Local Database Overview for Windows Phone

How to: Create a Basic Local Database Application for Windows Phone

LINQ to SQL Support for Windows Phone

Introduction to LINQ Queries (C#)

How to: Insert Rows Into the Database (LINQ to SQL)

How to: Update Rows in the Database (LINQ to SQL)

How to: Delete Rows From the Database (LINQ to SQL)

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: