# Friday, September 18, 2009

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.

image

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.

image

 

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.

image

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.

image

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.

image

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!

posted on Friday, September 18, 2009 6:37:59 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Thursday, September 17, 2009

As I showed yesterday, the new version of the Telerik OpenAccess WCF Wizard now allows you to work with any version of OpenAccess. Here is a video of how to do that by .NET Ninja in training Peter Bahaa.

Telerik OpenAccess WCF Wizard: How-to Video #6-Auto detection of OpenAccess Version from Stephen Forte on Vimeo.

posted on Thursday, September 17, 2009 4:19:58 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Wednesday, September 16, 2009

When the Telerik OpenAccess WCF Wizard shipped last month, it only worked with the Q2 version of OpenAccess. The problem is that Telerik makes internal builds of a product available in-between releases so developers can play around with new bits faster-and the OpenAccess WCF Wizard did not work with them. Telerik Internal Builds fix bugs out of band so lots of folks us them. The feedback we got on the WCF Wizard is that the wizard should work with any version of OpenAccess, including internal builds.

image

The latest version of the Telerik OpenAccess WCF Wizard does just that. Now when you open the Wizard and you attempt to connect to a data access layer built by a version of OpenAccess that the Wizard does not understand, a versions store dialog will come up as shown below. The dialog will tell you what version of OpenAccess the DAL was created with and you will have to navigate to the Telerik.OpenAccess.dll file and add it to the versions store. You will also have to exit the wizard and come back in. Once you have done so, you are free to use the wizard with that version of OpenAccess. You will also be allowed to use older versions that are saved in the store.

image

Enjoy!

posted on Wednesday, September 16, 2009 2:46:40 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Tuesday, September 15, 2009

The following video shows how to use the updated Telerik OpenAccess WCF Wizard with ATOMPub services via the WCF REST Starter Kit. The video is done by .NET Ninja in training Peter Bahaa and uses the same ATOMPub project I showed yesterday on my blog. Enjoy!

Telerik OpenAccess WCF Wizard: How-to Video #5: ATOMPub Property Selection from Stephen Forte on Vimeo.

posted on Tuesday, September 15, 2009 1:12:44 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Monday, September 14, 2009

Last week I showed how to use the Telerik OpenAccess WCF Wizard to automatically create a feed in Atom Syndication Format using the WCF REST Starter Kit’s Visual Studio project templates. Based on the feedback we have gotten from the community on the ATOMPub projects, Telerik has refreshed the WCF Wizard (go download it now) to enhance the ATOMPub projects.

Let’s take a step back first. The Atom Syndication Format is for feeds, similar to RSS. So when you run the project that we looked at in my blog last week in default IE 8 (or IE 7) feed view, it would look something like this. This is no different from what my feeds look like from CNN and ESPN, etc.

image

But how did IE decide what to put as the title or the description of each item in the feed? (And since we are just exposing OpenAccess entities as feeds, the real question is which properties (or database fields at the end of the day) did IE choose for you?)

If you go back to IE settings and turn off feed view (which I always recommend when working with Astoria, REST Collection, and ATOMPub services) you can view the raw XML.

image

As shown in raw XML ATOM feed below in lines 3 and 4 you can see that the Company Name and Contact name were randomly placed into those fields (and that is how IE uses that data to display.) Well it was not randomly selected, I just hard coded those two fields in my demo. For applications these two fields are usually ignored. But it would be nice to have them set to something useful.

   1:  <entry>
   2:    <id>ALFKI</id> 
   3:    <title type="text">Alfreds Futterkiste</title> 
   4:    <summary type="text">Maria Anders</summary> 
   5:    <updated>2009-09-14T08:16:30Z</updated> 
   6:   
   7:    <category term="customers" /> 
   8:  - <content type="text/xml">
   9:  
  10:    <Address>Obere Str. 57</Address> 
  11:    <City>Berlin</City> 
  12:    <CompanyName>Alfreds Futterkiste</CompanyName> 
  13:    <ContactName>Maria Anders</ContactName> 
  14:    <ContactTitle>Sales Representative</ContactTitle> 
  15:    <Country>Germany</Country> 
  16:    <CustomerID>ALFKI</CustomerID> 
  17:    <Fax>030-0076545</Fax> 
  18:    <Phone>030-0074321</Phone> 
  19:    <PostalCode>12209</PostalCode> 
  20:    <Region i:nil="true" /> 
  21:    </Customer>
  22:    </content>
  23:    </entry>

 

The latest version of the WCF Wizard will now give you a dialog, so you don’t have to alter the code at all. As you can see from the screen shot below, the wizard now gives us a drop down so we can choose which property to expose in feed view as the title and description. So just to be different, I will choose the company name as the title and the Address as the description.

image

 

When we look at the raw ATOMPub XML you will see Company Name and Address in lines 3 and 4.

   1:  <entry>
   2:    <id>ALFKI</id> 
   3:    <title type="text">Alfreds Futterkiste</title> 
   4:    <summary type="text">Obere Str. 57</summary> 
   5:    <updated>2009-09-14T08:47:12Z</updated> 
   6:     
   7:    <category term="customers" /> 
   8:  - <content type="text/xml">
   9:  
  10:    <Address>Obere Str. 57</Address> 
  11:    <City>Berlin</City> 
  12:    <CompanyName>Alfreds Futterkiste</CompanyName> 
  13:    <ContactName>Maria Anders</ContactName> 
  14:    <ContactTitle>Sales Representative</ContactTitle> 
  15:    <Country>Germany</Country> 
  16:    <CustomerID>ALFKI</CustomerID> 
  17:    <Fax>030-0076545</Fax> 
  18:    <Phone>030-0074321</Phone> 
  19:    <PostalCode>12209</PostalCode> 
  20:    <Region i:nil="true" /> 
  21:    </Customer>
  22:    </content>
  23:    </entry>

 

If you view the service in feed view in IE, you will now see the Company Name and Address as the title and description.

image

Of course this did not effect our Silverlight front end since the application just ignored those fields. Those fields are available to the application, but we did not use them (we only used two fields (company name and contact name from the main XML tree.)

image

posted on Monday, September 14, 2009 9:08:51 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Friday, September 11, 2009

When SQL Data Services (now SQL Azure) released its first CTP it did not look anything like SQL Server: there were no tables, stored procedures, views, etc. With the new CTP, SQL Azure embraces SQL Server in the sky and supports the relational model, including stored procedures. This is good since there are millions of lines of stored procedures out there in production today and migrating them to SQL Azure is pretty easy.

I decided to give a simple stored procedure a test drive. I opened up SQL Management Studio, started a new query, entered in my SQL:

   1:  CREATE Procedure sel_CustomerOrder   
   2:  @CustomerID char(5)   
   3:  AS
   4:  SELECT o.OrderID, o.OrderDate,  p.ProductName,   
   5:       (od.UnitPrice*od.Quantity)-((od.UnitPrice*od.Quantity)*od.Discount) as TotalCost   
   6:  FROM orders o   
   7:       inner join [order details] od   
   8:              on o.OrderID=od.OrderID  
   9:       inner join products p  
  10:              on od.ProductID=p.ProductId  
  11:  WHERE CustomerID=@CustomerID
  12:  ORDER BY o.OrderID

 

I hit F5 and all was right with the world. So I decided to try it out:

Exec sel_CustomerOrder @CustomerID='ALFKI'

And got the expected results.

image

You can now use this stored procedure in all the applications that you connect to since SQL Azure supports standard ADO.NET connection strings. Pretty cool.

posted on Friday, September 11, 2009 12:48:44 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Thursday, September 10, 2009

I like the idea of a database in the cloud. We have sort of been doing it for years, connecting to a SQL Server database over 1433. With SQL Azure, we take this one level further and let Azure worry about the hardware. So we don’t have to worry about scaling out with the bandwidth, RAIDs, etc. 

Last week I showed the basics on how to migrate data from a SQL Server 2008 database to SQL Azure. Yesterday I showed using SQL Azure with Telerik OpenAccess  and the WCF REST Toolkit. Today I will show how to build a simple REST based application using ADO.NET Data Services (aka Astoria.)

To get started we need a SQL Azure database. See my previous blog post about the CTP and getting data into your SQL Azure database. Once you have a SQL Azure database all set up let’s get to work.

The next thing we need to do is to create a new Web project and create our Entity Framework data model. I’ll go ahead and create an Entity Data Model against my local SQL Server 2008 Northwind database that has the same schema as my SQL Azure one. This is because SQL Azure and the Entity Framework chokes on the designer (or at least my version!) I will map:

  • Customers
  • Orders
  • Order Details

Now that my EDM is all set up, I will go in and change the connection string in my web.config to use SQL Azure. Here is my new connection string:

<add name="NorthwindEntities" 
connectionString="metadata=res://*/Northwind.csdl
|res://*/Northwind.ssdl|
res://*/Northwind.msl;
provider=System.Data.SqlClient;
provider connection string=&quot;Data Source=tcp:tpzlfbclx123.ctp.database.windows.net;
Initial Catalog=Northwind_Lite;
Integrated Security=False;UID=Stevef;PWD=GoMets!;
MultipleActiveResultSets=False&quot;"
providerName="System.Data.EntityClient"/>

You have to manipulate the EF connection string and put in the SQL Azure server name of your CTP in the “Data Source” and put in the database name in the Initial Catalog, turn off integrated security and put in the UID/PWD from the CTP. I set MARS set to false since SQL Azure does not support MARS.

Now let’s create the Astoria Service. Add a new “ADO.NET Data Service” to your project. I named mine NwindRestService.

image

Astoria can’t make it any easier for you to get the service up and running. All you need to do is set up the name of your EDM in line 2, in our case it was NorthwindEntities and also set the access permissions on line 8. I just uncommented the generated line and put in an “*” so all of my entities will inherit the AllRead access rule. With that we are good to go!

   1:  //Enter the name of your EDM (NorthwindEntities)
   2:  public class NwindRestService : DataService<NorthwindEntities>
   3:  {
   4:      public static void InitializeService(IDataServiceConfiguration config)
   5:      {
   6:          //Must set up the AccessRule, here I allow read only access
   7:          //to all entities. I can also do this one by one.
   8:          config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
   9:      }
  10:  }

 

For the reality check, let’s run the service in the browser, being a RESTful service, Astoria will allow you to browse all of the Customers by typing in this URL:

http://localhost:1075/NwindRestService.svc/Customers

We should see this:

image

I also edited my first row in Northwind (ALFKI) to say “SQL Azure” at the end of the customer name so I know I am working with the SQL Azure and did not mess up my connection strings. That is it, you now have a RESTful service that is hooked up to SQL Azure.

The hard part is over. Now let’s build a simple ASP.NET client to consume the RESTful data.

First you have to set a reference to your service. This will give you a proxy to write some LINQ (to Astoria) code against.

image

 

Next we will create a simple ASP.NET GridView control and bind some data to it on the page load event. (Sure we can do a lot more, but this is just to get our feet wet with SQL Azure.)

   1:  //the address of our service
   2:  Uri url = new Uri("http://localhost:1075/NwindRestService.svc/", UriKind.Absolute);
   3:  //a ref to our proxy 
   4:  ServiceReference1.NorthwindEntities dat = 
   5:          new ServiceReference1.NorthwindEntities(url);
   6:   
   7:  //link statement to get the data, can use WHERE, Orderby, etc
   8:  var customers =
   9:      from c in dat.Customers
  10:      where c.Country == "Germany"
  11:      orderby c.CustomerID
  12:      select c;
  13:   
  14:  //bind to the grid
  15:  GridView1.DataSource = customers;
  16:  GridView1.DataBind();
 

This is pretty basic code from here.  Line 2 is a Uri reference to our service (which is technically in the same project, but it could (and should) be in a different project.) Line 4-5 is setting up a reference to the proxy we created and this is also our data context, representing the Astoria service. Lines 8-12 is a simple LINQ to Astoria statement to filter by the German customers (look like LINQ to SQL? That is the point!) and Lines 15-16 is where we do the data binding to the ASP.NET GridView. Our gridview looks like this, notice the ALFKI record says it is coming from SQL Azure:

image

That is all there is too it. Enjoy.

posted on Thursday, September 10, 2009 5:39:59 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Wednesday, September 9, 2009

The following video shows how to use the Telerik OpenAccess WCF Wizard with ATOMPub services via the WCF REST Starter Kit. The video is done by .NET Ninja in training Peter Bahaa  and uses the same ATOMPub project I showed yesterday on my blog. Enjoy!

Telerik OpenAccess WCF Wizard: How-to Video #4- AtomPub from Stephen Forte on Vimeo.

posted on Wednesday, September 9, 2009 2:11:36 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback