# Monday, November 2, 2009

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.

image

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.

image

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!

Technorati Tags: ,,,
posted on Monday, November 2, 2009 5:13:59 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Sunday, November 1, 2009

I’ll be speaking this week at TechDays in Hong Kong. Doing a new session on TSQL Tips and Tricks, a session on Silverlight, and of course the Daily Scrum (on Agile development as well as scrum.)

Hope to see you there.

image

Technorati Tags:
posted on Sunday, November 1, 2009 8:44:35 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Friday, October 30, 2009

With the official release of SQL Azure less than three weeks away, we are starting to see mainstream vendor support for SQL Azure. Telerik’s OpenAccess ORM is no exception. With the Q3 release of OpenAccess next week, OpenAccess will have full support for SQL Azure, going further than the basic support available today that I demonstrated on my blog last month. Full wizard support, forward and reverse mapping, and of course data services support via the Telerik Data Services Wizard. Let’s take a look at the basics here.

Getting Started

To get up and running and show the mapping and LINQ support, I will open Visual Studio 2008 (or 2010) and create a simple Console Application named OpenAccess.Azure.Demo. The first step is to enable the project to use OpenAccess via the Enable Project Wizard. As part of the wizard you need to specify which database you are going to connect to. OpenAccess gives you many choices besides Microsoft SQL Server, and one of the native choices is Microsoft SQL Azure. After you select SQL Azure, you will need to provide your credentials (don’t forget to put the tcp: in front of your server name.)

image

After you connect and finish the wizard, it is time to do some mapping.

Mapping Database Objects

To map some database objects to persistent classes, choose the Reverse Mapping wizard. This will bring up the Reverse Mapping dialog where you can select which tables, views, and stored procedures you want to map. In this case I will just select all of the defaults and map all of Northwind (remember I migrated Northwind up to SQL Azure.) Now it is time to build a simple application.

image

Working with SQL Azure Data

Let’s write a LINQ statement to fetch all of the customers from one country and print it out to the console window. First tings first, you have to put in a using statement for OpenAccess:

using Telerik.OpenAccess;

Next we will create our LINQ statement. The LINQ statement will work like any LINQ statement in OpenAccess, there is nothing special for SQL Azure, this LINQ statement would work against SQL Server, MySQL, or Oracle.

   1:  static void Main(string[] args)
   2:  {
   3:      //data context
   4:      IObjectScope dat = ObjectScopeProvider1.GetNewObjectScope();
   5:      //LINQ Statement
   6:      var result = from c in dat.Extent<Customer>()
   7:                   where c.Country == "Germany"
   8:                   select c;
   9:      //Print out the company name
  10:      foreach (var cust in result)
  11:      {
  12:          Console.WriteLine("Company Name: " + cust.CompanyName);
  13:      }
  14:      //keep the console window open
  15:      Console.Read();
  16:  }

The LINQ statement uses the data context, or IObjectScope in line 4, has a simple LINQ statement on lines 6-8 to filter by the customers in Germany and then iterates those customers and prints them out to the console window in lines 10-13. The result is shown here:

image

Pretty basic application, however, you can see that Telerik OpenAccess has full support for SQL Azure. Next week I will show a more complete example.

Enjoy!

Technorati Tags: ,
posted on Friday, October 30, 2009 2:47:58 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Thursday, October 29, 2009

I am lucky to get to speak at many conferences in many different countries each year. A lot of people ask what it is like to be an international confrence speaker. For the most part, mind airport security and flight delays, it is the most fun you can have and still be working. You get to hang out with your peers all over the world and geek out. Then of course there is the partying…

Sometimes your friends and colleagues want to keep partying well after the bar is closed. At the last BASTA confrence in Germany last month, I retired around 2am well after the we shut down the bar. My colleagues lead by fellow Telerik employee Peter Brunner and thinktecture’s  Christian Weyer aka the tall German, decided to keep drinking and raided their minibars. When they exhausted their minibars, they came to my room.

Posing as housekeeping they come to my room in the middle of the night to wake me up. They even turn around my do not disturb sign to “come on in.” I knew it was not housekeeping, but I did not expect a party at my door. After I answer the door in my underwear, I give them a tour of my “roomlet” as well as drink a beer before kicking them out. Christian Weyer and I also play with my beer stein Oktoberfest hat that I won at Oktoberfest-it is not meant to fit on a normal human’s head, but it does fit Christian’s.

Daniel Walzenbach caught this all on film.

 

Technorati Tags: ,

posted on Thursday, October 29, 2009 2:19:35 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Wednesday, October 28, 2009

The developer ecosystem is starting to rally around SQL Azure, and that is a healthy thing. I stumbled across this neat SQL Azure query tool from Microguru today. It is called the Gem Query Tool for SQL Azure and you can download it here. It uses the .NET Framework 3.51 and is pretty lightweight. The cool thing is that it has a community edition that is free to use.

According to Micoguru the tool:

provides an intuitive user interface to connect to and work with SQL Azure databases. Gem Query Tool supports execution of any DDL and DML script supported by SQL Azure. To facilitate authoring of SQL queries, Gem Query Tool for SQL Azure displays tables and columns in your database.

I gave it a go today and it is simple to use. What stands out about this tool and why I highlight it after I highlighted a few other tools is the database browse schema feature. This is a basic feature that SQL Server’s own Management tool and the SQL Azure web site both lack. Gem Query allows you to log into one database (my only complaint is that there is not a “browse databases” feature) and select your tables or views. Once you click on one you can view the schema in a nice grid:

image

Access to this metadata is important after you have migrated some data since some of the data types or defaults may not be what you expect. I also like that when you click on a database object you have a “column” or “data” view. When you click on the “data” tab, you will have the option to select the Top 100, Top 1000 (pretty convenient) and All.

image

Gem Query has a very easy to use Query interface. While most other tools leave you on your own to write SQL, Gem Query gives you three windows with the SQL keywords supported by SQL Azure and a list of row returning database objects (tables, views, etc..) When you select a SQL command, it will put it into the query window for you, as it will for the objects. When you select an object you will also see the available columns and click on those to build your query. I wrote this very simple query using the builder:

 

   1:  SELECT CustomerID, ContactName, Country
   2:  FROM Customers 
   3:  ORDER BY Country 

image

 

Enjoy!

Technorati Tags:
posted on Wednesday, October 28, 2009 5:53:55 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Tuesday, October 27, 2009

The Telerik WCF Wizard that I have shown on this blog several times has been updated and is available on Telerik Labs for download.

Whenever Telerik puts something up on Telerik Labs we hope that some early adopters will try it out and give us feedback. We had an amazing demand for the Telerik OpenAccess WCF wizard and got tons of feedback. The most overwhelming piece of feedback is the name of the wizard: so as of this CTP, the wizard is now renamed the Telerik Data Services Wizard.

screen1

We made some key enhancements in this build for useability. The big thing is that you no longer have to be an ADMIN user to use the wizard. In addition, we made some of the navigation simpler as well as squashed some bugs (mostly around using LINQ statements in older versions of OpenAccess). Speaking of older versions of OpenAccess, the wizard will automatically detect what version of OpenAccess you have installed and auto-update itself to work with that version.

We’re working around the clock on a new build that will have Visual Studio integration and a new UI that is more consistent with the OpenAccess UI. A little further out, we will also include support for ADO.NET Data Services 1.5 CTP2 using the new “data service provider” interface available in Astoria 1.5. Once we are at that point, we will release this a full fledged beta with a target for the wizard to be part of OpenAccess proper by Q1 release next year at the latest.

Enjoy!

Technorati Tags: ,,,
posted on Tuesday, October 27, 2009 6:27:04 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Sunday, October 25, 2009

While visiting Sichuan Province in China this weekend, I traveled to the Wolong Nature Reserve. Wolong is the new home of the giant pandas after the deadly earthquake last April destroyed their last home (and unfortunately killed a panda).

Wolong is very special since it is the only place in the world where you can go into the habitat and feed and play with the pandas. While playing with the pandas, he bit me on my leg (and Kathleen on her finger!)

Now that is something that does not happen every day. No harm was done, except for a few black and blues. Of course the pandas were just playing! We all kissed and made up-after we fed them of course! :)

IMG_0687

Technorati Tags: ,
posted on Sunday, October 25, 2009 7:44:39 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Friday, October 23, 2009

As you most likely know by now, SQL Azure is now on production servers and the team is feature complete for the PDC build. What is funny is that they did a migration in the middle of the night on Tuesday, right smack dab in the middle of the day in Bulgaria where I was demoing SQL Azure at DevReach. That lead to some funny demos crashing. What fun!

The good news is that SQL Azure now supports BCP, so you should be able to easier bulk load data on into SQL Azure. No more crazy work around like the ones I posted on this blog and on many others.

Another new feature, which took me a while to discover when all of my unit tests failed, is a firewall. You can now specify which IP Addresses can access your SQL Azure account. At first this kind of pissed me off, but I like that I can completely control who can access my account. (Especially since Microsoft will start charging soon for SQL Azure.) To set your allowed IP Addresses, just click on the “Firewall” tab in the SQL Azure manager and add a range of IP Addresses and you will be in good shape.

image

Anyway, SQL Azure support is improved in SQL Server Management Studio, however, you still can’t use the object explorer. I reviewed SQL Azure manager a week or two ago, and that still works as advertised against the new production builds.

image

If you are working with Visual Studio 2010 beta2 that shipped this week, you can use a refreshed version of the SQL Azure Explorer. The 0.2.2 version is built specifically for VS 2010 b2 and is very stable, the author on codeplex marked it now as a beta. After you download and install SQL Azure Explorer, you have to go into Visual Studio and enable extensions. You can do this via Tools|Extension Manager. You will see that SQL Azure Explorer is disabled and extensions are not available. You have to click on the hyperlink and then check the allow button in the dialog that follows. After you close and reopen Visual Studio, you will see SQL Azure Manager as the first item in the Tools menu.

image

There have been some major speed improvements to the login and object view process. Also the ability to create and drop databases, users, etc. A nice feature (that was also in the last version but I did not see it) is that you can parse your query and even format your TSQL. Pretty cool.

image

Technorati Tags:
posted on Friday, October 23, 2009 4:15:02 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback