Getting Started With Local Database Operations in Windows Phone 7

Introduction

In this article we are going to explore how to use a local database with Windows Phone 7 and we will further discuss the database operations such as insert, select or delete the data to the local database from the Windows Phone 7. In this whole task we will use the property concept and LINQ concept of C# which will very helpful to us to accomplish such task to operate these operation with the local database from Windows Phone 7. For using the LINQ first of all you have to add the namespace named as System.data.linq to your reference folder which will be selected from the .NET library you will see also that from where you have to add it. In this article we will take you to the Class file separately for which we will create our own database by using LINQ. Now the operation shows you how to insert the data into the local database and how to select the data and further you can delete the data from the local database. Whatever the data we want to insert into the local database it will be inserted through the TextBox. Now it's time to getting started to accomplish such type of task you have to follow the steps given below:

Step 1 : In this step first of all we have to open a Windows Phone application; let us see how you will open it.

• Go to Visual Studio 2010
• File->New->Project
• Select the template named as silverlight for Windows Phone
• Select the Windows Phone application
• Give it a name as you want.   

Step 2 : In this step you will see how to use the LINQ we have to add the reference of the namespace; let us see from where you have to add that references.

Go to the Solution Explorer and right-click on the reference folder.

You just see the namespace reference named System.data.linq; select it and click oK.

Select to add it then you see that the library has been added to the reference folder as you see in the figure below:

Step 3 : In this step you have to add the two class files named MyCity.cs and CityDataContext.cs; let us see from where you have to add them.

Step 4 : In this step we will write code for the MyCity.cs file in which we are going to make a table named MyCity; inside we are using the property and it's all done using LINQ. Let us see the code, which is given below.

MyCity.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.Mapping;
using System.Data.Linq;
namespace mylocaldatabse
{
    [Table]
    public class MyCity
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int C_ID
        {
            get;
            set;
        }
        [Column(CanBeNull = false)]
        public string C_Name
        {
            get;
            set;
        }
    }
}

Step 5 : In this step you have to write the code for the second class file named CityDataContext.cs and the code of that file is given below.

CityDataContext.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 mylocaldatabse
{
    public class CityDataContext : DataContext
    {
        public CityDataContext(string connectionString) : base(connectionString)
        {
        }
        public Table<MyCity> Cities
        {
            get
            {
                return this.GetTable<MyCity>();
            }
        }
    }
}

Step 6 : In this step you just see the code for the MainPage.xaml.cs file which is given below.

MainPage.xaml.cs 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;
namespace mylocaldatabse
{
    public partial class MainPage : PhoneApplicationPage
    {
        // short connection string format
        private const string Con_String = @"isostore:/amit.sdf";
        // Constructor
        public MainPage()
        {
            InitializeComponent();
            textBox1.Text = "";
            using (CityDataContext context = new CityDataContext(Con_String))
            {
                if (!context.DatabaseExists())
                {
                    context.CreateDatabase();
                }
            }
        }
        public IList<MyCity> GetCities()
        {
            IList<MyCity> cityList = null;
            using (CityDataContext context = new CityDataContext(Con_String))
            {
                IQueryable<MyCity> query = from c in context.Cities select c;
                cityList = query.ToList();
            }
            return cityList;
        }
        private void AddMyCity()
        {
            using (CityDataContext context = new CityDataContext(Con_String))
            {
                MyCity city = new MyCity();
                city.C_Name = textBox1.Text;
                context.Cities.InsertOnSubmit(city);
                context.SubmitChanges();
            }
        }
        private void DeleteMyCity()
        {
            using (CityDataContext context = new CityDataContext(Con_String))
            {
                IQueryable<MyCity> cityQuery = from c in context.Cities where c.C_Name == textBox1.Text select c;
                MyCity cityToDelete = cityQuery.FirstOrDefault();
                context.Cities.DeleteOnSubmit(cityToDelete);
                context.SubmitChanges();
            }
        }
       private void button1_Click(object sender, RoutedEventArgs e)
        {
 
            this.AddMyCity();
            MessageBox.Show("City has been added");
            textBox1.Text = "";
        }
        private void button2_Click(object sender, RoutedEventArgs e)
        {
            IList<MyCity> cities = this.GetCities();
            StringBuilder messageBuilder = new StringBuilder();
            messageBuilder.AppendLine("Cities are:");
            foreach (MyCity city in cities)
            {
                messageBuilder.AppendLine(city.C_Name);
            }
            MessageBox.Show(messageBuilder.ToString());
        }
        private void button3_Click(object sender, RoutedEventArgs e)
        {
            this.DeleteMyCity();
            MessageBox.Show("City has been Deleted ");
        }
    }
}

Step 7 : In this step you just see the code for the MainPage.xaml file which is given below.

MainPage.xaml Code:

<phone:PhoneApplicationPage
    x:Class="mylocaldatabse.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">
        <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="MY APPLICATION" Style="{StaticResource PhoneTextNormalStyle}" FontSize="24"
                       FontFamily="Comic Sans MS" Foreground="#FFFE70FF"/>
            <TextBlock x:Name="PageTitle" Text="My Local database" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"
                       FontFamily="Comic Sans MS" FontSize="48" Height="76" Width="446" Foreground="#FF06FFFF"></TextBlock>
        </StackPanel>
        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
            <StackPanel Height="409" HorizontalAlignment="Left" Margin="6,6,0,0" Name="stackPanel1" VerticalAlignment="Top" Width="444">
                <StackPanel.Background>
                    <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                        <GradientStop Color="Black" Offset="0" />
                        <GradientStop Color="White" Offset="1" />
                    </LinearGradientBrush>
                </StackPanel.Background>
                <TextBlock Height="41" Name="textBlock1" Text="Enter City to add " Width="433" Foreground="#FFFF9FFF" FontFamily="Comic Sans MS" FontSize="28" />
                <TextBox Height="83" Name="textBox1" Text="Enter City" Width="433" FontSize="32" FontFamily="Comic Sans MS" Foreground="#FFFF6500" BorderBrush="#BF81FFFF">
                    <TextBox.Background>
                        <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                            <GradientStop Color="Black" Offset="0" />
                            <GradientStop Color="#FF9FAE9D" Offset="1" />
                        </LinearGradientBrush>
                    </TextBox.Background>
                </TextBox>
                <Button Content="Insert into LocalDatabase" Height="73" Name="button1" Width="372" Click="button1_Click" FontFamily="Comic Sans MS" FontSize="26">
                    <Button.BorderBrush>
                        <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                            <GradientStop Color="Black" Offset="0" />
                            <GradientStop Color="#FFFFFF49" Offset="0.717" />
                        </LinearGradientBrush>
                    </Button.BorderBrush>
                    <Button.Background>
                        <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                            <GradientStop Color="Black" Offset="0" />
                            <GradientStop Color="#FF70B3E5" Offset="1" />
                        </LinearGradientBrush>
                    </Button.Background>
                </Button>
                <Button Content="Select from database" Height="76" Name="button2" Width="312" Click="button2_Click" FontFamily="Comic Sans MS" FontSize="26">
                    <Button.Background>
                        <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                            <GradientStop Color="Black" Offset="0" />
                            <GradientStop Color="#FF187D7D" Offset="1" />
                        </LinearGradientBrush>
                    </Button.Background>
                </Button>
                <Button Content="Delete" Height="69" Name="button3" Width="224" FontFamily="Comic Sans MS" FontSize="26" Click="button3_Click">
                    <Button.Background>
                        <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                            <GradientStop Color="Black" Offset="0" />
                            <GradientStop Color="#FF00DBF5" Offset="1" />
                        </LinearGradientBrush>
                    </Button.Background>
                    <Button.BorderBrush>
                        <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                            <GradientStop Color="Black" Offset="0" />
                            <GradientStop Color="#FFF141F0" Offset="1" />
                        </LinearGradientBrush>
                    </Button.BorderBrush>
                </Button>
            </StackPanel>
        </Grid>
        <Grid.Background>
            <LinearGradientBrush EndPoint="1,0.5" StartPoint="0,0.5">
                <GradientStop Color="Black" Offset="0" />
                <GradientStop Color="#E648A4A6" Offset="1" />
            </LinearGradientBrush>
        </Grid.Background>
    </Grid>
</phone:PhoneApplicationPage>

Step 8 : In this step you will see the design of the MainPage.xaml file; let's see the figure given below.

Step 9 : Now it's time to get the application running by pressing F5 and the output is given below.

Output 1 : It's the default output whenever we run the application.

Output 2 : In this output we have to enter the city to the TextBox and click to the first button named as Insert and you can enter multiple cities inside it and by clicking or entering the city name inside the TextBox and you can see it as well in the figure given below.

Output 3 : In this output whatever the data you have entered into the local database, you can see that by clicking on second button named as select as you show in the figure given below.

Output 4 : Now in this output you can delete the city from the database whichever you want by entering the city name inside the TextBox.

Output 5 : In this output you just see that after deleting a city and want to see the rest then click on the select and the output will be as shown in the figure given below.

Here are some resources which may help you.

Windows Azure Storage Client Library For Windows Phone: Part 1

Local Database application for Windows Phone

The installed battery may not be properly connected to the computer

Getting URL of current page in Windows Phone dynamically through code

Running the SQL script file by using the System.Diagnostics.Process class