DotNetSpeaks - Part 76 - Windows Phone

Part 76 - Windows Phone - Create Local Database

Published:

April 02, 2012 By Sumit Dutta

Visited: 8423

In this article I will discuss about how to create local database in Windos Phone. Windows Phone uses LINQ to SQL to store and retrieve data from database. One can create local database in Windows Phone which resides in application's isolated storage.

Let's write code to achieve local database support for Windows Phone.

Step 1: Add reference of System.Data.Linq

Step 2: Create a class Employee.cs and add below using directive.

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

Step 3: Add below code in the Employee class. Employee.cs is entity class which represents database table of application in the local database. [Table] attributes maps class to a local database.

INotifyPropertyChanged is used for change tracking.

INotifyPropertyChanging limits memory consumption related to change tracking.

Above the entity propery name column definition needs to be defined.

There should be one primary key in the table.

[Table]
public class Employee : INotifyPropertyChanged, INotifyPropertyChanging
{
   private int _empId;

   [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
   public int EmpId
   {
      get
      {
         return _empId;
      }
      set
      {
         if (_empId != value)
         {
            NotifyPropertyChanging("EmpId");
            _empId = value;
            NotifyPropertyChanged("EmpId");
         }
      }
   }

private string _name;

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

   public string Name
   {
      get
      {
         return _name;
      }
      set
      {
         if (_name != value)
         {
            NotifyPropertyChanging("Name");
            _name = value;
            NotifyPropertyChanged("Name");
         }
      }
   }

private string _Address;

   [Column(CanBeNull = false, AutoSync = AutoSync.OnInsert)]
   public string Address
   {
      get
      {
         return _Address;
      }
      set
      {
         if (_Address != value)
         {
            NotifyPropertyChanging("Address");
            _Address = value;
            NotifyPropertyChanged("Address");
         }
      }
   }

private int _Age;

   [Column(CanBeNull = false, AutoSync = AutoSync.OnInsert)]
   public int Age
   {
      get
      {
         return _Age;
      }
      set
      {
         if (_Age != value)
         {
            NotifyPropertyChanging("Age");
            _Age = value;
            NotifyPropertyChanged("Age");
         }
      }
   }

public event PropertyChangedEventHandler PropertyChanged;

   // Used to notify the page that a data context property changed
   private void NotifyPropertyChanged(string propertyName)
   {
      if (PropertyChanged != null)
      {
         PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
      }
   }

public event PropertyChangingEventHandler PropertyChanging;

   // Used to notify the data context that a data context property is about to change
   private void NotifyPropertyChanging(string propertyName)
   {
      if (PropertyChanging != null)
      {
         PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
      }
   }
}

Step 4: Add another class EmployeeDataContext.cs and add below using directive.

using System.Data.Linq;

Step 5: Place below code in the EmployeeDataContext.cs. EmployeeDataContext inherits DataContext. DataContext represents database proxy containing the Table objects to represent the tables in the database. Below code calls constructor declares Employee table name.

public class EmployeeDataContext :DataContext
{
   public static string DBConnectionString = "Data Source=isostore:/Employee.sdf";
   public EmployeeDataContext(string connectionString): base(connectionString)
   { }

// Specify a single table for the employee items.
public Table<Employee> employees;
}

Step 5: Open App.xaml.cs, add below code in the constructior name App(). This code will create the database if doesn't exist.

// Create the database if it does not exist.
using (EmployeeDataContext db = new EmployeeDataContext(EmployeeDataContext.DBConnectionString))
{
   if (db.DatabaseExists() == false)
   {
      db.CreateDatabase();
   }
}

Step 6: Open MainPage.xaml and add three textblocks and textboxes inside ContentPanel.

Step 7: Add application bar in MainPage.xaml like below.

<phone:PhoneApplicationPage.ApplicationBar>
   <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
      <shell:ApplicationBarIconButton IconUri="/Images/appbar_button1.png" Text="Save" Click="Save_Click" />
      <shell:ApplicationBarIconButton IconUri="/Images/appbar_button2.png" Text="Retrieve" Click="Retrieve_Click" />
      <shell:ApplicationBarIconButton IconUri="/Images/appbar_button2.png" Text="Delete" Click="Delete_Click" />
   </shell:ApplicationBar>
</phone:PhoneApplicationPage.ApplicationBar>

Refer Application Bar to know more about application bar.

Step 8: Add below code above the constructor class MainPage.xaml.cs. ObservableCollection will bind data to UI.

private EmployeeDataContext employeeDB;

// Define an observable collection property that controls can bind to.
private ObservableCollection<Employee> _employee;

public ObservableCollection<Employee> Employees
{
   get
   {
      return _employee;
   }
   set
   {
      if (_employee != value)
      {
         _employee = value;
         NotifyPropertyChanged("Employees");
      }
   }
}

Step 9: Modify the constructor of the MainPage.xaml.cs like below. Below code will connect to the database and instantiates data context.

// Constructor
public MainPage()
{
InitializeComponent();

employeeDB = new EmployeeDataContext(EmployeeDataContext.DBConnectionString);
this.DataContext = this;
}

Step 10: This will initialize the Employees and place if any data is available in the table.

protected override void OnNavigatedTo(System.Windows.Navigation.NavigationEventArgs e)
{
   //Define the query to gather all of the employee items.
   var employeesInDB = from Employee emp in employeeDB.employees
                        select emp;

// Execute the query and place the results into a collection.
Employees = new ObservableCollection<Employee>(employeesInDB);

// Call the base method.
base.OnNavigatedTo(e);
}

Step 11: Add Save event handler in MainPage.xaml.cs which will be triggered on click of save from application bar. The data will be populated in the employee object and added to Employees observable collection. Then InsertOnSubmit method will be called to add item in the local database. Data won't be saved until SubmitChanges is called.

private void Save_Click(object sender, EventArgs args)
{
   // Create a new employee item based on the text box.
   Employee employee = new Employee();
   employee.Name = txtName.Text;
   employee.Address = txtAddress.Text;
   employee.Age = Convert.ToInt16(txtage.Text);

// Add a employee item to the observable collection.
Employees.Add(employee);

// Add a employee item to the local database.
employeeDB.employees.InsertOnSubmit(employee);

employeeDB.SubmitChanges();
}

Step 12: Add retrieve event handler in MainPage.xaml.cs which will show the number of record saved in the local database.

private void Retrieve_Click(object sender, EventArgs args)
{
var employeesInDB = from Employee emp in employeeDB.employees
select emp;

// Execute the query and place the results into a collection.
Employees = new ObservableCollection<Employee>(employeesInDB);

MessageBox.Show(Employees.Count.ToString());
}

Step 13: Add delete event handler in MainPage.xaml.cs which will delete empId specified. In below case I have hardcoded the EmpId = 1. The employee id 1 will be deleted.

private void Delete_Click(object sender, EventArgs args)
{
   //// Remove employee item from the observable collection.
   var employeeFind = from Employee emp in employeeDB.employees
                        where emp.EmpId == 1 select emp;

   Employee employeeDelete = employeeFind.FirstOrDefault();
   Employees.Remove(employeeDelete);
   employeeDB.employees.DeleteOnSubmit(employeeDelete);

// Save changes to the database.
employeeDB.SubmitChanges();
}

Step 14: Now run the application, enter name, address and age and save multiple times using Save option in application bar.

Now click on retrieve button of application bar you will notice number of records saved will be displayed.

Now click on delete button of application the record with empid 1 will be deleted.

Now click retrieve button of application bar again and you will see number of record reduced by 1.

Getting SQL CE DB From Isolated Storage

Step 1: Go to C:\Program Files\Microsoft SDKs\Windows Phone\v7.1\Tools\IsolatedStorageExplorerTool you will get ISETool.exe

Step 2: Open WMAppManifest.xml, you will get productid.

Step 3: Run the application in non debug mode and keep emulator running. Open command prompt and change the directory to C:\Program Files\Microsoft SDKs\Windows Phone\v7.1\Tools\IsolatedStorageExplorerTool and run this command.

ISETool.exe ts xd d519d3f7-7f1f-4a70-95db-4385980aafc0 "C:\Downloads\IS"

I have used C:\Downloads\IS directory you can specify your own directory.

Windows Phone - SQL CE local DB

If application is running on Debug mode you will get download error like shown below.

Windows Phone - SQL CE Error

Step 4: Now navigate to the folder you specified in ISETool.exe command and you will see Employee.sdf.

Windows Phone ISETool

Step 5: Now go back to applicaion in Visual Studio. Go to View menu and select Server Explorer.

Step 6: Right click on DataConnection and select Add Connection.

Step 7: Add Employee.sdf in the Database section.

Step 8: Now click ok.

Step 9: Expand Employee.sdf, right click on Employee and click on Show Data Table.

Windows Phone - SQL CE Add Connection

The data in the table will be displayed like below.

Windows Phone - SQL CE Show Data

This ends the article of creating local database (SQL CE) in windows phone.

Like us if you find this post useful. Thanks!

Title:	Hello Sir, SQL Commands??
Name:	Vignesh PT
Date:	4/5/2012 1:13:49 AM
Comments:	Is it possible to execute SQL commands to retrieve specific records using this method?

Title:	Re: Hello Sir, SQL Commands??
Name:	Sumit
Date:	4/6/2012 11:00:43 AM
Comments:	No, SQL command can not be executed. You can use LINQ to retrieve the specific records. I have used LINQ in Step 13.

Title:	Error
Name:	Chinmay
Date:	12/12/2012 8:03:23 PM
Comments:	Hello Sir, I followed all the above steps. But i am not getting non debug mode step... How to run the application in non debug mode? please tell me asap. I want it urgently...

WCF service to return GenericList and consumed by client application as GenericList