SQL with Manoj

Here in this post we will use C# .net code (for beginners like me) to see how to:
1. Connect to a Cosmos DB instance
2. Get list of all Databases in a Cosmos DB
3. Iterate through all the Databases and get the list of all Collections (or Tables)
4. Get COUNT of all documents/items (or records) in these Collections

On Azure portal go to your Azure Cosmos DB instance, from Overview tab go to Keys tab and copy the “URI” & “PRIMARY READ-ONLY KEY” key values:

1. First we will use the Uri & Primary Key to connect to CosmosDB service by using DocumentClient() method.

2. Then we will form a simple query to retrieve all Databases, will use with CreateDatabaseQuery() method to retrieve list of all databases.

3. Now to get list of all Collections present in each Database we will iterate through each Database, use the CreateDocumentCollectionQuery() method using the Database instance with Self link property.

4. Now to get the Document link we will also iterate through each Collection by using CreateDocumentQuery() method.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;

namespace CosmosTables
{
    class ReadCosmosDB
    {
        static void Main(string[] args)
        {
            string EndPointUri = "https://YourCosmosDBname.documents.azure.com:443/";
            string PK = "YourPrimaryKey==";

            DocumentClient cosmosClient = new DocumentClient(new Uri(EndPointUri), PK);

            string sqlQuery = "SELECT * FROM d";
            var dbs = cosmosClient.CreateDatabaseQuery(sqlQuery).ToList();

            foreach (var db in dbs)
            {
                Console.WriteLine("Database: " + db.id);
                Console.WriteLine("- Collections: "); 

                List<DocumentCollection> collections = cosmosClient.CreateDocumentCollectionQuery((String)db._self).ToList();

                foreach (var collection in collections)
                {

                    var count = cosmosClient.CreateDocumentQuery<int>(collection.SelfLink, $"SELECT value count(1) FROM c",
                                    new FeedOptions(){EnableCrossPartitionQuery = true, MaxItemCount = 1,}).AsEnumerable().First();

                    Console.WriteLine("  - " + collection.Id + $", Count: " + count);
                }
                Console.WriteLine(Environment.NewLine);
            }
            Console.WriteLine("Finished reading all DB Collections");
        }
    }
}