Silverlight 3 Tutorial - ADO.NET Data Services

Krzysztof Mossakowski

Tutorials

Demonstrated issues:

ADO.NET Data Services
- ADO.NET Data Services is the data provider for Silverlight-based applications. You can use ADO.NET Data Services to display and update data from a remote data source in a Silverlight-based application. ADO.NET Data Services exposes data based on an entity and relationship model that enables you to access data in the style of representational state transfer (REST) resources. Silverlight-based applications can access the data through the standard HTTP protocol to execute queries and to create, update, and delete data on the server.
Binding data to the ComboBox and DataGrid controls.

Prerequisities:

Visual Studio 2008 SP1
Silverlight 3 Tools for Visual Studio 2008 SP1
The Northwind database installed in SQL Server (SQL Server 2008 Express was used for this tutorial). [Installation script of the database]

Steps:

Create an ASP.NET Web application which will serve data for requests from a Silverlight application.
- Name the Web application 'NorthwindDataService':
- In the configuration of the project choose the option of using local IIS Web server to serve the application and create in IIS a virtual directory for it. Use the following Url for the application:
  http://localhost/NorthwindDataService
  (it will be used by a Silverlight application).
- Add some content to the page, compile the project and test in the browser this Uri: http://localhost/NorthwindDataService:
Create a data model and a data service in the existing project
- Add to the project a new ADO.NET Entity Data Model item, name it 'Northwind.edmx':
  - Generate the model from the database:
  - Choose a connection if exists or create a new one if necessary. Allow to use the default name for the connection string in the Web.config file ('NorthwindEntities'):
  - Choose all tables to include in the model, use the default name for the model ('NorthwindModel'):
  - A set of classes representing entities from the database has been created. The generated code is stored in the Northwind.Designer.cs file. Using the Northwind.edmx file, the structure of dependencies between classes and mappings between columns from database tables and fields defined in classes can be modified using the visual designer:
- Add to the project a new ADO.NET Data Service item, name it 'Northwind.svc':
  - Replace the placeholder comment in the generated Northwind.svc.cs file:
```
public class Northwind : DataService< /* TODO: put your data source class name here */ >
```
    with the name of the main class of the model (NorthwindEntities):
```
public class Northwind : DataService<NorthwindEntities>
```
    Rebuild the project to check if it compiles successfully.
  - Following the comment placed in the Northwind.InitializeService method, make setup for access to the created data service. Assuming that our Silverlight application will modify data only from Orders, Order_Details, and Products table and read data from Employees and Customers tables, the configuration should be as follows:
```
public class Northwind : DataService<NorthwindEntities>
```
```
{
```
```
    public static void InitializeService(IDataServiceConfiguration config)
```
```
    {
```
```
        config.SetEntitySetAccessRule("Employees", EntitySetRights.AllRead);
```
```
        config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead);
```
```
        config.SetEntitySetAccessRule("Orders", EntitySetRights.All);
```
```
        config.SetEntitySetAccessRule("Order_Details", EntitySetRights.All);
```
```
        config.SetEntitySetAccessRule("Products", EntitySetRights.All);
```
```
    }
```
```
}
```
  - To test if the data service works properly, make a request from a browser for the service's Url:
    http://localhost/NorthwindDataService/northwind.svc/
  - Test it further and request for data from one table, e.g. Customers:
    http://localhost/NorthwindDataService/northwind.svc/Customers
    
    The data service returns data using RSS format, that's why the browser display it as RSS channel.
    - If a message "An error occurred while processing this request." is displayed, check if the configuration of the data service is correct. At this time (in contrast with the previous test), the data service must connect to the database and if it is impossible due to incorrect configuration such error can occur.
      - If the connection to the SQL Server used by the project uses Integrated Security, it is likely that the user account used by IIS to execute ASP.NET Web applications cannot access to the database. There are two solutions in such case. (1) Use ASP.NET impersonation in the Web application. (2) Configure the SQL Server instance to allow to connect using SQL Server Authentication, create a user in SQL Server, give him proper rights for the Northwind database, and use this user's login and password in the connection string (e.g. replace 'Integrated Security=True;' with 'User Id=nwuser;Password=nwpass;').
  - To get all orders of a customer, the following address can be used:
    http://localhost/NorthwindDataService/northwind.svc/Customers(ALFKI)/Orders
    ('ALFKI' is an identifier of one of customers existing in the database.)
Create a Silverlight application to consume data served by the data service.
- Add a new Silverlight Application project to the solution:
  - Use all the default settings, i.e. host the Silverlight application in the existing Web application, add a test page to the Web application, and make it the start page:
- Add a data service reference to the Silverlight application:
  - Right click on the project in the Solution Explorer window and choose 'Add Service Reference...' option. Let the tool discover available services. Take a look at the public entities, name the namespace 'Northwind' and click 'OK':
    - Note the Service Reference added to the project. Double click it to see available classes and their members:

Add a ComboBox control which will allow the user to select a customer. Add also a TextBox control which will be used to display information. Place both these controls in StackPanel, to make an arrangement:

              <StackPanel>
                  <TextBox
                      x:Name="StatusTextBox" 
                      Margin="5"
                      Padding="5"
                      TextWrapping="Wrap"
                      HorizontalAlignment="Center" 
                      VerticalAlignment="Center"
                      VerticalScrollBarVisibility="Auto" 
                      />
               
                  <ComboBox
                      x:Name="CustomersComboBox" 
                      Width="260"
                      ItemsSource="{Binding}"
                      />
              </StackPanel>
            

A collection of customers data retrieved from the data service will be bound to the ComboBox control.

Write code to get data from the data service. Write also some helpful methods for displaying information:

public partial class MainPage : UserControl

    private NorthwindEntities dataContext;

    public MainPage()

        InitializeComponent();

        dataContext = new NorthwindEntities(new Uri("Northwind.svc", UriKind.Relative));

        GetCustomers();

    private void GetCustomers()

        ShowWaiting("Retrieving customers...");

        DataServiceQuery<Customers> query = dataContext.Customers;

        query.BeginExecute(OnCustomersQueryComplete, query);

    private void OnCustomersQueryComplete(IAsyncResult ar)

        Dispatcher.BeginInvoke(() =>

try

                var query = (DataServiceQuery<Customers>)ar.AsyncState;

                CustomersComboBox.DataContext = query.EndExecute(ar);

                ShowSuccess("Customers retrieved");

            catch (Exception e)

                ShowError("Error during retrieving customers: " + e);

});

    private void ShowWaiting(string text)

        ShowStatus(Colors.Yellow, Colors.Black, text);

    private void ShowSuccess(string text)

        ShowStatus(Colors.Green, Colors.White, text);

    private void ShowError(string text)

        ShowStatus(Colors.Red, Colors.White, text);

    private void ShowStatus(Color backgroundColor, Color foregroundColor, string text)

        StatusTextBox.Background = new SolidColorBrush(backgroundColor);

        StatusTextBox.Foreground = new SolidColorBrush(foregroundColor);

        StatusTextBox.Text = text;

Compile and run the application:

It works - the customers are properly retrieved from the data service, the only problem is displaying the retrieved data. Currently, by default, the ToString method of the Customers objects is called and the result is displayed in the ComboBox control. It is enough to define a data template for the control.

Define the data template for the ComboBox control:

<ComboBox

    x:Name="CustomersComboBox"

    Width="260"

    ItemsSource="{Binding}"

    <ComboBox.ItemTemplate>

        <DataTemplate>

            <StackPanel Orientation="Horizontal">

                <TextBlock Text="{Binding CustomerID}" Margin="2,0,2,0"/>

                <TextBlock Text="{Binding CompanyName}" Margin="2,0,2,0"/>

            </StackPanel>

        </DataTemplate>

    </ComboBox.ItemTemplate>

</ComboBox>

Now it looks much better:

Add a DataGrid control to display all orders of selected customer.

Insert a DataGrid control below the ComboBox control. Set its AutoGenerateColumns property to true and also set binding for the ItemsSource property. Name it 'OrdersDataGrid':
```
<data:DataGrid
```
```
    x:Name="OrdersDataGrid"
```
```
    Height="200"
```
```
    Margin="5"
```
```
    Padding="5"
```
```
    AutoGenerateColumns="True"
```
```
    ItemsSource="{Binding}"
```
```
     />
```

Generate an event handler for the SelectionChanged event of the CustomersComboBox control:

<ComboBox

    x:Name="CustomersComboBox"

    Width="260"

    ItemsSource="{Binding}"

    SelectionChanged="CustomersComboBox_SelectionChanged"

In response for this event, query the data service for orders of the selected customer:

private void CustomersComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)

    if (CustomersComboBox.SelectedItem != null)

        Customers selectedCustomer = (Customers) CustomersComboBox.SelectedItem;

        GetOrders(selectedCustomer);

private void GetOrders(Customers customer)

    ShowWaiting("Retrieving orders...");

    dataContext.BeginLoadProperty(customer, "Orders", OnOrdersQueryComplete, null);

private void OnOrdersQueryComplete(IAsyncResult ar)

    Dispatcher.BeginInvoke(() =>

try

            OrdersDataGrid.DataContext = dataContext.EndLoadProperty(ar);

            ShowSuccess("Orders retrieved");

        catch (Exception e)

            ShowError("Error during retrieving orders: " + e);

});

Compile and run the application. Orders should be retrieved successfully:

The last 3 columns of the DataGrid controls are useless. These columns contain references to other tables, so displaying them in this way does not make sense. These columns can be collapsed (i.e. hidden without taking place in the layout):

private void OnOrdersQueryComplete(IAsyncResult ar)

    Dispatcher.BeginInvoke(() =>

try

            OrdersDataGrid.DataContext = dataContext.EndLoadProperty(ar);

            OrdersDataGrid.UpdateLayout();

           CollapseDataGridColumn(OrdersDataGrid, "Customers");

           CollapseDataGridColumn(OrdersDataGrid, "Employees");

           CollapseDataGridColumn(OrdersDataGrid, "Order_Details");

            ShowSuccess("Orders retrieved");

        catch (Exception e)

            ShowError("Error during retrieving orders: " + e);

});

private static void CollapseDataGridColumn(DataGrid dataGrid, string columnHeader)

    var column = dataGrid.Columns.Where(c => (string) c.Header == columnHeader).FirstOrDefault();

    if (column != null)

        column.Visibility = System.Windows.Visibility.Collapsed;

The DataGrid control presenting the selected customer's orders is editable, however changes are not saved in the database. Let's add this functionality.

Add a new button below the grid. Add also an event handler for the grid's RowEditEnded event:

<data:DataGrid

    x:Name="OrdersDataGrid"

    Height="200"

    Margin="5"

    Padding="5"

    AutoGenerateColumns="True"

    ItemsSource="{Binding}"

    RowEditEnded="OrdersDataGrid_RowEditEnded"

/>

<Button

    Width="100"

    HorizontalAlignment="Left"

    Margin="5,0,5,0"

    x:Name="SaveButton"

    Content="Save changes"

    Click="SaveButton_Click"

/>

In response for the RowEditEnded event of the grid control, update the data context:

private void OrdersDataGrid_RowEditEnded(object sender, DataGridRowEditEndedEventArgs e)

    dataContext.UpdateObject(OrdersDataGrid.SelectedItem);

In the Click event handler of the Save button, begin the saving operation and handle its ending:

private void SaveButton_Click(object sender, RoutedEventArgs e)

    ShowWaiting("Saving changes...");

    dataContext.BeginSaveChanges(SaveChangesOptions.Batch, OnChangesSaved, dataContext);

private void OnChangesSaved(IAsyncResult ar)

    Dispatcher.BeginInvoke(() =>

        dataContext = (NorthwindEntities)ar.AsyncState;

try

            DataServiceResponse response = dataContext.EndSaveChanges(ar);

            ShowSuccess(string.Format(

                "Changes saved, status code: {0}", response.BatchStatusCode));

        catch (Exception e)

            ShowError("Error during saving changes: " + e);

});

[Source code]