Local Data Access in Windows Phone Mango

By Alessandro Del Sole – Microsoft MVP

Download the code

Preparing the Database and the Object Model

Before starting Visual Studio 2010, you need to do a couple of things. First you need a database. You can create one from scratch depending on your needs, by using well-known tools such as Microsoft SQL Server Management Studio. For the sake of simplicity we are going to use the SQL CE version of the Northwind database, called Northwind.sdf and that you will find in the folder called C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples. It is a good idea to copy this file into another folder which is not under the UAC of Windows 7 and Vista, for example C:\Temp. Once you have your database, you need to generate an object model based on LINQ to SQL. Visual Studio 2010 offers a LINQ to SQL designer for all the other kinds of applications, however this is not available in Windows Phone development. This means that you have to generate the LINQ to SQL model manually. This is an easy task and requires invoking a command line tool called SQLMetal.exe, which is also used by Visual Studio 2010 behind the scenes when generating the object models for other application types. Launch the Visual Studio 2010 command prompt via the appropriate shortcut available in Start, All Programs, Microsoft Visual Studio 2010, Tools. When the command prompt is ready, write the following line:

SQLMetal.exe C:\temp\Northwind.sdf /language:vb /code:NorthwindDataClasses.vb /pluralize /context:NorthwindDataContext /Namespace:Northwind

Some information on command line options:

• /language: specifies the programming language that will be used to generate the object model. Available choices are vb and cs
• /code: specifies the output file that will contain the code for the object model and its classes
• /pluralize: enables the pluralization option for names of entities based on the English grammar. For instance, if you have a Customer entity then the collection of Customer automatically becomes Customers
• /context: provides the name of the DataContext class
• /Namespace: specifies the namespace that contains the object model

At this point you can launch Visual Studio, where you will embed the generated code files into your applications.

Creating a Sample Project

You can use either Visual Studio Express for Windows Phone or Visual Studio 2010 to create the sample project. The project template that we use is the Windows Phone Application. Create a new one with a custom name, and ensure you select the Windows Phone 7.1 OS option. Once the project has been created, you need to add a reference to the assembly called System.Data.Linq.dll, which adds support for LINQ to SQL. At this point the code file generated with SQLMetal, which contains the object model definition, can be added to the project. Select Project, Add Existing Item and select the NorthwindDataClasses.vb file. When this becomes available in Visual Studio, double-click it to take a look at the code. You can see a long list of classes that map tables and properties that map columns from tables. At this point you need to remove the two overloads of the constructor shown in Listing 1, since these are not supported in Mango and will be highlighted as incorrect.

Public Sub New(ByVal connection As System.Data.IDbConnection)
    MyBase.New(connection, mappingSource)
    OnCreated()
End Sub
Public Sub New(ByVal connection As System.Data.IDbConnection, ByVal mappingSource As System.Data.Linq.Mapping.MappingSource)
    MyBase.New(connection, mappingSource)
    OnCreated()
End Sub

Listing 1

Now all the introductory steps have been completed and we can design the user interface.

Designing the User Interface

The user interface for the sample project is very simple so that you will focus on the code that works with data. Imagine you want to work with the list of customers from the Customers table in the Northwind database and you want to show some columns. An appropriate control to do this is the ListBox that can be placed inside the default-provided Grid called ContentPanel. The code in Listing 2 demonstrates how to implement a ListBox that shows a couple of columns from the Customers table by taking advantage of a data template (this implies you have some existing knowledge of data templates in XAML).

<!--ContentPanel - place additional content here-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
    <ListBox HorizontalAlignment="Left" ItemsSource="{Binding}"
             Name="ListBox1">
        <ListBox.ItemTemplate>
            <DataTemplate>
                <Grid>
                    <Grid.ColumnDefinitions>
                        <ColumnDefinition Width="150"/>
                        <ColumnDefinition Width="150"/>
                    </Grid.ColumnDefinitions>
                    <TextBlock Text="{Binding CompanyName}"/>
                    <TextBlock Text="{Binding City}" Grid.Column="1"/>
                </Grid>
            </DataTemplate>
        </ListBox.ItemTemplate>
    </ListBox>
</Grid>

Listing 2

As you can see two TextBlock controls are data-bound to specific properties in the LINQ to SQL model representing columns in the Customers table. Now you need a way to perform C.R.U.D. operations on the data. For demonstration purposes this will be accomplished by implementing some buttons inside the application bar, so that you can also learn how to leverage another feature in Windows Phone. By default Visual Studio adds a commented code snippet for interacting with the application bar. Uncomment the code and replace it with the snippet shown in Listing 3.

<!--Sample code showing usage of ApplicationBar-->
<phone:PhoneApplicationPage.ApplicationBar>
    <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
        <shell:ApplicationBarIconButton x:Name="AddButton" Click="AddButton_Click"
                                IconUri="/Images/appbar_button1.png" Text="Insert"/>
        <shell:ApplicationBarIconButton x:Name="RemoveButton" Click="RemoveButton_Click"
                                IconUri="/Images/appbar_button2.png" Text="Delete"/>
        <shell:ApplicationBarIconButton x:Name="FilterButton" Click="FilterButton_Click"
                                IconUri="/Images/appbar_button3.png" Text="Filter"/>
    </shell:ApplicationBar>
</phone:PhoneApplicationPage.ApplicationBar>

Listing 3

As you can see by taking advantage of the application bar there is no need of implementing additional controls. Each button has a self-explanatory name and content and a Click event handler has been declared. The next step is implementing the application logic.

Executing Data Operations

The application logic is implemented in the code-behind file for the main page (at least in this particular example). The first thing to do is defining the connection string and the instance of the DataContext. Listing 4 demonstrates this.

Private connectionString As String = "Data Source=isostore:/Northwind.sdf"
Private northwindContext As Northwind.NorthwindDataContext
' Constructor
Public Sub New()
    InitializeComponent()
    Me.northwindContext = New Northwind.NorthwindDataContext(connectionString)
    If Not Me.northwindContext.DatabaseExists Then
        northwindContext.CreateDatabase()
    End If
End Sub
Private Sub MainPage_Unloaded(sender As Object, e As System.Windows.RoutedEventArgs) _
                          Handles MyBase.Unloaded
    Me.northwindContext.Dispose()
End Sub

Listing 4

The connectionString variable stores the connection string which is very simple. The isostore identifier is fixed and identifies the Isolated Storage of your device, which is the reserved space where your application can store data. The northwindContext variable holds a reference to the DataContext instance exposed by the LINQ to SQL model, which is instantiated in the constructor. Also notice how the code in the constructor first checks if the database exists; if it does not exist yet then the code creates a new one. It is very important to remember to release resources such as the connection to the database, which is performed in the Unloaded event of the page. The next step is writing a Click event handler for buttons that add and remove customers. This is a simple way to demonstrate insert and delete operations and is represented in Listing 5.

Private Sub AddButton_Click(sender As System.Object, e As System.EventArgs)
    Try
        Dim c1 As New Northwind.Customer With {.CompanyName = "Del Sole",
                                               .City = "Rome", .CustomerID = "DELSO"}
        Dim c2 As New Northwind.Customer With {.CompanyName = "Contoso Ltd.",
                                               .City = "Redmond", .CustomerID = "CONTO"}
        Dim c3 As New Northwind.Customer With {.CompanyName = "Fabolous Company",
                                               .City = "Bellevue", .CustomerID = "FABOL"}
        Dim c4 As New Northwind.Customer With {.CompanyName = "Magic Company",
                                               .City = "Seattle", .CustomerID = "MAGIC"}
        northwindContext.Customers.InsertOnSubmit(c1)
        northwindContext.Customers.InsertOnSubmit(c2)
        northwindContext.Customers.InsertOnSubmit(c3)
        northwindContext.Customers.InsertOnSubmit(c4)
        northwindContext.SubmitChanges()
        'Popola la ListBox con la tabella
        Me.ListBox1.ItemsSource = northwindContext.Customers
    Catch ex As System.Data.Linq.DuplicateKeyException
        MessageBox.Show("Cannot add duplicate information")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
Private Sub RemoveButton_Click(sender As System.Object, e As System.EventArgs)
    Dim currentCustomer = TryCast(Me.ListBox1.SelectedItem, Northwind.Customer)
    If currentCustomer Is Nothing Then Exit Sub
    northwindContext.Customers.DeleteOnSubmit(currentCustomer)
    northwindContext.SubmitChanges()
    Me.ListBox1.ItemsSource = Nothing
    Me.ListBox1.ItemsSource = northwindContext.Customers
End Sub

Listing 5

Specifying CustomerID and CompanyName is required for each Customer object, whereas the City is not required but is something that we want to display. It is worth mentioning that catching a DuplicateKeyException will prevent from adding Customer instances with a duplicate primary key. Regarding the remove operation, the code retrieves the currently selected customer in the list and in case this is not null it removes the instance from the data context. Notice that reloading data is necessary to re-bind data to the user interface. Both handlers explicitly invoke the context’s SubmitChanges method that you can also invoke to perform a save operation elsewhere in your application (for example if you have updated your data). Finally you can implement a LINQ query that filters data based on the given criterion, as demonstrated in Listing 6.

Private Sub FilterButton_Click(sender As System.Object, e As System.EventArgs)
    Me.ListBox1.ItemsSource = New ObservableCollection(Of Northwind.Customer)((
                              From cust In northwindContext.Customers
                              Where cust.CompanyName.StartsWith("C")
                              Select cust))
End Sub

Listing 6

So it is easy to bind a LINQ query to the ItemsSource property of the ListBox exactly as you would do in other development environments such as WPF or Silverlight.

Running the Application

When you run the application in either the device emulator or on the physical device you will be able to see how you can add, remove, and filter data. Figure 1 shows an example based on the result of both the insert and remove operations. As you can see data is correctly added, removed, filtered, and saved exactly as you would expect; all of this happens over a true local database.

Figure 1 Running the Application

Conclusion

The Windows Phone development experience has been dramatically enhanced with Mango and having an opportunity of using a local database opens to an incredible number of new scenarios. Last but not least, free versions of the development tools are available as well as SQL Server Compact Edition which also ships for free.

About the Author

Alessandro Del Sole is a Microsoft Visual Basic MVP and Team Member in the Italian “Visual Basic Tips & Tricks” Community. He writes many Italian and English language community articles and books about .NET development and is the author of the books “Visual Basic 2010 Unleashed” and “Visual Studio LightSwitch Unleashed”. He also enjoys writing freeware and open-source developer tools. You can visit Alessandro’s Italian language blog or his English language blog.