Isolated Storage Database Windows Phone 8

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.

  1. using System.IO.IsolatedStorage  
  2. 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.

LocalDBDemo

The pink color box is the LongList selector.

XAML Code

  1. <phone:PhoneApplicationPage  
  2. x:Class="LocalDBDemo.MainPage"  
  3.     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  4.     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  5.     xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"  
  6.     xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"  
  7.     xmlns:d="http://schemas.microsoft.com/expression/blend/2008"  
  8.     xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"  
  9.     mc:Ignorable="d"  
  10.     FontFamily="{StaticResource PhoneFontFamilyNormal}"  
  11.     FontSize="{StaticResource PhoneFontSizeNormal}"  
  12.     Foreground="{StaticResource PhoneForegroundBrush}"  
  13.     SupportedOrientations="Portrait" Orientation="Portrait"  
  14.     shell:SystemTray.IsVisible="True">  
  15.     <Grid x:Name="LayoutRoot" Background="Transparent">  
  16. <Grid.RowDefinitions>  
  17. <RowDefinition Height="Auto"/>  
  18.     <RowDefinition Height="*"/>  
  19. </Grid.RowDefinitions>  
  20. <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">  
  21.     <TextBlock Text="www.windowsapptutorials.wordpress.com" Style="    {StaticResource PhoneTextNormalStyle}" Margin="12,0" Foreground="#FF0C0CEC" FontSize="22"/>  
  22.     <TextBlock Text="Local DB Demo" Margin="2,-7,7,0" Style="{StaticResource PhoneTextTitle1Style}" FontSize="68"/>  
  23. </StackPanel>  
  24. <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">  
  25.     <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"/>  
  26. <Button x:Name="createbtn" Content="Create DB" HorizontalAlignment="Left" Margin="19,306,0,0" VerticalAlignment="Top" Click="createbtn_Click"/>  
  27. <Button x:Name="Savebtn" Content="Save" HorizontalAlignment="Left" Margin="168,306,0,0" VerticalAlignment="Top" Width="107" Click="Savebtn_Click"/>  
  28. <Button x:Name="Readbtn" Content="Read" HorizontalAlignment="Left" Margin="275,306,0,0" VerticalAlignment="Top" Click="Readbtn_Click"/>  
  29. <Button x:Name="updatebtn" Content="Update" HorizontalAlignment="Left" Margin="19,378,0,0" VerticalAlignment="Top" Click="updatebtn_Click"/>  
  30. <Button x:Name="deletebtn" Content="Delete" HorizontalAlignment="Left" Margin="177,378,0,0" VerticalAlignment="Top" Click="deletebtn_Click"/>  
  31. <phone:LongListSelector SelectionChanged="studentlist_SelectionChanged_1" x:Name="studentlist" HorizontalAlignment="Left" Height="63" Margin="26,460,0,0" VerticalAlignment="Top" Width="253" Background="#FFE23232"/>  
  32. </Grid>  
  33.     <TextBlock HorizontalAlignment="Left" TextWrapping="Wrap" Text="Address :" VerticalAlignment="Top" Margin="106,233,0,0" Grid.Row="1"/>  
  34.     <TextBlock HorizontalAlignment="Left" TextWrapping="Wrap" Text="Name :" VerticalAlignment="Top" Margin="123,110,0,0" Grid.Row="1"/>  
  35.     <TextBox x:Name="addresstxtbox" HorizontalAlignment="Left" Height="72" TextWrapping="Wrap" VerticalAlignment="Top" Width="207" Margin="218,208,0,0" Grid.Row="1"/>  
  36.    <TextBox x:Name="nametxtbox" HorizontalAlignment="Left" Height="72" TextWrapping="Wrap" VerticalAlignment="Top" Width="207" Margin="220,85,0,0" Grid.Row="1"/>  
  37. </Grid>  
  38. </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.

Add class

Solution Explore

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

 class name

Write the following code for the "StudentDetails" class.

C# Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Data.Linq;  
  7. using System.Data.Linq.Mapping; 

  8. namespace LocalDBDemo  
  9. {  
  10.     [Table]  
  11.     public class StudentDetails  
  12.     {  
  13.        [Column(IsPrimaryKey=true,IsDbGenerated=true,CanBeNull=false,AutoSync=Aut        oSync.OnInsert)]  
  14.         public int id  
  15.         {get;set;}  
  16.   
  17.        [Column(CanBeNull = false)]  
  18.        public string Name  
  19.        { getset; }  
  20.   
  21.        [Column(CanBeNull = false)]  
  22.        public int No  
  23.        { getset; }
     
  24.        [Column(CanBeNull = false)]  
  25.        public string Address  
  26.        { getset; }  
  27.    }  

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

C# Code For

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Data.Linq;  
  7.   
  8. namespace LocalDBDemo  
  9. {  
  10.     public class StudentDataContext:DataContext  
  11.     {  
  12.         public StudentDataContext(string connectionPath)  
  13.         : base(connectionPath)  
  14.     {  
  15. }  
  16. public Table<StudentDetails> Students  
  17. {  
  18.     get  
  19.     {  
  20.         return this.GetTable<StudentDetails>();  
  21.     }  
  22. }   

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

  1. private void createbtn_Click(object sender, RoutedEventArgs e)  
  2. {  
  3.     using (StudentDataContext studentdb = new StudentDataContext(path))  
  4.     {  
  5.         if (studentdb.DatabaseExists() == false)  
  6.         {  
  7.           studentdb.CreateDatabase();  
  8.           MessageBox.Show("Database create Successfully");  
  9.        }  
  10.        else  
  11.        {  
  12.           MessageBox.Show("Database Already Exists ");  
  13.        }  
  14.     }  

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.

  1. private void Savebtn_Click(object sender, RoutedEventArgs e)  
  2. {  
  3.     using (StudentDataContext studetdb = new StudentDataContext(path))  
  4.     {  
  5.         StudentDetails newdata = new StudentDetails  
  6.         {  
  7.             Name=nametxtbox.Text,  
  8.             No=Convert.ToInt16(notextbox.Text),  
  9.             Address=addresstxtbox.Text,  
  10.        };  
  11.        studetdb.Students.InsertOnSubmit(newdata);  
  12.        studetdb.SubmitChanges();  
  13.        MessageBox.Show("New Record Added Successfully");  
  14.    }  

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.

  1. public IList<StudentDetails> GetStudentDetails()  
  2. {  
  3.     IList<StudentDetails> Studebtlist = null;  
  4.     using (StudentDataContext studentdb = new StudentDataContext(path))  
  5.     {  
  6.        IQueryable<StudentDetails> query = from student in studentdb.Students select student;  
  7.        Studebtlist = query.ToList();  
  8.     }  
  9.     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.

  1. private void Readbtn_Click(object sender, RoutedEventArgs e)  
  2. {  
  3.     IList<StudentDetails> Studentlist = this.GetStudentDetails();  
  4.     StringBuilder details = new StringBuilder();  
  5.   
  6.     foreach (StudentDetails std in Studentlist)  
  7.     {  
  8.        details.AppendLine("Name:" + std.Name + "No:" + std.No + "Address:" + std.Address);  
  9.     }  
  10.     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.

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

C# Code for Update

  1. private void updatebtn_Click(object sender, RoutedEventArgs e)  
  2. {  
  3.     using (StudentDataContext studentdb = new StudentDataContext(path))  
  4.     {  
  5.         IQueryable<StudentDetails> query = from update in studentdb.Students    where update.Name == selectedName select update;  
  6.          StudentDetails updatevalue = query.FirstOrDefault();  
  7.          updatevalue.Name = nametxtbox.Text;  
  8.          updatevalue.No =Convert.ToInt16( notextbox.Text);  
  9.          updatevalue.Address = addresstxtbox.Text;  
  10.          studentdb.SubmitChanges();  
  11.      }  
  12.      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

  1. private void deletebtn_Click(object sender, RoutedEventArgs e)  
  2. {  
  3.    if (selectedName == "")  
  4.    {  
  5.        MessageBox.Show("Select Name");  
  6.    }  
  7.    else  
  8.    {  
  9.        using (StudentDataContext studentdb = new StudentDataContext(path))  
  10.        {  
  11.            IQueryable<StudentDetails> query = from std in studentdb.Students where std.Name == selectedName select std;  
  12.            StudentDetails delete = query.FirstOrDefault();  
  13.            studentdb.Students.DeleteOnSubmit(delete);  
  14.            studentdb.SubmitChanges();  
  15.            MessageBox.Show("Deleted Successfully");  
  16.        }  
  17.     }  

All the functions are completed. Now run the application by pressing the F5 key and see the output.

output

result

Next Recommended Readings