In Focus

Stored Procedures In Azure Cosmos DB

There are many performance benefits when executing complex operations like Batching in case of using Stored Procedures. Reason is all these operations get executed closer to data as Stored Procedures are stored at server side.

Introduction

  • Stored Procedures are written in JavaScript when it comes to Cosmos DB context.
  • They are written and stored at Collection Level.
  • They provide a secure and transactional environment to perform complex sequences of operations.

Advantages of Stored Procedures

  • Atomic Environment
    • All operations performed under single Stored Procedure execution are Atomic. That means either all operations will get succeed or none of them will.

  • Performance
    • There are many performance benefits when executing complex operations, like Batching, in case of using Stored Procedures. he reason behind this is that all these operations get executed closer to data as Stored Procedures are stored at server side.

  • Cost Effective
    • As Stored Procedures are pre-compiled, it saves JavaScript compilation cost.

Creating First Stored Procedure

  • Create Solution and add NuGet Package
    1. Create a new Console app.
    2. Right-click on Project -> Manage NuGet Package.
    3. Search for ‘documentdb’ under the Browse tab.
    4. Select Microsoft.Azure.DocumentDB package and click "Install".

      Azure Cosmos DB 
  • Add Config Settings.
    1. <appSettings>  
    2.   <!--Add Cosmos DB Endpoint URL-->  
    3.   <add key="DocDbEndpoint" value =""/>  
    4.    <!--Add Primary Key-->  
    5.   <add key="DocDbMasterKey" value =""/>  
    6. </appSettings> 
  • How to get Endpoint URL and Master Key?

    1. Open Azure portal.
    2. Go to Cosmos DB account.
    3. Click on "Keys".
    4. Copy the URI and Primary Key.

      Azure Cosmos DB

    5. URI = DocDbEndpoint
    6. Primary Key = DocDbMasterKey
  • Create A New Stored Procedure

    1. Add a new folder to the application and give it a name as StoredProcedure.
    2. Right-click on Folder -> Add New Item -> JavaScript File and give it a name as ‘spHelloWorld’.

      Azure Cosmos DB

    3. Add the following JavaScript Ccode.
      1. function spHelloWorld() {  
      2.     var context = getContext();  
      3.     var response = context.getResponse();  
      4.     response.setBody("Greetings from the DocumentDB server");  
Initialize Document Client Instance
  1. In Program.cs, add reference to Configuration;
  2. Add reference to Microsoft.Azure.Documents.Client;
  3. Retrieve Endpoint URL and Primary Key.
    1. using Microsoft.Azure.Documents;  
    2. using Microsoft.Azure.Documents.Client;  
    3. using System;  
    4. using System.Configuration;  
    5. using System.Linq;  
    6. using System.Threading.Tasks;  
    7.   
    8. namespace CosmosDBCrudOperationd  
    9. {  
    10.     class Program  
    11.     {  
    12.         static void Main(string[] args)  
    13.         {  
    14.             Task.Run(async () =>  
    15.             {  
    16.                   var endpoint = ConfigurationManager.AppSettings["DocDbEndpoint"];  
    17.                 var masterKey = ConfigurationManager.AppSettings["DocDbMasterKey"];  
    18.                 using (var client = new DocumentClient(new Uri(endpoint), masterKey))  
    19.                 {  
    20.                     
    21.                 }  
    22.   
    23.             }).Wait();  
    24.         }  
    25.     }  
    26. }  
Execute Stored Procedure
  1. Read Stored Procedure text from spHelloWorld.js.
    1. var sprocBody = File.ReadAllText(@"..\..\StoredProcedures\spHelloWorld.js"); 
  1. Create SP definition.
    1. var spDefinition = new StoredProcedure  
    2.             {  
    3.                 Id = "spHelloWorld",  
    4.                 Body = sprocBody  
    5.             }; 
  1. Create new SP.
    1. StoredProcedure sproc = await client.CreateStoredProcedureAsync  
    2.                 (UriFactory.CreateDocumentCollectionUri("dbFamily""Families"), spDefinition);  
    3.             Console.WriteLine($"\r\nCreated Store procedure Id:{sproc.Id} ");  
    Note - You need to create a database (dbFamily) and Collection (Families) before creating an SP as SPs are stored at Collection level. You can create a Database and Collection using portal or in Code.
  1. Execute Stored Procedure
    1. //Execute Store Procedure  
    2.             var result = await client.ExecuteStoredProcedureAsync<string>  
    3.                 (UriFactory.CreateStoredProcedureUri("dbFamily""Families""spHelloWorld"));  
    4.             Console.WriteLine($"Executed Store Procedure: response:{result.Response}");  
  1. Delete Stored Procedure
    1. //Delete Store Procedure  
    2. await client.DeleteStoredProcedureAsync  
    3.     (UriFactory.CreateStoredProcedureUri("dbFamily""Families""spHelloWorld"));  
    4. Console.WriteLine("Stored Procedure Deleted Successfully"); 
Overall Code
  1. using Microsoft.Azure.Documents;  
  2. using Microsoft.Azure.Documents.Client;  
  3. using System;  
  4. using System.Configuration;  
  5. using System.IO;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace AzureCosmosDbStoredProcedure  
  9. {  
  10.     class Program  
  11.     {  
  12.         static void Main(string[] args)  
  13.         {  
  14.             Task.Run(async () =>  
  15.             {  
  16.                 var endpoint = ConfigurationManager.AppSettings["DocDbEndpoint"];  
  17.                 var masterKey = ConfigurationManager.AppSettings["DocDbMasterKey"];  
  18.                 using (var client = new DocumentClient(new Uri(endpoint), masterKey))  
  19.                 {  
  20.                     var sprocBody = File.ReadAllText(@"..\..\StoredProcedures\spHelloWorld.js");  
  21.                     var spDefinition = new StoredProcedure  
  22.                     {  
  23.                         Id = "spHelloWorld",  
  24.                         Body = sprocBody  
  25.                     };  
  26.   
  27.                     //Create Store Procedure  
  28.                     StoredProcedure sproc = await client.CreateStoredProcedureAsync  
  29.                         (UriFactory.CreateDocumentCollectionUri("dbFamily""Families"), spDefinition);  
  30.                     Console.WriteLine($"\r\nCreated Store procedure Id:{sproc.Id} ");  
  31.   
  32.                     //Execute Store Procedure  
  33.                     var result = await client.ExecuteStoredProcedureAsync<string>  
  34.                         (UriFactory.CreateStoredProcedureUri("dbFamily""Families""spHelloWorld"));  
  35.                     Console.WriteLine($"Executed Store Procedure: response:{result.Response}");  
  36.   
  37.                     //Delete Store Procedure  
  38.                     await client.DeleteStoredProcedureAsync  
  39.                         (UriFactory.CreateStoredProcedureUri("dbFamily""Families""spHelloWorld"));  
  40.                     Console.WriteLine("Stored Procedure Deleted Successfully");  
  41.                 }  
  42.             }).Wait();  
  43.         }  
  44.     }  
  45. }