Stored Procedures In Azure Cosmos DB

Introduction

• Stored Procedures are written in JavaScript when it comes to the 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 the 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".
    
    

• Add Config Settings.

  <appSettings>
    <!--Add Cosmos DB Endpoint URL-->
    <add key="DocDbEndpoint" value =""/>
     <!--Add Primary Key-->
    <add key="DocDbMasterKey" value =""/>
  </appSettings>

• How to get Endpoint URL and Master Key?
   
  1. Open the Azure portal.
  2. Go to the Cosmos DB account.
  3. Click on "Keys".
  4. Copy the URI and Primary Key.
      
      
  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’.
      
      
  3. Add the following JavaScript Code.

     function spHelloWorld() {
         var context = getContext();
         var response = context.getResponse();
         response.setBody("Greetings from the DocumentDB server");
     }

Initialize Document Client Instance

1. In Program.cs, add a reference to Configuration;
2. Add a reference to Microsoft.Azure.Documents.Client;
3. Retrieve the Endpoint URL and Primary Key.

   using Microsoft.Azure.Documents;
   using Microsoft.Azure.Documents.Client;
   using System;
   using System.Configuration;
   using System.Linq;
   using System.Threading.Tasks;
   
   namespace CosmosDBCrudOperationd
   {
       class Program
       {
           static void Main(string[] args)
           {
               Task.Run(async () =>
               {
                     var endpoint = ConfigurationManager.AppSettings["DocDbEndpoint"];
                   var masterKey = ConfigurationManager.AppSettings["DocDbMasterKey"];
                   using (var client = new DocumentClient(new Uri(endpoint), masterKey))
                   {
   
                   }
   
               }).Wait();
           }
       }
   }

Execute Stored Procedure

 

1. Read Stored Procedure text from spHelloWorld.js.

   var sprocBody = File.ReadAllText(@"..\..\StoredProcedures\spHelloWorld.js");

2. Create SP definition.

   var spDefinition = new StoredProcedure
               {
                   Id = "spHelloWorld",
                   Body = sprocBody
               };

3. Create a new SP.

   StoredProcedure sproc = await client.CreateStoredProcedureAsync
                   (UriFactory.CreateDocumentCollectionUri("dbFamily", "Families"), spDefinition);
               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.

4. Execute Stored Procedure

   //Execute Store Procedure
               var result = await client.ExecuteStoredProcedureAsync<string>
                   (UriFactory.CreateStoredProcedureUri("dbFamily", "Families", "spHelloWorld"));
               Console.WriteLine($"Executed Store Procedure: response:{result.Response}");

5. Delete Stored Procedure

   //Delete Store Procedure
   await client.DeleteStoredProcedureAsync
       (UriFactory.CreateStoredProcedureUri("dbFamily", "Families", "spHelloWorld"));
   Console.WriteLine("Stored Procedure Deleted Successfully");

Overall Code

using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using System;
using System.Configuration;
using System.IO;
using System.Threading.Tasks;

namespace AzureCosmosDbStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            Task.Run(async () =>
            {
                var endpoint = ConfigurationManager.AppSettings["DocDbEndpoint"];
                var masterKey = ConfigurationManager.AppSettings["DocDbMasterKey"];
                using (var client = new DocumentClient(new Uri(endpoint), masterKey))
                {
                    var sprocBody = File.ReadAllText(@"..\..\StoredProcedures\spHelloWorld.js");
                    var spDefinition = new StoredProcedure
                    {
                        Id = "spHelloWorld",
                        Body = sprocBody
                    };

                    //Create a Store Procedure
                    StoredProcedure sproc = await client.CreateStoredProcedureAsync
                        (UriFactory.CreateDocumentCollectionUri("dbFamily", "Families"), spDefinition);
                    Console.WriteLine($"\r\nCreated Store procedure Id:{sproc.Id} ");

                    //Execute Store Procedure
                    var result = await client.ExecuteStoredProcedureAsync<string>
                        (UriFactory.CreateStoredProcedureUri("dbFamily", "Families", "spHelloWorld"));
                    Console.WriteLine($"Executed Store Procedure: response:{result.Response}");

                    //Delete Store Procedure
                    await client.DeleteStoredProcedureAsync
                        (UriFactory.CreateStoredProcedureUri("dbFamily", "Families", "spHelloWorld"));
                    Console.WriteLine("Stored Procedure Deleted Successfully");
                }
            }).Wait();
        }
    }
}