Windows Phone 7 : Using Local Database for Application

Windows Phone 7 : Using Local Database for Application

  • Comments 4

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

Windows Phone DataContext and Local Database

To understand more on this refer http://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!!!

Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post
  • Hi, Thanks for the nice piece of code. When I closed the emulator and re-started it, the stored contacts were not displayed. Every time the emulator is re-started, it hits the line db.CreateDatabase(); there by re-creating the database which makes the old data to get cleared.

    Please suggest on how to rectify this.

  • 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

  • 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

  • How to update a data in a table using windows phone 7

Page 1 of 1 (4 items)