CODEWITHSUNDEEP
c#azureazure-cosmosdb
epotter
epotter

Reputation: 7799

Getting more than 100 documents back from ExecuteStoredProcedureAsync

I have a CosmosDB instance that is using the SQL / DocumentDB interface. I am accessing it via the .NET SDK.

I have the stored procedure that I call with ExecuteStoredProcedureAsync. But I can only get a max of 100 documents back. I know this is the default option. Can I change it?

The optional parameter to ExecuteStoredProcedureAsync is a RequestOptions object. The RequestOptions doesn't have properties for MaxItemCount or continuation tokens.

Upvotes: 2

Views: 886

Answers (2)

Kiryl
Kiryl

Reputation: 1486

You need to change the SP itself to adjust the amount of records you'd like to return. Here is a complete example with the implemented skip/take logic in SP- 

function storedProcedure(continuationToken, take){

     var filterQuery = "SELECT * FROM ...";
     var accept = __.queryDocuments(__.getSelfLink(), filterQuery, {pageSize: take, continuation: continuationToken},

     function (err, documents, responseOptions) {
         if (err) throw new Error("Error" + err.message);

         __.response.setBody({
             result: documents,
             continuation: responseOptions.continuation
         });
     });
}

Here is a corresponding C# code:

        string continuationToken = null;
        int pageSize = 500;

        do
        {
            var r = await client.ExecuteStoredProcedureAsync<dynamic>(
                UriFactory.CreateStoredProcedureUri(DatabaseId, CollectionId, "SP_NAME"),
                new RequestOptions { PartitionKey = new PartitionKey("...") },
                continuationToken, pageSize);

            var documents = r.Response.result;
            // processing documents ...
            // 'dynamic' could be easily substituted with a class that will cater your needs

            continuationToken = r.Response.continuation;
        }
        while (!string.IsNullOrEmpty(continuationToken));

As you can see, there is a parameter that controls the number of records to send back - pageSize. As you've noticed, pageSize is 100 by default. In case you need to return all at once, specify -1.

Upvotes: 2

Jay Gong
Jay Gong

Reputation: 23782

The RequestOptions doesn't have properties for MaxItemCount or continuation tokens.

MaxItemCount is a parameter in Feedoptions.

ExecuteStoredProcedureAsync method does not limit the returned data entries, the key is your query operation in the Stored Procedure set the maximum number of entries you want to return.

Please refer to the sample stored procedure code as below :

function sample(prefix) {
    var collection = getContext().getCollection();



    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM root r',
       { pageSize: 1000 },
    function (err, feed, options) {
        if (err) throw err;
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            var response = getContext().getResponse();
            var body = "";
            for(var i=0 ; i<feed.length;i++){
                body +="{"+feed[i].id+"}";
            }
            response.setBody(JSON.stringify(body));
        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

Result :

enter image description here

Upvotes: 0

Related Questions