Windows Phone 7 : Using Local Database for Application

Windows Phone uses concept of Local Database and by implementing LINQ to SQL you can create 100% relational database driven application.

To understand more on this refer https://msdn.microsoft.com/en-us/library/hh202860(VS.92).aspx

So what you need is the good old tie up between Silverlight and LINQ to SQL. Here we go.

You need to refer System.Data.Linq assembly and use the below two namespaces to define the model.

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

After that you need to define the model by hand.

 //Model Class
[Table]
public class EmailClass
{
    [Column(IsDbGenerated = true, IsPrimaryKey = true)]
    public int Id { get; set; }
    [Column()]
    public string EmailAddress { get; set; }
}

Once the model is defined you then need the DataContext

 public class EmailContext : DataContext
{
    public EmailContext(string sConnectionString)
        : base(sConnectionString)
    { }

    public Table<EmailClass> Emails
    {
        get
        {
            return this.GetTable<EmailClass>();
        }
    }
}

Connection string would look like,

 string ConnectionString = "Data Source=isostore:/EmailDB.sdf";

Some reusable methods to Add and Display

 public IList<EmailClass> GetEmails()
{
    List<EmailClass> emails = new List<EmailClass>();
    using (var db = new EmailContext(ConnectionString))
    {
        var query = from e in db.Emails
                    select e;
        emails = query.ToList();
    }

    return emails;
}

public void AddEmail(EmailClass _email)
{
    using (var db = new EmailContext(ConnectionString))
    {
        db.Emails.InsertOnSubmit(_email);
        db.SubmitChanges();
    }
}

Then comes UI which would help you to save and display data,

 <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
    <Grid.RowDefinitions>
        <RowDefinition Height="Auto"></RowDefinition>
        <RowDefinition Height="*"></RowDefinition>
    </Grid.RowDefinitions>
    <StackPanel Grid.Row="0" Orientation="Horizontal">
        <TextBox Width="300" x:Name="txtEmail"></TextBox>
        <Button HorizontalAlignment="Right" x:Name="btnSave" Content="Save" Width="120" Click="btnSave_Click"></Button>
    </StackPanel>
    <ListBox x:Name="lstEmails" Grid.Row="1" >
        <ListBox.ItemTemplate>
            <DataTemplate>
                <StackPanel>
                    <TextBlock Text="{Binding Path=EmailAddress}"></TextBlock>
                    <TextBlock Text="-----"></TextBlock>
                </StackPanel>
            </DataTemplate>
        </ListBox.ItemTemplate>
    </ListBox>            
</Grid>

The complete code is as below,

 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.Data.Linq;
using System.Data.Linq.Mapping;

namespace WP7_Samples
{    
    public partial class LocalDB_WP7 : PhoneApplicationPage
    {
        const string ConnectionString = "Data Source=isostore:/EmailDB.sdf";
        public LocalDB_WP7()
        {
            InitializeComponent();

            InputScope _scope = new InputScope();
            InputScopeName _scopeName = new InputScopeName();
            _scopeName.NameValue = InputScopeNameValue.EmailNameOrAddress;
            _scope.Names.Add(_scopeName);

            txtEmail.InputScope = _scope;

            using (var db = new EmailContext(ConnectionString))
            {
                if (!db.DatabaseExists())
                    db.CreateDatabase();

                LoadData();
            }
        }

        public void LoadData()
        {
            lstEmails.ItemsSource = GetEmails();
        }

        public IList<EmailClass> GetEmails()
        {
            List<EmailClass> emails = new List<EmailClass>();
            using (var db = new EmailContext(ConnectionString))
            {
                var query = from e in db.Emails
                            select e;
                emails = query.ToList();
            }

            return emails;
        }

        public void AddEmail(EmailClass _email)
        {
            using (var db = new EmailContext(ConnectionString))
            {
                db.Emails.InsertOnSubmit(_email);
                db.SubmitChanges();
            }
        }

        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            if (txtEmail.Text.Trim() != "")
            {
                var emailObj = new EmailClass() { EmailAddress = txtEmail.Text.Trim() };
                AddEmail(emailObj);
            }
            LoadData();
            txtEmail.Text = "";
        }
    }

    //Model Class
    [Table]
    public class EmailClass
    {
        [Column(IsDbGenerated = true, IsPrimaryKey = true)]
        public int Id { get; set; }
        [Column()]
        public string EmailAddress { get; set; }
    }

    public class EmailContext : DataContext
    {
        public EmailContext(string sConnectionString)
            : base(sConnectionString)
        { }

        public Table<EmailClass> Emails
        {
            get
            {
                return this.GetTable<EmailClass>();
            }
        }
    }
}

Namoskar!!!

Comments

• Anonymous
  August 30, 2011
  Hi, Thanks so much for this helpful blog. But I want to add 2 or more tables having different schema in my Local Storage Database so for that do I need to define different ".sdf files and DataContext object" for all of them. It will also be helpful if you can provide a Example for this. Thanks in advance

• Anonymous
  March 11, 2012
  The comment has been removed

• Anonymous
  March 27, 2012
  Whenever you re-start the Emulator it will not keep the previous state. Do not close the Emulator till you finish your task. That should solve the issue

• Anonymous
  May 20, 2012
  Hi i want to perform operations on sql-lite DB, i tried with different code  but not getting any solution for this. can u help me . reply me over shiva.ankit@gmail.com

• Anonymous
  May 08, 2013
  How to update a data in a table using windows phone 7