Join Chander Dhall for an in-depth discussion in this video Query with SQL parameters, part of Cosmos DB: Import, Manipulate, Index and Query.
- [Instructor] Now that we've seen some of the queries especially the parallel and the sequential ones, next what we could do is show you some kind of SQL-like syntax. So I could have a query client.CreateDocumentQuery of type company and then I could have the collectionUri, but I can pass in SqlQuerySpecification.
And in this case, you could say well I have a QueryText and that could be anything you like which is more like SQL as in for example Select * from Companies c where we have c.name and it should be equals to @name. Now what's @name? If you remember SQL, we have SQL parameters. And in this case, that's exactly what it is. And here I can say I have a new SqlParameterCollection and that could be new SqlParameter and @name could be defined here.
Now just to keep things simple, we're just going to hard code it. Otherwise, you can pass it in your function if you like to. And then we have the default options. And then it's a deferred query, but to make things simple, I'm just going to do ToList so it gets resolved right here and we can just put a break point here to see if we get the results. So if I hover over it, I have a company which is network_hosting and all I did was look for Omnidrive as the name.
And if you were to check this, you can easily verify this by going to your portal, go to the Query Explorer, you're inside Cazton, you're inside company, and here we could use the name we used over there. Once we run the query, we see the same exact data we got programmatically. Now the same thing could be done in just a little bit different syntax.
We're going to copy this as it is and then make certain changes here. So if you notice, we still have client.CreateDocumentQuery and then we have new SqlQuerySpec, but then we're going to add the QueryText and we're going to do an AND query here. So for example we have this, can we do an AND query here? Absolutely, so we could say AND category_code = @category_code.
This is just to show you that we can have two SQL parameters. So very simply what we'll do is we'll add it right here. Say new SqlParameter and in this case, it happens to be @category_code and then let's grab the category_code from the portal. You can see the category_code, in this case it's network_hosting. Back to Visual Studio and we don't really need this.
We're going to press F5. We still get back the same exact result. Now this is an AND. We could have done an OR and you can see that the results will be a little different. We could do an OR here and press F5 and you see we got two companies because I bet when you do an OR, they got the category_code network_hosting and there's another company Cisco in this case that also has the same category_code. If we needed a little bit more results in the collection we have, we could have used something like web 'cause that happens to be a category that has a lot of results.
Let's press F5. You could see we have 33 companies now. So this is how you could do some AND, OR, and at the same time you can have your SQL parameters passed into your queries.
- 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