Introduction



Home Screen :

Name of Experiment : Student Database

Exp No : DB1

Background : Student should have basic knowledge of C#.

Summary : DBMS is a necessary requirement for any Mobile Application. We need to store and retrieve certain information at any point of time. Though the cloud services are available in the eco-system, we even require to store data locally.
SQL Server CE (for Mobile) is embedded with the mango version Windows Phone. Hence we don’t need to look for explicit database server to manage the user´s data.

Learning Objective : To learn the steps for database handling in windows phone 7 (mango version) locally through this experiment. Each steps are clearly mentioned (i.e. CRUD) and explained properly in order to proper understanding of the scenario.

Target

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

Procedure

SourceCode

Source Code : Comments :
MainPage.xaml
<phone:PhoneApplicationPage
x:Class="StudentDB.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
FontFamily="{StaticResource PhoneFontFamilyNormal}"
FontSize="{StaticResource PhoneFontSizeNormal}"
Foreground="{StaticResource PhoneForegroundBrush}"
SupportedOrientations="Portrait" Orientation="Portrait"
shell:SystemTray.IsVisible="True">

<!--LayoutRoot is the root grid where all page content is placed-->
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="173"/>
<RowDefinition Height="595*"/>
</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="ExperiementTitle" Text="Exp No:DB1" TextAlignment="Right" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="ApplicationTitle" Text="Local DBMS" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="Student DB" 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">
<StackPanel Orientation="Horizontal">
<TextBlock Text="Student ID" Width="100" Margin="12" Height="75"/> <TextBox x:Name="txtID" Width="335" Height="75" /> </StackPanel> <StackPanel Orientation="Horizontal"> <TextBlock Text="Student Name" Width="130" Margin="0" Height="75"/>
<TextBox x:Name="txtName" Width="330" Height="75"/>
</StackPanel>
<StackPanel Orientation="Horizontal">
<TextBlock Text="Student Age" Width="130" Height="75"/>
<TextBox x:Name="txtAge" Width="330" Height="75"/>
</StackPanel>
<Button x:Name="bCreateDB" Content="Create Database" Width="400" Height="70" Click="bCreateDB_Click"/>
<Button x:Name="bAdd" Content="Add Student" Width="400" Height="70" Click="bAdd_Click"/>
<Button x:Name="bDelete" Content="Delete Student" Width="400" Height="70" Click="bDelete_Click"/>
<Button x:Name="bRetrieve" Content="Fetech all Students" Width="400" Height="70" Click="bRetrieve_Click"/>
<Button x:Name="bDeleteDB" Content="Delete Database" Width="400" Height="70" Click="bDeleteDB_Click"/>
</StackPanel>
</Grid>
</Grid>
</phone:PhoneApplicationPage>
























 Exp No DB1(Experiment title)


 Application Title(Local DBMS)


 Controls Elements


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>










 Country Database (Application Title)
 city (PageTitle)















 CountryList picker
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="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> </Grid> </Grid>
 
MainPage.xaml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using Microsoft.Phone.Controls;
using System.Text;

namespace StudentDB
{
public partial class MainPage : PhoneApplicationPage
{
private const string connectionString = @"isostore:/StudentDB.sdf";
// Constructor
public MainPage()
{
InitializeComponent();

}
private void bCreateDB_Click(object sender, RoutedEventArgs e)
{
// for creation of database
using (StudentDataContext Studb = new StudentDataContext(connectionString)) {
if (Studb.DatabaseExists() == false)
{
Studb.CreateDatabase();
MessageBox.Show("Student Database Created Successfully!!");
}
else {
MessageBox.Show("Student Database already exists!!");
}
}
}

private void bAdd_Click(object sender, RoutedEventArgs e)
{
// for inserting the values in the database
using (StudentDataContext Studb = new StudentDataContext(connectionString)) {
Student newStudent = new Student
{
studentID = Convert.ToInt32(txtID.Text),
studentAge = Convert.ToInt16(txtAge.Text),
studentName = txtName.Text.ToString()
};
Studb.Students.InsertOnSubmit(newStudent);
Studb.SubmitChanges(); MessageBox.Show("Student Added Successfully!!"); }
}

private void bDelete_Click(object sender, RoutedEventArgs e)
{// for deleting the values in the database
using (StudentDataContext Studb = new StudentDataContext(connectionString)) {
IQueryable StuQuery = from Stu in Studb.Students where Stu.studentName == txtName.Text select Stu;
Student StuRemove = StuQuery.FirstOrDefault();
Studb.Students.DeleteOnSubmit(StuRemove);
Studb.SubmitChanges();
MessageBox.Show("Student removed successfully!!"); }
}

private void bRetrieve_Click(object sender, RoutedEventArgs e)
{
// for feteching all values from the database
IList StudentList = this.GetStudentList();
StringBuilder str = new StringBuilder();
str.AppendLine("Student Details");
foreach (Student stu in StudentList) {
str.AppendLine("Name-" + stu.studentName + " " + "Age-" + stu.studentAge);

}
MessageBox.Show(str.ToString());

}
private void bDeleteDB_Click(object sender, RoutedEventArgs e)
{//delete database
using (StudentDataContext Studb = new StudentDataContext(connectionString)) {
if (Studb.DatabaseExists()) {
Studb.DeleteDatabase();
MessageBox.Show("Student Database Deleted Successfully!! ");
}
}

}
public IList GetStudentList() {
//Feteching data from local database
IList StudentList = null;
using (StudentDataContext Studb = new StudentDataContext(connectionString)) {
IQueryable StuQuery = from Stu in Studb.Students select Stu;
StudentList = StuQuery.ToList();

}
return StudentList;
}
}
}




 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
Student.cs

using System.Data.Linq.Mapping;

namespace StudentDB
{
//Schema for Database
[Table]
public class Student
{
[Column(IsPrimaryKey=true,IsDbGenerated=true)]
public int studentID
{get;
set;}
[Column(CanBeNull = false)]
public int studentAge { get; set; }
[Column(CanBeNull = false)]
public string studentName
{
get;
set;
}
}
}




 Define the Schema for the Database.





StudentDataContext.cs

using System.Data.Linq;

namespace StudentDB
{
public class StudentDataContext: DataContext
{ //View Model for MVVM
public StudentDataContext(string connectionString) : base(connectionString) {
}
public Table<Student> Students {
get { return this.GetTable(); }
}
}
}



 Define the Model View for the MVVM architecture.



Snapshots








Observation

Observation It is observed by the programmer that we can easily handle the databases using inbuilt SQL server compact edition for Mobiles. The local database is stored at Isolated Storage of the phone. User cannot see the database file by normal procedure.

*Note- Use ISETool.exe tool to copy the database file at your local machine through certain procedure explained at http://msdn.microsoft.com/en-us/library/windowsphone/develop/hh286408(v=vs.105).aspx.

Video Link

For Windows Phone

Download

Right Click Here And Select Save Target For Entire Download :

Student database