Floating FB popout byF5debug

Learn Windows Phone 7 Development in 31 Days – Day 26 – Working with Creating a Local Database in WP7

 

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.      [more]

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.

clip_image002

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.

clip_image004

XAML Code:

01

<Grid x:Name="LayoutRoot" Background="Transparent">

02

<Grid.RowDefinitions>

03

<RowDefinition Height="Auto"/>

04

<RowDefinition Height="*"/>

05

</Grid.RowDefinitions>

06

 

07

<!--TitlePanel contains the name of the application and page title-->

08

<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">

09

<TextBlock x:Name="ApplicationTitle" Text="F5DEBUG WP7 TUTORIALS" Style="{StaticResource PhoneTextNormalStyle}"/>

10

<TextBlock x:Name="PageTitle" Text="Employee DB" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>

11

</StackPanel>

12

 

13

<!--ContentPanel - place additional content here-->

14

<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">

15

<TextBox Height="72" HorizontalAlignment="Left" Margin="113,28,0,0" Name="txtName" Text="" VerticalAlignment="Top" Width="324" />

16

<TextBlock Height="30" HorizontalAlignment="Left" Margin="33,57,0,0" Name="textBlock1" Text="Name" VerticalAlignment="Top" />

17

<TextBox Height="72" HorizontalAlignment="Left" Margin="113,93,0,0" Name="txtAge" Text="" VerticalAlignment="Top" Width="324" />

18

<TextBlock Height="30" HorizontalAlignment="Left" Margin="33,122,0,0" Name="textBlock2" Text="Age" VerticalAlignment="Top" />

19

<TextBox Height="72" HorizontalAlignment="Left" Margin="113,159,0,0" Name="txtEmpid" Text="" VerticalAlignment="Top" Width="324" />

20

<TextBlock Height="30" HorizontalAlignment="Left" Margin="33,188,0,0" Name="textBlock3" Text="Emp ID" VerticalAlignment="Top" />

21

<Button Content="Create Database" Height="72" HorizontalAlignment="Left" Margin="33,255,0,0" Name="button1" VerticalAlignment="Top" Width="404" Click="button1_Click" />

22

<Button Content="Add an Employee" Height="72" HorizontalAlignment="Left" Margin="33,319,0,0" Name="button2" VerticalAlignment="Top" Width="404" Click="button2_Click" />

23

<Button Content="Delete an Employee" Height="72" HorizontalAlignment="Left" Margin="33,384,0,0" Name="button3" VerticalAlignment="Top" Width="404" Click="button3_Click" />

24

<Button Content="Fetch all Employees" Height="72" HorizontalAlignment="Left" Margin="33,449,0,0" Name="button4" VerticalAlignment="Top" Width="404" Click="button4_Click" />

25

<Button Content="Delete Database" Height="72" HorizontalAlignment="Left" Margin="33,514,0,0" Name="button5" VerticalAlignment="Top" Width="404" Click="button5_Click" />

26

</Grid>

27

</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.

clip_image006

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.

clip_image008

Code Behind:

1

using System.Data.Linq;

2

using System.Data.Linq.Mapping;

3

using System.ComponentModel;

4

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.

clip_image010

Code Behind:

01

using System;

02

using System.Net;

03

using System.Windows;

04

using System.Windows.Controls;

05

using System.Windows.Documents;

06

using System.Windows.Ink;

07

using System.Windows.Input;

08

using System.Windows.Media;

09

using System.Windows.Media.Animation;

10

using System.Windows.Shapes;

11

using System.Data.Linq.Mapping;

12

using System.Data.Linq;

13

 

14

namespace F5debugWp7LocalDatabase

15

{

16

[Table]

17

public class Employee

18

{

19

[Column(IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false, AutoSync = AutoSync.OnInsert)]

20

public int EmployeeID

21

{

22

get;

23

set;

24

}

25

 

26

[Column(CanBeNull = false)]

27

public string EmployeeName

28

{

29

get;

30

set;

31

}

32

 

33

[Column(CanBeNull = false)]

34

public string EmployeeAge

35

{

36

get;

37

set;

38

}

39

}

40

}

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.

clip_image012

Code Behind:

01

using System;

02

using System.Net;

03

using System.Windows;

04

using System.Windows.Controls;

05

using System.Windows.Documents;

06

using System.Windows.Ink;

07

using System.Windows.Input;

08

using System.Windows.Media;

09

using System.Windows.Media.Animation;

10

using System.Windows.Shapes;

11

using System.Data.Linq;

12

 

13

namespace F5debugWp7LocalDatabase

14

{

15

public class EmployeeDataContext:DataContext

16

{

17

public EmployeeDataContext(string connectionString)

18

: base(connectionString)

19

{

20

}

21

 

22

public Table<Employee> Employees

23

{

24

get

25

{

26

return this.GetTable<Employee>();

27

}

28

}

29

}

30

}

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:

1

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.

clip_image014

Code Behind:

01

private void button1_Click(object sender, RoutedEventArgs e)

02

{

03

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

04

{

05

if (Empdb.DatabaseExists() == false)

06

{

07

Empdb.CreateDatabase();

08

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

09

}

10

else

11

{

12

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

13

}

14

}

15

}

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.

clip_image016

Code Behind:

01

private void button2_Click(object sender, RoutedEventArgs e)

02

{

03

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

04

{

05

Employee newEmployee = new Employee {

06

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

07

EmployeeAge= txtAge.Text.ToString(),

08

EmployeeName=txtName.Text.ToString()

09

};

10

 

11

Empdb.Employees.InsertOnSubmit(newEmployee);

12

Empdb.SubmitChanges();

13

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

14

}

15

}

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.

clip_image018

Code Behind:

01

public IList<Employee> GetEmployeeList()

02

{

03

IList<Employee> EmployeeList = null;

04

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

05

{

06

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

07

EmployeeList = EmpQuery.ToList();

08

}

09

return EmployeeList;

10

}

11

 

12

private void button4_Click(object sender, RoutedEventArgs e)

13

{

14

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

15

 

16

StringBuilder strBuilder = new StringBuilder();

17

strBuilder.AppendLine("Employee Details");

18

foreach (Employee emp in EmployeesList)

19

{

20

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

21

}

22

MessageBox.Show(strBuilder.ToString());

23

}

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.

clip_image020

Code Behind:

01

private void button3_Click(object sender, RoutedEventArgs e)

02

{

03

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

04

{

05

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

06

Employee EmpRemove = EmpQuery.FirstOrDefault();

07

Empdb.Employees.DeleteOnSubmit(EmpRemove);

08

Empdb.SubmitChanges();

09

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

10

}

11

}

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.

clip_image022

Code Behind:

01

private void button5_Click(object sender, RoutedEventArgs e)

02

{

03

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

04

{

05

if (Empdb.DatabaseExists())

06

{

07

Empdb.DeleteDatabase();

08

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

09

}

10

}

11

}

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:

001

using System;

002

using System.Collections.Generic;

003

using System.Linq;

004

using System.Net;

005

using System.Windows;

006

using System.Windows.Controls;

007

using System.Windows.Documents;

008

using System.Windows.Input;

009

using System.Windows.Media;

010

using System.Windows.Media.Animation;

011

using System.Windows.Shapes;

012

using Microsoft.Phone.Controls;

013

using System.Text;

014

using System.Data.Linq;

015

using System.Data.Linq.Mapping;

016

using System.ComponentModel;

017

using System.Collections.ObjectModel;

018

 

019

namespace F5debugWp7LocalDatabase

020

{

021

public partial class MainPage : PhoneApplicationPage

022

{

023

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

024

 

025

// Constructor

026

public MainPage()

027

{

028

InitializeComponent();

029

}

030

 

031

private void button1_Click(object sender, RoutedEventArgs e)

032

{

033

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

034

{

035

if (Empdb.DatabaseExists() == false)

036

{

037

Empdb.CreateDatabase();

038

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

039

}

040

else

041

{

042

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

043

}

044

}

045

}

046

 

047

private void button2_Click(object sender, RoutedEventArgs e)

048

{

049

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

050

{

051

Employee newEmployee = new Employee {

052

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

053

EmployeeAge= txtAge.Text.ToString(),

054

EmployeeName=txtName.Text.ToString()

055

};

056

 

057

Empdb.Employees.InsertOnSubmit(newEmployee);

058

Empdb.SubmitChanges();

059

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

060

}

061

}

062

 

063

public IList<Employee> GetEmployeeList()

064

{

065

IList<Employee> EmployeeList = null;

066

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

067

{

068

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

069

EmployeeList = EmpQuery.ToList();

070

}

071

return EmployeeList;

072

}

073

 

074

private void button4_Click(object sender, RoutedEventArgs e)

075

{

076

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

077

 

078

StringBuilder strBuilder = new StringBuilder();

079

strBuilder.AppendLine("Employee Details");

080

foreach (Employee emp in EmployeesList)

081

{

082

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

083

}

084

MessageBox.Show(strBuilder.ToString());

085

}

086

 

087

private void button3_Click(object sender, RoutedEventArgs e)

088

{

089

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

090

{

091

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

092

Employee EmpRemove = EmpQuery.FirstOrDefault();

093

Empdb.Employees.DeleteOnSubmit(EmpRemove);

094

Empdb.SubmitChanges();

095

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

096

}

097

}

098

 

099

private void button5_Click(object sender, RoutedEventArgs e)

100

{

101

using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))

102

{

103

if (Empdb.DatabaseExists())

104

{

105

Empdb.DeleteDatabase();

106

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

107

}

108

}

109

}

110

}

111

 

112

}

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:

clip_image024

clip_image026

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Comments (1) -

andreas
andreas
9/5/2012 2:17:17 AM #

hi sir i'm new on WP7
thanks sir for share it..
i was try and it works..

but i have a quesiton, it's said that the database is located in isolated storage device. But when we use emulator, where is the database file actually located?

and how to see the content of databases, like a table, column and detil of databases.
I mean like on mysql server use command "show databases", "describe database" "show tables" etc.

thanks sir

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter