Join Chander Dhall for an in-depth discussion in this video Execute ImportDocument stored procedure, part of Cosmos DB: Import, Manipulate, Index and Query.
- [Instructor] We can actually reuse quite a bit of code from this particular method, changing the name to ImportDocument. So we have the collection and all we need is the collection URI which we have in this case, and then the selfLink which is collection.Resource.SelfLink and in this case we have a different name for the file which is ImportDocument.js.
Once we read the document, we will have the ID and we will have the body which is the same as what we did earlier. Then we can try to delete and then recreate the storedProcedure. Once we have that, we can actually execute the storedProcedure. Now in order to execute this storedProcedure, we can get our JSON from anywhere, and it could be very simply the JSON that we used earlier.
So we can go back to the companies file, right click, and Edit with Notepad++ and pick any of the values here. I can go to line number, let's say 28, and hit Home, and then use Shift + Control + C to copy the entire line. Go back to Visual Studio, Create a Folder, in this case I'm calling it Data, and then create a JSON file.
We can call it anything. I'll just call it test. Just keeping it camel case, just because it's JSON, and paste the document as it is. Well, we might not need the additional curly brackets that already existed, so I'm just going to go remove them and then press Control + K + D. This actually formats JSON for me, and it also formats the rest of my C# code if I was in a C# file, but actually tells me if the JSON that is on the file is correctly formatted which is a good way to look at your data.
Going back to program.cs, now we'll write code to import this JSON as a document that we will use to execute the storedProcedure. So I could say string testJson, because test is the name of the file, and say File.ReadAllText. And in this case, I'm hard coding again, not a very good practice. It could be data\test.json.
And don't forget to right click on the file and change the Copy to Output Directory to Copy if newer option. Next we'll key the parameters that we need to pass. And it's a dynamic array. You may not have to use dynamic. You could just use array. Just so you know that it is dynamic, I'm using dynamic. And then use JsonConvert, control dot, and then Deserialize the Object.
In this case the type of that object is dynamic because we don't want to convert it into an entity as we haven't created one. And we want the entire document to go to Cosmos DB as it is. So now that I have the parameters, I would like to execute the stored prog. The type in this case is going to be int and then we have storedProcedure.SelfLink. The next thing I could do is create a PartitionKey if I have to, and maybe I'll just call it my last name.
We do not need this because we are now creating parameters which is of type new dynamic array, and that should get the job done. Now before we run this, there are a few things to keep in mind, for example, the PartitionKey. Now in this case, the PartitionKey happens to be my last name, dhall. But if I were to go back and check what's the PartitionKey in this document, well, it happens to be category_code which is advertising.
Well so the first thing we have to do is change this value. We could have done it either way. We could have converted that over to dhall or changed this to advertising, but we have to make sure that it's the same exact name. The second thing we need to make sure is that this is actually an array. So I'm going to do Control + End, go all the way down and then close the array. Even though it's one document, the input is still an array, otherwise it wouldn't work. The third thing we need to do is again go Control + Home, and make sure the ID exists and there's a reason behind that.
If you remember in the previous module, we used select token, so we can change the ID to something that Document DB understands. This is what Document DB understands. Now you don't have to provide an ID necessarily. But since we used disableAutomaticIdGeneration equals true, and the reason behind that was because we didn't want Cosmos DB or Document DB to parse the entire document and wanted to speed up our import, we decided to go with this particular option.
Now I'm going to hit F5. We can see that we have testJson. We can come in here and do a Text Visualizer and even a JSON Visualizer. JSON is a little better because I prefer this option over others as this also tells me that it's a well formatted JSON object. Here are the parameters and then hit F10, and very quickly we get the response, and the response happens to be one.
Since we only had one document, we're good, everything worked. Let's do F5 and go to the portal. So once we are in the portal, we can go to Document Explorer or maybe, since we have a lot documents, it probably makes more sense to go to the Query Explorer and then look for what we just added. So in this case we know that we have a category_code of advertising, and this might give us a lot of results.
And it did, but we can see we have Technorati. And one way to look for this would be that particular ID. We can go to test.json and see the ID is 52c and then it ends with da5. We can see we have 52c, and then it ends with da5. So this document made it to Azure Cosmos DB instance. And of course, there's more data here that is for category_code which is advertising.
- 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