Using Azure Cosmos DB In MVC Web Application

Introduction 

 

Microsoft Azure Cosmos DB is a database service native to Azure that focuses on providing a high-performance database regardless of your selected API or data model.

 

Azure Cosmos DB storage APIs

 

Azure Cosmos DB can be accessed by using five different APIs. The underlying data structure in Azure Cosmos DB is a data model based on atom record sequences that enabled Azure Cosmos DB to support multiple data models.

 

Azure Cosmos DB will be able to support many more models and APIs over time.

• MongoDB API 
• Table API
• Gremlin API
• Apache Cassandra API
• SQL API

In this article, we will focus on the use of the SQL API.

 

The SQL API in Azure Cosmos DB is a JavaScript and JavaScript Object Notation (JSON) native API based on the Azure Cosmos DB database engine. The SQL API also provides query capabilities rooted in the familiar SQL query language.

 

Create an Azure Cosmos DB account

 

Before writing the code, we need to create a Cosmos DB account, so let's create an Azure Cosmos DB account.

 

In a new browser window, sign in to the Azure portal.

 

Select Create a resource > Databases > Azure Cosmos DB.

 

On the Create Azure Cosmos DB Account page, enter the basic settings for the new Azure Cosmos DB account.

 

 

 

 

 

The account creation takes a few minutes. Wait for the portal to display the "Congratulations! Your Azure Cosmos DB account was created" page.

 

 

 

Create an MVC Web Application

 

Open Visual Studio 2017 on your computer. On the File menu, select New, and then choose Project.

 

In the New Project dialog, select Templates / Visual C# / Web/ ASP.NET Web Application (.NET Framework), name your project, and then select MVC and then click OK.

 

 

 

In the Solution Explorer, right click on your new web application, which is under your Visual Studio solution, and then click Manage NuGet Packages

 

In the NuGet tab, click Browse, and type Azure DocumentDB in the search box.

 

Within the results, find Microsoft.Azure.DocumentDB and click Install.

 

 

The project ready now to start writing some code. Let’s start.

 

Connect to an Azure Cosmos DB account

 

First, we need to create employee entity class in Models folder,

public class Employee
{
    public string Name { get; set; }
    public float Salary { get; set; }
    public DateTime JoinDate { get; set; }
}

 In HomeController class add Employees Action 

public ActionResult Employees()
        {
            return View();
        }

Then, we need to add a View to the employee action.

 

 

 

add these references in HomeController.cs,

using System.Net;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;

Now, add these two constants in constructor 

string EndpointUrl;
    private string PrimaryKey;
    private DocumentClient client;
    public HomeController()
    {
        EndpointUrl = "<your endpoint URL>";
        PrimaryKey = "<your primary key>";

    }

Next, head back to the Azure portal to retrieve your endpoint URL and primary key. In the Azure portal, navigate to your Azure Cosmos DB account, and then click Keys.

 

  

 

Copy the URI from the portal and paste it into <your endpoint URL>. Then copy the PRIMARY KEY from the portal and paste it into <your primary key>.

public HomeController()
{
    EndpointUrl = "https://youraccountname.documents.azure.com:443/";
    PrimaryKey = "F4tQtOfR8AWs6oy7E4ZbbbssqBxjqbL4EWQzoZQgKrZbyj1wT6ffdk6UjIjCQCwVRjp7vJcVQvwh8oVSJg==";

}

Next, we'll start the application by creating a new instance of the DocumentClient.

public  HomeController()
    {
        EndpointUrl = "https:// youraccountname.documents.azure.com:443/";
        PrimaryKey = "F4tQtOfR8AWs6oy7E4ZjvQ3mr3R3OBxjqbL4EWQzoZQgKrZbyj1wT6ffdk6UjIjCQCwVRjp7vJcVQvwh8oVSJg==";
        client = new DocumentClient(new Uri(EndpointUrl), PrimaryKey);

    }

Your Azure Cosmos DB database can be created by using the CreateDatabaseIfNotExistsAsync method of the DocumentClient class. A database is the logical container of JSON document storage partitioned across collections.

 

In Employees Action we need to call the CreateDatabaseIfNotExistsAsync.

public async Task<ActionResult> Employees()
      {
          await this.client.CreateDatabaseIfNotExistsAsync(new Database { Id = "HRDB" });
          return View();
      }

You have successfully created an Azure Cosmos DB database.

 

Create a collection

 

A collection can be created by using the CreateDocumentCollectionIfNotExistsAsync method of the DocumentClient class. A collection is a container of JSON documents and associated JavaScript application logic.

 

Inside Employee action add the following code,

await this.client.CreateDocumentCollectionIfNotExistsAsync(UriFactory.CreateDatabaseUri("HRDB"),
              new DocumentCollection { Id = "EmployeesCollection" });

Query Azure Cosmos DB

 

Azure Cosmos DB supports rich queries against JSON documents stored in each collection.

 

Azure Cosmos DB SQL syntax as well as LINQ. This is a simple Query that returns all employee where employee salary less than 100.

FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };


    IQueryable<Employee> employeeQuery = this.client.CreateDocumentQuery<Employee>(
                  UriFactory.CreateDocumentCollectionUri("HRDB", "EmployeesCollection"), queryOptions)
                  .Where(f => f.Salary >= 100);

Create JSON documents

 

A document can be created by using the CreateDocumentAsync method of the DocumentClient class. Documents are user-defined (arbitrary) JSON content.

 

We can now insert one or more documents. First, we need to create an employee action in the home controller.

public ActionResult AddEmployee()
      {
          return View();
      }

      [HttpPost]
      public async Task<ActionResult> AddEmployee(Employee employee)
      {
          await this.client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri("HRDB", "EmployeesCollection"), employee);
          return RedirectToAction("Employees");
      }

Then, add a View to add employee action.

 

 

 

 Now, run your application and test it.

 

 

 

Delete JSON document

 

Azure Cosmos DB supports deleting JSON documents. We need to create an action for deleting the employee as in the following:

public async Task<ActionResult> DeleteEmployee(string documentId)
        {
            await this.client.DeleteDocumentAsync(UriFactory.CreateDocumentUri("HRDB", "EmployeesCollection", documentId));
            return RedirectToAction("Employees");
        }

Congratulations! Your application is ready now.

 

Here is the full source code. And you can download the full project source code. 😊

public class Employee
    {
        [JsonProperty(PropertyName = "id")]
        public string Id { get; set; }
        public string Name { get; set; }
        public float Salary { get; set; }
        public DateTime JoinDate { get; set; }
    }

public class HomeController : Controller
    {

        string EndpointUrl;
        private string PrimaryKey;
        private DocumentClient client;
        public HomeController()
        {
            EndpointUrl = "https://sbeehlab.documents.azure.com:443/";
            PrimaryKey = "F4tQtOfR8AWs6oy7E4ZjvQ3mr3R3OBxjqbL4EWQzoZQgKrZbyj1wT6ffdk6UjIjCQCwVRjp7vJcVQvwh8oVSJg==";


            client = new DocumentClient(new Uri(EndpointUrl), PrimaryKey);

        }
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
        public async Task<ActionResult> Employees()
        {
            await client.CreateDatabaseIfNotExistsAsync(new Database { Id = "HRDB" });

            await client.CreateDocumentCollectionIfNotExistsAsync(UriFactory.CreateDatabaseUri("HRDB"),
                new DocumentCollection { Id = "EmployeesCollection" });


            FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };


            IQueryable<Employee> employeeQuery = this.client.CreateDocumentQuery<Employee>(
                    UriFactory.CreateDocumentCollectionUri("HRDB", "EmployeesCollection"), queryOptions)
                    .Where(f => f.Salary >= 100);

            return View(employeeQuery);
        }

        public ActionResult AddEmployee()
        {
            return View();
        }

        [HttpPost]
        public async Task<ActionResult> AddEmployee(Employee employee)
        {

            await this.client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri("HRDB", "EmployeesCollection"), employee);

            return RedirectToAction("Employees");
        }

        public async Task<ActionResult> DeleteEmployee(string documentId)
        {
            await this.client.DeleteDocumentAsync(UriFactory.CreateDocumentUri("HRDB", "EmployeesCollection", documentId));
            return RedirectToAction("Employees");
        }
    }