Working with a Local Database in Windows Phone 7

Introduction

In this article we are going to see the usage of Isolated Storage by creating a local relational database and save the data locally that are accessible to the specific Windows Phone 7 application. The local relational database will be created on to the Isolated Storage Container where in Windows Phone 7 we need to make use of the LINQ to SQL for all the database operations. LINQ to SQL plays a major role in creating the data schema, selecting the data and making operation to the data as and when required. LINQ to SQL object model uses the System.Data.Linq.DataContext namespace to basically make a proxy call to the local database in the Isolated Storage container. LINQ to SQL Runtime plays as a bridge between the data context object and the real data to do the manipulations based on the user selection.

When considering making use of a local database for Windows Phone 7 Application development we need to consider some of the points mentioned below to get a much more performance and usage over the application.

• Database file will be stored in the Isolated Storage Container.
• Database is available specific to the application targeted as it is Isolated from other application.
• LINQ is used to query the data from the database since TSQL Querying is not supported.
• Local database feature can be accessed directly by adding System.Data.Linq assembly only since primary support is available with the framework.
• Connection string much be use in the format of "Data Source ='isostore:/DirectoryName/Databasename.sdf";

We are going to see how to perform the CRUD operation for the Windows Phone 7 Application Isolated Storage Local database using the Data Context class with a sample application. We will get clear idea on the below tasks on how to perform with the Windows Phone 7, which will be covered in details in this article

• Creating a local database
• Adding data to the local database
• Fetching data from local database
• Deleting data from the local database
• Deleting a local database

Let us jump start to see the step by step process on how to achieve the above tasks with the Isolated Storage Local Database in Windows Phone 7 Application development by creating a sample application.

Steps:

Open Visual Studio 2010 IDE in administrator mode and create a new Silverlight for Windows Phone 7 Application project with a valid project name as shown in the screen below.

Now let us design the User Interface to achieve the tasks listed above, Copy the below XAML code to get the unique user interface with the controls provided to achieve each and every task as shown in the screen below.

XAML Code

<Grid x:Name="LayoutRoot" Background="Transparent">
    <Grid.RowDefinitions>
        <RowDefinition Height="Auto"/>
        <RowDefinition Height="*"/>
    </Grid.RowDefinitions>
    <!-TitlePanel contains the name of the application and page title-->
    <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
        <TextBlock x:Name="ApplicationTitle" Text="F5DEBUG WP7 TUTORIALS" Style="{StaticResource PhoneTextNormalStyle}"/>
        <TextBlock x:Name="PageTitle" Text="Employee DB" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
    </StackPanel>
  <!--ContentPanel - place additional content here-->
    <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
        <TextBox Height="72" HorizontalAlignment="Left" Margin="113,28,0,0" Name="txtName" Text="" VerticalAlignment="Top" Width="324" />
        <TextBlock Height="30" HorizontalAlignment="Left" Margin="33,57,0,0" Name="textBlock1" Text="Name" VerticalAlignment="Top" />
        <TextBox Height="72" HorizontalAlignment="Left" Margin="113,93,0,0" Name="txtAge" Text="" VerticalAlignment="Top" Width="324" />
        <TextBlock Height="30" HorizontalAlignment="Left" Margin="33,122,0,0" Name="textBlock2" Text="Age" VerticalAlignment="Top" />
        <TextBox Height="72" HorizontalAlignment="Left" Margin="113,159,0,0" Name="txtEmpid" Text="" VerticalAlignment="Top" Width="324" />
        <TextBlock Height="30" HorizontalAlignment="Left" Margin="33,188,0,0" Name="textBlock3" Text="Emp ID" VerticalAlignment="Top" />
        <Button Content="Create Database" Height="72" HorizontalAlignment="Left" Margin="33,255,0,0" Name="button1" VerticalAlignment="Top" Width="404" Click="button1_Click" />
        <Button Content="Add an Employee" Height="72" HorizontalAlignment="Left" Margin="33,319,0,0" Name="button2" VerticalAlignment="Top" Width="404" Click="button2_Click" />
        <Button Content="Delete an Employee" Height="72" HorizontalAlignment="Left" Margin="33,384,0,0" Name="button3" VerticalAlignment="Top" Width="404" Click="button3_Click" />
        <Button Content="Fetch all Employees" Height="72" HorizontalAlignment="Left" Margin="33,449,0,0" Name="button4" VerticalAlignment="Top" Width="404" Click="button4_Click" />
        <Button Content="Delete Database" Height="72" HorizontalAlignment="Left" Margin="33,514,0,0" Name="button5" VerticalAlignment="Top" Width="404" Click="button5_Click" />
    </Grid>
</Grid>

Now we are done with the User interface design, now we need to create a data context and start using the data object model and start using the data schema to perform the operations. Let us start with adding the LINQ to SQL data context reference by right clicking the project from the solution explorer and adding the reference from the Add Reference menu as shown in the screen below.



Once we added the reference for the System.Data.Linq to the application, we need to add the below namespaces to the code behind page of the MainPage.Xaml as shown in the screen below.



Code Behind

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.ComponentModel;
using System.Collections.ObjectModel;

Now our first task is to create a database which is used locally isolated to the application. Since we are going to use the LINQ to SQL data context as a bridge between the local database and the data context we need to create 2 classes as shown below.
 
The first class is the Employee class which holds the properties for the fields that are tables and columns which are used to build the database, copy the below code to a new class Employee as shown in the screen below. The class has 3 properties that are going to be the data columns which we are going to get as inputs from the end users. If we notice in the below code we are providing the properties for each of the column with one being a primary key, and other are not null etc which we need to specify based on the database design that is normally changes based on the requirement.



Code Behind

using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Data.Linq.Mapping;
using System.Data.Linq;
 
namespace F5debugWp7LocalDatabase
{
    [Table]
    public class Employee
    {
        public int EmployeeID
        {
            get;
            set;
        }
        [Column(CanBeNull = false)]
        public string EmployeeName
        {
            get;
            set;
        }
        [Column(CanBeNull = false)]
        public string EmployeeAge
        {
            get;
            set;
        }
    }
}

Now we need to add another class as EmployeeDataContext which is basically used as the database schema to create an instance, copy the code from the below code block as shown in the screen below.



Code Behind

using System;
 using System.Net;
 using System.Windows;
 using System.Windows.Controls;
 using System.Windows.Documents;
 using System.Windows.Ink;
 using System.Windows.Input;
 using System.Windows.Media;
 using System.Windows.Media.Animation;
 using System.Windows.Shapes;
 using System.Data.Linq;
namespace F5debugWp7LocalDatabase
 {
 public class EmployeeDataContext:DataContext
 {
 public EmployeeDataContext(string connectionString)
 : base(connectionString)
 {
 }
public Table<Employee> Employees
 {
 get
 {
 return this.GetTable<Employee>();
 }
 }
 }
 }

Now let us start with our code on the MainPage.Xaml.cs to perform each of the tasks that are mentioned above.

Task 1 - Creating a local database

First let us start with creating a database which is the very first step we need to perform when we are going to use the local database storage to store the data with in the application Isolated Storage Container. We need to have a connection string which is pointing to the local database storage, let us create the connection string as a private constant as shown in the code below.

Code Behind

private const string strConnectionString = @"isostore:/EmployeeDB.sdf";

Now we need to add the below code to create a database instance, here we are going to create an instance of the Data context by passing the connection string which is pointing to the local database storage as shown in the screen below.

-

Code Behind

private void button1_Click(object sender, RoutedEventArgs e)
{

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
{
if (Empdb.DatabaseExists() == false)
{
Empdb.CreateDatabase();
MessageBox.Show("Employee Database Created Successfully!!!");
}
else
{
MessageBox.Show("Employee Database already exists!!!");
}
}
 }

Task 2 - Adding data to the local database

Our second task is to add some records to the database which we have created, so to add the employee we need to create an instance of the data context and pass the properties that are matching the column properties which we are created in our data context initially. Here we are going to add an employee which has the Employee ID, Employee Name and Employee Age, we have created the instance and passed the values appropriately. Once we fetched the user inputs we need to insert the details on the submit so we use the
 InsertonSubmit method to add the employees as shown in the screen below.

Code Behind

private void button2_Click(object sender, RoutedEventArgs e)
{
using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

{
Employee newEmployee = new Employee {
EmployeeID = Convert.ToInt32(txtEmpid.Text.ToString()),
EmployeeAge= txtAge.Text.ToString(),
EmployeeName=txtName.Text.ToString()
};
Empdb.Employees.InsertOnSubmit(newEmployee);
Empdb.SubmitChanges();
MessageBox.Show("Employee Added Successfully!!!");
}
}

Task 3 - Fetching data from local database

Our next task is to fetch the consolidated list of data from the local database which normally as an end user we require to report the data in some particular format. So in order to fetch the data we use the LINQ query format to query the data based on the data table and the data context. We have used the simple List to consolidate the data using the LINQ query and we are using the string builder to consolidate the data as user readable as shown in the screen below.



Code Behind

public IList<Employee> GetEmployeeList()
{
IList<Employee> EmployeeList = null;
using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString)
{
IQueryable<Employee> EmpQuery = from Emp in Empdb.Employees select Emp;
EmployeeList = EmpQuery.ToList();
}

return EmployeeList;
}
private void button4_Click(object sender, RoutedEventArgs e)
{
IList<Employee> EmployeesList = this.GetEmployeeList();
StringBuilder strBuilder = new StringBuilder();
strBuilder.AppendLine("Employee Details");
foreach (Employee emp in EmployeesList)
{
strBuilder.AppendLine("Name - " + emp.EmployeeName + " Age - " + emp.EmployeeAge);
}
MessageBox.Show(strBuilder.ToString());
}

Task 4 - Deleting data from the local database

Our next task is to delete the user specific data from the local isolated storage database, this is straight forward as we query the data from the data context and delete it as shown in the screen below.



Code Behind

private void button3_Click(object sender, RoutedEventArgs e)
{
using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
{
IQueryable<Employee> EmpQuery = from Emp in Empdb.Employees where Emp.EmployeeName == txtName.Text select Emp;
Employee EmpRemove = EmpQuery.FirstOrDefault();
Empdb.Employees.DeleteOnSubmit(EmpRemove);
Empdb.SubmitChanges();
MessageBox.Show("Employee Deleted Successfully!!!");
}
}

Task 5 - Deleting a local database

Our final task is to delete the database completely once the user removes the application from the device. So to delete the data we can use the below code as shown in the screen below.



Code Behind

private void button5_Click(object sender, RoutedEventArgs e)
{
using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
{
if (Empdb.DatabaseExists())
{
Empdb.DeleteDatabase();
MessageBox.Show("Employee Database Deleted Successfully!!!");
}
}
}

Once we are done with the above code, our consolidated code will be like below. Just copy and paste it directly on to the cs page for easy access to the code which we have in the above 5 tasks

Consolidated Code Behind
 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using Microsoft.Phone.Controls;

using System.Text;

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.ComponentModel;

using System.Collections.ObjectModel;

 

namespace F5debugWp7LocalDatabase

{

    public partial class MainPage : PhoneApplicationPage

    {

        private const string strConnectionString = @"isostore:/EmployeeDB.sdf";

        // Constructor

        public MainPage()

        {

            InitializeComponent();

        }

       

        private void button1_Click(object sender, RoutedEventArgs e)

        {

            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

            {

                if (Empdb.DatabaseExists() == false)

                {

                    Empdb.CreateDatabase();

                    MessageBox.Show("Employee Database Created Successfully!!!");

                }

                else

                {

                    MessageBox.Show("Employee Database already exists!!!");

                }

            }

        }

 

        private void button2_Click(object sender, RoutedEventArgs e)

        {

            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

            {

                Employee newEmployee = new Employee

                {

                    EmployeeID = Convert.ToInt32(txtEmpid.Text.ToString()),

                    EmployeeAge = txtAge.Text.ToString(),

                    EmployeeName = txtName.Text.ToString()

                };

 

                Empdb.Employees.InsertOnSubmit(newEmployee);

                Empdb.SubmitChanges();

                MessageBox.Show("Employee Added Successfully!!!");

            }

        }

 

        public IList<Employee> GetEmployeeList()

        {

            IList<Employee> EmployeeList = null;

            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

            {

                IQueryable<Employee> EmpQuery = from Emp in Empdb.Employees select Emp;

                EmployeeList = EmpQuery.ToList();

            }

            return EmployeeList;

        }

 

        private void button4_Click(object sender, RoutedEventArgs e)

        {

            IList<Employee> EmployeesList = this.GetEmployeeList();

 

            StringBuilder strBuilder = new StringBuilder();

            strBuilder.AppendLine("Employee Details");

            foreach (Employee emp in EmployeesList)

            {

                strBuilder.AppendLine("Name - " + emp.EmployeeName + " Age - " + emp.EmployeeAge);

            }

            MessageBox.Show(strBuilder.ToString());

        }

 

        private void button3_Click(object sender, RoutedEventArgs e)

        {

            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

            {

                IQueryable<Employee> EmpQuery = from Emp in Empdb.Employees where Emp.EmployeeName == txtName.Text select Emp;

                Employee EmpRemove = EmpQuery.FirstOrDefault();

                Empdb.Employees.DeleteOnSubmit(EmpRemove);

                Empdb.SubmitChanges();

                MessageBox.Show("Employee Deleted Successfully!!!");

            }

        }

 

        private void button5_Click(object sender, RoutedEventArgs e)

        {

            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

            {

                if (Empdb.DatabaseExists())

                {

                    Empdb.DeleteDatabase();

                    MessageBox.Show("Employee Database Deleted Successfully!!!");

                }

            }

        }

    }

 

}

 
Now we are done with our code, to build and test the application press F5 and we can see the expected outputs as shown in the screen below.

Output Screens:




Conclusion:

So in this article we have seen how to use the local isolated storage to create and use the database by using the Linq to SQL data context and also we have seen the different steps to achieve the tasks mentioned on start of this article.