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.
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0"> <TextBlock Text="Name" Height="30" HorizontalAlignment="Left" Margin="10,15,0,0" Name="textName" VerticalAlignment="Top" FontSize="18" /> <TextBox InputScope="Default" Height="70" HorizontalAlignment="Left" Margin="100,0,0,0" Name="txtName" Text="" VerticalAlignment="Top" Width="350" />
<TextBlock Text="Address" Height="30" HorizontalAlignment="Left" Margin="10,100,0,0" Name="textAddress" VerticalAlignment="Top" FontSize="18" /> <TextBox InputScope="Default" Height="70" HorizontalAlignment="Left" Margin="100,85,0,0" Name="txtAddress" Text="" VerticalAlignment="Top" Width="350" />
<TextBlock Text="Age" Height="30" HorizontalAlignment="Left" Margin="10,185,0,0" Name="textAge" VerticalAlignment="Top" FontSize="18" /> <TextBox InputScope="Default" Height="70" HorizontalAlignment="Left" Margin="100,170,0,0" Name="txtage" Text="" VerticalAlignment="Top" Width="350" /> </Grid>
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.
If application is running on Debug mode you will get download error like shown below.

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

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.

The data in the table will be displayed like below.

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