Local Database application for Windows Phone

Version: Windows Phone 7.1, Mango, 7.5 [at time of writing this code]

Local database support in Windows Phone 7.1 is enabled via LINQ to SQL. LINQ to SQL enables application to use isolated storage as databases.




Each application has its own isolated storage and LINQ to SQL in Windows 7.1 enables us to use that as a local database. LINQ to SQL gives us an object model to work with local database.



Note: Image taken from MSDN.

There are a few points' worth discussing about local databases

• It runs as an application process
• Unlikely to SQL Server , it does not run in the background
• Local databases belongs to a particular application and cannot be accessed by other application
• Local databases resides in isolated storage
• Local databases do not support TSQL.

Application

I am going to create a table Bloggers in the local database of Windows Phone 7.1. UI will be as below to work with the data from the local storage. User can

1. Add detail of blogger to local database.
2. Delete blogger from the local database.
3. Page will get refreshed dynamically.

Create a Windows Phone application by choosing target framework 7.1. After creation of a project add a reference of System.Data.Linq to the project.

Creating Entity class

Let us go ahead and decide on the database schema and entity of database.. I am going to have a table called Bloggers in the database. To represent the table you need to create an entity class. I am implementing the INotifyPropertyChanged interface to support two way binding. However you can choose not to implement this interface and it has nothing to do with local database support.

You need to add the namespace to the class,

And implement the interface as below,

Since this class is representing a table, we attribute it with the [Table]

Implement the property of INotifyPropertyChanged as below,



Now each column of the table will be mapped to a property in the entity class as below. The Name property is representing the Name column of the table.



You will have to create properties like below for all the columns you want as part of the table. You must have a primary key column in the table and that could be created by giving an extra parameter to the column attribute.



After adding all the columns, the Bloggers class would be as below,

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

namespace LocalDatabaseApplication.Model
{

    [Table]
    public class Bloggers : INotifyPropertyChanged
    {
        private string name;
        [Column]
        public string Name
        {
            get
            {
                return name;
            }
            set
            {
                NotifyPropertyChanged("Name");
                name = value;
            }
        }

        private string interest;
        [Column]
        public string Interest
        {
            get
            {
                return interest;
            }
            set
            {
                NotifyPropertyChanged("Interest");
                interest = value;
            }
        }

        private int  id;
        [Column(IsPrimaryKey=true,
            CanBeNull= false,
            IsDbGenerated= true ,
            DbType="INT NOT NULL Identity",
            AutoSync=AutoSync.OnInsert)]
        public int  Id
        {
            get
            {
                return id;
            }
            set
            {
                NotifyPropertyChanged("Id");
                id = value;
            }
        }

         private int  totalposts;
        [Column]
         public int Totalposts
        {
            get
            {
                return totalposts;
            }
            set
            {
                NotifyPropertyChanged("Totalposts");
                totalposts = value;
            }
        } 
 
        #region INotifyPropertyChanged Members
 
        public event PropertyChangedEventHandler PropertyChanged;
 
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion

        #region INotifyPropertyChanging Members

        #endregion

    }
}

You have created an entity class representing the Bloggers table.

Creating Datacontext class

Create a class and inherit the DataContext class



Pass the connection string to base class as below,



The context class would be implemented as below,

using System.Data.Linq;

namespace LocalDatabaseApplication.Model
{
    public class BloggersDataContext : DataContext
    {
          public static string DBConnectionString = "Data Source=isostore:/Bloggers.sdf";

          public BloggersDataContext(string connectionString)
                : base(connectionString)
           {
           }  
              public Table<Bloggers> bloggers;
 
    }
}

Creating the Database

There are two steps involved in creating the database

1. Check whether the databases exist or not?

2. If not exist then create the database.

You will have to perform the above two steps in the application constructor. So the code will be added to App.xaml.cs.

Add the below line of codes to constructor of application,

public App()
        {
 
  using (BloggersDataContext db = new BloggersDataContext(BloggersDataContext.DBConnectionString))
            {
                if (db.DatabaseExists() == false)
                {
                    //Create the database
                    db.CreateDatabase();
                }
            }

Using Database

As of now, the database schema has been created. To use it on the page, you first need to implement INotifyPropertyChanged on the page as below,



Next step you need to do is

1. Create a reference to BloggerDataContext

2. Define a property as an ObservableCollection of the Bloggers entity

Now in the constructor create an instance of BloggersDataContext ,



Adding a new Record

Adding a new record is very much straightforward.

1. Create an instance of Bloggers

2. Add it to the property definitions on the page

3. Call InsertOnSubmit

4. Finally call SubmitChanges.

Fetching all Records

You can use simple LINQ query to fetch all the records.

lstData is the name of the list box here.

Deleting a Record

To delete a particular item

1. Fetch the selected item from the list box

2. Get a reference of the selected item in the data context

3. Remove the selected item

Full code for reference is as below,

using System;
using System.Linq;
using System.Windows;
using Microsoft.Phone.Controls;
using System.ComponentModel;
using LocalDatabaseApplication.Model;
using System.Collections.ObjectModel;
using System.Windows.Controls;

namespace LocalDatabaseApplication
{
    public partial class MainPage : PhoneApplicationPage, INotifyPropertyChanged
    {

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
        #endregion

        private  BloggersDataContext  bloggerDB;      
        private ObservableCollection<Bloggers > bloggerItems;
        public ObservableCollection<Bloggers> BloggerItems
        {
            get
            {
                return bloggerItems;
            }
            set
            {
                if (bloggerItems != value)
                {
                    bloggerItems = value;
                    NotifyPropertyChanged("BloggerItems");
                }
            }
        } 

        public MainPage()
        {
            InitializeComponent();
            bloggerDB = new BloggersDataContext(BloggersDataContext.DBConnectionString);
            this.DataContext = this;
        } 

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Update");
        } 

        protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
        {
            var bloggers = from r in bloggerDB.bloggers select r;
            BloggerItems = new ObservableCollection<Bloggers>(bloggers);
            lstData.ItemsSource = BloggerItems;
            base.OnNavigatedTo(e);
        }
 

        private void Button_Click(object sender, RoutedEventArgs e)
        {

            Bloggers data = (sender as Button).DataContext as Bloggers;
            ListBoxItem bloggerToDeleteFromListBox = this.lstData.ItemContainerGenerator.ContainerFromItem(data)
                                                     as ListBoxItem;          

            var bloggerToDelete = (from r in bloggerDB.bloggers
                                   where r.Id == data.Id
                                   select r).FirstOrDefault();
            BloggerItems.Remove(bloggerToDelete);
            bloggerDB.bloggers.DeleteOnSubmit(bloggerToDelete);
            bloggerDB.SubmitChanges();
            MessageBox.Show("Delete");
        } 

        private void btnAdd_Click(object sender, RoutedEventArgs e)
        {
            Bloggers bloggerToAdd = new Bloggers
            {
                Interest = txtInterest.Text,
                Name = txtName.Text ,
                Totalposts = Convert.ToInt32(txtPosts.Text)
            };

            BloggerItems.Add(bloggerToAdd);
            bloggerDB.bloggers.InsertOnSubmit(bloggerToAdd);
            bloggerDB.SubmitChanges();
            MessageBox.Show("Add");
        }
    }
}

Design UI

For purpose of this post, I have made the UI very simple. UI consists of

1. List box to display data

2. Textbox to get user input to add

3. Button to add and delete

For reference XAML of UI is as below,

<phone:PhoneApplicationPage
    x:Class="LocalDatabaseApplication.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
    xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"
    shell:SystemTray.IsVisible="True">

    <!--LayoutRoot is the root grid where all page content is placed-->
    <!--LayoutRoot is the root grid where all page content is placed-->
    <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="local database application" Style="{StaticResource PhoneTextNormalStyle}"/>
          </StackPanel>

        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
            <Grid.RowDefinitions>
                <RowDefinition Height="*" />
                <RowDefinition Height="320" />
            </Grid.RowDefinitions>
            <ListBox x:Name="lstData" Margin="5,7,6,15">
                <ListBox.ItemTemplate>
                    <DataTemplate >
                        <Grid x:Name="TopGrid">
                            <Grid.ColumnDefinitions>
                                <ColumnDefinition Width="50" />
                                <ColumnDefinition Width="*" />
                            </Grid.ColumnDefinitions>
                            <Button Margin="5,5,5,5" Click="Button_Click">
                                <Button.Template >
                                    <ControlTemplate >
                                        <Image Source="delete.png" VerticalAlignment="Center"   Height="30" Width="30"/>
                                    </ControlTemplate>
                                </Button.Template>
                            </Button>
                            <Grid x:Name="nestedGrid" Grid.Column="1">
                            <Grid.RowDefinitions>
                                <RowDefinition Height="auto" />
                                <RowDefinition Height="auto" />
                            </Grid.RowDefinitions>                           
                            <TextBlock Text="{Binding Name}" Style="{StaticResource PhoneTextTitle2Style}" />
                                <StackPanel Grid.Row="1" Orientation="Horizontal">
                                    <TextBlock Text="{Binding Interest}"  Style="{StaticResource PhoneTextSubtleStyle}" />
                                <TextBlock Text="{Binding Totalposts}" Style="{StaticResource PhoneTextAccentStyle}"/>
                            </StackPanel>
                        </Grid>
                        </Grid>
                    </DataTemplate>
                </ListBox.ItemTemplate>
            </ListBox>
            <Grid HorizontalAlignment="Left" Height="320" VerticalAlignment="Bottom" Grid.Row="1">
              <Grid.RowDefinitions>
                     <RowDefinition Height="80"/>
                     <RowDefinition Height="80"/>
                     <RowDefinition Height="80"/>
                     <RowDefinition Height="80"/>                 
                </Grid.RowDefinitions>
                <Grid.ColumnDefinitions >
                    <ColumnDefinition Width="77" />
                    <ColumnDefinition Width="29*" />
                </Grid.ColumnDefinitions>
                <TextBlock  Text="Name" Style="{StaticResource PhoneTextNormalStyle}" />
                <TextBlock  Grid.Row="1"  Text="Interest" Style="{StaticResource PhoneTextNormalStyle}" Grid.ColumnSpan="2" Margin="12,0,6,0" />
                <TextBlock  Grid.Row="2"  Text="Posts" Style="{StaticResource PhoneTextNormalStyle}"/>
                <TextBox x:Name="txtName" Grid.Column="1" Margin="35,0,-327,0" VerticalAlignment="top" Height="80" />           
                <TextBox x:Name="txtInterest" Grid.Row="1" Grid.Column="1" Margin="35,0,-327,0" VerticalAlignment="top" Height="80" />
                <TextBox x:Name="txtPosts" Grid.Row="2" Grid.Column="1" Margin="35,0,-327,0" />

            <Button x:Name="btnAdd" Grid.Row="3" Content="Add" Click="btnAdd_Click" VerticalAlignment="Top" Grid.ColumnSpan="2" Margin="0,0,-296,0" />
            </Grid>
        </Grid>
    </Grid>

</phone:PhoneApplicationPage>

Press F5 to run the application and you should get the application running with local database. I hope this post was useful. Thanks for reading.

If you find my posts useful you may like to follow me on twitter http://twitter.com/debug_mode or may like Facebook page of my blog http://www.facebook.com/DebugMode.Net If you want to see post on a particular topic please do write on FB page or tweet me about that, I would love to help you.