Using WCF with SQL Azure and Telerik OpenAccess

Microsoft’s SQL Azure database offering has great 3rd party support. This week Telerik is releasing its Q3 version of its entire product line and the OpenAccess ORM will have more robust and native support for SQL Azure over what is currently available. I will expand on the example I did last week on connecting to SQL Azure by showing how to work with OpenAccess entities via WCF in a Silverlight application.

The fist thing that you have to do is create a project for your data access layer and connect to SQL Azure. I started a new class library and used the Enable Project to use ORM wizard. This is where you can specify your SQL Azure user account and credentials. I showed how to do this last week, so I will skip the steps here. (This is a new feature of Q3, in the previous version of OpenAccess, you had to use the SQL Server provider, now OpenAccess supports SQL Azure natively!)

Next we have to create a project to contain our WCF service. What we have to do next is point the Telerik Data Service Wizard to the DAL project and have it automatically create the SVC and CS files of our service for us.  The wizard will automatically create all of the CRUD methods for our entities. (In this demo I only used Customers.) In case you have not used the wizard yet, here is a walk through video on how to do that.

Telerik OpenAccess WCF Wizard Part I from Stephen Forte on Vimeo.

Now we will have two projects, one for our DAL and one for our WCF service. Now, add a Silverlight project and your solution should look like this, four projects: the DAL project, the WCF service project, the Silverlight Web project and the Silverlight project itself.

Next up we set a service reference to our WCF service and call the ReadCustomers method to get a list of all the customers and bind it to a XAML grid. (Remember that this being Silverlight, we have to do it all asynchronously.) We do this inside of a LoadData method in our form.

 1: private void LoadData()

 2: {

 3:  //ref to our service
proxy

 4:  SampleWCFServiceClient wcf = new SampleWCFServiceClient();

 5:  //register the event
handler-can move this up if you want

 6:  wcf.ReadCustomersCompleted += ReadCustomersCompleted;

 7:  //make an async
call to ReadCustomer method of our WCF service

 8:  //get only the first 100 records
(default)

 9:  wcf.ReadCustomersAsync(0, 100);

 10: }

The first thing that we do in the code above is create a reference to our WCF service in line 4 and then register an event handler to catch the asynchronous completion of the event on line 6. On line 9 we make the (asynchronous) call to ReadCustomers(). Since ReadCustomers() will process asynchronously, we will have to go to the ReadCustomersCompleted() method to catch the event. Let’s look at that here:

 1:  

 2: void ReadCustomersCompleted(object sender,
ReadCustomersCompletedEventArgs e)

 3: {

 4:  //if the filter is set use a
LINQ statement

 5:  //this can also
be done on the server via the service

 6:  if (CheckFilter.IsChecked
== true)

 7:  {

 8:  var filter = from c in e.Result

 9:  where c.Country
== "Germany"

 10:  select c;

 11:  

 12:  dataGridCustomers.ItemsSource = filter;

 13:  }

 14:  else

 15:  {

 16:  dataGridCustomers.ItemsSource = e.Result;

 17:  }

 18: }

In ReadCustomersCompleted we are doing seeing if a checkbox is checked and if so, we do some client side LINQ statements to filter on the client for only customers in Germany. (This is a holdover from a demo I did at BASTA in Germany, of course you should move your countries to a drop down list and then filter with a parameter! Better yet, filter via the WCF service on the server!) If the checkbox is not checked, we will just show all of the customers.

If you want to edit a customer (or add, etc), the Silverlight grid allows you to do this inside the grid itself. However, you have to make sure that all of your dirty records are recorded so you only send back the dirty records to your backend WCF service. (Why bother updating all of the records?)

Here is the code to build the collection on the Begin Edit of the grid. This code just adds the current customer object into our custom collection (editedCustomers) so we can loop through it later on if we are doing an update.

 1: void dataGridCustomers_BeginningEdit(object sender,

 2:  DataGridBeginningEditEventArgs e)

 3: {

 4:  //build a list of Customer that
are dirty

 5:  Customer customer = e.Row.DataContext as NorthwindWCFService.Customer;

 6:  

 7:  if (!editedCustomers.Contains(customer))

 8:  {

 9:  editedCustomers.Add(customer);

 10:  }

 11: }

Now that we have our collection of dirty customers, we have to deal with the save button. The code below is run when the user clicks on the save button, saving all dirty records. Line 6 sets up the WCF service via the proxy and line 8 registers the event. Lines 11-15 is a loop of all of the dirty customers. (I get them via the custom collection editedCustomers shown above.) Inside of the loop on line 14 we make the actual asynchronous call to the WCF service’s UpdateCustomer method passing in the object and its correct ID. While the UpdateCusotmerCompleted event will fire (since this method is called asynchronously) when the update is complete, we have nothing really in that method except some cleanup of our custom collection and a message box to the users that the update is complete.

 1: void ButtonSave_Click(object sender,
RoutedEventArgs e)

 2: {

 3:  

 4:  //the WCF service

 5:  //ref to our service
proxy

 6:  SampleWCFServiceClient client = new SampleWCFServiceClient();

 7:  //register the event
handler-can move this up if you want

 8:  client.UpdateCustomerCompleted += UpdateCustomerCompleted;

 9:  

 10:  //save only the dirty customers

 11:  foreach (NorthwindWCFService.Customer
customer in editedCustomers)

 12:  {

 13:  //call the WCF
method async to update the customer

 14:  client.UpdateCustomerAsync(customer.CustomerID.ToString(),
customer);

 15:  }

 16:  

 17: }

That is all there is too it! An add or delete is done in the same way.

Enjoy!