Introduction



Home Screen :

Name of Experiment : Country Database

Exp No : DB2

Background : Student should have basic knowledge of C#.

Summary : Database Management is one of the key factors in any Mobile application development framework. We need to understand each aspect of database at all levels i.e. Physical level, Logical level and View level. In order to do so Windows Phone has MVVM architecture.

Learning Objective : To learn the MVVM architecture in Database using this experiment.

Target

Target Platform : This experiment is tested on Windows Phone Emulator and Nokia Lumia 800.

Procedure

SourceCode

Source Code : Comments :
MainPage.xaml
<phone:PhoneApplicationPage.Resources>
<DataTemplate x:Key="ToDoListBoxItemTemplate">

<Grid HorizontalAlignment="Stretch" Width="420">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="100" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="Auto" />
<ColumnDefinition Width="100" />
</Grid.ColumnDefinitions>
<CheckBox IsChecked="False" Grid.Column="0" VerticalAlignment="Top" Background="DarkOliveGreen"/>

<TextBlock Text="{Binding CityName}" FontSize="{StaticResource PhoneFontSizeLarge}" Grid.Column="1" Grid.ColumnSpan="2" VerticalAlignment="Top" Margin="-36, 12, 0, 0"/>

<Button Grid.Column="3" x:Name="deleteTaskButton" BorderThickness="0" Margin="0, -18, 0, 0" Click="deleteTaskButton_Click">
<Image Source="/Images/appbar.delete.rest.png" Height="75" Width="75"/>

</Button>
</Grid>
</DataTemplate>
</phone:PhoneApplicationPage.Resources>
<!--LayoutRoot is the root grid where all page content is placed-->
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<!--TitlePanel contains the name of the application and page title-->
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ExperimentTitle" Text="Exp No:DB2" TextAlignment="Right" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="ApplicationTitle" Text="Country Database" Style="{StaticResource PhoneTextNormalStyle}"/>
<!--<TextBlock x:Name="PageTitle" Text="page name" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>-->
</StackPanel>
<!--ContentPanel - place additional content here-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<controls:Pivot Margin="0, -36, 0, 0">

<controls:PivotItem Header="all">
<ListBox x:Name="allToDoItemsListBox" ItemsSource="{Binding AllCities}" Margin="12, 0, 12, 0" Width="440" ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
</controls:PivotItem>

<controls:PivotItem Header="india">
<ListBox x:Name="homeToDoItemsListBox" ItemsSource="{Binding IndianCity}" Margin="12, 0, 12, 0" Width="440"
ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
</controls:PivotItem>
<controls:PivotItem Header="uk">
<ListBox
x:Name="workToDoItemsListBox"
ItemsSource="{Binding UKCity}"
Margin="12, 0, 12, 0" Width="440"
ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
</controls:PivotItem>

<controls:PivotItem Header="usa">
<ListBox x:Name="hobbiesToDoItemsListBox"
ItemsSource="{Binding USACity}"
Margin="12, 0, 12, 0" Width="440"
ItemTemplate="{StaticResource ToDoListBoxItemTemplate}" />
</controls:PivotItem>

</controls:Pivot>
</Grid>
</Grid>

<!--Sample code showing usage of ApplicationBar-->
<phone:PhoneApplicationPage.ApplicationBar>
<shell:ApplicationBar IsVisible="True" IsMenuEnabled="False">
<shell:ApplicationBarIconButton IconUri="/Images/appbar.new.rest.png" Text="new" x:Name="newTaskAppBarButton" Click="newTaskAppBarButton_Click"/>
<shell:ApplicationBarIconButton IconUri="/Images/appbar.feature.search.rest.png" Text="search" x:Name="newSearchAppBarButton" Click="newSearchAppBarButton_Click"/>

</shell:ApplicationBar>
</phone:PhoneApplicationPage.ApplicationBar>

</phone:PhoneApplicationPage>







































 Exp No DB2(Experiment title)


 Country Database(Application Title)


 Pivot Page Display having different headers.








 india (Pivot Header)




 uk(Pivot Header)







 usa (Pivot Header)













 new (ApplicationBarButton)

 search(ApplicationBarButton)
NewTaskPage.xaml

<!--LayoutRoot is the root grid where all page content is placed-->
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<!--TitlePanel contains the name of the application and page title-->
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="Country Database" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="city" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!--ContentPanel - place additional content here-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="auto"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="100"/>
<RowDefinition Height="300"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<TextBlock Text="City Name:" Width="100" Margin="10,30,0,10" Grid.Column="0" Grid.Row="0"/>
<TextBox x:Name="newTaskNameTextBox" Height="100" Width="350" Grid.Column="1" Grid.Row="0" TextWrapping="Wrap"/>
<TextBlock Text="Country" Width="100" Margin="10,30,0,10" Grid.Column="0" Grid.Row="1"/>
<toolkit:ListPicker x:Name="categoriesListPicker" ItemsSource="{Binding CountryList}" DisplayMemberPath="Name" Grid.Row="1" Grid.Column="1">
</toolkit:ListPicker>
</Grid>
</Grid>
<!--Sample code showing usage of ApplicationBar-->
<phone:PhoneApplicationPage.ApplicationBar>
<shell:ApplicationBar IsVisible="True" IsMenuEnabled="False">
<shell:ApplicationBarIconButton IconUri="/Images/appbar.check.rest.png" Text="ok" x:Name="appBarOkButton" Click="appBarOkButton_Click"/>
<shell:ApplicationBarIconButton IconUri="/Images/appbar.cancel.rest.png" Text="cancel" x:Name="appBarCancelButton" Click="appBarCancelButton_Click"/>
</shell:ApplicationBar>
</phone:PhoneApplicationPage.ApplicationBar>
</phone:PhoneApplicationPage>




 Add namespaces






 Define Connection string (for Database)


 Create Database if it is not existing.




 Get the values from UI controls












 Insert values into database

 Query to delete the record from database









 Delete the complete database.













 Query to get the list of records
ItemSearch.xaml

<!--LayoutRoot is the root grid where all page content is placed-->
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>

<!--TitlePanel contains the name of the application and page title-->
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="Country Database" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="search city" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>

<!--ContentPanel - place additional content here-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<StackPanel Orientation="Vertical">
<TextBlock Text="Search" Style="{StaticResource PhoneTextGroupHeaderStyle}"/>
<TextBox x:Name="itemToSearch" Height="100" TextWrapping="Wrap" />
<Button x:Name="SearchButton" Content="Go!" Click="SearchButton_Click" Height="75" Width="120" BorderBrush="Green" BorderThickness="5" Foreground="Wheat"/>
<ListBox x:Name="responseList" ScrollViewer.HorizontalScrollBarVisibility="Auto" ScrollViewer.VerticalScrollBarVisibility="Auto">
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel> <TextBlock Text="{Binding Result}" Width="350" Height="100"/>
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
</StackPanel>hello
</Grid>
</Grid>










 Country Database
(ApplicationTitle)

 search city (PageTitle)
MainPage.xaml.cs

using System;
using System.Windows;
using System.Windows.Controls;
using Microsoft.Phone.Controls;
using CountryDatabase.Model;

namespace CountryDatabase
{
public partial class MainPage : PhoneApplicationPage
{
// Constructor
public MainPage()
{
InitializeComponent();
this.DataContext = App.ViewModel;
}
private void newTaskAppBarButton_Click(object sender, EventArgs e)
{
NavigationService.Navigate(new Uri("/NewTaskPage.xaml", UriKind.Relative));
}

private void deleteTaskButton_Click(object sender, RoutedEventArgs e)
{
// Cast the parameter as a button.
var button = sender as Button;

if (button != null)
{
// Get a handle for the to-do item bound to the button.
City deleteCity = button.DataContext as City;

App.ViewModel.DeleteCity(deleteCity);
}

// Put the focus back to the main page.
this.Focus();
}

protected override void OnNavigatedFrom(System.Windows.Navigation.NavigationEventArgs e)
{
// Save changes to the database.
App.ViewModel.SaveChangesTODB();
}

private void newSearchAppBarButton_Click(object sender, EventArgs e)
{
NavigationService.Navigate(new Uri("/ItemSearch.xaml", UriKind.Relative));
}

}
}




 Define the Schema for the Database.





NewTaskPage.xaml.cs

using System; using Microsoft.Phone.Controls;
using CountryDatabase.Model;

namespace CountryDatabase
{
public partial class NewTaskPage : PhoneApplicationPage
{
public NewTaskPage()
{
InitializeComponent();
this.DataContext = App.ViewModel;
}
private void appBarOkButton_Click(object sender, EventArgs e)
{
// Confirm there is some text in the text box.
if (newTaskNameTextBox.Text.Length > 0)
{
// Create a new to-do item.
City newToDoItem = new City
{
CityName = newTaskNameTextBox.Text,
country = (Country)categoriesListPicker.SelectedItem
};

// Add the item to the ViewModel.
App.ViewModel.AddCity(newToDoItem);
// Return to the main page.
if (NavigationService.CanGoBack)
{
NavigationService.GoBack();
}
}
}

private void appBarCancelButton_Click(object sender, EventArgs e)
{
// Return to the main page.
if (NavigationService.CanGoBack)
{
NavigationService.GoBack();
}
}
}
}
 
ViewModel/CountryViewModel.cs

using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.ComponentModel;
using CountryDatabase.Model;

namespace CountryDatabase.ViewModel {
public class CountryViewModel : INotifyPropertyChanged {
private CountryDataContext CountryDB;
public CountryViewModel(string toDBConnectionString) {
CountryDB = new CountryDataContext(toDBConnectionString);
}
public void SaveChangesTODB() {
CountryDB.SubmitChanges();
}
#region INotifyPropertyChanges Members
public event PropertyChangedEventHandler PropertyChanged;
private void NotifyPropertyChanged(string propertyName) {
if (PropertyChanged != null) {
PropertyChanged(this,new PropertyChangedEventArgs(propertyName));
}
}
#endregion
private ObservableCollection _allCities;
public ObservableCollection AllCities {
get { return _allCities; }
set { _allCities = value;
NotifyPropertyChanged("All Cities");
}
}
private ObservableCollection _indianCity;
public ObservableCollection IndianCity {
get { return _indianCity; }
set { _indianCity = value;
NotifyPropertyChanged("Indian City");
}
}
private ObservableCollection _ukCity;
public ObservableCollection UKCity {
get { return _ukCity; }
set { _ukCity = value;
NotifyPropertyChanged("UK City");
}
}
private ObservableCollection _usaCity;
public ObservableCollection USACity {
get { return _usaCity; }
set { _usaCity = value;
NotifyPropertyChanged("USA City");
}
}
private List _countryList;
public List CountryList {
get { return _countryList; }
set { _countryList = value;
NotifyPropertyChanged("Country List");
}

}
public void LoadCollectionFromDatabase(){
var citiesInDB = from City c in CountryDB.Cities select c;
AllCities = new ObservableCollection(citiesInDB);

var countriesInDB = from Country ct in CountryDB.Countries select ct;
foreach(Country country in countriesInDB){
switch(country.Name){
case "India": IndianCity = new ObservableCollection(country.city);
break;
case "UK": UKCity = new ObservableCollection(country.city);
break;
case "USA": USACity = new ObservableCollection(country.city);
break;
default:
break;
}
}
CountryList = CountryDB.Countries.ToList();
}
public void AddCity(City newCity) {

CountryDB.Cities.InsertOnSubmit(newCity);

CountryDB.SubmitChanges();
AllCities.Add(newCity);
switch (newCity.country.Name) {
case "India": IndianCity.Add(newCity);
break;
case "UK": UKCity.Add(newCity);
break;
case "USA": USACity.Add(newCity);
break;
default:
break;

}
}
public bool SearchCity(string qCity) {

var query = from item in CountryDB.Cities where item.CityName.Contains(qCity) select item.CityName;
if (query.FirstOrDefault()!= null) {
return true;
}
return false;
}
public void DeleteCity(City delCity) {
AllCities.Remove(delCity);
CountryDB.Cities.DeleteOnSubmit(delCity);
switch (delCity.country.Name) {
case "India": IndianCity.Remove(delCity);
break;
case "UK": UKCity.Remove(delCity);
break;
case "USA": USACity.Remove(delCity);
break;
default:
break;
}
CountryDB.SubmitChanges();
}
}
}



 Define the Model View for the MVVM architecture.



Model/CountryDataContext.cs

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

namespace CountryDatabase.Model {
public class CountryDataContext : DataContext {
public CountryDataContext(string connectionString) : base(connectionString) { }
public Table Cities;
public Table Countries;
}

[Table]
public class Country: INotifyPropertyChanged {

private string name;
[Column]
public string Name {
get { return name;}
set
{
if (name != value)
{
name = value;
NotifyPropertyChanged("Name");
}
}
}
private int id;
[Column(IsPrimaryKey = true, CanBeNull = false, IsDbGenerated = true, DbType = "INT NOT NULL IDENTITY", AutoSync = AutoSync.OnInsert)]
public int ID {
get { return id; }
set
{
if (id != value)
{
id = value;
NotifyPropertyChanged("ID");
}

}
}
private EntitySet _city;
[Association(Storage = "_city", OtherKey = "_countryId", ThisKey = "ID")]
public EntitySet city {
get { return this._city; }
set
{
this._city.Assign(value);
}
}
public Country(){
_city = new EntitySet(new Action(this.attach_city),
new Action(this.dettach_city));
}
private void attach_city(City cities) {
NotifyPropertyChanged("City");
cities.country = this;
}
private void dettach_city(City cities) {
NotifyPropertyChanged("City");
cities.country = null;
}
#region INotifyPropertyChanged Members

public event PropertyChangedEventHandler PropertyChanged;

private void NotifyPropertyChanged(string propertyName)

{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
#endregion

#region INotifyPropertyChanging Members
public event PropertyChangingEventHandler PropertyChanging;
private void NotifyPropertyChaning(string propertyName) {
if (PropertyChanging != null) {
PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
}
}
#endregion
}
[Table]
public class City: INotifyPropertyChanged,INotifyPropertyChanging{

private int _cityId;

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

[Column]
internal int _countryId;
private EntityRef _country;
[Association(Storage = "_country", ThisKey = "_countryId", OtherKey = "ID", IsForeignKey = true)]
public Country country {
get { return _country.Entity; }
set { NotifyPropertyChanged("Country");
_country.Entity = value;
if (value != null) {
_countryId = value.ID;
}
}
}

#region INotifyPropertyChanged Members

public event PropertyChangedEventHandler PropertyChanged;

private void NotifyPropertyChanged(string propertyName)

{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
#endregion
#region INotifyPropertyChanging Members
public event PropertyChangingEventHandler PropertyChanging;
private void NotifyPropertyChanging(string propertyName)
{
if (PropertyChanging != null)
{
PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
}
}
#endregion
}
}

Snapshots













Observation

Observation It is observed by developer, that MVVM architecture is very flexible to use in Windows Phone during database management. It enhances the response for querying to the database in many folds.

Video Link

For Windows Phone

Download

Right Click Here And Select Save Target For Entire Download :

Country Database