Local data storage is a common requirement for many applications, allowing them to function offline or manage user-specific information efficiently. In .NET environments, particularly with older platforms like Windows Phone 7 that relied on SQL Server Compact Edition (files with a .sdf extension), LINQ to SQL provides a straightforward approach to interact with these embedded databases. This article demonstrates how to set up a local database, define a data model, and perform basic data operations using LINQ to SQL.

To begin, ensure you have the necessary LINQ to SQL components referenced in your project:

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

Defining the Data Model

The first step involves creating a C# class that represents the structure of your database table. This class will be mapped to a table in the local .sdf file using LINQ to SQL attributes. Each property in the class corresponds to a column in the database table.

[Table(Name = "ContactEntries")]
public class ContactEntry
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true, Name = "EntryId")]
    public int EntryIdentifier { get; set; }

    [Column(CanBeNull = false, Name = "EmailAddressValue")]
    public string EmailAddress { get; set; }
}

In this ContactEntry model:

• The [Table] attribute indicates that this class maps to a database table, optionally specifying a table name.
• The [Column] attribute defines properties as database columns.
• IsPrimaryKey = true marks a column as the primary key.
• IsDbGenerated = true instructs the database to automatically generate values for this column (e.g., auto-incrementing IDs).
• CanBeNull = false enforces a NOT NULL constraint on the column.

Establishing the Data Context

The DataContext acts as the primary conduit for interacting with your database. It menages connections, tracks changes to objects, and translates LINQ queries into SQL commands. You'll create a custom class inheriting from System.Data.Linq.DataContext and expose Table<T> properties for each entity you wish to query.

public class AppDbContext : DataContext
{
    public AppDbContext(string connectionString)
        : base(connectionString)
    { }

    public Table<ContactEntry> Contacts
    {
        get
        {
            return GetTable<ContactEntry>();
        }
    }
}

Configuring the Connection String

The connection string specifies how to connect to your local database file. For Windows Phone applications, databases are typically stored in isolated storage, which is referenced using isostore:.

const string LocalDatabaseConnectionString = "Data Source=isostore:/MailDatabase.sdf";

Implementing Data Operations

With the model and context defined, you can now implement methods to perform common database operations like retrieving and adding data.

Retrieving Records

public ICollection<ContactEntry> RetrieveEntries(string dbConnectionString)
{
    using (var context = new AppDbContext(dbConnectionString))
    {
        // Execute a LINQ query to fetch all entries
        var allEntries = from entry in context.Contacts
                         select entry;

        return allEntries.ToList();
    }
}

Adding a New Record

public void PersistNewEntry(ContactEntry newEntry, string dbConnectionString)
{
    using (var context = new AppDbContext(dbConnectionString))
    {
        // Add the new entity to the table
        context.Contacts.InsertOnSubmit(newEntry);
        // Commit changes to the database
        context.SubmitChanges();
    }
}

Integrating with the User Interface (Example)

To demonstrate interaction, consider a simple UI that allows users to input an email address and save it to the database, then display all saved entries. The following XAML defines a basic layout for such an interface, which would typically reside within a page or window.

<Grid x:Name="LayoutRoot" Margin="12,0,12,0">
    <Grid.RowDefinitions>
        <RowDefinition Height="Auto"/>
        <RowDefinition Height="*"/>
    </Grid.RowDefinitions>
    <StackPanel Grid.Row="0" Orientation="Horizontal">
        <TextBox x:Name="emailInputBox" Width="300" PlaceholderText="Enter email address"/>
        <Button x:Name="saveButton" Content="Add Entry" HorizontalAlignment="Right" Width="120" Click="OnSaveButtonClick"/>
    </StackPanel>
    <ListBox x:Name="entryDisplayList" Grid.Row="1" Margin="0,10,0,0">
        <ListBox.ItemTemplate>
            <DataTemplate>
                <StackPanel>
                    <TextBlock Text="{Binding EmailAddress}" Style="{StaticResource PhoneTextNormalStyle}"/>
                    <TextBlock Text="-----------" Style="{StaticResource PhoneTextSubtleStyle}"/>
                </StackPanel>
            </DataTemplate>
        </ListBox.ItemTemplate>
    </ListBox>
</Grid>

The corresponding C# code-behind handles initialization, database creation, data loading, and event handling for saving new entries. The InputScope is configured for the text box to provide an optimized keyboard for email input on mobile devices.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Input; // For InputScope
using System.Data.Linq;
using System.Data.Linq.Mapping;
using Microsoft.Phone.Controls; // Specific to Windows Phone pages

namespace MyApplication.DataDemo
{
    // Define the connection string globally or as a constant
    public partial class LocalDbPage : PhoneApplicationPage
    {
        const string LocalDatabaseConnectionString = "Data Source=isostore:/MailDatabase.sdf";

        public LocalDbPage()
        {
            InitializeComponent();

            // Configure input scope for email addresses
            InputScope emailInputScope = new InputScope();
            InputScopeName emailScopeName = new InputScopeName();
            emailScopeName.NameValue = InputScopeNameValue.EmailNameOrAddress;
            emailInputScope.Names.Add(emailScopeName);
            emailInputBox.InputScope = emailInputScope;

            // Ensure the database exists; create it if not
            using (var context = new AppDbContext(LocalDatabaseConnectionString))
            {
                if (!context.DatabaseExists())
                    context.CreateDatabase();
                
                RefreshEntryList();
            }
        }

        private void RefreshEntryList()
        {
            entryDisplayList.ItemsSource = RetrieveEntries(LocalDatabaseConnectionString);
        }

        public ICollection<ContactEntry> RetrieveEntries(string dbConnectionString)
        {
            using (var context = new AppDbContext(dbConnectionString))
            {
                // Retrieve all entries and convert to a list
                return context.Contacts.ToList();
            }
        }

        public void PersistNewEntry(ContactEntry newEntry, string dbConnectionString)
        {
            using (var context = new AppDbContext(dbConnectionString))
            {
                context.Contacts.InsertOnSubmit(newEntry);
                context.SubmitChanges();
            }
        }

        private void OnSaveButtonClick(object sender, RoutedEventArgs e)
        {
            string currentEmail = emailInputBox.Text.Trim();
            if (!string.IsNullOrEmpty(currentEmail))
            {
                var newContact = new ContactEntry() { EmailAddress = currentEmail };
                PersistNewEntry(newContact, LocalDatabaseConnectionString);
                emailInputBox.Text = string.Empty; // Clear input
                RefreshEntryList(); // Update displayed data
            }
            else
            {
                MessageBox.Show("Please enter an email address.");
            }
        }
    }

    // Model Class - typically defined in its own file
    [Table(Name = "ContactEntries")]
    public class ContactEntry
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true, Name = "EntryId")]
        public int EntryIdentifier { get; set; }

        [Column(CanBeNull = false, Name = "EmailAddressValue")]
        public string EmailAddress { get; set; }
    }

    // DataContext Class - typically defined in its own file
    public class AppDbContext : DataContext
    {
        public AppDbContext(string connectionString)
            : base(connectionString)
        { }

        public Table<ContactEntry> Contacts
        {
            get
            {
                return GetTable<ContactEntry>();
            }
        }
    }
}