Working with NoSQL using Azure DocumentDB

NoSQL has emerged as an alternative to relational databases. NoSQL does not represent a single product or a single technology. It represents a diverse set of concepts about data storage and manipulation. The motivation behind NoSQL is simplicity, scalability, agility and better control over data availability.

Microsoft entered the NoSQL database space via its Azure platform. Azure provides two NoSQL options: DocumentDB and Azure Table Storage. DocumentDB is a schema free database service fully managed by Microsoft and was designed to natively support JSON documents. It automatically indexes all documents for relational and hierarchical queries. DocumentDB is reliable, highly scalable and can store several terabytes of data. From a learning perspective, if you are familiar with SQL Server or SQL, you can easily pick up DocumentDB.

This article is published from the DNC Magazine for .NET Developers and Architects. Download this magazine from here [Zip PDF] or Subscribe to this magazine for FREE and download all previous and current editions

Important Features of DocumentDB

• Provides default index to all documents in a database.
• Provides support for SQL query like syntax to query JSON documents.
• Uses standard scalar, string functions in a query.
• Provides support for Stored Procedures, User Defined functions, Triggers, etc.

The database entities that DocumentDB manages are referred to as resources, uniquely identified by a logical URI. The Resource Model structure of DocumentDB is shown in the following diagram:

As illustrated in the diagram, each DocumentDB account contains one or more databases. Each database can have one or more users, with permissions associated with them. Each database further contains document collection. The collection contains data along with stored procedures, triggers and user defined functions, using which data can be manipulated.

How to Create DocumentDB Database, Collections?

Since we are using Microsoft Azure, we need an Azure subscription to create and work with the DocumentDB. Please visit this link to go through the free trial, pricing details and the documentation.

We can create a DocumentDB using the following mechanisms:

• Using Microsoft Azure Portal
• Using .NET DocumentDB SDK.
• Using REST APIs.
• Using JavaScript

In this article, we will use the .NET DocumentDB SDK. We can use this SDK in a Console Application, WinForm, ASP.NET as well as MVC applications. We can also create a Web API middle-tier and use .NET DocumentDB SDK in it. To explain the concept, the application we will see shortly is implemented using C# Console application and Visual Studio 2015. (Note: This application can be implemented using Visual Studio 2013 as well.)

Let us first create a DocumentDB account using the portal.

Step 1: Visit the Microsoft Azure Portal, log in with your credentials. On this portal, select New > Data+Storage > Azure DocumentDB, as shown in the following image:

This will open the DocumentDB Account panel, enter the account ID in it as shown in the following image:

Now select the Resource Group. These are containers which are used to manage a collection of Azure resources. The Location is the data center where the resource will be created. Click on Create button, a new DocumentDB account will be created. We can make use of this account to create Database, Collections etc. in it. The account will be created with the following URL:

<account-name>.documents.azure.com

Once the account is created, we will need the URI information for the account. This is the Endpoint URL using which we can connect to the database for performing operations. We also need a Master Key. This is the Primary Key used to authorize the client application to access, create, and query databases. The following image shows the DocumentDB Account Panel from where we can access keys:

Copy these keys, URI and PRIMARY KEY and keep them aside, we will require them in the client application.

Step 2: Open Visual Studio and create a Console application of the name Demo.DDb.SDKApp. In this application, add the Microsoft Azure DocumentDB package using NuGet Package Manager. Right click on the References and select option Manager NuGet Packages. Search for documentdb client and you should see the Microsoft.Azure.DocumentDB client library as shown in the following image:

Install this package in the current application. This will add Microsoft.Azure.Documents.Client assembly in the project. This will also add Netwonsoft.Json assembly in the project. In the project, add a reference to System.Configuration.dll assembly so that we can read contents from the config file.

What is the DocumentDB Client SDK?

The DocumentDB Client SDK provides classes using which we can directly make use of the DocumentDB client classes to create, access and query DocumentDB Database. This SDK internally manages all HTTP calls.

Step 3: In the App.config file of the console application, add the following appSettings for the DocumentDB Account URL and the Primary Key:

<appSettings>
<add key="DDbEndPoint" value="https://xxxx.documents.azure.com:443/"/>
<add key="DDbMasterKey" value="<The PRIMARY KEY>"/>
</appSettings>

Step 4: In the project, add a new class file of the name ModelClass.cs and add the following code in it:

using Newtonsoft.Json;

namespace Demo.DDb.SDKApp
{
    public class College
    {
        [JsonProperty(PropertyName = "id")]
        public string collegeId { get; set; }
        public string collegeName { get; set; }
        public string city { get; set; }
        public string state { get; set; }
        public Branch[] branches { get; set; }
    }

    public class Branch
    {
        public string branchId { get; set; }
        public string branchName { get; set; }
        public int capacity { get; set; }
        public Course[] courses { get; set; }
    }

    public class Course
    {
        public string courseId { get; set; }
        public string courseName { get; set; }
        public bool isOptional { get; set; }
    }
}

The above entity classes are used to define the College structure. These classes will be used to create document data in the DocumentDB database. The JsonProperty attribute applied on the CollegeId property of the College class will define the ‘id’ for the document.

Step 5: In Program.cs, add the following:

1. The declaration for endpoint url and authorization key. This is used to store information of DocumentDB Account URL and Primary key in it.

namespace Demo.DDb.SDKApp
{
class Program
{
    //1.
    static string endpointUrl;
    static string authorizationKey;

2. The DocumentClient object - this is the client side logical representation of the DocumentDB Service. The DocumentCollection object represents document collection which further contains the Document object.

static DocumentClient ddbClient = null;
static DocumentCollection docCCollection = null;
static Document document = null;

3. The Main () method contains the logic for reading Endpoint URL and Authorization Key from the App.Config file.

static void Main(string[] args)
{
    //3.
    endpointUrl = ConfigurationManager.AppSettings["DDbEndPoint"];
    authorizationKey = ConfigurationManager.AppSettings["DDbMasterKey"];

    try
    {
        //4.
        var database = createDatabase("CollegesDb").Result;
        //5.
        createDocumentCollection(database, "CollegesInfo");

    }
    catch (Exception ex)
    {
        Console.WriteLine("Error Occured " + ex.Message);
    }
    Console.ReadLine();
}

4. Call to the createDatabase() method.

//Method to Create a Database in DocumentDb
    static async Task<Database> createDatabase(string ddbName)
    {
        //4a.  
        ddbClient = new DocumentClient(new Uri(endpointUrl), authorizationKey);
        //4b. 
        Database ddbDatabase = ddbClient.CreateDatabaseQuery()
                        .Where(d => d.Id == ddbName).AsEnumerable().FirstOrDefault();
        if (ddbDatabase == null)
        {
            //4c.
            ddbDatabase = await ddbClient.CreateDatabaseAsync(new Database()
            {
                Id = ddbName
            });

            Console.WriteLine("The DocumentDB of name " + ddbName + " created successfully.");
        }
        else
        {
            Console.WriteLine("This Database is already available");
        }
        return ddbDatabase;
    }

The code does the following:

· Create the DocumentClient object based on the Endpoint url and authorization key. This will work on our Azure subscription where we have created a DocumentDB account.

· This will query over the account to retrieve database under the account.

· If the database, in our case it is CollegesDB, is not available, it will be created.

5. Call to the createDocumentCollection() method.

static async void createDocumentCollection(Database ddb, string colName)
{
    //6a.  
    docCCollection = ddbClient.CreateDocumentCollectionQuery("dbs/" + ddb.Id)
                .Where(c => c.Id == colName).AsEnumerable().FirstOrDefault();


    if (docCCollection == null)
    {
        //6b. 
        docCCollection = await ddbClient.CreateDocumentCollectionAsync("dbs/" + ddb.Id,
            new DocumentCollection
            {
                Id = colName
            });

        Console.WriteLine("Created dbs" + ddb.Id + "Collection " + colName);


    }
    //7
    createDocumentData(ddb, docCCollection.Id, "Colleges");

}

This method does the following:

· query the document collection to check if the collection is already present under the database.

· If the collection is not already present, in our case it is CollegesInfo, the collection will be created.

7. The createDocumentCollection() method calls the createDocumentData() method.

static async void createDocumentData(Database ddb, string collectionName, string docname)
{
// 7a.
document = ddbClient.CreateDocumentQuery("dbs/" + ddb.Id + "/colls/" + collectionName)
                    .Where(d => d.Id == docname).AsEnumerable().FirstOrDefault();
if (document == null)
{
    //7b.
    //Record 1
    College msEngg = new College()
    {
        collegeId = "MSEngg",
        collegeName = "MS College of Engineeering",
        city = "Pune",
        state = "Maharashtra",
        branches = new Branch[] {
                new Branch() {
                    branchId="Mech",branchName="Mechanical",capacity=2,
                    courses = new Course[] {
                        new Course() {courseId="EngDsgn",courseName="Engineering Design",isOtional=false },
                        new Course() {courseId="MacDw",courseName="Machine Drawing",isOtional=true }
                    }
                },
                new Branch() {
                    branchId="CS",branchName="Computer Science",capacity=1,
                    courses = new Course[] {
                        new Course() {courseId="DS",courseName="Data Structure",isOtional=false },
                        new Course() {courseId="TOC",courseName="Theory of Computation",isOtional=true },
                        new Course() {courseId="CPD",courseName="Compiler Design",isOtional=false }
                    }
                }
            }
    };

    //7c.
    await ddbClient.CreateDocumentAsync("dbs/" + ddb.Id + "/colls/" + collectionName, msEngg);


    //Record 2
    College lsEngg = new College()
    {
        collegeId = "LSEngg",
        collegeName = "LS College of Engineeering",
        city = "Nagpur",
        state = "Maharashtra",
        branches = new Branch[] {
                new Branch() {
                    branchId="Cvl",branchName="Civil",capacity=2,
                    courses = new Course[] {
                        new Course() {courseId="EngDsgn",courseName="Engineering Design",isOtional=false },
                        new Course() {courseId="TOM",courseName="Theory of Mechanics",isOtional=true }
                    }
                },
                new Branch() {
                    branchId="CS",branchName="Computer Science",capacity=3,
                    courses = new Course[] {
                        new Course() {courseId="APS",courseName="Application Software",isOtional=false },
                        new Course() {courseId="TOC",courseName="Theory of Computation",isOtional=true },
                        new Course() {courseId="TNW",courseName="Theory of Computer Networks",isOtional=false }
                    }
                },
                new Branch() {
                    branchId="IT",branchName="Information Technology",capacity=3,
                    courses = new Course[] {
                        new Course() {courseId="DS",courseName="Data Structure",isOtional=false },
                        new Course() {courseId="CSX",courseName="Computer Security Extension",isOtional=true },
                        new Course() {courseId="APS",courseName="Application Software",isOtional=false }
                    }
                }
            }
    };
    await ddbClient.CreateDocumentAsync("dbs/" + ddb.Id + "/colls/" + collectionName, lsEngg);


    Console.WriteLine("Created dbs/" + ddb.Id + "/colls/" + collectionName + "/docs/" + docname);
}

}

This method does the following:

• The query for the document is executed to check if the document is already present or not. In our case it is Colleges document.
• If the document is not already present, then College structure is created.
• The College structure is created as document in the collection in the database.

Step 6: Run the application and the following result will be displayed:

Step 7: To verify the database, collection and document; open the portal and click on the DocumentDB Account name. You will see the CollegesDb database created:

Step 8: Double-click on CollegesDB to view the CollegesInfo collection in it as shown in the following image:

Step 9: Click on the Document Explorer (2^nd from left) in CollegesDB. The documents created will be displayed with their names:

Step 10: On clicking on the first GUID, the Document details defined in our code will be shown in JSON format as shown in the following image

The steps so far explained how to create DocumentDB, Collection and document in it. Next, let’s see how to query.

Querying DocumentDB

Step 11: In the beginning of the document, we have discussed that DocumentDB can be queried using a SQL like query syntax. In the Program.cs file, add the following method:

static void getDatafromDocument(Database ddb,string collectioName, string docId)
{
     var colleages = ddbClient.CreateDocumentQuery("dbs/" + ddb.Id + "/colls/" + collectioName,
        "SELECT * " +
        "FROM CollegesInfo ci " +
        "WHERE ci.id = \"MSEngg\"");

    foreach (var College in colleages)
    {
        Console.WriteLine("\t Result is" + College);
    }
}

Here we have written a query on the CollegesInfo collection for the document MSEngg. The DocumentDB query uses alias similar to SQL statements. Call this method in the Main () method as shown in the following code:

//5.
    var database =  createDatabase("CollegesDb").Result;
//6.
//      createDocumentCollection(database, "CollegesInfo");
//8
getDatafromDocument(database, "CollegesInfo", "MSEngg");

After running the application, the following result will be displayed:

The result shows some additional data properties as follows:

Executing Queries from the Azure Portal

Queries can also be executed directly from the portal.

Step 12: In the portal, click on the Query Explorer of the CollegesDB database as shown in the following image:

Doing so will open the Query Explorer panel with the Database and collection selected. The default select query will be displayed as:

SELECT * FROM c

Here ‘c’ means all collections in the database. When the Run Query button is clicked, this query will be executed on all collections and result will be returned in a JSON format.

We can write queries as per our requirements.

For e.g. Test the following query in the Query Explorer

SELECT * FROM CollegesInfo c where
c.city="Nagpur"

This will query College information from a city such as Nagpur.

Conclusion:

DocumentDB provides a schema-free JSON form of data store on Microsoft Azure. This allows us to create database schema, read/write data from and to it. A SQL like syntax for querying data provides an easy feature to manage data.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @maheshdotnet or connect with him on LinkedIn