Implement Stored Procedure, UDFs And Trigger In Cosmos DB Using ASP.NET Core Web Application

Today, in this article we will discuss how to execute any server-side objects (like Stored Procedure, Triggers, or User-defined Functions) in Cosmos DB using Asp.Net Core Web application. Now, as we all know that we can create or develop stored procedures, triggers, or user-defined functions in a SQL-API based Cosmos DB Database. We can develop these server-side objects using JavaScript language since Cosmos DB provides integration on JavaScript languages and also allow transactional execution of JavaScript. So, first, we will discuss some basic concepts about Server-Side objects in Cosmos DB and then we will discuss how to integrate these using an Asp.Net Core application.

 

Advantages of Server-Side Programming

 

In SQL API Based Cosmos DB database, we can develop or implement three different types of Server-side objects; i.e. User-Defined Functions, Stored Procedures, and Triggers. In Cosmos DB Database, we can create these server-side objects as functions in JavaScript (ECMA-262). We can’t create or change any server-side objects using data-definition languages (DDL) just like RDBMS systems. Using JavaScript for defining Stored Procedure, Triggers, and User-defined functions (UDFs) in Cosmos DB, we can develop any type of rich application which provides the following benefits or advantages,

 

Procedural Logic

 

As one of the high-level languages, JavaScript can provide us with a rich and familiar interface to express business logic. We can perform a sequence of complex operations on the data.

 

Atomic Transactions

 

Azure Cosmos DB always assures that the database operations which are performed within a single stored procedure or a trigger are atomic. This atomic functionality lets an application combine related operations into a single batch so that either all of the operations succeed or none of them succeed.

 

Performance

 

Since Cosmos DB mainly used JSON based data objects which is internally mapped to the JavaScript language type system. This type of mapping provides us several optimizations like the lazy materialization of JSON documents in the buffer pool and making them available on-demand to the executing code.

 

Batching

 

You can group operations like inserts and submit them in bulk. The network traffic latency costs and the store overhead to create separate transactions are reduced significantly. Pre-compilation: Stored procedures, triggers, and UDFs are implicitly precompiled to the byte code format to avoid compilation cost at the time of each script invocation. Due to precompilation, the invocation of stored procedures is fast and has a low footprint.

 

Encapsulation

 

Stored procedures can be used to group logic in one place. Encapsulation adds an abstraction layer on top of the data, which enables you to evolve your applications independently from the data. This layer of abstraction is helpful when the data is schema-less and you don't have to manage to add additional logic directly into your application. The abstraction lets your keep the data secure by streamlining the access from the scripts.

 

Create an Asp.Net Core MVC Application to Store Data in Cosmos DB

 

So, first, we develop an Asp.Net Core MVC Application through which we will perform the normal CRUD based operation in the Cosmos DB.

 

Step 1 - Create Asp.Net Core MVC Application

Now open the Microsoft Visual Studio and Click on Create a New Project

In the Create New Project dialog box, select Asp.Net Core Web Application for C# and then Click on Next Button.

 

 

In the Configure your new project window, provide the project name and then click on the Create button.

 

In the Create a New ASP.Net Core Web Application dialog, select Web Application (Model-View-Controller) and then click on Create Button.

 

 

Now, Visual Studio creates a blank MVC application.

 

Now Click on the Solution Explorer and Select the Solution and then Right and choose Add --> New Project options.

 

In the New Project dialog box, select Class Library (.Net Core) Project Template and Click on Next Button.

 

Type the Project name as DataContextLibrary and then click on Ok Button. This project mainly contains all the operation codes related to the Cosmos DB connect and also operations code like Insert data, delete data, etc.

 

 

Now similar to the above project add another Class Library (.Net Core) project with the name ModelClass which mainly contains all the Model or Entity class files.

 

Step 2 - Add NuGet Packages Related to Cosmos DB

 

In Solution Explorer, Select the DataContextLibrary project and right-click and select Manage Nuget Packages.

 

In the NuGet Package Manager, search Microsoft.Azure.CosmosDB and Install It. 

 

Visual Studio will download and installs the Azure Cosmos DB Package and its dependencies.

 

Step 3 - Prepare the Asp.Net Core MVC Application

 

Now, add models, data context service, the views, and controllers to the Application. First, we will prepare the application to perform the basic CRUD operation with the help Cosmos DB library in .Net Core.

 

Select the ModelClass Project and add a new class called Product.cs and add the below class file,

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using Newtonsoft.Json;
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json.Serialization;

namespace ModelClass
{
    public class Product
    {
        public Product()
        {
            CreatedDate = DateTime.Now;
        }

        [BsonId]
        [JsonProperty("id")]
        public string Id { get; set; }

        [Key]
        public int ProductId { get; set; }

        [Required(ErrorMessage = "Please Enter Name")]
        [Column(TypeName = "varchar(50)")]
        [JsonProperty("name")]
        public string Name { get; set; }

        [Required(ErrorMessage = "Please Enter Description")]
        [Column(TypeName = "varchar(500)")]
        [JsonProperty("description")]
        public string Description { get; set; }

        [Required(ErrorMessage ="Please Enter Quantity")]
        [JsonProperty("quantity")]
        public int Quantity { get; set; }

        [JsonProperty("unitPrice")]
        public decimal UnitPrice { get; set; }

        [JsonProperty("totalStockPrice")]
        public decimal TotalStockPrice { get; set; }

        [JsonProperty("createdOn")]
        public DateTime CreatedDate { get; set; }
    }
}

Now select the DataContextLayer project and Create a New Folder called AppConfig.

 

Now add a new class file named AppConfiguration.cs under the AppConfig folder and add the below code,

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace DataContextLayer.AppConfig
{
    public static class AppConfiguration
    {
        private static IConfiguration currentConfig;

        public static void SetConfig(IConfiguration configuration)
        {
            currentConfig = configuration;
        }


        public static string GetConfiguration(string configKey)
        {
            try
            {
                string connectionString = currentConfig.GetConnectionString(configKey);
                return connectionString;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            return "";
        }

    }
}

Now, add an Interface called ICosmosDBSQLDataContext.cs file and add the below code in that file,

using ModelClass;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;

namespace DataContextLayer.SQL
{
    public interface ICosmosDBSQLDataContext
    {
        Task<IEnumerable<Product>> GetProductsAsync(string query);

        Task<Product> GetProductByIdAsync(string id);

        Task<IEnumerable<Product>> GetAllProducts();

        Task AddProductAsync(Product item);

        Task UpdateProductAsync(string id, Product item);

        Task DeleteProductAsync(string id);
    }
}

Now add a class file called CosmosDBSQLDataContext.cs to implement the above interface class.

using DataContextLayer.AppConfig;
using Microsoft.Azure.Cosmos;
using ModelClass;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataContextLayer.SQL.Implementations
{
    public class CosmosDBSQLDataContext : ICosmosDBSQLDataContext
    {
        private Container _container;

        public string containerName { get; set; }

        public CosmosDBSQLDataContext(CosmosClient dbClient, string databaseName, string containerName)
        {
            this._container = dbClient.GetContainer(databaseName, containerName);
        }

        public async Task AddProductAsync(Product item)
        {
            await this._container.CreateItemAsync<Product>(item, new PartitionKey(item.Id));
        }

        public async Task UpdateProductAsync(string id, Product item)
        {
            await this._container.UpsertItemAsync<Product>(item, new PartitionKey(id));
        }

        public async Task DeleteProductAsync(string id)
        {
            await this._container.DeleteItemAsync<Product>(id, new PartitionKey(id));
        }

        public async Task<Product> GetProductByIdAsync(string id)
        {
            try
            {
                ItemResponse<Product> response = await this._container.ReadItemAsync<Product>(id, new
                PartitionKey(id));
                return response.Resource;
            }
            catch (CosmosException ex) when (ex.StatusCode == System.Net.HttpStatusCode.NotFound)
            {
                return null;
            }
        }

        public async Task<IEnumerable<Product>> GetProductsAsync(string queryString)
        {
            var query = this._container.GetItemQueryIterator<Product>(new QueryDefinition(queryString));
            List<Product> results = new List<Product>();
            while (query.HasMoreResults)
            {
                var response = await query.ReadNextAsync();
                results.AddRange(response.ToList());
            }
            return results;
        }

        public async Task<IEnumerable<Product>> GetAllProducts()
        {
            var query = this._container.GetItemQueryIterator<Product>();
            List<Product> results = new List<Product>();
            while (query.HasMoreResults)
            {
                var response = await query.ReadNextAsync();
                results.AddRange(response.ToList());
            }
            return results;
        }

    }
}

Now select the view folder under Asp.net Core MVC project and add the below views –

• Add a List Product View (Index.cshtml)
• Add a Create New Product View (Create.cshtml)
• Add an Edit Product View (Edit.cshtml)
• Add a Delete Product View (Delete.cshtml)
• Add a Display Product details View (Details.cshtml)

Now click on the Controller folder and add a new Controller called ProductsController.cs file and add the below code in that file,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DataContextLayer;
using DataContextLayer.SQL;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using ModelClass;
using MongoDB.Bson;
using MongoDB.Driver;



namespace CosmosDb_Demo_Crud_ServerObjects.Controllers
{
    public class ProductsController : Controller
    {
        private readonly ICosmosDBSQLDataContext _cosmosDBService;

        public ProductsController(ICosmosDBSQLDataContext cosmosDBService)
        {
            _cosmosDBService = cosmosDBService;
        }

        [ActionName("Index")]
        public async Task<IActionResult> Index()
        {
            return View(await _cosmosDBService.GetProductsAsync("SELECT * FROM c"));
        }

        [ActionName("Create")]
        public ActionResult Create()
        {
            return View();
        }

        [HttpPost]
        [ActionName("Create")]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> CreateAsync(Product item)
        {
            if (ModelState.IsValid)
            {
                item.Id = Guid.NewGuid().ToString();
                await _cosmosDBService.AddProductAsync(item);
                return RedirectToAction("Index");
            }
            return View(item);
        }

        [HttpPost]
        [ActionName("Edit")]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> EditAsync(Product item)
        {
            if (ModelState.IsValid)
            {
                await _cosmosDBService.UpdateProductAsync(item.Id, item);
                return RedirectToAction("Index");
            }
            return View(item);
        }

        [ActionName("Edit")]
        public async Task<ActionResult> EditAsync(string id)
        {
            if (id == null)
            {
                return BadRequest();
            }
            Product item = await _cosmosDBService.GetProductByIdAsync(id);
            if (item == null)
            {
                return NotFound();
            }
            return View(item);
        }

        [ActionName("Delete")]
        public async Task<ActionResult> DeleteAsync(string id)
        {
            if (id == null)
            {
                return BadRequest();
            }
            Product item = await _cosmosDBService.GetProductByIdAsync(id);
            if (item == null)
            {
                return NotFound();
            }
            return View(item);
        }

        [HttpPost]
        [ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> DeleteConfirmedAsync( string id)
        {
            await _cosmosDBService.DeleteProductAsync(id);
            return RedirectToAction("Index");
        }

        [ActionName("Details")]
        public async Task<ActionResult> DetailsAsync(string id)
        {
            return View(await _cosmosDBService.GetProductByIdAsync(id));
        }

    }
}

Now open the appsettings.json file and add the below configuration key,

"ConnectionStrings": {
    "CosmosDBAccount": "https://xxxxxx.documents.azure.com:443/",
    "ConnectionKey": "xxxxxxxxxxxxxxxxxxxxxxxxyyyyyyyyyyyyyyyyyyyyyyy==",
    "DatabaseName": "SampleDB",
    "ContainerName": "Products"
  }

In the above configuration setting, CosmosDBAccount and ConnectionKey are available in the Connection string section of the Cosmos DB Accounts in the Azure Portal.

 

Also, we provide the Database Name and Container Name as a key.

 

Now if the mentioned database exists, it will connect it otherwise application needs to create the database at the time of first time loading.

 

For that purpose, open the Startup.cs file and then add the below method which checks the existence of the database and if the database does not exist, it will create the database.

private static async Task<CosmosDBSQLDataContext> InitializeCosmosClientInstanceAsync(IConfigurationSection configurationSection)
        {
            string databaseName = configurationSection.GetSection("DatabaseName").Value;
            string containerName = configurationSection.GetSection("ContainerName").Value;
            string account = configurationSection.GetSection("CosmosDBAccount").Value;
            string key = configurationSection.GetSection("ConnectionKey").Value;
            Microsoft.Azure.Cosmos.CosmosClient client = new Microsoft.Azure.Cosmos.CosmosClient(account, key);
            CosmosDBSQLDataContext cosmosDbService = new CosmosDBSQLDataContext(client, databaseName, containerName);
            Microsoft.Azure.Cosmos.DatabaseResponse database = await
            client.CreateDatabaseIfNotExistsAsync(databaseName);
            await database.Database.CreateContainerIfNotExistsAsync(containerName, "/id");
            return cosmosDbService;
        }

Now inject the ICosmosDBSQLDataContext in the ConfigureServices().

services.AddSingleton(_ => Configuration);
services.AddSingleton<ICosmosDBSQLDataContext>(InitializeCosmosClientInstanceAsync(Configuration.GetSection("ConnectionStrings")).GetAwaiter().GetResult());

Now run the application and it will load the Product list UI.

 

 

Now click on the Add button to add a new Product,

 

After clicking on the Save Button, the newly added record will be displayed in the Product List view.

 

Step 4 - Define UDFs in Cosmos DB and fetch the value

 

Now, open the Data Explorer in Azure Portal and create a User Defined Function (UDFs) which will convert the product rate in USD and return in the select statement.

function getPriceInUSD(price, rate) {
    if (typeof price != 'number')
        throw new Error("Parameter must be numeric");
    if (price < 0)
        throw new Error("Parameter must be non-negative");

    var finalPrice = price / rate;
    return finalPrice;
}

Now in the ProductsController, make the below changes in the Index() –

[ActionName("Index")]
        public async Task<IActionResult> Index()
        {
            return View(await _cosmosDBService.GetProductsAsync("SELECT c.id, c.ProductId, c.name, c.description, c.quantity, c.unitPrice,c.createdOn, udf.getPriceInUSD(c.unitPrice, 70) rateInUSD FROM c"));
        }

Now to display the Price in USD, we need to add the below property in the Product.cs file –

[JsonProperty("rateInUSD")]
public decimal RateInUSD { get; set; }

Add this property in the Index.cshtml file and then execute the projects.

 

Step 5 - Create a Stored Procedure to Save the Data into Product Collection

 

Still now we implement the CRUD operation without using any server-side objects. Now, we will insert the data into the Product collection using the Stored Procedure. For that purpose, open the Data Explorer in Azure Portal and create a Stored Procedure that will insert the product data into the collections.

function spInsertProductData(items) {
    var collection = getContext().getCollection();
    var collectionLinkInfo = collection.getSelfLink();

    var count = 0;
    if (!items) throw new Error("The array is undefined or null.");

    var numItems = items.length;
    if (numItems == 0) {
        getContext().getResponse().setBody(0);
        return;
    }

    tryCreate(items[count], callback);

    function tryCreate(item, callback) {
        var options = { disableAutomaticIdGeneration: false };
        var isAccepted = collection.createDocument(collectionLinkInfo, item, options, callback);
        if (!isAccepted) getContext().getResponse().setBody(count);
    }

    function callback(err, item, options) {
        if (err) throw err;
        count++;
        if (count >= numItems) {
            getContext().getResponse().setBody(count);
        } else {
            tryCreate(items[count], callback);
        }
    }
}

Now open the CosmosDBSQLDataContext.cs file and make the below changes in the AddProductAsync().

public async Task AddProductAsync(Product item)
        {
            try
            {
                string item1 = JsonConvert.SerializeObject(item);
                List<Product> products = new List<Product>();
                products.Add(item);
                await this._container.Scripts.ExecuteStoredProcedureAsync<string>("spInsertProductData", new PartitionKey(item.Id.ToString()), new[] { products });
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Now execute the application and try to insert a new product.

 

Step 6 - Create a Pre-Trigger to calculate and save the Stock Price value

 

Now, we need to create a trigger to manipulate the inserted data to perform either any special type of data validation or any business operation. As we know that Trigger can be two types – PreTrigger and Post Trigger. Now, in this example, we will create a Pre-trigger that will calculate the stock price and save it into the collections. For that purpose, open the Data Explorer in Azure Portal and create a Trigger which will calculate the stock price.

function validateProductData() {
    var context = getContext();
    var request = context.getRequest();

    // item to be created in the current operation
    var itemToCreate = request.getBody();

    // validate properties
    if (!("timestamp" in itemToCreate)) {
        var ts = new Date();
        itemToCreate["timestamp"] = ts.getTime();
    }

    //update Total Stock Amount
    var quantity = 0;
    var price = 0;
    var stockPrice = 0;
    if (("quantity" in itemToCreate)) {
        quantity = itemToCreate["quantity"];
    }

    if (("unitPrice" in itemToCreate)) {
        price = itemToCreate["unitPrice"];
    }

    if (quantity > 0 && price > 0) {
        stockPrice = quantity * price;
    }
    itemToCreate["totalStockPrice"] = stockPrice;

    // update the item that will be created
    request.setBody(itemToCreate);
}

Now open the CosmosDBSQLDataContext.cs file and make the below changes in the AddProductAsync().

public async Task AddProductAsync(Product item)
        {
            await this._container.CreateItemAsync<Product>(item, new PartitionKey(item.Id), new ItemRequestOptions { PreTriggers = new List<string> { "validateProductData" } });

        }

Now execute the application and try to insert a new product and check either Stock Price is calculated or not for the newly added document.

 

Conclusion

 

In this article, we discuss how to store data using the Asp.Net Core application in Cosmos DB. Also, we discussed the steps related to the implementation of Server-Side Objects in Cosmos DB. Any suggestions or feedback or query related to this article are most welcome.