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

• Step1. Repeat steps [1-4] as in Experiment no MC1.[Refer Hello World]
  
  
• Step2. Add a reference for System.Data.Linq by making right click on the project and add this library to the project.
  
  
• Step3. Now, add a new class and named it Student.cs by making right click on the project.
  
  
• Step4.Open Student.cs class and add using System.Data.Linq.Mapping.
  
  
• Step5. Define the Table and its schema. [Refer Student.cs from Source Code Section]
  
  
• Step6. Now again add one more class and named it as StudentDataContext.cs
  
  
• Step7. Open StudentDataContext.cs and add using System.Data.Linq.
  
  
• Step8. Here just define the ModelView of the MVVM architecture.[Refer StudentDataContext.cs from Source Code Section]
  
  
• Step9. Now go back to the MainPage.xaml.cs and add using System.Data.Linq, using System, using System.Collection.Generic to the class.
  
  
• Step10. Define the connection string that is called at each time in order to communicate with the database as @ "isostore:/StudentDB.sdf". [It is a standard way to define the connection string, you can change the sdf file name as your convenience (all remains the same)]
  
  
• Step11. Now for each button handler define the action performed on OnClick event.[Refer MainPage.xaml.cs from Source code]
  
  
• Step12. Save all changes made to the project, by clicking on Ctrl + S.
  
  
• Step13. Press F5, in order to run the experiment on the Windows Phone Emulator.
  
  *Note- All SQL queries are made through Linq to SQL queries.

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