Azure for Developers Tutorial Step 3: Creating the WCF service, part 2

This is the third step of the Azure for Developers tutorial, in which we set up a WCF service running in Azure to provide CRUD operations to a client application. For more information, please check out the Introduction.

In this step, we will finish creating the WCF service and run it in the local compute emulator. In the next step, we’ll set up the client application and call the service.

First, a word about Windows Azure SQL Databases (WASD). When we were testing everything in Azure, I had done load testing, and everything was fine. But when we actually moved our customers to the new production environment running on Azure, I started seeing lots of connection problems. These are detailed in my article about our migration to Azure. To summarize, Microsoft hadn’t done a lot of testing for the case of a client hitting the database periodically rather than frequently, and having a small database. We were squeezed out by the other large and active databases on the server. They have made great headway in fixing this problem, but you really have to put in exponential retry code.

“Exponential retry code” means you have to try to connect or execute your data request, and if it fails, you have to wait a couple of seconds and try again, and if that fails, wait longer and try again, until you finally give up. Now, Microsoft recommends that you use their connection management framework, but I didn’t find out about that until I’d already done this the hard way, and I haven’t had time to go back and take a close look at it.

The Microsoft framework only does retries for specific SQL Server errors (which is good), and it almost completely wraps the process for you (which is good). I like a lot of logging, and I like to know when we’re having problems, so I log the retries and where they’re happening. This way I can tell how frequent they are, and where we might want to increase the retry count specifically. When I first looked at the framework, that capability wasn’t in there. I’ll leave it to you to check out the recommended solution. In the meantime, I’ll share how we did it.

We need to add the four classes to update and retrieve data to/from the Azure database. I’m going to post the first one here with a bunch of comments in the code to explain what it’s doing, and then I’ll post a link to download the other three, which use the same retry methodology.

Right-click on the web role and add a class. Call it CustomerFavorites.cs. First add the using clauses:

using System.Diagnostics;
using System.Data.SqlClient;
using Microsoft.WindowsAzure.ServiceRuntime;
using System.Data;
using System.Threading;

And here’s the code for the class itself:

internal class CustomerFavorites

  /// <summary>
  /// Given the first and last name, return the favorite movie and language.
  /// </summary>
  internal string GetCustomerFavorites(out string favoriteMovie, out string favoriteLanguage,
    string firstName, string lastName)
    //write to diagnostics that this routine was called, along with the calling parameters.
    Trace.TraceInformation("[GetCustomerFavorites] called. FirstName = {0}, LastName = {1}", 
      firstName, lastName);
    string errorMessage = string.Empty;
    favoriteMovie = string.Empty;
    favoriteLanguage = string.Empty;

    //tryCount is the number of times to retry if the SQL execution or connection fails.
    //This is compared against tryMax, which is in the configuration 
    //   and set in GlobalStaticProperties.
    int tryCount = 0;

    //success is set to true when the SQL Execution succeeds.
    //Any subsequent errors are caused by your own code, and shouldn't cause a SQL retry.
    bool success = false;

    //This is the overall try/catch block to handle non-SQL exceptions and trace them.
      //This is the top of the retry loop. 
        //blank this out in case it loops back around and works the next time
        errorMessage = string.Empty;
        //increment the number of tries

        //this is the try block for the SQL code 
          //put all SQL code in using statements, to make sure you are disposing of 
          //  connections, commands, datareaders, etc.
          //note that this gets the connection string from GlobalStaticProperties,
          //  which retrieves it the first time from the Role Configuration.
          using (SqlConnection cnx 
            = new SqlConnection(GlobalStaticProperties.dbConnectionString))
            //This can fail due to a bug in ADO.Net. They are not removing dead connections
            //  from the connection pool, so you can get a dead connection, and when you 
            //  try to execute this, it will fail. An immediate retry almost always succeeds.

            //Execute the stored procedure and get the data.
            using (SqlCommand cmd = new SqlCommand("Customer_GetByName", cnx))
              cmd.CommandType = CommandType.StoredProcedure;

              SqlParameter prm = new SqlParameter("@FirstName", SqlDbType.NVarChar, 50);
              prm.Direction = ParameterDirection.Input;
              prm.Value = firstName;

              prm = new SqlParameter("@LastName", SqlDbType.NVarChar, 50);
              prm.Direction = ParameterDirection.Input;
              prm.Value = lastName;

              SqlDataAdapter da = new SqlDataAdapter(cmd);
              DataTable dt = new DataTable();
              //the call to get the data was successful
              //any error after this is not caused by connection problems, so no retry is needed
              success = true;

              if (dt == null || dt.Rows.Count <= 0)
                errorMessage = string.Format("Error retrieving favorites; "
                    + "record not found for '{0}' '{1}'.",
                    firstName, lastName);
                DataRow dr = dt.Rows[0];
                favoriteMovie = dr["FavoriteMovie"].ToString();
                favoriteLanguage = dr["FavoriteLanguage"].ToString();

                Trace.TraceInformation("[GetCustomerFavorites] FirstName = {0}, LastName = {1}, " 
                  + "FavoriteMovie = {2}, FavoriteLanguage = {3}",
                    firstName, lastName, favoriteMovie, favoriteLanguage);
            }//using SqlCommand
          } //using SqlConnection
        catch (SqlException ex)
          //This is handling the SQL Exception. It traces the method and parameters, the retry #, 
          //  how long it's going to sleep, and the exception that occurred.
          //Note that it is using the array retrySleepTime set up in GlobalStaticProperties.
          errorMessage = "Error retrieving customer favorites.";
          Trace.TraceError("[GetCustomerFavorites] firatName = {0}, lastName = {1}, Try #{2}, "
            + "will sleep {3}ms. SQL Exception = {4}",
              firstName, lastName, tryCount, 
              GlobalStaticProperties.retrySleepTime[tryCount - 1], ex.ToString());

          //if it is not the last try, sleep before looping back around and trying again
          if (tryCount < GlobalStaticProperties.MaxTryCount 
            && GlobalStaticProperties.retrySleepTime[tryCount - 1] > 0)
            Thread.Sleep(GlobalStaticProperties.retrySleepTime[tryCount - 1]);
        //it loops until it has tried more times than specified, or the SQL Execution succeeds
      } while (tryCount < GlobalStaticProperties.MaxTryCount && !success);
    //catch any general exception that occurs and send back an error message
    catch (Exception ex)
      Trace.TraceError("[GetCustomerFavorites] firstName = {0}, lastName = {1}, "
        + "Overall Exception thrown = {2}", firstName, lastName, ex.ToString());  
      errorMessage = "Error getting customer favorites.";
    return errorMessage;

Rather than post the other three modules, I’ve zipped them up. Download them from this link, then unzip the file, copy the files to your project folder. Right-click on the web role and select “Add Existing” and browse for the three classes.

Now that you’ve added the classes, uncomment the method calls in CustomerServices.svc.cs.

Now your service should build successfully, and you can run it by clicking F5. If it’s successful, you should see this:

Don’t panic!

This is because you don’t have access to the root of the webserver. Append “CustomerServices.svc” to the end of the URL, and you should see this:

So now your service is running. In the next step, I’ll show you how to hook up the client application to the service.


One Response to “Azure for Developers Tutorial Step 3: Creating the WCF service, part 2”

  1. Azure for Developers: Introduction to the Tutorial | RobinDotNet's Blog Says:

    […] Step 3: Creating the WCF service, part 2 […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: