Join Chander Dhall for an in-depth discussion in this video Simple stored procedure query, part of Cosmos DB: Import, Manipulate, Index and Query.
And I need the self link, which I can get by using collection, which is getContext.getCollection, and then run a method on it, which is getSelfLink, and that should give me the value. And then our query, whatever query you want to do at this point of time. You can also, like start from the root, where let's say you have category code equals web, but to keep things simple, I would just like to add Select star FROM root for now.
And then the function, which is your callback, it has the error and any result we get back, and then you also have another property, options, which we're now going to use for this particular example. In case you have an error, you can throw that error, and this error will get past your C# code. And maybe you did not get any result back, which is a possibility. So you're going to check for that. Or maybe you did get a result back, but it was more like an empty result.
So in this case, result did not have any length. I messed up a little bit of the code, but it's simple. If you did not have any result or the result did not have any length, then you've got to do something. In that case you can say, well, let me get the context back, and from that context, I'm actually going to send a response back. So I do a response back, but then I want to set the body of the response to something else.
For example, Query Returned No Results. However, since I'm making the query, I'm hoping that I will get something back, and that is this particular else statement. And if I do get something back, then I also want to set the response to that particular result. So in this case, I could either say result, and keep in mind, that's an array, so in this case, we'll just say JSON.stringify(result) and pass back just the first value.
Something went wrong. So just to recap on what's happening, we got the context and the collections from the context, and this context is coming from C# code, and then we use the API to query the document. And this is just a Select star FROM root, and if that goes through, then we take the results and send it back, but in this case, we're only sending the first result.
Otherwise, if it doesn't go through, then we say, well, Query Returned No Results. And if this is correct, then that's awesome, but if not, then the query wasn't really accepted by the server, so we're just saying Something went wrong. A very simplistic way of creating a stored procedure. Keep in mind this is not production code, but gives you an idea how to write a simple stored procedure. So now in our C# code, we'll like to create an async method.
Client.ReadDocumentCollectionAsync. And then we need the URI for the document collection, and for that we need the databaseId and the collectionId that we already have. So once we have that, the next thing would be to get the scriptId. There are a few ways to get it, but we already have the path. So why not use a function that proc class has already, which will now take the path and remove the extension and give me the ID of the stored proc.
And I can say var storedProcedure equals a new instance of StoredProc, and all I need is the ID, which we just got back from the scriptId, and then the body of the stored proc. And that's very simple. I have the script path, and all I'm going to do is File.ReadAllText and get the entire body of the stored proc.
So, this is just a stored procedure in memory. We haven't created one in Azure Cosmos DB, so what we'd like to do is try to delete a stored procedure with the same name if it already exists. For that, we need to get one. What we do is client.createStoredProcedureQuery, and then pass in the collection link. In this case, that link would be collection.Resource.SelfLink.
And then we're going to do the query on it, and in this case, it's going to be x, which is the lambda, and make sure that that ID is equal to the ID of this particular stored procedure, and if that is the case, we want the first value or the default value. Now, if that exists... By the way, we might have to change this name a bit, because it already exists as storedProcedure.
So I'm going to change this to procedure. Now, if this procedure comes back, that means it's not null. That means it already exists. So if that's the case, then we'll like to go delete this. And now to delete this, we would need the storedProcedure.Id, which is the SelfLink. So now, this could be something that you could take in a different function called tryDeleteStoredProcedure, and you may use it over and over again.
But to keep things simple, we just did some inline coding here. So after we delete the existing stored procedure, next, what we'll do is use client to create this stored procedure in an async manner, and then pass in the collection.Resource.SelfLink, and of course the procedure we want to create.
So this line would create that particular procedure in Azure Cosmos DB. Next, we would like to run it and then grab the response. So executing a stored procedure is very simply client.ExecuteStoredProcedure. We're going to assign the type. In this case, it's string. Get the SelfLink, which is storedProcedure.SelfLink, and then any request options.
There's multiple of them. We could do anything. For example, let's say I have a PartitionKey, and I want to make sure that you create a PartitionKey of web. So in this case, no matter what my stored procedure does, we're only looking at PartitionKey, which is web. And then response.Response is what I'm looking for when it comes to getting the response back from the stored procedure.
The response is. Now, this is great, but it wouldn't run, because we haven't called this function yet. So what we'll do is call this function, use the databaseId. So in this case, it's Cazton, and then we could use one of the collections we created yesterday. One of them was Test. And then we have a script path. In this case, that script path would be something like ./Scripts/SimpleQuery.js.
Since it's an async function, it's better to call it with wait. Now, one thing to keep in mind is that when you have scripts, for example, this .js file, you want to make sure that this is copied to bin debug folder. Now, we do the same thing we did earlier, which is copy if newer. Now, since we're debugging, we're going to use F5 and not Control + F5. Once you do F5, you can see that we have the collectionId, which is Test, databaseId, which is Cazton, and the script path, which is SimplyQuery.js inside the Scripts folder.
If you hit F10, you can see that we got the scriptId as we wanted. Another F10, and you can see that the StoredProcedure actually has the entire body here. So if you click this, you can see the entire body, which is your function. Next, we look from that particular stored procedure, and since that procedure exists, it deletes it. Now, you may not see that while you are coding with me because you may not have created that stored procedure, but since I'd already cleared that stored procedure, it already existed.
And then we're going to execute the stored procedure with this PartitionKey as our request option. Once I run this, you can see I have response.Response, which is this value. Now, that's a lot of values, because I think we have select star, and that will give you a lot of values back. We can copy it. You can pull up a JSON Viewer. You can go to URL jsonviewer.stack.hu, and then paste this as it is, and you can see that this is the JSON data that we got back.
And keep in mind, it's actually just one JSON object, and why is that? Because when we did the select star, I knew we were going to get a lot of records. For that particular reason, if you look back into your code, you can see that we made sure that we only get result zero, which means just the first document and not the entire set of documents. So this worked, and we got the JSON back. We can also pass parameters to this particular function.
Let's assume that we wanted to add our own whereClause on the fly, and that whereClause could be something that's coming from C#. So I could say maybe a space here and then say + whereClause. And this is cool, because I would like for my whereClause to be a little bit dynamic, and in this case, it makes my life so much easier if I could pass in dynamically through my C# code in Cosmos DB.
So in this case, maybe I want to say r.name equals Wetpaint, because that was one of the records that we added. And just a bit of typo here. We're going to move this. But these are our RequestOptions, as you know, and then we had a comma. And if you notice, there's another parameter here called params, and it's a dynamic array. And that dynamic array could be multiple values, but in this case, we just chose to pass in one value, which is a whereClause that's coming from C#.
So we can go back up and run this again, do an F5, and do F10, get all these values again. We're debugging this. Delete the stored proc that was already existing, create the new stored proc, and then hit F5. You can see the response in this case. So the name in this case is Wetpaint, and that's exactly what we wanted. Now, this is something you can also check on your own using the portal. So if I go back to the portal, I can go to something called Query Explorer and say where c.name equals Wetpaint, and I will change the collections to Test and then say Run Query.
As you can see, it's the same exact record. Just to make sure you can remember the ID, which is 52c and ends with 8a, go back to Visual Studio and look at the ID. It's 52c and ends a 8a. This is the same exact result you get from your Azure portal.
- Data import scenarios
- Creating a database
- Creating a partitioned collection
- Data manipulation
- Importing documents with a stored procedure
- User-defined functions
- Excluding indexing at a document level
- Range indexing on strings
- Querying with SQL parameters
- Range operations