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!!!