Isolated Storage Database Windows Phone 8

Suresh M
9y
12.2k
0
2

Article

Introduction

In this article, we will see the relational local database in Windows Phone 8 and save the data locally. It is accessible only to a specific application. In Windows Phone 8, LINQ is a major role to create a schema. CRUD operations LINQ to SQL will act as a bridge between data context objects. In Windows Phone 8, the local DB is called Isolated Storage.

Note

LINQ is used to query the data from isolated storage.

using System.IO.IsolatedStorage
using System.Linq

Is the namespace to access Isolated Storage APIs.

Now, we will see the Create, Read, Update, Delete (CRUD) operations.

Procedure

Create a new Windows Phone 8 project name as LocalDBDemo and design the UI to perform CRUD operation as shown in the following screen.

The pink color box is the LongList selector.

XAML Code

<phone:PhoneApplicationPage
x:Class="LocalDBDemo.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"
FontFamily="{StaticResource PhoneFontFamilyNormal}"
FontSize="{StaticResource PhoneFontSizeNormal}"
Foreground="{StaticResource PhoneForegroundBrush}"
SupportedOrientations="Portrait" Orientation="Portrait"
shell:SystemTray.IsVisible="True">
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock Text="www.windowsapptutorials.wordpress.com" Style=" {StaticResource PhoneTextNormalStyle}" Margin="12,0" Foreground="#FF0C0CEC" FontSize="22"/>
<TextBlock Text="Local DB Demo" Margin="2,-7,7,0" Style="{StaticResource PhoneTextTitle1Style}" FontSize="68"/>
</StackPanel>
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<TextBlock HorizontalAlignment="Left" Margin="132,173,0,0" TextWrapping="Wrap" Text="NO :" VerticalAlignment="Top"/> <TextBox x:Name="notextbox" HorizontalAlignment="Left" Height="72" Margin="207,149,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="207"/>
<Button x:Name="createbtn" Content="Create DB" HorizontalAlignment="Left" Margin="19,306,0,0" VerticalAlignment="Top" Click="createbtn_Click"/>
<Button x:Name="Savebtn" Content="Save" HorizontalAlignment="Left" Margin="168,306,0,0" VerticalAlignment="Top" Width="107" Click="Savebtn_Click"/>
<Button x:Name="Readbtn" Content="Read" HorizontalAlignment="Left" Margin="275,306,0,0" VerticalAlignment="Top" Click="Readbtn_Click"/>
<Button x:Name="updatebtn" Content="Update" HorizontalAlignment="Left" Margin="19,378,0,0" VerticalAlignment="Top" Click="updatebtn_Click"/>
<Button x:Name="deletebtn" Content="Delete" HorizontalAlignment="Left" Margin="177,378,0,0" VerticalAlignment="Top" Click="deletebtn_Click"/>
<phone:LongListSelector SelectionChanged="studentlist_SelectionChanged_1" x:Name="studentlist" HorizontalAlignment="Left" Height="63" Margin="26,460,0,0" VerticalAlignment="Top" Width="253" Background="#FFE23232"/>
</Grid>
<TextBlock HorizontalAlignment="Left" TextWrapping="Wrap" Text="Address :" VerticalAlignment="Top" Margin="106,233,0,0" Grid.Row="1"/>
<TextBlock HorizontalAlignment="Left" TextWrapping="Wrap" Text="Name :" VerticalAlignment="Top" Margin="123,110,0,0" Grid.Row="1"/>
<TextBox x:Name="addresstxtbox" HorizontalAlignment="Left" Height="72" TextWrapping="Wrap" VerticalAlignment="Top" Width="207" Margin="218,208,0,0" Grid.Row="1"/>
<TextBox x:Name="nametxtbox" HorizontalAlignment="Left" Height="72" TextWrapping="Wrap" VerticalAlignment="Top" Width="207" Margin="220,85,0,0" Grid.Row="1"/>
</Grid>
</phone:PhoneApplicationPage>

Now we will create two classes to make a bridge between the local database and data context. The first class is the “StudentDetails” class that holds the properties for the tables and columns that are used to create a database. The next class is called “StudentDataContext” that is used as a schema to create an instance.

Right-click on your project in the Solution Explorer and select Add class as shown in the following screen.

Change the class name to "StudentDetails" and click Add. Add another class and name it to “StudentDataContext” as shown below.

Write the following code for the "StudentDetails" class.

C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LocalDBDemo
{
[Table]
public class StudentDetails
{
[Column(IsPrimaryKey=true,IsDbGenerated=true,CanBeNull=false,AutoSync=Aut oSync.OnInsert)]
public int id
{get;set;}
[Column(CanBeNull = false)]
public string Name
{ get; set; }
[Column(CanBeNull = false)]
public int No
{ get; set; }
[Column(CanBeNull = false)]
public string Address
{ get; set; }
}
}

Now write the following code for “StudentDataContext”. It is used to design the database schema to create an instance.

C# Code For

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Linq;
namespace LocalDBDemo
{
public class StudentDataContext:DataContext
{
public StudentDataContext(string connectionPath)
: base(connectionPath)
{
}
public Table<StudentDetails> Students
{
get
{
return this.GetTable<StudentDetails>();
}
}

The database design is now ready to store and retrieve. Go to the MainPage.xaml.cs page to do the CRUD operations. Create the database instance with the following code.

C# Code to create DB

private void createbtn_Click(object sender, RoutedEventArgs e)
{
using (StudentDataContext studentdb = new StudentDataContext(path))
{
if (studentdb.DatabaseExists() == false)
{
studentdb.CreateDatabase();
MessageBox.Show("Database create Successfully");
}
else
{
MessageBox.Show("Database Already Exists ");
}
}
}

Create (insert) new data in isolated storage

Next, we will insert new data. Create an instance for the database and another instance for the table. Once we get the user input, insert it to the table using the InsertOnSubmit method and finally submit the data using the SubmitChanges method.

private void Savebtn_Click(object sender, RoutedEventArgs e)
{
using (StudentDataContext studetdb = new StudentDataContext(path))
{
StudentDetails newdata = new StudentDetails
{
Name=nametxtbox.Text,
No=Convert.ToInt16(notextbox.Text),
Address=addresstxtbox.Text,
};
studetdb.Students.InsertOnSubmit(newdata);
studetdb.SubmitChanges();
MessageBox.Show("New Record Added Successfully");
}
}

Read the data from isolated storage

Next we will read the data from isolated storage. Write the following code.

C# Code

Read all the data from the student table. Write the following code using a LINQ query.

public IList<StudentDetails> GetStudentDetails()
{
IList<StudentDetails> Studebtlist = null;
using (StudentDataContext studentdb = new StudentDataContext(path))
{
IQueryable<StudentDetails> query = from student in studentdb.Students select student;
Studebtlist = query.ToList();
}
return Studebtlist;
}

Next call this function to view all the data from the tables.

C# Code

Add the namespace using System.Text; to bind the string using StringBuilder.

private void Readbtn_Click(object sender, RoutedEventArgs e)
{
IList<StudentDetails> Studentlist = this.GetStudentDetails();
StringBuilder details = new StringBuilder();
foreach (StudentDetails std in Studentlist)
{
details.AppendLine("Name:" + std.Name + "No:" + std.No + "Address:" + std.Address);
}
MessageBox.Show(details.ToString());
}

Next we will update the data. Select the name from the longList Slector (see the Pink color box in the UI). Get the name that you select from the longlist selector and assign it to selectedName declared as a public string. Using that name we will update the values. Write the following code to assign the selected name.

string selectedName = "";
private void studentlist_SelectionChanged_1(object sender, SelectionChangedEventArgs e)
{
selectedName = studentlist.SelectedItem.ToString();
}

C# Code for Update

private void updatebtn_Click(object sender, RoutedEventArgs e)
{
using (StudentDataContext studentdb = new StudentDataContext(path))
{
IQueryable<StudentDetails> query = from update in studentdb.Students where update.Name == selectedName select update;
StudentDetails updatevalue = query.FirstOrDefault();
updatevalue.Name = nametxtbox.Text;
updatevalue.No =Convert.ToInt16( notextbox.Text);
updatevalue.Address = addresstxtbox.Text;
studentdb.SubmitChanges();
}
MessageBox.Show("Details Updated");
}

Delete data from Isolated Storage

Now we will delete the data from isolated storage by the selected student from the LongList selector. Write the following code.

C# Code

private void deletebtn_Click(object sender, RoutedEventArgs e)
{
if (selectedName == "")
{
MessageBox.Show("Select Name");
}
else
{
using (StudentDataContext studentdb = new StudentDataContext(path))
{
IQueryable<StudentDetails> query = from std in studentdb.Students where std.Name == selectedName select std;
StudentDetails delete = query.FirstOrDefault();
studentdb.Students.DeleteOnSubmit(delete);
studentdb.SubmitChanges();
MessageBox.Show("Deleted Successfully");
}
}
}