Create a SQL Azure CRUD Application with Telerik OpenAccess and the WCF Wizard

Over the past few weeks I have showed how to use Telerik OpenAccess with the WCF Wizard. I think that this wizard is a crucial piece of technology since I hate to write plumbing code myself, it takes me too long and I usually make mistakes.

I wish that SQL Azure would provide RESTful and WCF services wrapped around your database tables and views with just a check box in the Azure management page. But alas, I can dream. Until that day arrives, you will have to code the services yourself. So I decided to do it with OpenAccess and WCF (and of course the Wizard.)

First you need to get some data up in SQL Azure. Refer to my post from a few weeks ago as how to do that. Next you have to create a data access layer with Telerik OpenAccess. But you have to create your DAL against a local SQL Server 2005/2008 database with the same schema as the one up in SQL Azure. You have to do this because SQL Azure does not support querying of the schema. After you map your tables to classes (I will do just Customers for this demo), you have to go in and change the connection string in your DAL’s app.config to use SQL Azure instead of the database you used for the mapping:

 1:  <connection id="Connection1">

 2:  <databasename>Northwind_Lite</databasename>

 3:  <servername>tcp:tpzlfbclx1234.ctp.database.windows.net</servername>

 4:  <integratedSecurity>False</integratedSecurity>

 5:  <backendconfigurationname>mssqlConfiguration</backendconfigurationname>

 6:  <user>Stevef</user>

 7:  <password>gomets!</password>

 8:  </connection>

Now you have to create the WCF service via the wizard (if you forgot how to do that, watch this video). This will be done in a separate project to achieve a full separation of concerns.

After you create the WCF service via the Wizard, you can go ahead and create a Silverlight client. Your solution set up should consist of a DAL project, a WCF service project, a Silverlight web project, and a Silverlight client project.

Ok, XAML time. (In my head I am saying that to the tune of Hammer Time, but I digress….)  I will create a grid that will bind to the CompanyName, ContactName, City, and Phone fields of the Customer table. The grid will do most of the magic for us. I will also add a “Refresh” button as well as a “Save” button. The Refresh button will have the same LoadData() method as in all of my previous blog posts. We’ll talk about Save in a minute.

 1: <data:DataGrid x:Name="dataGridCustomers" Grid.ColumnSpan="4" ItemsSource="{Binding}">

 2:  <data:DataGrid.Columns>

 3:  <data:DataGridTextColumn Binding="{Binding
Path=CompanyName}"

 4: Header="Company
Name"></data:DataGridTextColumn>

 5:  <data:DataGridTextColumn Binding="{Binding
Path=ContactName}" 

 6: Header="Contact
Name"></data:DataGridTextColumn>

 7:  <data:DataGridTextColumn Binding="{Binding
Path=City}"

 8:  Header="City"></data:DataGridTextColumn>

 9:  <data:DataGridTextColumn Binding="{Binding
Path=Phone}"

 10:  Header="Phone"></data:DataGridTextColumn>

 11:  </data:DataGrid.Columns>

 12: </data:DataGrid>

If we run our application, you can see that the grid works as advertised, fetching data from SQL Azure.

I’ll go in and edit the city for the first record to say “Hong Kong.” In order to facilitate this, we need to handle the BeginEdit event of the grid. During this event handler shown below, we will stuff a Customer object into our own private collection so we know that the entity is dirty. (If we don’t do this, we won’t know which items are dirty and would have to update all of them, a big waste of resources.) I do this on line 9 (after a check to see if it already in my collection.)

 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 we have to handle the button click. (When the user hits save.) The user can hit save after editing the entire page (or after each row if they prefer, but find me a user who wants to do that.) The code below calls the WCF service we created with the wizard asynchronously. In this case we call the service for each customer in our collection (loop is from lines 8-111, the call to the WCF service is online 10).

 1:  

 2: void ButtonSave_Click(object sender,
RoutedEventArgs e)

 3: {

 4:  SampleWCFServiceClient client = new SampleWCFServiceClient();

 5:  

 6:  client.UpdateCustomerCompleted += UpdateCustomerCompleted;

 7:  //save only the
dirty customers

 8:  foreach (NorthwindWCFService.Customer
customer in editedCustomers)

 9:  {

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

 11:  }

 12:  

 13: }

In the code directly above, we registered an event, UpdateCustomerCompleted, to fire when each Customer is done updating. In theory we don’t need to do anything as far as the data is concerned, however, we have to clean up our collection of dirty Customers (line 8) as well as set an internal counter to 0 (line 7). This will give us a clean slate when we start editing again. We will also use the opportunity to show a message box (line 10) to the user that the data was updated. (Yea, yea I know I need error handling, etc. This is a demo!! :) ) We do this clean up only after all Customers have been edited and their async calls have been caught. We do this with our counter (line 3 and 5), comparing our count to the number of dirty records. Old school, but effective.

 1: void UpdateCustomerCompleted(object sender,
UpdateCustomerCompletedEventArgs e)

 2: {

 3:  this.updatedCount++;

 4:  

 5:  if (updatedCount
== editedCustomers.Count)

 6:  {

 7:  updatedCount = 0;

 8:  editedCustomers.Clear();

 9:  

 10:  MessageBox.Show("All Customers
have been updated successfully!",

 11:  "Updating
Data", MessageBoxButton.OK);

 12:  }

 13: }

You can see the results here.

So let’s do one last thing. Let’s add a client side filter. This will be a dirt simple one, using LINQ to Objects to filter for only customers in Germany. (Of course the filter should be dynamic, etc. Also you may want to move this functionality to the server via your WCF Service.)

In the XAML page, I provide a checkbox that says “Show only Germany.” When this is checked we will filter our results with a LINQ statement. When it is unchecked, we will show all the records.

Our LoadData() method calls the WCF service and registered an event, ReadCustomersCompleted. LoadData() is called on the page load, refresh button click, and check box click events.

In past demos, this just took the result and assigned it to the ItemSource property of the grid. In this case we will check to see if our filter check box is checked (Line 6) and if so, we will perform a LINQ query (Lines 8-10) and assign the result to the gird (Line 12). If the check box is not checked, we perform no filter (line 16).

 1: void ReadCustomersCompleted(object sender, 

 2:  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: }

That is it! Of course you can create nice add and delete dialogs as well, it is just as easy.

Enjoy!