Create a SQL Azure CRUD Application with Telerik OpenAccess and the WCF Wizard
Posted by: the telerik blogs,
on 24 Sep 2009 |
View original | Bookmarked: 0 time(s)
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 DALs 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. Well 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.
Ill 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 dont do this, we wont 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 dont 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 lets do one last thing. Lets 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!
