Documentdb Sql 简明教程

DocumentDB SQL - JavaScript Integration

如今，JavaScript 无处不在，而不仅仅在浏览器中。DocumentDB 作为一种现代版 T-SQL 接受 JavaScript，支持原生地对 JavaScript 逻辑进行事务执行，就在数据库引擎内部。DocumentDB 提供了一种编程模型，用于根据存储过程和触发器直接对集合中的基于 JavaScript 的应用程序逻辑进行执行。

These days JavaScript is everywhere, and not just in browsers. DocumentDB embraces JavaScript as a sort of modern day T-SQL and supports the transactional execution of JavaScript logic natively, right inside the database engine. DocumentDB provides a programming model for executing JavaScript-based application logic directly on the collections in terms of stored procedures and triggers.

我们来看一个创建简单存储过程的示例。以下步骤 −

Let’s take a look at an example where we create a simple store procedure. Following are the steps −

Step 1 − 创建一个新的控制台应用程序。

Step 1 − Create a new console applications.

Step 2 − 从 NuGet 添加 .NET SDK。我们此处使用 .NET SDK，这意味着我们将编写一些 C# 代码来创建、执行，然后删除我们的存储过程，但存储过程本身是用 JavaScript 编写的。

Step 2 − Add in the .NET SDK from NuGet. We are using the .NET SDK here, which means that we’ll be writing some C# code to create, execute, and then delete our stored procedure, but the stored procedure itself gets written in JavaScript.

Step 3 − 右键单击解决方案资源管理器中的项目。

Step 3 − Right-click on the project in Solution explorer.

Step 4 − 添加用于存储过程的新 JavaScript 文件并将其称为 HelloWorldStoreProce.js

Step 4 − Add a new JavaScript file for the stored procedure and call it HelloWorldStoreProce.js

每个存储过程都只是一个 JavaScript 函数，因此我们将创建一个新函数，并自然地也将此函数称为 HelloWorldStoreProce 。无论是否为函数命名都没有关系。DocumentDB 只会通过我们在创建时提供的 Id 来引用这个存储过程。

Every stored procedure is just a JavaScript function so we’ll create a new function and naturally we’ll also name this function HelloWorldStoreProce. It doesn’t matter if we give the function a name at all. DocumentDB will only refer to this stored procedure by the Id that we provide when we create it.

function HelloWorldStoreProce() {
   var context = getContext();
   var response = context.getResponse();
   response.setBody('Hello, and welcome to DocumentDB!');
}

存储过程的所有操作就是从上下文中获取 response 对象并调用其 setBody 方法，向调用方返回一个字符串。在 C# 代码中，我们将创建存储过程，执行它，然后删除它。

All the stored procedure does is obtain the response object from the context and call its setBody method to return a string to the caller. In C# code, we will create the stored procedure, execute it, and then delete it.

存储过程的范围是每个集合，因此我们将需要集合的 SelfLink 来创建存储过程。

Stored procedures are scoped per collection, therefore we will need the SelfLink of the collection to create the stored procedure.

Step 5 − 首先查询 myfirstdb 数据库，然后查询 MyCollection 集合。

Step 5 − First query for the myfirstdb database and then for the MyCollection collection.

创建存储过程就像创建 DocumentDB 中的任何其他资源一样。

Creating a stored procedure is just like creating any other resource in DocumentDB.

private async static Task SimpleStoredProcDemo() {
   var endpoint = "https://azuredocdbdemo.documents.azure.com:443/";
   var masterKey =
      "BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";

   using (var client = new DocumentClient(new Uri(endpoint), masterKey)) {
      // Get database
      Database database = client
         .CreateDatabaseQuery("SELECT * FROM c WHERE c.id = 'myfirstdb'")
         .AsEnumerable()
         .First();

      // Get collection
      DocumentCollection collection = client
         .CreateDocumentCollectionQuery(database.CollectionsLink, "SELECT * FROM
         c WHERE c.id = 'MyCollection'")
         .AsEnumerable()
         .First();

      // Create stored procedure
      var sprocBody = File.ReadAllText(@"..\..\HelloWorldStoreProce.js");

      var sprocDefinition = new StoredProcedure {
         Id = "HelloWorldStoreProce",
         Body = sprocBody
      };

      StoredProcedure sproc = await client.
         CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition);
      Console.WriteLine("Created stored procedure {0} ({1})",
         sproc.Id, sproc.ResourceId);

      // Execute stored procedure
      var result = await client.ExecuteStoredProcedureAsync(sproc.SelfLink);
      Console.WriteLine("Executed stored procedure; response = {0}", result.Response);

      // Delete stored procedure
      await client.DeleteStoredProcedureAsync(sproc.SelfLink);
      Console.WriteLine("Deleted stored procedure {0} ({1})",
         sproc.Id, sproc.ResourceId);
   }
}

Step 6 - 首先使用新资源的 Id 创建一个定义对象，然后在 DocumentClient 对象上调用 Create 方法之一。对于一个存储过程来说，定义包括用于传输到服务器的 Id 和实际 JavaScript 代码。

Step 6 − First create a definition object with the Id for the new resource and then call one of the Create methods on the DocumentClient object. In the case of a stored procedure, the definition includes the Id and the actual JavaScript code that you want to ship over to the server.

Step 7 - 调用 File.ReadAllText 以从 JS 文件中提取存储过程代码。

Step 7 − Call File.ReadAllText to extract the stored procedure code out of the JS file.

Step 8 - 将存储过程代码分配给定义对象的 body 属性。

Step 8 − Assign the stored procedure code to the body property of the definition object.

对于 DocumentDB 来说，我们在定义中指定的 Id 是存储过程的名称，无关乎我们实际为 JavaScript 函数命名的名称。

As far as DocumentDB is concerned, the Id we specify here, in the definition, is the name of the stored procedure, regardless of what we actually name the JavaScript function.

然而，在创建存储过程和其他服务器端对象时，建议为 JavaScript 函数命名，而且这些函数名称要与我们在 DocumentDB 定义中设置的 Id 匹配。

Nevertheless when creating stored procedures and other server-side objects, it is recommended that we name JavaScript functions and that those function names do match the Id that we have set in the definition for DocumentDB.

Step 9 - 调用 CreateStoredProcedureAsync ，传递给 SelfLink 的 MyCollection 以及存储过程定义。这将创建 DocumentDB 为其分配的存储过程和 ResourceId 。

Step 9 − Call CreateStoredProcedureAsync, passing in the SelfLink for the MyCollection collection and the stored procedure definition. This creates the stored procedure and ResourceId that DocumentDB assigned to it.

Step 10 - 调用存储过程。 ExecuteStoredProcedureAsync 采用类型参数，将其设置为存储过程返回的值的预期数据类型，如果你想要返回一个动态对象，则可以简单地将其指定为一个对象。那是一个在运行时将绑定其属性的对象。

Step 10 − Call the stored procedure. ExecuteStoredProcedureAsync takes a type parameter that you set to the expected data type of the value returned by the stored procedure, which you can specify simply as an object if you want a dynamic object returned. That is an object whose properties will be bound at run-time.

在本例中，我们知道存储过程只返回一个字符串，因此我们调用 ExecuteStoredProcedureAsync<string> 。

In this example we know that our stored procedure is just returning a string and so we call ExecuteStoredProcedureAsync<string>.

以下是 Program.cs 文件的完整实现。

Following is the complete implementation of Program.cs file.

using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;

using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DocumentDBStoreProce {
   class Program {
      private static void Main(string[] args) {
         Task.Run(async () => {
            await SimpleStoredProcDemo();
         }).Wait();
      }

      private async static Task SimpleStoredProcDemo() {
         var endpoint = "https://azuredocdbdemo.documents.azure.com:443/";
         var masterKey =
            "BBhjI0gxdVPdDbS4diTjdloJq7Fp4L5RO/StTt6UtEufDM78qM2CtBZWbyVwFPSJIm8AcfDu2O+AfV T+TYUnBQ==";

         using (var client = new DocumentClient(new Uri(endpoint), masterKey)) {
            // Get database
            Database database = client
               .CreateDatabaseQuery("SELECT * FROM c WHERE c.id = 'myfirstdb'")
               .AsEnumerable()
               .First();

            // Get collection
            DocumentCollection collection = client
               .CreateDocumentCollectionQuery(database.CollectionsLink,
               "SELECT * FROM c WHERE c.id = 'MyCollection'")
               .AsEnumerable()
               .First();

            // Create stored procedure
            var sprocBody = File.ReadAllText(@"..\..\HelloWorldStoreProce.js");

            var sprocDefinition = new StoredProcedure {
               Id = "HelloWorldStoreProce",
               Body = sprocBody
            };

            StoredProcedure sproc = await client
               .CreateStoredProcedureAsync(collection.SelfLink, sprocDefinition);

            Console.WriteLine("Created stored procedure {0} ({1})", sproc
               .Id, sproc.ResourceId);

            // Execute stored procedure
            var result = await client
               .ExecuteStoredProcedureAsync<string>(sproc.SelfLink);
            Console.WriteLine("Executed stored procedure; response = {0}",
               result.Response);

            // Delete stored procedure
            await client.DeleteStoredProcedureAsync(sproc.SelfLink);
            Console.WriteLine("Deleted stored procedure {0} ({1})",
               sproc.Id, sproc.ResourceId);
         }
      }
   }
}

执行以上代码时，会产生以下输出。

When the above code is executed, it produces the following output.

Created stored procedure HelloWorldStoreProce (Ic8LAMEUVgACAAAAAAAAgA==)

Executed stored procedure; response = Hello, and welcome to DocumentDB!

如上面的输出所示，response 属性包含存储过程返回的“Hello, and welcome to DocumentDB!”。

As seen in the above output, the response property has the “Hello, and welcome to DocumentDB!” returned by our stored procedure.