Channels ▼
RSS

C/C++

Working with Azure DocumentDB: SQL & NoSQL Together


So far, I've just added two game documents to the sample Games document collection, so the previous query will retrieve just a few game levels. However, in a real database, you will want to limit the number of results returned by the query. The .NET SDK allows you to use the MaxItemCount and RequestContinuation feed options included in the FeedOptions class to you to control the result sets.

The following lines show a simple example of using the MaxItemCount option with a new version of the previous query that retrieves a maximum of two levels per request. The use of AsDocumentQuery enables the code to access the HasMoreResults bool property in a loop that makes calls to the asynchronous method ExecuteNextAsync to retrieve more results as long as they are available.

// I just want a maximum of two levels per request
var queryOptions = new FeedOptions { EnableScanInQuery = true, MaxItemCount = 2 };
int requestNumber = 0;

// AsDocumentQuery() allows me to access HasMoreResults
var levelsQuery = client.CreateDocumentQuery<Game>(collection.SelfLink, queryOptions)
    .SelectMany(game => game.Levels)
    .Where(levels => levels.ParrotsCount > 2)
    .Select(l => l)
    .AsDocumentQuery();

while (levelsQuery.HasMoreResults)
{
    var levels = await levelsQuery.ExecuteNextAsync();
    requestNumber++;
    Console.WriteLine("*** Request #{0} - Levels retrieved:", requestNumber);
    foreach (Level level in levels)
    {
        Console.WriteLine("Title: {0}, Rocks: {1}, Parrots: {2}, Ghosts: {3}",
            level.Title,
            level.GhostsCount,
            level.ParrotsCount,
            level.RocksCount);
    }
}

When you want to load pages at different times, you can work with a continuation token. The following lines show an example of using a continuation token to run two queries that retrieve a maximum of two levels from the games document collection.

// I just want a maximum of two levels per request
var queryOptions = new FeedOptions { EnableScanInQuery = true, MaxItemCount = 2 };

var levelsQuery1 = client.CreateDocumentQuery<Game>(collection.SelfLink, queryOptions)
    .SelectMany(game => game.Levels)
    .Select(l => l)
    .AsDocumentQuery();
            
var feedResponse1 = await levelsQuery1.ExecuteNextAsync();
// Save the ResponseContinuation continuation token to use it in a future request
var continuation = feedResponse1.ResponseContinuation;
Console.WriteLine("*** Page #1 - Levels retrieved:");
foreach (Level level in feedResponse1.AsEnumerable())
{
    Console.WriteLine("Title: {0}, Rocks: {1}, Parrots: {2}, Ghosts: {3}",
        level.Title,
        level.GhostsCount,
        level.ParrotsCount,
        level.RocksCount);
}

if (levelsQuery1.HasMoreResults)
{
    // Now, use the continuation token to perform a second request
    queryOptions.RequestContinuation = continuation;
    var levelsQuery2 = client.CreateDocumentQuery<Game>(collection.SelfLink, queryOptions)
        .SelectMany(game => game.Levels)
        .Select(l => l)
        .AsDocumentQuery();

    var feedResponse2 = await levelsQuery2.ExecuteNextAsync();
    // Save the ResponseContinuation continuation token to use it in a future request
    var anotherContinuation = feedResponse2.ResponseContinuation;
    Console.WriteLine("*** Page #2 - Levels retrieved:");
    foreach (Level level in feedResponse2.AsEnumerable())
    {
        Console.WriteLine("Title: {0}, Rocks: {1}, Parrots: {2}, Ghosts: {3}",
            level.Title,
            level.GhostsCount,
            level.ParrotsCount,
            level.RocksCount);
    }
}

The first query, levelsQuery1, uses AsDocumentQuery to enable access to HasMoreResults and the continuation token (ResponseContinuation). After the call to the ExecuteNextAsync method, the code saves the value of the continuation token (feedResponse1.ResponseContinuation) in the continuation variable for its later usage to continue retrieving results for the query from this point.

After displaying the results of the first feed response, the code determines whether there are more results by checking the bool value of the levelsQuery1.HasMoreResults property. If more results are available, the code sets the queryOptions.RequestContinuation value to the previously saved continuation token (continuation) and creates a new query named levelQuery2 that specifies the query options with this continuation token. A new call to the ExecuteNextAsync method retrieves the new result set and the code saves the value of the new continuation token for a potential future usage in the anotherContinuation variable. Finally, the code shows the results of this second query.

You can save the continuation token each time to call the ExecuteNextAsync method and use it for the next query. You just need to make sure that you always check the value of the HasMoreResults property before making another call to the ExecuteNextAsync method. This way, you can easily work with paged and limited result sets.

Working with Server-side JavaScript

You can use JavaScript code to create stored procedures, triggers and, User-Defined Functions (UDFs). As you might guess, the DocumentDB JavaScript server-side SDK precompiles them to avoid the overhead of a compilation cost each time they're invoked. However, it is not a good practice to abuse these features. You always have to consider the benefits and tradeoffs, as with any other database management system.

I'll use a simple example to demonstrate the creation of a UDF within the project and its use in a query. The UDFs are related to a document collection and their management is similar to the other DocumentDB resources with the .NET SDK.

Add a new Scripts folder to the Visual Studio project and create a new realscore.js JavaScript file within this folder. The following lines show the code for the realscore JavaScript function that receives a game document (doc), calculates a score factor based on the categories to which the game belongs, computes the total scores achieved by the players, and then returns the result of multiplying the total score by the score factor. Of course, you might find better ways of writing this code and you might want to add more code to check for the existence of certain properties as you are working with a schema-free database. In this case, I just want to focus on how UDFs work and I've added just one property check. The code simply checks whether the received document has a categories key. If it doesn't, it throws a new Error.

/**
* UDF
* For given document (Game), compute and return the real score based on the game categories.
*
* @param {Document} doc - the document (Game) to compute the real score for.
*/
function realscore(doc) {
    if (typeof doc.categories === 'undefined') {
        throw new Error("The game doesn't support categories.");
    }

    // The default score factor is 1
    var scoreFactor = 1;
    if (doc.categories.indexOf("puzzle") != -1) {
        scoreFactor = 3;
    }
    if (doc.categories.indexOf("arcade") != -1) {
        scoreFactor = 2;
    }

    var totalScore = 0;
    for (var i = 0; i < doc.scores.length; i++) {
        totalScore += doc.scores[i].score;
    }

    return totalScore * scoreFactor;
}

The following lines show the contents of a new version of the RunAsync method.

// Try to retrieve a Database if exists, else create the Database
var database = await RetrieveOrCreateDatabaseAsync(databaseId);

// Try to retrieve a Document Collection, else create the Document Collection
var collection = await RetrieveOrCreateCollectionAsync(database.SelfLink, collectionId);

var realScoreUDFFileName = @"Scripts\realscore.js";
var realScoreUDFId = System.IO.Path.GetFileNameWithoutExtension(realScoreUDFFileName);
var realScoreUDF = new UserDefinedFunction
{
    Id = realScoreUDFId,
    Body = System.IO.File.ReadAllText(realScoreUDFFileName),
};

UserDefinedFunction udf = client.CreateUserDefinedFunctionQuery(collection.SelfLink)
    .Where(u => u.Id == realScoreUDFId)
    .AsEnumerable()
    .FirstOrDefault();
            
if (udf != null)
{
    await client.DeleteUserDefinedFunctionAsync(udf.SelfLink);
}
            
await client.CreateUserDefinedFunctionAsync(collection.SelfLink, realScoreUDF);

var gameNamesAndRealTotalScores = client.CreateDocumentQuery<dynamic>(collection.SelfLink, string.Format("SELECT g.name, realscore(g) AS totalscore FROM game g", realScoreUDFId));

Console.WriteLine("Game names and real total scores:");
foreach (var result in gameNamesAndRealTotalScores)
{
    Console.WriteLine("{0}", result);
}

The code creates a new Microsoft.Azure.Documents.UserDefinedFunction, sets the Id to the JavaScript file name without its extension (realscore.js), and sets the Body to the text read from the JavaScript file. Then, a call to the client.CreateUserDefinedFunctionQuery method creates a query to check whether the games document collection has a UDF with the same ID or not. If the query returns a UserDefinedFunction instance, a call to the client.DeleteUserDefinedFunctionAsync with the SelfLink of the retrieved UserDefinedFunction deletes the existing UDF. This way, you can make changes to the JavaScript file and the query, and use the code as a baseline to test different UDFs many times.

Then, a call to the client.CreateUserDefinedFunctionAsync with the games document collection SelfLink and the UserDefinedFunction instance (realScoreUDF) creates the UDF associated to the games document collection. After the UDF is created, a call to client.CreateDocumentQuery runs the following query that retrieves the game's name and the computed real score for the game. The second value calls the realscore UDF with the game document as its parameter:

SELECT g.name, realscore(g) AS totalscore 
FROM game g

Finally, a foreach loop displays the dynamic results with each game name and computed score. As you can see from this example, all the DynamicDB resources work in a similar way. You use similar methods to create, query, and delete them.

Conclusion

There are many things that you must take into account when you start evaluating DocumentDB for your projects. In these two articles, I've focused on usage of the .NET SDK to provide examples that you can use as baselines to start working with DocumentDB. I've used the default consistency and indexing options. You should definitely explore the different consistency levels and indexing options to find the combinations that best suit your application's needs. After working with these examples, you will find it easy to perform operations with other DocumentDB resources.


Gastón Hillar is a senior contributing editor at Dr. Dobb's.

Related Article

Azure DocumentDB: Working with Microsoft's NoSQL Database in the Cloud.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.