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

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.

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 call the OnGetData event method to update the contents of the grid.

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