Silverlight 2 Web Service Part IV - Making database calls through a ADO.NET Data Service

Jonas Fagerberg
Sep 18, 2014

22.8k
0
0
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

WebService - Part IV.zip|WebService - Part IV - Database logic in class.zip

For this tutorial you need to have the Northwind database installed on your SQL Server. If you don't have the database you can download it from the following address.

http://www.microsoft.com/Downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

The sample is created using Visual Studio 8 SP1 and .NET Framework 3.5 SP1.

This tutorial will shed some light upon how you can make calls to a database receiving and updating data using the ADO.NET Entity Data Model and an ADO.NET Data Service. We will be focusing on one table in the database, namely the Customer table. To display the data we will be using a DataGrid control; this tutorial will not go in to any depth as to how a DataGrid works, it will only be a means to display and edit data.

Creating the Silverlight application

Let's start by creating a new Silverlight application that will host our control.

Open Visual Studio 8 and create a new Silverlight project called ADO.NET Data Services.
Make sure that the Add a new ASP.NET Web project to the solution to host Silverlight option is selected. Make sure the Project Type dropdown is set to ASP.NET Web Application Project, then click OK.

Two projects will be added to the solution. The ADO.NET Data Services project is the Silverlight application project and the ADO.NET Data Services.Web project is the ASP.NET application hosting the Silverlight application. The ADO.NET Data Services.Web project contains one .aspx and one .html page, which one you choose to use as the container is up to you; in this example however we will use the ADO.NET Data ServicesTestPage.aspx page, so make sure it is set as the start page.

The page.axml page is the main page in the Silverlight application, and it should contain the basic framework for hosting Silverlight controls. We will later use this page to test our ADO.NET Data Service.

Creating the Silverlight page

We will be adding three buttons inside a StackPanel and a DataGrid to accommodate the data. We will be using a DataGrid because it is an efficient way to display and modify data. We will however not go into any details concerning the DataGrid control. Switch to the Page.xaml page.

Let's begin by defining two rows and the size of the Grid control.

Width="800" Height="400">
<Grid x:Name="LayoutRoot" Background="White">
    <Grid.RowDefinitions>
        <RowDefinition Height="*"></RowDefinition>
        <RowDefinition Height="7*"></RowDefinition>
    </Grid.RowDefinitions>
</Grid>

Next we will add the buttons inside the StackPanel control. We will need three buttons: Get Data, Insert Data and Delete Data; we don't need a button for updates because we will do the updates from an event in the DataGrid. Add the StackPanel below the RowDefinitions nodes. Be sure to add the click event for each of the buttons and name the events OnGetData, OnInsertData and OnDelete Data.

</Grid.RowDefinitions>
<StackPanel Orientation="Horizontal" Margin="5,10,0,0">
    <Button Content="Get Data" Margin="5" Click="OnGetData"></Button>
    <Button Content="Insert Data" Margin="5" Click="OnInsertData"></Button>
    <Button Content="Delete Data" Margin="5" Click="OnDeleteData"></Button>
</StackPanel>

To gain access to the DataGrid we need to add a reference to the System.Windows.Controls.Data assembly. To add the reference, right click on the References node of the Silverlight project in the Solution Explorer and select Add Reference. Select the assembly in the list and click the OK button.

When the reference has been added to the project we need to add an assembly reference in the Page.xaml page. Add the following declaration above the Width statement of the UserControl.

xmlns:data="clr-namespace:System.Windows.Controls;assembly=
System.Windows.Controls.Data"

Now we are ready to add the DataGrid to the page. Make sure that the DataGrid is placed in the second row and that the AutoGenerateColumns is set to True so that it will generate the columns for us. Also make sure that the data will be bound to the control by setting the ItemsSource to {Binding}.

<data:DataGrid Name="customerDataGrid" Grid.Row="1" Margin="10" AutoGenerateColumns="True" ItemsSource="{Binding}"></data:DataGrid>

The page should now look like this:

To avoid runtime errors we need to add the Click event methods for the buttons. The easiest way to do this is to switch to Page.xaml and right click on the Click event method name and select Navigate to Event Handler in the context menu. Because we will be writing code for reading, writing and updating we will wrap the event methods in regions called Get Data, Insert Data, Delete Data and Update Data inside which we will add all the code used in retrieving and updating data in the database.

#region Get Data
private void OnGetData(object sender, RoutedEventArgs e)
{
}
#endregion

#region Insert Data
private void OnInsertData(object sender, RoutedEventArgs e)
{
}
#endregion

#region Delete Data
private void OnDeleteData(object sender, RoutedEventArgs e)
{
}
#endregion

#region Update Data - Committing changes when a cell has been edited
#endregion

Creating the Data Service

To create a functioning data service we need to add two objects to the ADO.NET Data Services.Web project. The first object is an ADO.NET Entity Data Model that will be our link to the database, it is through this object that we can receive and update data in the database tables. The second object is an ADO.NET Data Service that is the service handling the calls to the database.

Adding the ADO.NET Entity Data Model

We need to specify which tables and other database objects we want access to through our ADO.NET Entity Data Model.

Right click on the ADO.NET Data Services.Web project and select Add-New Item in the context menu.
Select ADO.NET Entity Data Model in the dialog.
Rename it DatabaseModel.edmx in the textbox and click Add.
Make sure that Generate from database is selected and click the Next button.
Select the Northwind database from the dropdown, if it's not present click the New Connection button to add it. Note the name (NorthwindEntities) of the entity connection added to the Web.Config in the textbox at the bottom of the dialog. Click the Next button.
Select all the tables displayed in the dialog. This will enable us to access all tables from our service. Note the model namespace name in the text box at the bottom of the dialog. Click the Finish button.
A page named DatabaseMode.edmx is created along with all the references and the code behind for the page. Close this page, we won't need it open. Looking in the Solution Explorer we can see that the DatabaseModel.edmx and its code behind file have added. The code behind defines a proxy class that we will use when defining the service.

Adding the ADO.NET Data Service

The next step is to add the service that will broker the calls to the database through the ADO.NET Entity Data Model.

Right click on the ADO.NET Data Services.Web project and select Add-New Item in the context menu.
Select ADO.NET Data Service in the dialog.
Rename it DatabaseService.svc in the textbox and click Add.
All the necessary references and objects have been added to the Solution Explorer. We should now have one service object named DatabaseService.svc and its code behind page.
Open the DatabaseService.svc.cs page; we need to make a couple of modifications to it. The first thing you can do is to delete all commented out code. The next step is to let the service know what kind of objects we want the service to handle. In our case it is the NorthwindEntities proxy class that was generated when we added the DatabaseModel object.

public class Service : DataService< NorthwindEntities >
The next thing we need to do is to add an access rule for the services' entity that we specified in the former step.

config.SetEntitySetAccessRule("*", EntitySetRights.All);
The full code for the class in the DatabaseService.svc.cs page is as follows. This
public class Service : DataService< NorthwindEntities >
{
     public static void InitializeService(
         IDataServiceConfiguration config)
     {
         config.SetEntitySetAccessRule("*", EntitySetRights.All);
     }
}

If all has gone well you should now have a working service; let's try it out and see if it works.

Right click on the DatabaseService.svc object in the Solution Explorer and select Browse With and select Internet Explorer in the list of available browsers. Click the Browse button.
Write down or copy the path to the service, you'll need it later.

http://localhost:57498/DatabaseService.svc/
The service should display the available tables. Close the browser window when you have finished browsing the database contents.

Reading data from the database

Now that the database connection objects have been added to the ADO.NET Data Services.Web project it's time to put the service into use. To do this we need to write a bunch of code in the Page.xaml.cs page, so let's go ahead and open it.

The end result we are striving for is to display a subset of the data in the Customer table.

The UK customer result set returned from the database.

But before we start to write code we need to add a reference to the service we created earlier.

Right click on the ADO.NET Data Services project and select Add Service Reference in the context menu.
Click on the Discover button in the dialog. This should locate and display the service.
Select DatabaseService.svc in the list and name it ADOProxy in the textbox at the bottom of the dialog before you click the OK button. This will add a proxy object and all the necessary references in the Solution Explorer.

Now that the proxy has been created we need to add two using statements. The first (System.Data.Services.Client) is needed to query the database, and the second (System.Collections.ObjectModel) is needed to get access to the ObservableCollection. ObservableCollection objects keep track of changes to the collection and make it possible to act upon those changes. This type of collection is very well suited when handling database records.

using System.Data.Services.Client;
using System.Collections.ObjectModel;

Because we want to be able to display and modify many records we will need some kind of collection to store those records. The ObservableCollection is a good choice because it keeps track of the changes made to it and triggers events that we can use. It also updates the content of the control it is bound to. So if the collection is bound to a DataGrid control when we change one of its records, the DataGrid immediately will reflect the change. Let's go ahead and add a collection and while we are at it we might just as well add a proxy object that we can use to query the database. Add the two fields at the beginning of the class. Note that the collection is a generic collection that is defined by the class we assign to it, in our case it is the Customer class from the proxy.

ObservableCollection<ADOProxy.Customers> ocCustomers;
ADOProxy.NorthwindEntities proxy;

Next we will add code t handle the Page_Loaded event. In which we will add crucial code for the DataGrid and the proxy. When adding events you can let Visual Studio create the event methods for you by pressing TAB on the keyboard after writing +=.

Locate the Page constructor and add the following code after the call to the InitializeComponent method. The auto generated code is more bulky; to improve readability I have shortened it.

this.Loaded += Page_Loaded;

This should yield an event method named Page_Loaded. Delete the exception code that is placed in the method by default. The first line of code we will put into the Page_Loaded event method is an instantiation of the proxy field. We will need this instance to call the database through the service.

void Page_Loaded(object sender, RoutedEventArgs e)
{
proxy = new ADOProxy.NorthwindEntities(new
Uri("http://localhost:57498/DatabaseService.svc/"));
}

The OnGetData button event

Now that we have a proxy object we can begin calling the database through the service. We will begin by writing a method for receiving a result set from the Customers table in the Northwind database. We have to bear in mind that all calls in Silverlight to services are asynchronous and therefore need a callback method. We will write a query returning all customers that reside in the UK.

To do this we'll add a DataServiceQuery collection called query and fill it using LINQ to query the database. The query will select all customers that reside in the UK. Note that the DataServiceQuery is a generic collection that needs a class definition to determine what kind of objects it will store, in our case it will be objects of the Customers class in the ADOProxy.

DataServiceQuery<ADOProxy.Customers> query =
(DataServiceQuery<ADOProxy.Customers>)
(from c in proxy.Customers where c.Country == "UK" select c);

Next we will need to call the asynchronous BeginExecute method on the proxy object. This method is defined by the generic class that it declared with; in our case it will be the Customers class in the ADOProxy. It also takes three parameters: the first is the absolute path to the service we want to call, you can use the AbsoluteUri property of the query object. The second parameter is the name of the callback method that will be called when the asynchronous call to the service has completed. The third parameter is object state that you can use to pass state information to the BeginExecute method. We will return to the callback method shortly.

proxy.BeginExecute<ADOProxy.Customers>(new
Uri(query.RequestUri.AbsoluteUri), GetDataCallback, null);

The complete code for the OnGetData event method is as follows.

private void OnGetData(object sender, RoutedEventArgs e)
{
    DataServiceQuery<ADOProxy.Customers> query =
        (DataServiceQuery<ADOProxy.Customers>)
        (from c in proxy.Customers where c.Country == "UK" select c);

proxy.BeginExecute<ADOProxy.Customers>(new
Uri(query.RequestUri.AbsoluteUri), GetDataCallback, null);
}

The GetDataCallback callback method

To handle the result from the asynchronous call to the service we need to provide a callback method that has an IAsyncResult parameter. It is this parameter that will hold the returned data. We will also want to implement a try…catch block handling errors that might arise from the call; we will only implement a rudimentary error handling in this example, you however might want to implement a more detailed error handling. Write the method inside the Get Data region we created earlier.

void GetDataCallback(IAsyncResult iar)
{
    try
    {
    }
    catch
    {
    }
}

We need to declare a DataServiceQuery collection defined as taking Customer objects that will hold the returned result set sent back through the AsynchState property of the IAsyncResult parameter. To be able to enumerate the objects in the collection we need to create an IEnumerable collection that hold the enumerated objects created when the asynchronous call is ended when calling the ToList method on the asynchronous EndExecute method. Place the code inside the try block.

DataServiceQuery<ADOProxy.Customers> query =
(DataServiceQuery<ADOProxy.Customers>)iar.AsyncState;
IEnumerable<ADOProxy.Customers> results =
proxy.EndExecute<ADOProxy.Customers>(iar).ToList();

Because we are executing on a background thread we need to call the BeginInvoke method in the Deipatcher object to update the user interface. If we don't do this we will encounter errors when trying to update the DataGrid control. The call is an asynchronous call into the user interface thread. We will use a lambda expression to create an anonymous function to create a delegate that will be executed by the BeginInvoke method. Inside the function definition we will create an instance of the customers ObservableCollection that will hold the customers returned from the database. We use this type of collection because it keeps track of changes made to its objects and update the user interface accordingly. We use an for…each loop to populate the collection. When the collection is populated we assign it to the DataContext property of the customerDataGrid control.

void GetDataCallback(IAsyncResult iar)
{
    try
    {
        DataServiceQuery<ADOProxy.Customers> query =
           (DataServiceQuery<ADOProxy.Customers>)iar.AsyncState;
        IEnumerable<ADOProxy.Customers> results =
           proxy.EndExecute<ADOProxy.Customers>(iar).ToList();

          Dispatcher.BeginInvoke(() =>
          {
              customers = new ObservableCollection<ADOProxy.Customers>();
              foreach (ADOProxy.Customers customer in results)
              {
                   customers.Add(customer);
              }
              customerDataGrid.DataContext = customers;
        });
    }
    catch
    {
    }
}

Testing the Get Data button

Now we are ready to test the database call to get the UK customers. Press F5 on the keyboard and when the page is loaded click on the Get Data button. The result should look something like the images below.

Note that the Orders and CustomerDemographics columns have ugly values in them; this is because the collection doesn't know how to handle these kinds of objects. Let's tidy up the result in the grid and simply hide these two columns. To do this we need to add an event method for the AutoGereratingColumn event. Switch to the Page.xaml.cs file and find the Page_Loaded event method and in it put the following event declaration.

myDataGrid.AutoGeneratingColumn += myDataGrid_AutoGeneratingColumn;

Then write the following code in the event method generated. This will stop the grid from generating the two columns we don't want to display.

if (e.PropertyName == "Orders" || e.PropertyName == "CustomerDemographics")
e.Cancel = true;

The full code looks like this:

void Page_Loaded(object sender, RoutedEventArgs e)
{
proxy = new ADOProxy.NorthwindEntities(new
Uri("http://localhost:57498/DatabaseService.svc/"));

customerDataGrid.AutoGeneratingColumn +=
customerDataGrid_AutoGeneratingColumn;
}

void customerDataGrid_AutoGeneratingColumn(object sender,
     DataGridAutoGeneratingColumnEventArgs e)
{
     //To get rid of ugly columns
     if (e.PropertyName == "Orders" || e.PropertyName ==
          "CustomerDemographics")
              e.Cancel = true;
}

Press F5 on the keyboard and when the page is loaded click on the Get Data button. The result should now reflect the absence of the two columns if you scroll to the right in the grid.

Inserting data into the database

Now that we have some rows to play around with we'll add the possibility to insert new rows into the ObservableCollection and send the update to Customer table in the database; Navigate to the OnInsertData event method. We will use hard coded data for the new customer, but you could just as easily add a couple of textboxes and retrieve the values from them.

The OnInsert button event

Start by adding a try…catch block to handle unforeseen errors. Create a new customer object inside the try block and assign values to some of its properties.

#region Insert Data
private void OnInsertData(object sender, RoutedEventArgs e)
{
     try
     {
          ADOProxy.Customers newCustomer = new ADOProxy.Customers()
          {
              CustomerID = "JF",
              CompanyName = "The Company",
              Country = "UK"
          };
     }
     catch
     {
     }
}

The next step is to call the auto generated AddToCustomers method on the proxy object; this will prepare the NorthwindEntity for the insert. After the new customer has been saved we need to call the asynchronous method BeginSaveChanges that starts the insert call to the service. The call will be ended in the callback method that we need to supply in the parameter list. Let's name the callback method OnSaveChangesComplete; we will be calling this method from other methods as well.

private void OnInsertData(object sender, RoutedEventArgs e)
{
    try
    {
        ADOProxy.Customers newCustomer = new ADOProxy.Customers()
        {
           CustomerID = "JF",
           CompanyName = "The Company",
           Country = "UK"
        };
          proxy.AddToCustomers(newCustomer);
          proxy.BeginSaveChanges(OnSaveChangesComplete, null);
    }
    catch
    {
    }
}

The OnSaveChangesComplete callback method

Now we need to create the callback method; write it in the Insert Data region. This callback method will be called from several methods later on. The reason we reuse this method is that it ends the asynchronous call and updates the user interface. The OnSaveChangesComplete method takes one IAsyncResult object that will hold any return values.

In the method we need to call the asynchronous EndSaveChanges method on the proxy object to end the call to the service.

void OnSaveChangesComplete(IAsyncResult iar)
{
    try
    {
        proxy.EndSaveChanges(iar);
        //To refresh the data in the grid.
        Dispatcher.BeginInvoke(() =>
        {
           OnGetData(null, null);
        });
    }
    catch { }
}

Testing the Insert Data button

Now we are ready to test the database call to insert a new customer into the Customer table. Press F5 on the keyboard and when the page is loaded click on the Get Data button. When the customer list is displayed click the Insert Data button, this should add the new customer into the table and update the DataGrid. The result should look something like the image below.

Deleting data from the database

Now that we have seen how we can add a row, let's write some code to delete rows. Navigate to the OnDeleteData click event method for the Delete Data button.

The OnDeleteData button event

Start by adding a try…catch block to handle unforeseen errors. We need to check if a row in the DataGrid is selected, if so, we can proceed. Create a new customer object inside the try block and assign the selected customer from the selected DataGrid row. After the customer has been obtained we call the Deleteobject method on the proxy object passing in the customer object as a parameter. This will not delete the object only signal that the object should be deleted the next time the asynchronous BeginSaveChanges method of the proxy object is called. We pass in the method name of the callback method to the BeginSaveChanges method; here we reuse the OnSaveChangesComplete method that we wrote earlier.

#region Delete Data
private void OnDeleteData(object sender, RoutedEventArgs e)
{
    try
    {
        if (customerDataGrid.SelectedItem != null)
        {
           ADOProxy.Customers customer =
               (ADOProxy.Customers) customerDataGrid.SelectedItem;
           proxy.DeleteObject(customer);
           proxy.BeginSaveChanges(OnSaveChangesComplete, null);
        }
    }
    catch
    {
    }
}
#endregion

Testing the Delete Data button

Now we are ready to test the database call to delete a customer from the Customer table. Press F5 on the keyboard and when the page is loaded click on the Get Data button. Select a customer from the list, let's select the one we just added and click the Delete Data button, this should add the new customer into the table and update the DataGrid. The row should be removed from the DataGrid and the database; to be sure that the row actually has been removed from the database you can reload the page and click the Get Data button.

The list before the Delete Data button has been clicked.

The list after the Delete Data button has been clicked and the page refreshed

Updating data in the database

Here we could make it easy on ourselves and add an Update Data button, but that would not reflect the reality of our example; we off course want to update data directly when it has been changed in the grid. This poses a problem since there's no event declared on the grid for this purpose. There was one in the beta version, but it has been removed in this release. So until the day that Microsoft decides to implement it again we will have to make do with a workaround.

For this workaround to work we need to add three events and store the row that will be updated at some point to retrieve it at the moment when the update is being made. Let's take it step by step.

Navigate to the Page_Loaded event method. And add the following event declarations at the end of the method. Place the resulting event methods in the Update Data region we created earlier.

customerDataGrid.PreparingCellForEdit +=
customerDataGrid_PreparingCellForEdit;
customerDataGrid.BeginningEdit += customerDataGrid_BeginningEdit;

Navigate to the Update Data region we added earlier in the Page.xaml.cs file.

Now we need to create an ADOProxy.Customer field named item at the top of the region. This field will be assigned the current customer when the editing begins in a cell; this ensures that we have the updated value of the customer when we update the data. If we don't save the customer in this fashion we won't be able to update the customer properly when pressing Enter on the keyboard because the row already has changed to the next row. Locate the customerDataGrid_BeginningEdit event method and assign the item field the current customer from the grid; remember to cast the object in the SelectedItem property to an ADOProxy.Customers type.

#region Update Data - Committing changes when a cell has been edited
ADOProxy.Customers item;
void customerDataGrid_BeginningEdit(object sender,
DataGridBeginningEditEventArgs e)
{
item = (ADOProxy.Customers)customerDataGrid.SelectedItem;
}
#endregion

The next thing we need to do is to activate the LostFocus of the EditingElement of the grid row in the customerDataGrid_PreparingCellForEdit event method. This ensures that the LostFocus event is triggered when the edit is complete, and in this event we can update the customer in the database.

void customerDataGrid_PreparingCellForEdit(object sender,
DataGridPreparingCellForEditEventArgs e)
{
e.EditingElement.LostFocus += EditingElement_LostFocus;
}

The last thing we need to do to ensure a successful update is to call the UpdateObject method on the proxy object passing in the item object we saved earlier, and to call the asynchronous method BeginSaveChanges on the proxy object passing in the name of the callback method, OnSaveChangesComplete, that we wrote earlier.

void EditingElement_LostFocus(object sender, RoutedEventArgs e)
{
    //Fires when committing changes to a cell
    ADOProxy.Customers customer = item;
    proxy.UpdateObject(customer);
    proxy.BeginSaveChanges(OnSaveChangesComplete, null);
}

Testing updating data

Now we are ready to test the database call to update a customer in the Customer table. Press F5 on the keyboard and when the page is loaded click on the Get Data button. If the grid doesn't already contain the new customer then add it by clicking on the Insert Data button. Edit the customers' address. To make sure that the address actually has been updated refresh the page and click on the Get Data button; the address should still contain the address you wrote.

The newly inserted customer before editing

The customer after editing and refreshing

Recommended Free Ebook

Printing in C# Made Easy

Download Now!