Windows Phone Application Sample Database Example

A simple Databse example with CRUD operations(creation, retrieval,updation and deletion) in windows applications for the reference and to use accordingly :) feel free to use and have a good day with the code :)

1) the MainPage.xaml code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using Microsoft.Phone.Controls;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.ComponentModel;
using System.Collections.ObjectModel;


namespace SampleDatabaseExample
{
    public partial class MainPage : PhoneApplicationPage
    {
        private const string strConnectionString = @"isostore:/EmployeeDB.sdf";
        // Constructor
        public MainPage()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, RoutedEventArgs e)
        {
            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
            {
                if (Empdb.DatabaseExists() == false)
                {
                    Empdb.CreateDatabase();
                    MessageBox.Show("Employee Database Created Successfully!!!");
                }
                else
                {
                    MessageBox.Show("Employee Database already exists!!!");
                }
            }
        }
        private void button2_Click(object sender, RoutedEventArgs e)
        {
            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
            {
                Employee newEmployee = new Employee
                {
                    
                    EmployeeID = Convert.ToInt32(txtEmpid.Text.ToString()),
                    EmployeeAge = txtAge.Text.ToString(),
                    EmployeeName = txtName.Text.ToString()
                };
                Empdb.Employees.InsertOnSubmit(newEmployee);
                Empdb.SubmitChanges();
                MessageBox.Show("Employee Added Successfully!!!");
            }
        }
        public IList<Employee> GetEmployeeList()
        {
            IList<Employee> EmployeeList =
                null;
            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
            {
                IQueryable<Employee> EmpQuery =
                    from Emp in Empdb.Employees select Emp;
                EmployeeList = EmpQuery.ToList();
            } return EmployeeList;
        }
        private void button4_Click(object sender, RoutedEventArgs e)
        {
            IList<Employee> EmployeesList =
                this.GetEmployeeList();
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.AppendLine("Employee Details");
            foreach (Employee emp in EmployeesList)
            {
                strBuilder.AppendLine("Name - " + emp.EmployeeName + " Age - " + emp.EmployeeAge);
            }
            MessageBox.Show(strBuilder.ToString());
        }
        private void button3_Click(object sender, RoutedEventArgs e)
        {
            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
            {
                IQueryable<Employee> EmpQuery = from Emp in Empdb.Employees
                                                where Emp.EmployeeName ==
                                                    txtName.Text
                                                select Emp;
                Employee EmpRemove = EmpQuery.FirstOrDefault();
                Empdb.Employees.DeleteOnSubmit(EmpRemove);
                Empdb.SubmitChanges();
                MessageBox.Show("Employee Deleted Successfully!!!");
            }
        }
        private void button5_Click(object sender, RoutedEventArgs e)
        {
            using (EmployeeDataContext Empdb = new EmployeeDataContext(strConnectionString))
            {
                if (Empdb.DatabaseExists())
                {
                    Empdb.DeleteDatabase();
                    MessageBox.Show("Employee Database Deleted Successfully!!!");
                }
            }
        }
    }
}

2) The MainPage.xaml code for the desired layout:


<phone:PhoneApplicationPage 
    x:Class="SampleDatabaseExample.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="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="F5DEBUG WP7 TUTORIALS" Style="{StaticResource
PhoneTextNormalStyle}"/>
        <TextBlock x:Name="PageTitle" Text="Employee 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">
        <TextBox Height="72" HorizontalAlignment="Left" Margin="113,28,0,0" Name="txtName" Text=""
VerticalAlignment="Top" Width="324" />
        <TextBlock Height="30" HorizontalAlignment="Left" Margin="33,57,0,0" Name="textBlock1"
Text="Name" VerticalAlignment="Top" />
        <TextBox Height="72" HorizontalAlignment="Left" Margin="113,93,0,0" Name="txtAge" Text=""
VerticalAlignment="Top" Width="324" />
        <TextBlock Height="30" HorizontalAlignment="Left" Margin="33,122,0,0" Name="textBlock2"
Text="Age" VerticalAlignment="Top" />
        <TextBox Height="72" HorizontalAlignment="Left" Margin="113,159,0,0" Name="txtEmpid" Text=""
VerticalAlignment="Top" Width="324" />
        <TextBlock Height="30" HorizontalAlignment="Left" Margin="33,188,0,0" Name="textBlock3"
Text="Emp ID" VerticalAlignment="Top" />
        <Button Content="Create Database" Height="72" HorizontalAlignment="Left" Margin="33,255,0,0"
Name="button1" VerticalAlignment="Top" Width="404" Click="button1_Click" />
        <Button Content="Add an Employee" Height="72" HorizontalAlignment="Left" Margin="33,319,0,0"
Name="button2" VerticalAlignment="Top" Width="404" Click="button2_Click" />
        <Button Content="Delete an Employee" Height="72" HorizontalAlignment="Left"
Margin="33,384,0,0" Name="button3" VerticalAlignment="Top" Width="404" Click="button3_Click"
/>
        <Button Content="Fetch all Employees" Height="72" HorizontalAlignment="Left"
Margin="33,449,0,0" Name="button4" VerticalAlignment="Top" Width="404" Click="button4_Click"
/>
        <Button Content="Delete Database" Height="72" HorizontalAlignment="Left" Margin="33,514,0,0"
Name="button5" VerticalAlignment="Top" Width="404" Click="button5_Click" />
    </Grid>
    </Grid>



    <!--Sample code showing usage of ApplicationBar-->
    <!--<phone:PhoneApplicationPage.ApplicationBar>
        <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
            <shell:ApplicationBarIconButton IconUri="/Images/appbar_button1.png" Text="Button 1"/>
            <shell:ApplicationBarIconButton IconUri="/Images/appbar_button2.png" Text="Button 2"/>
            <shell:ApplicationBar.MenuItems>
                <shell:ApplicationBarMenuItem Text="MenuItem 1"/>
                <shell:ApplicationBarMenuItem Text="MenuItem 2"/>
            </shell:ApplicationBar.MenuItems>
        </shell:ApplicationBar>
    </phone:PhoneApplicationPage.ApplicationBar>-->

</phone:PhoneApplicationPage>

3) The EmployeeDataContext.cs code:


using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Data.Linq;






namespace SampleDatabaseExample
{
    public class EmployeeDataContext : DataContext
    {
        public EmployeeDataContext(string connectionString)
            : base(connectionString)
        { }
        public Table<Employee> Employees
        {
            get
            {
                return this.GetTable<Employee>();
            }
        }
    }
}

4) The Employee.cs table structure

using System;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Ink;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using System.Data.Linq.Mapping;

using System.Data.Linq;

namespace SampleDatabaseExample

{

    [Table]

    public class Employee : ObstractDb

    {

        [

Column(IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false, AutoSync =

AutoSync.OnInsert)]

        public int EmployeeID

        {

            get;

            set;

        }

        [Column(CanBeNull = false)]

        public string EmployeeName

        {

            get;

            set;

        }

        [Column(CanBeNull = false)]

        public string EmployeeAge

        {

            get;

            set;

        }

    }

}