Dhananjay Kumar

Version: Windows Phone 7.1, Mango, 7.5 [at time of writing this post this post was applicable to said versions]

Local data base support in windows phone 7.1 is enabled via LINQ to SQL. LINQ to SQL enables application to use isolated storage as database.

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

Image taken from MSDN.

There is few points’ worth to be discussed about local database

• · It runs as application process
• · Unlikely to SQL Server , it does not run in the background
• · Local database belongs to a particular application and cannot be  accessed   by other application
• · Local database resides in isolated storage
• · Local database does 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 cane

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

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

Creating Entity class

Note: Make sure you are calling OnNotifyPropertyChanged() after seeting the value of the property [Edited]

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

You need to add the namespace to class,

And implement interface as below,

Since this class is representing table so 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. Name property is representing Name column of table.

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

After adding all the columns 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

}
}

In this way you have created entity class representing Bloggers table.

Creating Datacontext class

Create a class and inherit DataContext class

Next you need to set the connection string as below,

Pass connection string to base class as below,

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 Database

There are two steps involved in creating database

1. Check whether databases exist or not?
2. If not exist then create database.

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

Add 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 database schema has been created. To use it on the page very first you need to implement INotifyPropertyChanged on the page as below,

Next step you need to do is

1. Create reference of BloggerDataContext
2. Define a property as observablecollection of Bloggers entity

Now in constructor create instance of BloggersDataContext ,

Adding new Record

Adding new record is very much straightforward.

1. Create instance of Bloggers
2. Add it to the property define 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 name of list box here.

Deleting a Record

To delete a particular item

1. Fetch the selected item from list box
2. Get reference of selected item in data context
3. Remove 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");
}
}
}

&nbsp;

Design UI

For purpose of this post, I have put 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="<a href="http://schemas.microsoft.com/winfx/2006/xaml/presentation">http://schemas.microsoft.com/winfx/2006/xaml/presentation</a>"
xmlns:x="<a href="http://schemas.microsoft.com/winfx/2006/xaml">http://schemas.microsoft.com/winfx/2006/xaml</a>"
xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
xmlns:d="<a href="http://schemas.microsoft.com/expression/blend/2008">http://schemas.microsoft.com/expression/blend/2008</a>"
xmlns:mc="<a href="http://schemas.openxmlformats.org/markup-compatibility/2006">http://schemas.openxmlformats.org/markup-compatibility/2006</a>"
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 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.

Follow @debug_mode