Coming in Mango–Sql Server CE

Mango From Scratch

With the forthcoming Mango release of Windows Phone (tools to be released this month) you can store structured data in a Sql Server Compact Edition (CE) database file in isolated storage (called a local database).

You don’t actually interact directly with the database, but instead you use Linq to SQL (see my background article on Linq).  Linq to SQL allows your application to use Linq to speak to the local (relational) database

To get started, let’s build a dead-simple example that hardwires two objects and places them into the database, and then extracts them and shows their values in a list box.

The program will store information about Books, including the Author(s) and the Publisher, and then display that information on demand.

The initial UI consists of a button that will create two books, a button that will retrieve the two books and a list box that will display the two books:

<Grid
    x:Name="ContentPanel"
    Grid.Row="1"
    Margin="12,0,12,0">
    <Grid.RowDefinitions>
        <RowDefinition
            Height="1*" />
        <RowDefinition
            Height="1*" />
        <RowDefinition
            Height="4*" />
    </Grid.RowDefinitions>
    <Button
        Name="CreateBook"
        Content="Create Books"
        VerticalAlignment="Center"
        HorizontalAlignment="Center"
        Margin="0" />
    <Button
        Name="ShowData"
        Content="Show Books"
        Grid.Row="1"
        VerticalAlignment="Center"
        HorizontalAlignment="Center"
        Margin="0" />
    <ListBox
        Name="BooksLB"
        Grid.Row="2"
        VerticalAlignment="Stretch"
        HorizontalAlignment="Stretch"
        Margin="20">
        <ListBox.ItemTemplate>
            <DataTemplate>
               <!-- Details elided -->
            </DataTemplate>
        </ListBox.ItemTemplate>
    </ListBox>
</Grid>

Note that for now, I’ve left out the details of the DataTemplate; we’ll return to that shortly.

The DataContext

We begin the work  of managing the data by creating a Data Context, which is, as noted above, an object that derives from DataContext.  Add a reference to System.data.linq and these three using statements to the top of every related file:

using System.Data.Linq;
using System.Data.Linq.Mapping;
using Microsoft.Phone.Data.Linq.Mapping;

With these in place we can create the BooksDataContext, which will consist of three Tables and a constructor that passes its connection string to the base class:

 public class BooksDataContext : DataContext
 {
     public Table<Book> Books;
     public Table<Author> Authors;
     public Table<Publisher> Publishers;
     public BooksDataContext( string connection )
          : base( connection ) { }
 }

As written the program will not compile as we have not yet defined the Book, Author and Publisher classes.  Let’s do so now, and we’ll do so with an understanding of relational data and normalization: that is, we’ll have the Book class keep an ID for each author and for each publisher, rather than duplicating that information in each instance of Book.

This approach is informed by relational database theory but is good class design in any case.

Note: to keep this first example simple, we’ll make the fatuous assumption that every book has but a single author.

The following code appears in Book.cs,

[Table]
public class Book
{
    [Column( IsPrimaryKey = true )]
    public string BookID { get; set; }

    [Column]
    public string Title { get; set; }

    [Column]
    public string Author { get; set; }

    [Column]
    public string Publisher { get; set; }

    [Column]
    public DateTime PublicationDate { get; set; }
}

Table and Column are Linq to SQL mapping attributes (there are many others).  We’ll return, in future tutorials, to the details of these attributes and to how you can index additional properties (the primary key is automatically indexed) and create relationships.

For completeness, here are the contents of Author.cs and Publisher.cs,

[Table]
public class Author
{
    [Column( IsPrimaryKey = true )]
    public string AuthorID { get; set; }

    [Column]
    public string FirstName { get; set; }

    [Column]
    public string LastName { get; set; }

}

 [Table]
 public class Publisher
 {
     [Column( IsPrimaryKey = true )]
     public string PublisherID { get; set; }

     [Column]
     public string Name { get; set; }

     [Column]
     public string City { get; set; }

     [Column]
     public string Url { get; set; }
 }

We create an instance of the database in MainPage.xaml.cs, in the constructor, where we also wire up event handlers for the two buttons,

public MainPage()
{
    InitializeComponent();
    DataContext db =
      new BooksDataContext( "isostore:/bookDB.sdf" );
    if (!db.DatabaseExists())
        db.CreateDatabase();

    CreateBook.Click +=
      new RoutedEventHandler( CreateBook_Click );
    ShowData.Click +=
      new RoutedEventHandler( ShowData_Click );

}

Notice the syntax for storing our database file (bookDB.sdf) inside of isolated storage. With the DataContext we get back we create the new database.

When CreateBook is clicked the event handler is called, and at this time we want to create two books.  Since our books have a publisher and different authors, we must create those objects first (or we’ll refer to a publisher or author ID that doesn’t exist).  Let’s start with the creation of the publisher:

BooksDataContext db =
   new BooksDataContext( "isostore:/bookDB.sdf" );
Publisher pub = new Publisher()
{
    PublisherID = "1",
    Name = "APress",
    City = "Acton",
    Url = "http://Apress.com"
};

db.Publishers.InsertOnSubmit( pub );

This code gets a new reference to the same database, and then instantiates a Publisher object, initializing all the Publisher fields.  It then instructs the database context to insert this record when the Submit command is called.  Before calling that command, however, we’ll create a few author records:

Author auth = new Author()
{
    AuthorID = "1",
    FirstName = "Jesse",
    LastName = "Liberty"
};

db.Authors.InsertOnSubmit( auth );

auth = new Author()
{
    AuthorID = "2",
    FirstName = "Paul",
    LastName = "Betts"
};

db.Authors.InsertOnSubmit( auth );

auth = new Author()
{
    AuthorID = "3",
    FirstName = "Jeff",
    LastName = "Blankenburg"
};

db.Authors.InsertOnSubmit( auth );

With these four records ready to be submitted, we call SubmitChanges on the databaseContext,

db.SubmitChanges();

We’re now ready to create the two book objects. Once again we instantiate the C# object and initialize its fields, and once again we mark each instance to be inserted on submission,

db = new BooksDataContext( "isostore:/bookDB.sdf" );

Book theBook = new Book()
{
    BookID = "1",
    Author = "2",
    Publisher = "1",
    PublicationDate = DateTime.Now,
    Title = "Programming Reactive Extensions"
};
db.Books.InsertOnSubmit( theBook );

theBook = new Book()
{
    BookID = "2",
    Author = "3",
    Publisher = "1",
    PublicationDate = DateTime.Now,
    Title = "Migrating to Windows Phone"
};
db.Books.InsertOnSubmit( theBook );

db.SubmitChanges();

The two books are now in the database, and we can prove that to ourselves by implementing the event handler for the ShowData button,

void ShowData_Click( object sender, RoutedEventArgs e )
{
    BooksDataContext db =
      new BooksDataContext( "isostore:/bookDB.sdf" );
    var q = from b in db.Books
            orderby b.Title
            select b;
    List<Book> books = q.ToList();
    BooksLB.ItemsSource = books;
}

Once again we create a new DataContext but pointing to the same local database.  We now execute a LINQ Query against the database, obtaining every record in the Books table, ordered by title.  We convert the result to an enumerable (a List<>) and set that as the ItemsSource property for the BooksListBox.  This then becomes the datacontext for the databinding in the DataTemplate of the list box.

<ListBox.ItemTemplate>
    <DataTemplate>
        <StackPanel>
            <TextBlock
                Text="{Binding Title}" />
            <StackPanel
                Orientation="Horizontal">
                <TextBlock
                    Text="Author ID: " />
                <TextBlock
                Text="{Binding Author}" />
            </StackPanel>
            <StackPanel
                Orientation="Horizontal">
                <TextBlock
                    Text="Published: " />
                <TextBlock
                    Text="{Binding PublicationDate}" />
            </StackPanel>
            <TextBlock
                Text="------------------" />
        </StackPanel>
    </DataTemplate>
</ListBox.ItemTemplate>

There is much more to do to turn this into anything approaching a useful application (e.g., creating the relationships between the tables so that we see the Author and Publisher’s names rather than their ID).  All of that is to come in future tutorials.

The take-away from this first tutorial, however, is, I hope, that working with the database in Mango is straight-forward and that the semantics (and to a large degree the syntax) is pretty much what you would expect.