Windows Phone Mango Local Database(SQL CE): Introduction

by WindowsPhoneGeek

I am starting a new "Windows Phone Mango Local Database(SQL CE)" series of short posts that will cover all you need to know in order  to get started using a Local Database in Windows Phone 7.1 Mango.  Here is what is included in this series:

• Windows Phone Mango Local Database(SQL CE): Introduction
• Windows Phone Mango Local Database(SQL CE): Linq to SQL
• Windows Phone Mango Local Database(SQL CE): [Table] attribute
• Windows Phone Mango Local Database(SQL CE): [Column] attribute
• Windows Phone Mango Local Database(SQL CE): [Association] attribute
• Windows Phone Mango Local Database(SQL CE): [Index] attribute
• Windows Phone Mango Local Database(SQL CE): Database mapping
• Windows Phone Mango Local Database(SQL CE): DataContext
• Windows Phone Mango Local Database(SQL CE): Connection Strings
• Windows Phone Mango Local Database(SQL CE): Creating the Database
• Windows Phone Mango Local Database(SQL CE): Database Queries with LINQ
• Windows Phone Mango Local Database(SQL CE): How to Insert data
• Windows Phone Mango Local Database(SQL CE): How to Update data
• Windows Phone Mango Local Database(SQL CE): How to Delete data

This is the first post so I am going to give a brief explanation of what a Local Database is and what else you need to know before getting started using SQL CE in  Windows Phone 7.1 Mango applications.

What is Local Database?

To begin with, lets first mention that with Windows Phone 7.1 Mango, you can now store relational data in a Local Database stored in your application's Isolated Storage. In Windows Phone all applications are "isolated" from each other, which means that one application can access only its own Isolated Storage. I.e one database can be used only from one application and can not be shared between several applications.

Local database in Windows Phone 7.1 is an implementation of SQL Compact for Mango. It is an in memory/embedded relationaldatabase management system (RDB MS). Local databases are very suitable for scenarios where you have related entities for example:  Customers and Orders.

The following scenarios are perfect for local database usage:

• Relatively many related Tables/entities containing a moderate amount of records
• A few tables containing large amounts of records/data
• Local cache of data that comes from the cloud

A local database offers the following advantages:

• the ability to make complex queries to the database which are performed efficiently and quickly
• allows only data that is necessary at a particular moment to be loaded in memory
• you can do all operations like : adding , removing, inserting, deleting, etc and all this operations are handled efficiently by the SQL Compact runtime.

Here is the Process that you need to follow in order to use a Local Database in Windows Phone:

• 1. Implement all database classes: tables, columns, relations, datacontext, etc.
• 2. Create a new database (,sdf) file in Isolated Storage(by default the database is empty).
• 3. Populate the database stored in the Isolated Storage with data

8 Things you need to know about SQL CE before you begin

• LINQ to SQL is used as the ORM engine
• Database files are stored in Isolated Storage
• LINQ is used to query data, T-SQL queries are not supported
• There is no need to distribute assemblies that will increase the application size, with Windows Phone Mango the Local Database support is part of the framework.
• A reference to the System.Data.Linq assembly must be added to the project
• A special format of the connection string must be used like for example: 
  "Data Source='isostore:/DIRECTORY/FILE.sdf'";
• The code-first approach to defining the database schema is preferred in this version of Windows Phone 7.1 Mango. I.e. at the moment there is no visual designer that can help developers mapping and configuring their classes to work with the database(so you have to write all classes on your own). However you can use tools like: Using SqlMetal to generate Windows Phone Mango Local Database classes
• Minimum requirements: Visual Studio 2010 SP1 and Windows Phone 7.1 Mango Developer Tools

You can also take a look at the MSDN section: Local Database Overview for Windows Phone

That was the intro post of the "Windows Phone Mango Local Database(SQL CE)"  series of articles. Stay tuned for the rest of the posts.