Tutorial: Build a .NET Core app to manage data stored in a SQL API account

As a developer, you might have applications that use NoSQL document data. You can use the SQL API account in Azure Cosmos DB to store and access this document data. This tutorial shows you how to build a .NET Core app to create and query data stored in the SQL API account of Azure Cosmos DB.

This tutorial covers the following tasks:

  • Creating and connecting to an Azure Cosmos account
  • Configuring your Visual Studio solution
  • Creating an online database
  • Creating a collection
  • Creating JSON documents
  • Performing CRUD operations on the items, container and database

Don't have time create the application? The complete solution is available on GitHub.

Prerequisites

  • An active Azure account. If you don't have one, you can sign up for a free account.

    You can Try Azure Cosmos DB for free without an Azure subscription, free of charge and commitments. Or, you can use the Azure Cosmos DB Emulator with a URI of https://localhost:8081. The Primary Key is provided in Authenticating requests.

  • Download and use the free Visual Studio 2017 Community Edition. If you are developing a Universal Windows Platform (UWP) app, you should use Visual Studio 2017 with version 15.4 or higher. Make sure that you enable Azure development during the Visual Studio setup.

    • For MacOS or Linux, you can develop .NET Core apps from the command line by installing the .NET Core SDK for the platform of your choice.

    • For Windows, you can develop .NET Core apps from the command line by installing the .NET Core SDK.

Create an Azure Cosmos account

Use the following steps to create an Azure Cosmos account:

  1. Sign in to the Azure portal.

  2. Select Create a resource > Databases > Azure Cosmos DB.

    The Azure portal Databases pane

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

    Setting Value Description
    Subscription Subscription name Select the Azure subscription that you want to use for this Azure Cosmos account.
    Resource Group Resource group name Select a resource group, or select Create new, then enter a unique name for the new resource group.
    Account Name Enter a unique name Enter a name to identify your Azure Cosmos account. Because documents.azure.com is appended to the ID that you provide to create your URI, use a unique ID.

    The ID can only contain lowercase letters, numbers, and the hyphen (-) character. It must be between 3-31 characters in length.
    API Core (SQL) The API determines the type of account to create. Azure Cosmos DB provides five APIs: Core (SQL) and MongoDB for document data, Gremlin for graph data, Azure Table, and Cassandra. Currently, you must create a separate account for each API.

    Select Core (SQL) to create a document database and query by using SQL syntax.

    Learn more about the SQL API.
    Location Select the region closest to your users Select a geographic location to host your Azure Cosmos DB account. Use the location that is closest to your users to give them the fastest access to the data.

    The new account page for Azure Cosmos DB

  4. Select Review + create. You can skip the Network and Tags sections.

  5. Review the account settings, and then select Create. It takes a few minutes to create the account. Wait for the portal page to display Your deployment is complete.

    The Azure portal Notifications pane

  6. Select Go to resource to go to the Azure Cosmos DB account page.

    The Azure Cosmos DB account page

Set up your Visual Studio solution

  1. Open Visual Studio 2017 on your computer.

  2. On the File menu, select New, and then choose Project.

  3. In the New Project dialog, select Templates > Visual C# > .NET Core > Console Application (.NET Core), name your project DocumentDBGettingStarted, and then select OK.

    Screenshot of the New Project window

  4. In the Solution Explorer, right-click DocumentDBGettingStarted.

  5. On the same menu, select Manage NuGet Packages.

    Screenshot of the right-click menu for the project

  6. On the NuGet tab, select Browse at the top of the window, and type azure documentdb in the search box. Ensure that the include prerelease checkbox is checked.

  7. In the results, find Microsoft.Azure.DocumentDB.Core and select Install.

    The package ID for the library is Microsoft.Azure.DocumentDB.Core. If you are targeting a .NET Framework version (like net461) that is not supported by this .NET Core NuGet package, then use Microsoft.Azure.DocumentDB, which supports all .NET Framework versions starting with .NET Framework 4.5.

  8. When prompted, accept the NuGet package installations and the license agreement.

Now that setup is complete, let's start writing some code. You can find a completed code project of this tutorial on GitHub.

Connect to an Azure Cosmos account

Connect to an Azure Cosmos account by importing the required dependencies. To import the dependencies, add the following code to the beginning of your Program.cs file:

using System;

// ADD THIS PART TO YOUR CODE
using System.Linq;
using System.Threading.Tasks;
using System.Net;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Newtonsoft.Json;

Next, add these two constants and your client variable underneath your public class Program.

class Program
{
    // ADD THIS PART TO YOUR CODE
    private const string EndpointUri = "<your endpoint URI>";
    private const string PrimaryKey = "<your key>";
    private DocumentClient client;

Next, go to the Azure portal to retrieve your URI and primary key. The Azure Cosmos DB URI and primary key are necessary for your application to understand where to connect to, and for Azure Cosmos DB to trust your application's connection.

In the Azure portal, go to your Azure Cosmos account, and then select Keys.

Copy the URI from the portal and paste it into <your endpoint URI> in the program.cs file. Then copy the PRIMARY KEY from the portal and paste it into <your key>. Make sure to remove the < and > but leave the double quotes around your endpoint and key.

Get keys from Azure portal

We'll start the getting started application by creating a new instance of the DocumentClient.

Below the Main method, add the new asynchronous task called GetStartedDemo, which will instantiate your new DocumentClient.

static void Main(string[] args)
{
}

// ADD THIS PART TO YOUR CODE
private async Task GetStartedDemo()
{
    this.client = new DocumentClient(new Uri(EndpointUri), PrimaryKey);
}

Add the following code to run your asynchronous task from your Main method. The Main method catches exceptions and writes them to the console.

static void Main(string[] args)
{
        // ADD THIS PART TO YOUR CODE
        try
        {
                Program p = new Program();
                p.GetStartedDemo().Wait();
        }
        catch (DocumentClientException de)
        {
                Exception baseException = de.GetBaseException();
                Console.WriteLine("{0} error occurred: {1}, Message: {2}", de.StatusCode, de.Message, baseException.Message);
        }
        catch (Exception e)
        {
                Exception baseException = e.GetBaseException();
                Console.WriteLine("Error: {0}, Message: {1}", e.Message, baseException.Message);
        }
        finally
        {
                Console.WriteLine("End of demo, press any key to exit.");
                Console.ReadKey();
        }

Select the DocumentDBGettingStarted button to build and run the application.

Create a database

Before you add the code for creating a database, add a helper method for writing to the console. Copy and paste the WriteToConsoleAndPromptToContinue method underneath the GetStartedDemo method.

// ADD THIS PART TO YOUR CODE
private void WriteToConsoleAndPromptToContinue(string format, params object[] args)
{
        Console.WriteLine(format, args);
        Console.WriteLine("Press any key to continue ...");
        Console.ReadKey();
}

Create your Azure Cosmos DB database by using the CreateDatabaseAsync method of the DocumentClient class. A database is the logical container of JSON document storage partitioned across collections. Copy and paste the following code to your GetStartedDemo method underneath the client creation. This creates a database named FamilyDB.

private async Task GetStartedDemo()
{
    this.client = new DocumentClient(new Uri(EndpointUri), PrimaryKey);

    // ADD THIS PART TO YOUR CODE
    await this.client.CreateDatabaseIfNotExistsAsync(new Database { Id = "FamilyDB_oa" });

Select the DocumentDBGettingStarted button to run your application.

Create a collection

Create a collection by using the CreateDocumentCollectionAsync method of the DocumentClient class. A collection is a container of JSON documents and associated JavaScript application logic.

Warning

CreateDocumentCollectionAsync creates a new collection with reserved throughput, which has pricing implications. For more details, go to the pricing page.

Copy and paste the following code to your GetStartedDemo method underneath the database creation. This code creates a document collection named FamilyCollection_oa.

    this.client = new DocumentClient(new Uri(EndpointUri), PrimaryKey);

    await this.client.CreateDatabaseIfNotExistsAsync(new Database { Id = "FamilyDB_oa" });

    // ADD THIS PART TO YOUR CODE
    await this.client.CreateDocumentCollectionIfNotExistsAsync(UriFactory.CreateDatabaseUri("FamilyDB_oa"), new DocumentCollection { Id = "FamilyCollection_oa" });

Select the DocumentDBGettingStarted button to run your application.

Create JSON documents

Create a document by using the CreateDocumentAsync method of the DocumentClient class. Documents are user-defined (arbitrary) JSON content. You can now insert one or more documents.

First, you create a Family class that represents objects stored within Azure Cosmos DB. You also create Parent, Child, Pet, and Address subclasses that are used within Family. The documents must have an Id property serialized as id in JSON. Create these classes by adding the following internal subclasses after the GetStartedDemo method. Copy and paste the Family, Parent, Child, Pet, and Address classes underneath the WriteToConsoleAndPromptToContinue method.

private void WriteToConsoleAndPromptToContinue(string format, params object[] args)
{
    Console.WriteLine(format, args);
    Console.WriteLine("Press any key to continue ...");
    Console.ReadKey();
}

// ADD THIS PART TO YOUR CODE
public class Family
{
    [JsonProperty(PropertyName = "id")]
    public string Id { get; set; }
    public string LastName { get; set; }
    public Parent[] Parents { get; set; }
    public Child[] Children { get; set; }
    public Address Address { get; set; }
    public bool IsRegistered { get; set; }
    public override string ToString()
    {
            return JsonConvert.SerializeObject(this);
    }
}

public class Parent
{
    public string FamilyName { get; set; }
    public string FirstName { get; set; }
}

public class Child
{
    public string FamilyName { get; set; }
    public string FirstName { get; set; }
    public string Gender { get; set; }
    public int Grade { get; set; }
    public Pet[] Pets { get; set; }
}

public class Pet
{
    public string GivenName { get; set; }
}

public class Address
{
    public string State { get; set; }
    public string County { get; set; }
    public string City { get; set; }
}

Copy and paste the CreateFamilyDocumentIfNotExists method underneath your CreateDocumentCollectionIfNotExists method.

// ADD THIS PART TO YOUR CODE
private async Task CreateFamilyDocumentIfNotExists(string databaseName, string collectionName, Family family)
{
    try
    {
        await this.client.ReadDocumentAsync(UriFactory.CreateDocumentUri(databaseName, collectionName, family.Id));
        this.WriteToConsoleAndPromptToContinue("Found {0}", family.Id);
    }
    catch (DocumentClientException de)
    {
        if (de.StatusCode == HttpStatusCode.NotFound)
        {
            await this.client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(databaseName, collectionName), family);
            this.WriteToConsoleAndPromptToContinue("Created Family {0}", family.Id);
        }
        else
        {
            throw;
        }
    }
}

And insert two documents, one each for the Andersen Family and the Wakefield Family. Copy and paste the code that follows // ADD THIS PART TO YOUR CODE to your GetStartedDemo method underneath the document collection creation.

    await this.client.CreateDatabaseIfNotExistsAsync(new Database { Id = "FamilyDB_oa" });

await this.CreateDocumentCollectionIfNotExists("FamilyDB_oa", "FamilyCollection_oa");

// ADD THIS PART TO YOUR CODE
Family andersenFamily = new Family
{
        Id = "Andersen.1",
        LastName = "Andersen",
        Parents = new Parent[]
        {
                new Parent { FirstName = "Thomas" },
                new Parent { FirstName = "Mary Kay" }
        },
        Children = new Child[]
        {
                new Child
                {
                        FirstName = "Henriette Thaulow",
                        Gender = "female",
                        Grade = 5,
                        Pets = new Pet[]
                        {
                                new Pet { GivenName = "Fluffy" }
                        }
                }
        },
        Address = new Address { State = "WA", County = "King", City = "Seattle" },
        IsRegistered = true
};

await this.CreateFamilyDocumentIfNotExists("FamilyDB_oa", "FamilyCollection_oa", andersenFamily);

Family wakefieldFamily = new Family
{
        Id = "Wakefield.7",
        LastName = "Wakefield",
        Parents = new Parent[]
        {
                new Parent { FamilyName = "Wakefield", FirstName = "Robin" },
                new Parent { FamilyName = "Miller", FirstName = "Ben" }
        },
        Children = new Child[]
        {
                new Child
                {
                        FamilyName = "Merriam",
                        FirstName = "Jesse",
                        Gender = "female",
                        Grade = 8,
                        Pets = new Pet[]
                        {
                                new Pet { GivenName = "Goofy" },
                                new Pet { GivenName = "Shadow" }
                        }
                },
                new Child
                {
                        FamilyName = "Miller",
                        FirstName = "Lisa",
                        Gender = "female",
                        Grade = 1
                }
        },
        Address = new Address { State = "NY", County = "Manhattan", City = "NY" },
        IsRegistered = false
};

await this.CreateFamilyDocumentIfNotExists("FamilyDB_oa", "FamilyCollection_oa", wakefieldFamily);

Select the DocumentDBGettingStarted button to run your application.

Hierarchical relationship between the account, the online database, the collection

Query Azure Cosmos DB resources

Azure Cosmos DB supports rich queries against JSON documents stored in each collection. The following sample code shows various queries - using both Azure Cosmos DB SQL syntax and LINQ - that you can run against the documents we inserted in the previous step.

Copy and paste the ExecuteSimpleQuery method underneath your CreateFamilyDocumentIfNotExists method.

// ADD THIS PART TO YOUR CODE
private void ExecuteSimpleQuery(string databaseName, string collectionName)
{
    // Set some common query options
    FeedOptions queryOptions = new FeedOptions { MaxItemCount = -1 };

        // Here we find the Andersen family via its LastName
        IQueryable<Family> familyQuery = this.client.CreateDocumentQuery<Family>(
                UriFactory.CreateDocumentCollectionUri(databaseName, collectionName), queryOptions)
                .Where(f => f.LastName == "Andersen");

        // The query is executed synchronously here, but can also be executed asynchronously via the IDocumentQuery<T> interface
        Console.WriteLine("Running LINQ query...");
        foreach (Family family in familyQuery)
        {
            Console.WriteLine("\tRead {0}", family);
        }

        // Now execute the same query via direct SQL
        IQueryable<Family> familyQueryInSql = this.client.CreateDocumentQuery<Family>(
                UriFactory.CreateDocumentCollectionUri(databaseName, collectionName),
                "SELECT * FROM Family WHERE Family.LastName = 'Andersen'",
                queryOptions);

        Console.WriteLine("Running direct SQL query...");
        foreach (Family family in familyQueryInSql)
        {
                Console.WriteLine("\tRead {0}", family);
        }

        Console.WriteLine("Press any key to continue ...");
        Console.ReadKey();
}

Copy and paste the following code to your GetStartedDemo method underneath the second document creation.

await this.CreateFamilyDocumentIfNotExists("FamilyDB_oa", "FamilyCollection_oa", wakefieldFamily);

// ADD THIS PART TO YOUR CODE
this.ExecuteSimpleQuery("FamilyDB_oa", "FamilyCollection_oa");

Select the DocumentDBGettingStarted button to run your application.

The following diagram illustrates how the Azure Cosmos DB SQL query syntax is called against the collection you created. The same logic applies to the LINQ query.

Diagram illustrating the scope and meaning of the query used by the NoSQL tutorial to create a C# console application

The FROM keyword is optional in the query because Azure Cosmos DB queries are already scoped to a single collection. Therefore, "FROM Families f" can be swapped with "FROM root r", or any other variable name you choose. Azure Cosmos DB infers that the Families, root, or variable name that you chose references the current collection by default.

Replace JSON document

Azure Cosmos DB supports replacing JSON documents.

Copy and paste the ReplaceFamilyDocument method underneath your ExecuteSimpleQuery method.

// ADD THIS PART TO YOUR CODE
private async Task ReplaceFamilyDocument(string databaseName, string collectionName, string familyName, Family updatedFamily)
{
    await this.client.ReplaceDocumentAsync(UriFactory.CreateDocumentUri(databaseName, collectionName, familyName), updatedFamily);
    this.WriteToConsoleAndPromptToContinue("Replaced Family {0}", familyName);
}

Copy and paste the following code to your GetStartedDemo method underneath the query execution. After replacing the document, this will run the same query again to view the changed document.

await this.CreateFamilyDocumentIfNotExists("FamilyDB_oa", "FamilyCollection_oa", wakefieldFamily);

this.ExecuteSimpleQuery("FamilyDB_oa", "FamilyCollection_oa");

// ADD THIS PART TO YOUR CODE
// Update the Grade of the Andersen Family child
andersenFamily.Children[0].Grade = 6;

await this.ReplaceFamilyDocument("FamilyDB_oa", "FamilyCollection_oa", "Andersen.1", andersenFamily);

this.ExecuteSimpleQuery("FamilyDB_oa", "FamilyCollection_oa");

Select the DocumentDBGettingStarted button to run your application.

Delete JSON document

Azure Cosmos DB supports deleting JSON documents.

Copy and paste the DeleteFamilyDocument method underneath your ReplaceFamilyDocument method.

// ADD THIS PART TO YOUR CODE
private async Task DeleteFamilyDocument(string databaseName, string collectionName, string documentName)
{
    await this.client.DeleteDocumentAsync(UriFactory.CreateDocumentUri(databaseName, collectionName, documentName));
    Console.WriteLine("Deleted Family {0}", documentName);
}

Copy and paste the following code to your GetStartedDemo method underneath the second query execution.

await this.ReplaceFamilyDocument("FamilyDB_oa", "FamilyCollection_oa", "Andersen.1", andersenFamily);

this.ExecuteSimpleQuery("FamilyDB_oa", "FamilyCollection_oa");

// ADD THIS PART TO CODE
await this.DeleteFamilyDocument("FamilyDB_oa", "FamilyCollection_oa", "Andersen.1");

Select the DocumentDBGettingStarted button to run your application.

Delete the database

Deleting the created database will remove the database and all child resources (collections, documents, etc.). Copy and paste the following code to your GetStartedDemo method underneath the document to delete the entire database and all child resources.

this.ExecuteSimpleQuery("FamilyDB_oa", "FamilyCollection_oa");

await this.DeleteFamilyDocument("FamilyDB_oa", "FamilyCollection_oa", "Andersen.1");

// ADD THIS PART TO CODE
// Clean up/delete the database
await this.client.DeleteDatabaseAsync(UriFactory.CreateDatabaseUri("FamilyDB_oa"));

Select the DocumentDBGettingStarted button to run your application.

Run your C# console application

Select the DocumentDBGettingStarted button in Visual Studio to build the application in debug mode. You should see the output of your get started app in the console window. The output will show the results of the queries we added and should match the example text below.

Created FamilyDB_oa
Press any key to continue ...
Created FamilyCollection_oa
Press any key to continue ...
Created Family Andersen.1
Press any key to continue ...
Created Family Wakefield.7
Press any key to continue ...
Running LINQ query...
    Read {"id":"Andersen.1","LastName":"Andersen","District":"WA5","Parents":[{"FamilyName":null,"FirstName":"Thomas"},{"FamilyName":null,"FirstName":"Mary Kay"}],"Children":[{"FamilyName":null,"FirstName":"Henriette Thaulow","Gender":"female","Grade":5,"Pets":[{"GivenName":"Fluffy"}]}],"Address":{"State":"WA","County":"King","City":"Seattle"},"IsRegistered":true}
Running direct SQL query...
    Read {"id":"Andersen.1","LastName":"Andersen","District":"WA5","Parents":[{"FamilyName":null,"FirstName":"Thomas"},{"FamilyName":null,"FirstName":"Mary Kay"}],"Children":[{"FamilyName":null,"FirstName":"Henriette Thaulow","Gender":"female","Grade":5,"Pets":[{"GivenName":"Fluffy"}]}],"Address":{"State":"WA","County":"King","City":"Seattle"},"IsRegistered":true}
Replaced Family Andersen.1
Press any key to continue ...
Running LINQ query...
    Read {"id":"Andersen.1","LastName":"Andersen","District":"WA5","Parents":[{"FamilyName":null,"FirstName":"Thomas"},{"FamilyName":null,"FirstName":"Mary Kay"}],"Children":[{"FamilyName":null,"FirstName":"Henriette Thaulow","Gender":"female","Grade":6,"Pets":[{"GivenName":"Fluffy"}]}],"Address":{"State":"WA","County":"King","City":"Seattle"},"IsRegistered":true}
Running direct SQL query...
    Read {"id":"Andersen.1","LastName":"Andersen","District":"WA5","Parents":[{"FamilyName":null,"FirstName":"Thomas"},{"FamilyName":null,"FirstName":"Mary Kay"}],"Children":[{"FamilyName":null,"FirstName":"Henriette Thaulow","Gender":"female","Grade":6,"Pets":[{"GivenName":"Fluffy"}]}],"Address":{"State":"WA","County":"King","City":"Seattle"},"IsRegistered":true}
Deleted Family Andersen.1
End of demo, press any key to exit.

You've now completed the tutorial and have a working C# console application.

Clean up resources

When they're no longer needed, you can delete the resource group, the Azure Cosmos account, and all the related resources. To do so, select the resource group for the virtual machine, select Delete, and then confirm the name of the resource group to delete.

Next steps

In this tutorial, you've learned how to build a .NET Core app to manage data stored in the SQL API account of Azure Cosmos DB. You can now proceed to the next article: