In my series of DocumentDB I will discuss more advanced features of DocumentDB. This post covers Stored Procedures.

Use of Stored Procedures

You can benefit a great deal from using stored procedures in DocumentDB. I will give you two examples where it might me useful but I don’t see a big difference in the usefulness between DocumentDB Stored Procedure and SQL Stored Procedures (or functions).

• Case scenario: In my case study from this series you could consider the scenario that a customer changes the last name. The customer name should then be changed on the orders. In a traditional SQL database you would typically only have one row to update, i.e. the LastName column in the Customer table. But in DocumentDB which is not structured and (typically) not normalized data (I would argue that if it is normalized – you are not using DocumentDB correctly) you would have the LastName of the customer on all his/her orders and in the customer Document. A valid case for a stored procedure  could be to update the name of the customer on all orders in a single fail ALL or succeed ALL transaction. This is the case scenario I will continue for this topic.

Before we start there are a few good things to note:

1. The Stored procedures are actually JavaScript functions and JavaScript syntax.
2. They changes made will be committed all or nothing. This is done implicitly (you don’t have to write any commit, abort or begin transaction code for it). You Abort the transaction by throwing an exception. You commit by not throwing any exceptions.
3. You can upload them from code or from the portal and also some third-party tools. I recommend starting at this url for options on how to develop SPs and options on how to edit the SPs and the syntax. I will use the Azure Portal myself in these examples as it is available to all.
   

   A simple HelloWorld SP

Before we do some more advanced document updating stored procedures I will create a simple Hello World one and construct the C# code to call it.

1. Go to portal.azure.com
2. Dive down to your DocumentDB collection and select Script Explorer
3. Create a new Stored Procedure
4. You will get a sample procedure that you can edit
   1. Type the name of your StoredProcedure in the top TextBox. This is the name that you should call from your code if you are going to call it by name (two-step operation see later). It does not have to be the same as the name of the function but I think it is good practice and will avoid some later confusion on what to call. So type HelloWorld in the text box.
   2.  Name the function HelloWorld as well.
   3. Add a parameter named name to the function.
   4. Write the following code as the body of the function
      // A hello world SP
      function HelloWorld(name) {
      var context = getContext();
      var response = context.getResponse();response.setBody(“Hello, ” + name);}
   5. Press save

The function asks for the current SP Context and asks for the response object. It then sets the return value as “Hello, ” + the name parameter.

The portal also allows you to test your SP.

Further down you can find a textbox with the label “Input”.

1. Enter “Nisse” (or any string) in the Input Field.
2. Press Save & Execute

You can now see the result of the function in the Results Section

Call the SP from C#

We start by adding a few methods to the DocumentDBRepository that we created earlier.

Note that I have added both ExecuteSP and ExecuteSPByName in the class. ExecuteSPByName can be more intuitive as you specify the name of the SP but has the drawback that  you will have to lookup the actual selflink. By skipping this step you can save some performance but will lose readability in your code.

You find the SP self link in the portal by pressing the properties button on the properties button in the script explorer.

We are now going to call it from our code. All the methods above returns a StoredProcedurerResponse<T>. By looking at the response property we can get the return value of the stored procedure. In the simple example above we define the result as a string. The example below also uses the overload that takes a parameter array (params dynamic[] parameters).

A more complex stored procedure

This example was a bit simplified perhaps so I will create a new one that actually modifies data stored in DocumentDB. To make a natural continuation of my sample throughout this DocumentDB series – I will continue on the case scenario described earlier. I will create a Stored Procedure that allows the user to update their name on all their orders.

1. Go to portal.azure.com
2. Dive down to your DocumentDB collection and select Script Explorer
3. Create a new Stored Procedure
4. You will get a sample procedure that you can edit
5. Type the name of your StoredProcedure in the top TextBox. I will call mine UpdateCustomerNamesOnOrders.
6. Name the function UpdateCustomerNamesOnOrders as well.
7. Add 3 parameters to the function UpdateCustomerNamesOnOrders (customerEmail, firstName, lastName). The first one will be used to find corresponding Orders (where the customerEmail equals the email entered). The last two parameters are values to replace the old values with.
8. We now need to get the DocumentDB Context. This is done by calling the getCollection method of the context object.
   var collection = getContext().getCollection();
9. Next we need a query to find the affected orders. This is done with the following syntax where we use the customerEmail parameter. Instead of concatenating SQL as string I use parameters (which is always a good pattern to avoid SQL injection among other reasons.
   var query = {query: “select * from Orders o where o.customer.email = @customerEmail”, parameters: [{name: “@customerEmail”, value: customerEmail}]};
10. Now  we have to execute the query. This is done by calling the collection.queryDocuments method. This will take a selflink to the collection we fetched earlier and the query we just entered. The third parameter is the callback if we have a successful result. In this case this is an inline function (which we’ll get back to later). The queryDocuments method will return true if the call succeeded but false it the call was not accepted by DocumentDB.

    var isAccepted = collection.queryDocuments(
    collection.getSelfLink(),
    query,
    function (err, documents, options) {
    if (err) throw err;

    //TODO

    });

11. After this call we have to check the isAccepted value. If the call to DocumentDB failed we will have a value of false that we need to propagate to the caller by throwing an Error.
     if (!isAccepted) throw new Error(‘The query was not accepted by the server.’);
12. Lets go back to the inline method. Now we need to see if we got any Orders matching our query and in that case we need to loop them to update each document. This code should be put in the //TODO part earlier. What we do is to check the length (count) of documents to see that there are actually any orders to update. If there are – we create a forEach loop to loop through all documents. To prepare for the update I also set the properties (but have left out the update code so far in the //TODO2 section). If there are no matching documents I thow and Error but this is up to you how you want to treat this scenario.
    if (documents.length > 0) {
    //Documents found. Set Name on each doc
    documents.forEach(function(doc) {
    doc.customer.firstName = firstName;
    doc.customer.lastName = lastName;//TODO2          }, this);
    }
    else {
    // Else a document with the given id does not exist..
    throw new Error(“Email not found.”);
    }
13. Now finally we need to persist the changes in the //TODO2 placeholder. This is very similar to the document query earlier. we call the replaceDocument method of the collection using a selflink to the document as well as the updated document and an inline callback. Just as before the call may be accepted or not (accept variable) and we might get an error in the callback itself.
    var accept = collection.replaceDocument(doc._self, doc,
    function (err, docReplaced) {
    if (err) throw “Unable to update order, abort “;
    })
    if (!accept) throw “Unable to update order, abort”;

Complete listing

Calling the SP from C#

The Stored Procedure above does not return anything but returns errors when it does not go well so I can just define the stored procedure as string generic again. I have some extra code below (not very nice one) but anyway it handles the errors also and can disregard the “Email not found” if I want to.

Looking at the documents now we can see that all orders connected to peter@example.com now belong to Pedda Cucumber.