In relational databases, a parameterized question is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. DocumentDB also supports parameterized queries, and parameters in parameterized query may be expressed with the familiar @ notation. The most essential purpose to use parameterized queries is to keep away from SQL injection attacks. It can also provide robust coping with and escaping of person enter.
Let’s test an instance where we are able to be using the .Net SDK. Following is the code which will delete the collection.
private async static Task DeleteCollection(DocumentClient client, string collectionId) {
Console.WriteLine();
Console.WriteLine(">>> Delete Collection {0} in {1} <<<",
collectionId, _database.Id);
var query = new SqlQuerySpec {
QueryText = "SELECT * FROM c WHERE c.id = @id",
Parameters = new SqlParameterCollection { new SqlParameter { Name =
"@id", Value = collectionId } }
};
DocumentCollection collection = client.CreateDocumentCollectionQuery(database.SelfLink,
query).AsEnumerable().First();
await client.DeleteDocumentCollectionAsync(collection.SelfLink);
Console.WriteLine("Deleted collection {0} from database {1}",
collectionId, _database.Id);
}
The construction of a parameterized query is as follows.
var query = new SqlQuerySpec {
QueryText = "SELECT * FROM c WHERE c.id = @id",
Parameters = new SqlParameterCollection { new SqlParameter { Name =
"@id", Value = collectionId } }
};
We aren't hardcoding the collectionId so this method can be used to delete any series. We can use ‘@’ symbol to prefix parameter names, much like SQL Server.
In the above example, we are querying for a selected series by using Id wherein the Id parameter is defined on this SqlParameterCollection assigned to the parameter's property of this SqlQuerySpec. The SDK then does the paintings of building the final query string for DocumentDB with the collectionId embedded internal of it. We run the question after which use its SelfLink to delete the collection.
Following is the CreateDocumentClient mission implementation.
private static async Task CreateDocumentClient() {
// Create a new instance of the DocumentClient
using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
database = client.CreateDatabaseQuery("SELECT * FROM
c WHERE c.id = 'earthquake'").AsEnumerable().First();
collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
"SELECT * FROM c WHERE c.id = 'myfirstdb'").AsEnumerable().First();
await DeleteCollection(client, "MyCollection1");
await DeleteCollection(client, "MyCollection2");
}
}
When the code is performed, it produces the subsequent output.
**** Delete Collection MyCollection1 in mydb ****
Deleted collection MyCollection1 from database myfirstdb
**** Delete Collection MyCollection2 in mydb ****
Deleted collection MyCollection2 from database myfirstdb
Let’s check some other instance. We can write a question that takes final call and cope with nation as parameters, and then executes it for various values of lastname and place.Nation based at the user input.
SELECT *
FROM Families f
WHERE f.lastName = @lastName AND f.location.state = @addressState
This request can then be sent to DocumentDB as a parameterized JSON question as proven inside the following code.
{
"query": "SELECT * FROM Families f WHERE f.lastName = @lastName AND
f.location.state = @addressState",
"parameters": [
{"name": "@lastName", "value": "Wakefield"},
{"name": "@addressState", "value": "NY"},
]
}