Coming in Mango–Sql Server CE

Mango From Scratch

With the forthcoming Mango release of Windows Phone Books(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

Geek Details:  Linq to SQL consists of an object model and a runtime.
The object model is encapsulated in the Data Context object (an object that inherits from System.Data.Linq.DataContext). 

The runtime mediates between objects (the DataContext ) and relational data (the local database). Thus, Linq To SQL acts as an ORM: an Object Relational Mapping framework.

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.

Share

About Jesse Liberty

Jesse Liberty is an independent consultant and programmer with three decades of experience writing and delivering software projects. He is the author of 2 dozen books and multiple Pluralsight courses, and has been a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Xamarin Certified Mobile Developer and a Xamarin MVP, Microsoft MVP and Telerik MVP.
This entry was posted in Data, Linq, Mango, Mini-Tutorial, WindowsPhone and tagged . Bookmark the permalink.

28 Responses to Coming in Mango–Sql Server CE

  1. Pingback: Is LINQ to SQL Dead or Alive? | ASK AND ANSWER

  2. Pingback: How to: Is LINQ to SQL Dead or Alive? | SevenNet

  3. TriFu says:

    Is there any chance you can release the full source code for the example above?

    I’m confused in the datacontext creation step as to where that file lives? and where the “book.cs” classes and such should be created as well.
    Do those book/author/publisher classes reside in the model folder? and the data context as well?

    Thank you kindly!

  4. Carl Perkins says:

    I just wanted to thank you, Jesse, for this sqlce example and the book you have written. I find your examples very clear and straightforward and you actually EXPLAIN WHY something is done. Congrats and thank you for you approach. Do you publish videos like LearnVisualStudio.Net and TrainingSpot.com??? Those are pretty good. Carl Perkins

  5. Pingback: VerySoftware Blog

  6. Pingback: VerySoftware

  7. With says:

    Hi Jessie,

    Could you expand on this example to demonstrate how the Tables (Books, Authors & Publishers properties) of the BooksDataContext are being instantiated?

    Using the example above as a guide for creating my own database file, I’m getting nullref exceptions calling .InsertOnSubmit(), I think because my Table objects never get constructed. It looks like Table is a sealed with no accessible constructors, I think I’m missing something obvious but I don’t see it.

  8. Pingback: Arsanth » Blog Archive » Arsanth Daily – May 25th

  9. Pingback: Arsanth » Blog Archive » Arsanth Daily – May 24th

  10. HELP says:

    how come i cannot add

    using System.Data;

    in my windows phone 7 app??? i tried adding a reference but System.Data is not shown in the .NET tab :((

  11. Pingback: Coming in Mango–Sql Server CE | WP7 Developers Links

  12. Pingback: Best Practices For Local Databases | WP7 Developers Links

  13. Chris Horn says:

    any plans to carry this over to SL 5? or will we have to wait for SL 6 ?

  14. Pingback: Coming in Mango – Query the Contacts List & Sql Server CE – www.nalli.net

  15. billg says:

    Didn’t MS put a stake through LINQ to SQL?

    • Scott says:

      Yes. Julia Lerman even wrote about it in her Entity Framework book that the ms data team announced that going forward from 2008 they would focus on the Entity Framework while maintaining LINQ to SQL. Why L2S in WP Mango? Great question.

  16. Sreenivas says:

    Though the compact db is ms sql, does the api support using the entity framework (linq to entities) on compact sql instead of linq to sql?

  17. ErikEJ says:

    Will it be possible to include a pre-poulated database file with your app installer?

  18. Martin says:

    “isostore:/bookDB.sdf”
    Will it be possible to use the isolated storage URI scheme in more places? Would be really neat e.g. for images.

  19. mj says:

    So how cool is this? Ball State University mathematical sciences student class project produces music video on Pivot Tables. Check it out! http://blogs.office.com/b/microsoft-excel/archive/2011/05/06/rockin-out-with-pivottables.aspx

Leave a Reply

Your email address will not be published.