Your cart is currently empty!
Category: documentdb Sql
-
Khóa học miễn phí DocumentDB SQL – Parameterized nhận dự án làm có lương
DocumentDB SQL – Parameterized
In relational databases, a parameterized query 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 can be expressed with the familiar @ notation. The most important reason to use parameterized queries is to avoid SQL injection attacks. It can also provide robust handling and escaping of user input.
Let’s take a look at an example where we will 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 are not hardcoding the collectionId so this method can be used to delete any collection. We can use ‘@’ symbol to prefix parameter names, similar to SQL Server.
In the above example, we are querying for a specific collection by Id where the Id parameter is defined in this SqlParameterCollection assigned to the parameter”s property of this SqlQuerySpec. The SDK then does the work of constructing the final query string for DocumentDB with the collectionId embedded inside of it. We run the query and then use its SelfLink to delete the collection.
Following is the CreateDocumentClient task 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 executed, it produces the following 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 take a look at another example. We can write a query that takes last name and address state as parameters, and then executes it for various values of lastname and location.state based on 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 query as shown in 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"}, ] }
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí Linq to SQL Translation nhận dự án làm có lương
DocumentDB SQL – Linq to SQL Translation
In DocumentDB, we actually use SQL to query documents. If we are doing .NET development, there is also a LINQ provider that can be used and which can generate appropriate SQL from a LINQ query.
Supported Data Types
In DocumentDB, all JSON primitive types are supported in the LINQ provider included with the DocumentDB .NET SDK which are as follows −
- Numeric
- Boolean
- String
- Null
Supported Expression
The following scalar expressions are supported in the LINQ provider included with the DocumentDB .NET SDK.
-
Constant Values − Includes constant values of the primitive data types.
-
Property/Array Index Expressions − Expressions refer to the property of an object or an array element.
-
Arithmetic Expressions − Includes common arithmetic expressions on numerical and Boolean values.
-
String Comparison Expression − Includes comparing a string value to some constant string value.
-
Object/Array Creation Expression − Returns an object of compound value type or anonymous type or an array of such objects. These values can be nested.
Supported LINQ Operators
Here is a list of supported LINQ operators in the LINQ provider included with the DocumentDB .NET SDK.
-
Select − Projections translate to the SQL SELECT including object construction.
-
Where − Filters translate to the SQL WHERE, and support translation between && , || and ! to the SQL operators.
-
SelectMany − Allows unwinding of arrays to the SQL JOIN clause. Can be used to chain/nest expressions to filter array elements.
-
OrderBy and OrderByDescending − Translates to ORDER BY ascending/descending.
-
CompareTo − Translates to range comparisons. Commonly used for strings since they’re not comparable in .NET.
-
Take − Translates to the SQL TOP for limiting results from a query.
-
Math Functions − Supports translation from .NET’s Abs, Acos, Asin, Atan, Ceiling, Cos, Exp, Floor, Log, Log10, Pow, Round, Sign, Sin, Sqrt, Tan, Truncate to the equivalent SQL built-in functions.
-
String Functions − Supports translation from .NET’s Concat, Contains, EndsWith, IndexOf, Count, ToLower, TrimStart, Replace, Reverse, TrimEnd, StartsWith, SubString, ToUpper to the equivalent SQL built-in functions.
-
Array Functions − Supports translation from .NET’s Concat, Contains, and Count to the equivalent SQL built-in functions.
-
Geospatial Extension Functions − Supports translation from stub methods Distance, Within, IsValid, and IsValidDetailed to the equivalent SQL built-in functions.
-
User-Defined Extension Function − Supports translation from the stub method UserDefinedFunctionProvider.Invoke to the corresponding user-defined function.
-
Miscellaneous − Supports translation of coalesce and conditional operators. Can translate Contains to String CONTAINS, ARRAY_CONTAINS or the SQL IN depending on context.
Let’s take a look at an example where we will be using the .Net SDK. Following are the three documents which we will be consider for this example.
New Customer 1
{
"name": "New Customer 1",
"address": {
"addressType": "Main Office",
"addressLine1": "123 Main Street",
"location": {
"city": "Brooklyn",
"stateProvinceName": "New York"
},
"postalCode": "11229",
"countryRegionName": "United States"
},
}
New Customer 2
{
"name": "New Customer 2",
"address": {
"addressType": "Main Office",
"addressLine1": "678 Main Street",
"location": {
"city": "London",
"stateProvinceName": " London "
},
"postalCode": "11229",
"countryRegionName": "United Kingdom"
},
}
New Customer 3
{
"name": "New Customer 3",
"address": {
"addressType": "Main Office",
"addressLine1": "12 Main Street",
"location": {
"city": "Brooklyn",
"stateProvinceName": "New York"
},
"postalCode": "11229",
"countryRegionName": "United States"
},
}
Following is the code in which we query using LINQ. We”ve defined a LINQ query in q, but it won”t execute until we run .ToList on it.
private static void QueryDocumentsWithLinq(DocumentClient client) {
Console.WriteLine();
Console.WriteLine("**** Query Documents (LINQ) ****");
Console.WriteLine();
Console.WriteLine("Quering for US customers (LINQ)");
var q =
from d in client.CreateDocumentQuery<Customer>(collection.DocumentsLink)
where d.Address.CountryRegionName == "United States"
select new {
Id = d.Id,
Name = d.Name,
City = d.Address.Location.City
};
var documents = q.ToList();
Console.WriteLine("Found {0} US customers", documents.Count);
foreach (var document in documents) {
var d = document as dynamic;
Console.WriteLine(" Id: {0}; Name: {1}; City: {2}", d.Id, d.Name, d.City);
}
Console.WriteLine();
}
The SDK will convert our LINQ query into SQL syntax for DocumentDB, generating a SELECT and WHERE clause based on our LINQ syntax.
Let’s call the above queries from the CreateDocumentClient task.
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 =
''myfirstdb''").AsEnumerable().First();
collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
"SELECT * FROM c WHERE c.id = ''MyCollection''").AsEnumerable().First();
QueryDocumentsWithLinq(client);
}
}
When the above code is executed, it produces the following output.
**** Query Documents (LINQ) **** Quering for US customers (LINQ) Found 2 US customers Id: 7e9ad4fa-c432-4d1a-b120-58fd7113609f; Name: New Customer 1; City: Brooklyn Id: 34e9873a-94c8-4720-9146-d63fb7840fad; Name: New Customer 1; City: Brooklyn
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
