Mango- Baby Steps: Creating an application with a local Database

Mango- Baby Steps are a series of posts to learn about Windows Phone 7.1. This is the third post and i hope post one and two were useful for you 🙂

I was working lately on a new app , ToDo list ( i know , nothing innovative here -_- ) but then I dropped the work till Mango is out,for the very good reason that with Mango I can have a local Database to store and manage my application’s structured data ! Clap ! Clap!

One of the new many features coming with the Windows Phone Codenamed Mango is the possibility of having local databases- Microsoft SQL Server Compact edition databases(SQL CE)- for your applications.

The database resides in the application’s isolated storageas a file.

In case you are not familiar with the isolated storage,I highly recommend you to read the ” All about WP7 Isolated Storage” articles on the ” WindowsPhone Geek” website.

In order to store and retrieve data in the local database, your Windows Phone application has to use LINQ to SQL.

” LINQ to SQL provides an object-oriented approach to working with data and comprises an object model and a runtime.The LINQ to SQL object model is made up primarily by the System.Data.Linq.DataContext object, which acts as a proxy for the local database. The LINQ to SQL runtime is responsible for bridging the world of objects (the DataContext object) with the world of data (the local database). This relationship is summarized in the following image.” MSDN

One more thing to explain is the Data Context . Data Context is the object model that represents the database, each object is a set of entities using the ” plain old CLR object ” (POCO) with attributes.

• Building the application

My ToDo application looks like this :

As you can see, my main page is a panorama containing :

* Hot list item : where you can find the tasks to be done soon

* Lists: tasks will be sorted , tasks related to work, family, etc…

The other page is a simple Windows Phone page that allows you to add a new task.

In this post we will be working with this user interface.By now you should know how to create a new Windows Phone 7.1 application and how to add pages, so all I have to do is to show you the XAML code :

Code Snippet

1. <Grid x:Name=”ContentPanel” Grid.Row=”1″ Margin=”12,0″>
2.             <TextBlock Height=”50″ HorizontalAlignment=”Left” Margin=”10,25,0,0″ Name=”textBlock1″ Text=”Subject:” VerticalAlignment=”Top” Width=”117″ />
3.             <TextBox Height=”66″ HorizontalAlignment=”Left” Margin=”105,9,0,0″ Name=”textBox1″ Text=”” VerticalAlignment=”Top” Width=”342″ />
4.             <TextBlock Height=”43″ HorizontalAlignment=”Left” Margin=”10,97,0,0″ Name=”textBlock2″ Text=”Category:” VerticalAlignment=”Top” Width=”103″ />
5.             <Canvas HorizontalAlignment=”Left” VerticalAlignment=”Top” Width=”345″ Height=”66″ Margin=”111,80,0,0″>
6.                 <toolkit:ContextMenuService.ContextMenu>
7.                     <toolkit:ContextMenu>
8.                         <toolkit:MenuItem Header=”pin to start menu” Click=”MenuItem_Click” Tag=”START_MENU” />
9.                         <toolkit:MenuItem Header=”delete” Click=”MenuItem_Click” Tag=”DELETE” />
10.                         <toolkit:MenuItem Header=”share” Click=”MenuItem_Click” Tag=”SHARE” />
11.                     </toolkit:ContextMenu>
12.                 </toolkit:ContextMenuService.ContextMenu>
13.                 <Rectangle Fill=”#FFF4F4F5″ Height=”47″ Stroke=”Black” Width=”319″ Canvas.Left=”6″ Canvas.Top=”6″ />
14.                 <TextBlock Name=”cat” TextWrapping=”Wrap” Text=” “ Foreground=”Black” Canvas.Left=”71″ Canvas.Top=”27″/>
15.             </Canvas>
16.             <TextBlock Height=”50″ HorizontalAlignment=”Left” Margin=”10,179,0,0″ Name=”textBlock3″ Text=”Priority:” VerticalAlignment=”Top” Width=”95″ />
17.             <RadioButton Content=”Low” Height=”76″ HorizontalAlignment=”Left” Margin=”140,156,0,0″ Name=”radioButton1″ VerticalAlignment=”Top” Width=”134″ />
18.             <RadioButton Content=”High” Height=”73″ HorizontalAlignment=”Right” Margin=”0,156,33,0″ Name=”radioButton2″ VerticalAlignment=”Top” Width=”126″ />
19.             <toolkit:DatePicker Header=”Start date:” Margin=”0,234,0,270″ />
20.             <toolkit:DatePicker Header=”Due date:” Margin=”0,331,0,180″ />
21.             <CheckBox Content=”Completed” Height=”76″ HorizontalAlignment=”Left” Margin=”6,411,0,0″ Name=”checkBox1″ VerticalAlignment=”Top” Width=”190″ />
22.             <toolkit:DatePicker Header=”Date completed:” Margin=”2,493,9,21″ />
23.         </Grid>

Now that the user interface is ready, we will build the Data Context.

First thing to do is to add the reference System.Data.Linq to be able to work with LINQ to SQL.

Then add a new class, I’ll call it task .

Code Snippet

1. [Table]
2.     public class task : INotifyPropertyChanged, INotifyPropertyChanging
3.     {
4.         private string _subject;
5.         [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = “INT NOT NULL Identity”, CanBeNull = false, AutoSync = AutoSync.OnInsert)]
6.         public string Subject
7.         {
8.             get
9.             {
10.                 return _subject;
11.             }
12.             set
13.             {
14.                 if (_subject != value)
15.                 {
16.                     NotifyPropertyChanging(“Subject”);
17.                     _subject = value;
18.                     NotifyPropertyChanged(“Subject”);
19.                 }
20.             }
21.         }
23.         private string _category;
24.         public string Category
25.         {
26.             get
27.             {
28.                 return _category;
29.             }
30.             set
31.             {
32.                 if (_category != value)
33.                 {
34.                     NotifyPropertyChanging(“Category”);
35.                     _category = value;
36.                     NotifyPropertyChanged(“Category”);
37.                 }
38.             }
39.         }
41.         private bool _completed;
42.         [Column]
43.         public bool Completed
44.         {
45.             get
46.             {
47.                 return _completed;
48.             }
49.             set
50.             {
51.                 if (_completed != value)
52.                 {
53.                     NotifyPropertyChanging(“Completed”);
54.                     _completed = value;
55.                     NotifyPropertyChanged(“Completed”);
56.                 }
57.             }
58.         }
60.         private bool _priority;
61.         [Column]
62.         public bool Priority
63.         {
64.             get
65.             {
66.                 return _priority;
67.             }
68.             set
69.             {
70.                 if (_priority != value)
71.                 {
72.                     NotifyPropertyChanging(“Priority”);
73.                     _priority = value;
74.                     NotifyPropertyChanged(“Priority”);
75.                 }
76.             }
77.         }
79.         private DateTime _startDate;
80.         [Column]
81.         public DateTime StartDate
82.         {
83.             get
84.             {
85.                 return _startDate;
86.             }
87.             set
88.             {
89.                 if (_startDate != value)
90.                 {
91.                     NotifyPropertyChanging(“StartDate”);
92.                     _startDate = value;
93.                     NotifyPropertyChanged(“StartDate”);
94.                 }
95.             }
96.         }
98.         private DateTime _dueDate;
99.         [Column]
100.         public DateTime DuetDate
101.         {
102.             get
103.             {
104.                 return _dueDate;
105.             }
106.             set
107.             {
108.                 if (_dueDate != value)
109.                 {
110.                     NotifyPropertyChanging(“DuetDate”);
111.                     _dueDate = value;
112.                     NotifyPropertyChanged(“DuetDate”);
113.                 }
114.             }
115.         }
117.         private DateTime _dateCompleted;
118.         [Column]
119.         public DateTime DateCompleted
120.         {
121.             get
122.             {
123.                 return _dateCompleted;
124.             }
125.             set
126.             {
127.                 if (_dateCompleted != value)
128.                 {
129.                     NotifyPropertyChanging(“DateCompleted”);
130.                     _dateCompleted = value;
131.                     NotifyPropertyChanged(“DateCompleted”);
132.                 }
133.             }
134.         }
139.         public event PropertyChangedEventHandler PropertyChanged;
142.         private void NotifyPropertyChanged(string propertyName)
143.         {
144.             if (PropertyChanged != null)
145.             {
146.                 PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
147.             }
148.         }
150.         public event PropertyChangingEventHandler PropertyChanging;
153.         private void NotifyPropertyChanging(string propertyName)
154.         {
155.             if (PropertyChanging != null)
156.             {
157.                 PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
158.             }
159.         }
168.     }

Add another class called TaskDataContext that derives from DataContext:

Code Snippet

1. namespace task
2. {
3.     public class TaskDataContext: DataContext
4.     {
5.         public TaskDataContext(string connectionString): base(connectionString)    {    }
6.         public Table<task> tasks { get { return this.GetTable<task>(); } }
7.     }
8. }

Notice that we have to check if the database exists , if not we need to create it .

In the MainPage.xaml.cs add the following code:

Code Snippet

1. using (TaskDataContext context = new TaskDataContext(connectionString))
2.             {
3.                 if (!context.DatabaseExists())
4.                 {
5.                 context.CreateDatabase();
6.                 }
7.             }

To sum up, in order to use local database, we need to specify the data context then create the database if it doesn’t exist.

In the coming post , we will learn how to query the database. Stay Tuned