Connecting The Same Cosmos DB Database Using SQL API And Mongo API From A Blazor App

Introduction to Cosmos DB

If you look at the metadata information of any collection in Cosmos DB, you can find the following metadata properties in both, the SQL API and MongoDB API.

• _rid: auto-generated resource id
• _ts: auto-generated timestamp (last updated) epoch value
• _etag: auto-generated GUID. Used for optimistic concurrency
• _self: auto-generated URI path to the resource. This is very useful when using with SQL API for Azure Cosmos DB
• _attachments: URI path suffix to the resource attachment

For those who are new to Cosmos DB, Azure Cosmos DB is Microsoft’s globally distributed, multi-model database. With the click of a button, Azure Cosmos DB enables you to elastically and independently scale throughput and storage across any number of Azure’s geographic regions. It offers throughput, latency, availability, and consistency guarantees with comprehensive service level agreements (SLAs), something no other database service can offer. Currently, there are five different types of APIs supported by Cosmos DB, as given below.

You can also refer to the below article to read more about Cosmos DB.

• Cosmos DB Articles on C# Corner

About Blazor Framework

As per Microsoft’s documentation about Blazor, it is an experimental .NET web framework using C#/Razor and HTML that runs in the browser with WebAssembly. Blazor provides all the benefits of a client-side web UI framework using .NET on the client and optionally, on the server.

You can refer to the below articles about Blazor to get more details.

• Blazor Articles on C# Corner

Create a Blazor application in Visual Studio 2017 Community

 

Open Visual Studio and choose “Create New project” and select ASP.NET Core Web Application. Please give a valid name to your project.

 

 

 

Currently, there are three types of Blazor templates available. We are going with Blazor (ASP.NET Core hosted) template.

 

 

Our application will be created in a moment. If we look at the solution structure, we can see there are three projects created under our solution - “Client”, “Server” and “Shared”.

 

 

By default, Blazor created some files in these three projects. We can remove all the unwanted files like “Counter.cshtml”, “FetchData.cshtml”, “SurveyPrompt.cshtml” from the Client project and “SampleDataController.cs” file from Server project and delete “WeatherForecast.cs” file from the Shared project too.

We can create a “Models” folder in the “Shared” project and create an “Employee” class under this folder. As we are creating a simple Employee app, we must provide all the required properties inside this class.

We are creating a common model for both, SQL and Mongo APIs. For MongoDB API, we need BsonTypeObjectId and BsonId. For that, we must install “MongoDB.Driver” NuGet package in the Shared project.

 

 

We must install “Newtonsoft.Json” for serializing the class objects and properties.

 

 

Employee.cs

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using Newtonsoft.Json;
namespace BlazorCosmosDBSQLandMongo.Shared.Models
{
    public class Employee
    {
        [JsonProperty(PropertyName = "id")]
        [BsonId]
        [BsonRepresentation(BsonType.ObjectId)]
        public string Id { get; set; }
        [JsonProperty(PropertyName = "name")]
        public string Name { get; set; }
        [JsonProperty(PropertyName = "address")]
        public string Address { get; set; }
        [JsonProperty(PropertyName = "gender")]
        public string Gender { get; set; }
        [JsonProperty(PropertyName = "company")]
        public string Company { get; set; }
        [JsonProperty(PropertyName = "designation")]
        public string Designation { get; set; }
    }
}

We are using separate providers for SQL API and Mongo API. Let us go to “Server” project and create a new “DataAccess” folder.

We can create a common “IDataAccessProvider” interface. We will create separate data access providers for SQL API and Mongo API using this interface.

IDataAccessProvider.cs

using BlazorCosmosDBSQLandMongo.Shared.Models;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCosmosDBSQLandMongo.Server.DataAccess
{
    public interface IDataAccessProvider
    {
        Task Add(Employee employee);
        Task Update(Employee employee);
        Task Delete(string id);
        Task<Employee> GetEmployee(string id);
        Task<IEnumerable<Employee>> GetEmployees();
    }
}

We must install “MongoDB.Driver” NuGet package again  for this “Server” project for Mongo API. Also install “Microsoft.Azure.DocumentDB.Core” for SQL API.

 

Please note, I have used .NET Core 2.1 version in this project. So, I am using the previous version of (v2.0.0) DocumentDB.Core NuGet.

 

The current version (when writing this article) is v2.2.2.

 

 

We can create “SqlApiRepository” generic class now. This class will be used in the data access provider class later.

 

SqlApiRepository.cs

using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCosmosDBSQLandMongo.Server.DataAccess
{
    public static class SqlApiRepository<T> where T : class
    {
        private static readonly string Endpoint = "https://localhost:8081/";
        private static readonly string Key = "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==";
        private static readonly string DatabaseId = "SarathCosmosDB";
        private static readonly string CollectionId = "EmployeeSQL";
        private static DocumentClient client;
        public static void Initialize()
        {
            client = new DocumentClient(new Uri(Endpoint), Key, new ConnectionPolicy { EnableEndpointDiscovery = false });
            CreateDatabaseIfNotExistsAsync().Wait();
            CreateCollectionIfNotExistsAsync(CollectionId).Wait();
        }
        private static async Task CreateDatabaseIfNotExistsAsync()
        {
            try
            {
                await client.ReadDatabaseAsync(UriFactory.CreateDatabaseUri(DatabaseId));
            }
            catch (DocumentClientException e)
            {
                if (e.StatusCode == System.Net.HttpStatusCode.NotFound)
                {
                    await client.CreateDatabaseAsync(new Database { Id = DatabaseId });
                }
                else
                {
                    throw;
                }
            }
        }
        private static async Task CreateCollectionIfNotExistsAsync(string collectionId)
        {
            try
            {
                await client.ReadDocumentCollectionAsync(UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionId));
            }
            catch (DocumentClientException e)
            {
                if (e.StatusCode == System.Net.HttpStatusCode.NotFound)
                {
                    await client.CreateDocumentCollectionAsync(
                        UriFactory.CreateDatabaseUri(DatabaseId),
                        new DocumentCollection { Id = collectionId },
                        new RequestOptions { OfferThroughput = 1000 });
                }
                else
                {
                    throw;
                }
            }
        }
        public static async Task<T> GetSingleItemAsync(string id, string collectionId)
        {
            try
            {
                Document document = await client.ReadDocumentAsync(UriFactory.CreateDocumentUri(DatabaseId, collectionId, id));
                return (T)(dynamic)document;
            }
            catch (DocumentClientException e)
            {
                if (e.StatusCode == System.Net.HttpStatusCode.NotFound)
                {
                    return null;
                }
                else
                {
                    throw;
                }
            }
        }
        public static async Task<IEnumerable<T>> GetItemsAsync(string collectionId)
        {
            IDocumentQuery<T> query = client.CreateDocumentQuery<T>(
                UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionId),
                new FeedOptions { MaxItemCount = -1 })
                .AsDocumentQuery();
            List<T> results = new List<T>();
            while (query.HasMoreResults)
            {
                results.AddRange(await query.ExecuteNextAsync<T>());
            }
            return results;
        }
        public static async Task<Document> CreateItemAsync(T item, string collectionId)
        {
            return await client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionId), item);
        }
        public static async Task<Document> UpdateItemAsync(string id, T item, string collectionId)
        {
            return await client.ReplaceDocumentAsync(UriFactory.CreateDocumentUri(DatabaseId, collectionId, id), item);
        }
        public static async Task DeleteItemAsync(string id, string collectionId)
        {
            await client.DeleteDocumentAsync(UriFactory.CreateDocumentUri(DatabaseId, collectionId, id));
        }
    }
}

Please note, we have hard coded the connection endpoint and account key in the above class.

We can create “SqlApiProvider” class now. This class will inherit the “IDataAccessProvider” interface.

 

SqlApiProvider.cs

using BlazorCosmosDBSQLandMongo.Shared.Models;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCosmosDBSQLandMongo.Server.DataAccess
{
    public class SqlApiProvider : IDataAccessProvider
    {
        private static readonly string CollectionId = "EmployeeSQL";
        public async Task Add(Employee employee)
        {
            try
            {
                await SqlApiRepository<Employee>.CreateItemAsync(employee, CollectionId);
            }
            catch
            {
                throw;
            }
        }
        public async Task<Employee> GetEmployee(string id)
        {
            try
            {
                return await SqlApiRepository<Employee>.GetSingleItemAsync(id, CollectionId);
            }
            catch
            {
                throw;
            }
        }
        public async Task<IEnumerable<Employee>> GetEmployees()
        {
            try
            {
                return await SqlApiRepository<Employee>.GetItemsAsync(CollectionId);
            }
            catch
            {
                throw;
            }
        }
        public async Task Update(Employee employee)
        {
            try
            {
                await SqlApiRepository<Employee>.UpdateItemAsync(employee.Id, employee, CollectionId);
            }
            catch
            {
                throw;
            }
        }
        public async Task Delete(string id)
        {
            try
            {
                await SqlApiRepository<Employee>.DeleteItemAsync(id, CollectionId);
            }
            catch
            {
                throw;
            }
        }
    }
}

For Mongo API, we create a Mongo DB context file and create a data access provider.

MongoApiContext.cs

using BlazorCosmosDBSQLandMongo.Shared.Models;
using MongoDB.Driver;
namespace BlazorCosmosDBSQLandMongo.Server.DataAccess
{
    public class MongoApiContext
    {
        private readonly IMongoDatabase _mongoDb;
        public MongoApiContext()
        {
            var client = new MongoClient("mongodb://localhost:C2y6yDjf5%2FR%2Bob0N8A7Cgv30VRDJIWEHLM%2B4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw%2FJw%3D%3D@localhost:10255/admin?ssl=true");
            _mongoDb = client.GetDatabase("SarathCosmosDB");
        }
        public IMongoCollection<Employee> Employee
        {
            get
            {
                return _mongoDb.GetCollection<Employee>("EmployeeMongo");
            }
        }
    }
}

We can create “MongoApiProvider” class and inherit “IDataAccessProvider” interface to this class.

MongoApiProvider.cs

using BlazorCosmosDBSQLandMongo.Shared.Models;
using MongoDB.Driver;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCosmosDBSQLandMongo.Server.DataAccess
{
    public class MongoApiProvider : IDataAccessProvider
    {
        MongoApiContext db = new MongoApiContext();
        public async Task Add(Employee employee)
        {
            try
            {
                await db.Employee.InsertOneAsync(employee);
            }
            catch
            {
                throw;
            }
        }
        public async Task<Employee> GetEmployee(string id)
        {
            try
            {
                FilterDefinition<Employee> filter = Builders<Employee>.Filter.Eq("Id", id);
                return await db.Employee.Find(filter).FirstOrDefaultAsync();
            }
            catch
            {
                throw;
            }
        }
        public async Task<IEnumerable<Employee>> GetEmployees()
        {
            try
            {
                return await db.Employee.Find(_ => true).ToListAsync();
            }
            catch
            {
                throw;
            }
        }
        public async Task Update(Employee employee)
        {
            try
            {
                await db.Employee.ReplaceOneAsync(filter: g => g.Id == employee.Id, replacement: employee);
            }
            catch
            {
                throw;
            }
        }
        public async Task Delete(string id)
        {
            try
            {
                FilterDefinition<Employee> data = Builders<Employee>.Filter.Eq("Id", id);
                await db.Employee.DeleteOneAsync(data);
            }
            catch
            {
                throw;
            }
        }
    }
}

Let us create “EmployeesController” controller file and add all the CRUD actions logic inside this file.

EmployeesController.cs

using BlazorCosmosDBSQLandMongo.Server.DataAccess;
using BlazorCosmosDBSQLandMongo.Shared.Models;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace BlazorCosmosDBSQLandMongo.Server.Controllers
{
    public class EmployeesController : Controller
    {
        private readonly IDataAccessProvider _dataAccessProvider;
        public EmployeesController(IDataAccessProvider dataAccessProvider)
        {
            _dataAccessProvider = dataAccessProvider;
        }
        [HttpGet]
        [Route("api/Employees/Get")]
        public async Task<IEnumerable<Employee>> Get()
        {
            return await _dataAccessProvider.GetEmployees();
        }
        [HttpPost]
        [Route("api/Employees/Create")]
        public async Task CreateAsync([FromBody]Employee employee)
        {
            if (ModelState.IsValid)
            {
                await _dataAccessProvider.Add(employee);
            }
        }
        [HttpGet]
        [Route("api/Employees/Details/{id}")]
        public async Task<Employee> Details(string id)
        {
            var result = await _dataAccessProvider.GetEmployee(id);
            return result;
        }
        [HttpPut]
        [Route("api/Employees/Edit")]
        public async Task EditAsync([FromBody]Employee employee)
        {
            if (ModelState.IsValid)
            {
                await _dataAccessProvider.Update(employee);
            }
        }
        [HttpDelete]
        [Route("api/Employees/Delete/{id}")]
        public async Task DeleteConfirmedAsync(string id)
        {
            await _dataAccessProvider.Delete(id);
        }
    }
}

Startup.cs

using BlazorCosmosDBSQLandMongo.Server.DataAccess;
using BlazorCosmosDBSQLandMongo.Shared.Models;
using Microsoft.AspNetCore.Blazor.Server;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.ResponseCompression;
using Microsoft.Extensions.DependencyInjection;
using System.Linq;
using System.Net.Mime;
namespace BlazorCosmosDBSQLandMongo.Server
{
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddResponseCompression(options =>
            {
                options.MimeTypes = ResponseCompressionDefaults.MimeTypes.Concat(new[]
                {
                    MediaTypeNames.Application.Octet,
                    WasmMediaTypeNames.Application.Wasm,
                });
            });
            services.AddScoped<IDataAccessProvider, SqlApiProvider>();
            //services.AddScoped<IDataAccessProvider, MongoApiProvider>();
        }
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            app.UseResponseCompression();
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseMvc(routes =>
            {
                routes.MapRoute(name: "default", template: "{controller}/{action}/{id?}");
            });
            SqlApiRepository<Employee>.Initialize();
            app.UseBlazor<Client.Program>();
        }
    }
}

I have injected two services here. Based on which API we choose, we can change. The same codebase will be working for both SQL API and Mongo API. Please note, I have commented the Mongo service now. After testing SQL API, I will comment SQL service and uncomment Mongo service.

We can go to the “Client” project and create 4 Razor views for CRUD actions.

 

ListEmployees.cshtml

@using BlazorCosmosDBSQLandMongo.Shared.Models
@page "/listemployees"
@inject HttpClient Http
<p>
    <a href="/addemployee">Create New Employee</a>
</p>
@if (employees == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>Address</th>
                <th>Gender</th>
                <th>Company</th>
                <th>Designation</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var employee in employees)
            {
                <tr>
                    <td>@employee.Name</td>
                    <td>@employee.Address</td>
                    <td>@employee.Gender</td>
                    <td>@employee.Company</td>
                    <td>@employee.Designation</td>
                    <td>
                        <a href='/editemployee/@employee.Id'>Edit</a>  |
                        <a href='/deleteemployee/@employee.Id'>Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
}
@functions {
Employee[] employees;
protected override async Task OnInitAsync()
{
    employees = await Http.GetJsonAsync<Employee[]>
("/api/Employees/Get");
}
}

AddEmployee.cshtml

@using BlazorCosmosDBSQLandMongo.Shared.Models
@page "/addemployee"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper
<h3>Create Employee</h3>
<hr />
<div class="row">
    <div class="col-md-4">
        <form>
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Address" class="control-label">Address</label>
                <input for="Address" class="form-control" bind="@employee.Address" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@employee.Gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </div>
            <div class="form-group">
                <label for="Company" class="control-label">Company</label>
                <input for="Company" class="form-control" bind="@employee.Company" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <input type="button" class="btn btn-default" onclick="@(async () => await CreateEmployee())" value="Save" />
                <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
            </div>
        </form>
    </div>
</div>
@functions {
Employee employee = new Employee();
protected async Task CreateEmployee()
{
    await Http.SendJsonAsync(HttpMethod.Post, "/api/Employees/Create", employee);
    UriHelper.NavigateTo("/listemployees");
}
void Cancel()
{
    UriHelper.NavigateTo("/listemployees");
}
}

EditEmployee.cshtml

@using BlazorCosmosDBSQLandMongo.Shared.Models
@page "/editemployee/{empId}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper
<h3>Edit Employee</h3>
<hr />
<div class="row">
    <div class="col-md-4">
        <form>
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@employee.Name" />
            </div>
            <div class="form-group">
                <label for="Address" class="control-label">Address</label>
                <input for="Address" class="form-control" bind="@employee.Address" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@employee.Gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </div>
            <div class="form-group">
                <label for="Company" class="control-label">Company</label>
                <input for="Company" class="form-control" bind="@employee.Company" />
            </div>
            <div class="form-group">
                <label for="Designation" class="control-label">Designation</label>
                <input for="Designation" class="form-control" bind="@employee.Designation" />
            </div>
            <div class="form-group">
                <input type="button" value="Save" onclick="@(async () => await UpdateEmployee())" class="btn btn-default" />
                <input type="button" value="Cancel" onclick="@Cancel" class="btn" />
            </div>
        </form>
    </div>
</div>
@functions {
[Parameter]
string empId { get; set; }
Employee employee = new Employee();
protected override async Task OnInitAsync()
{
    employee = await Http.GetJsonAsync<Employee>("/api/Employees/Details/" + empId);
}
protected async Task UpdateEmployee()
{
    await Http.SendJsonAsync(HttpMethod.Put, "api/Employees/Edit", employee);
    UriHelper.NavigateTo("/listemployees");
}
void Cancel()
{
    UriHelper.NavigateTo("/listemployees");
}
}

DeleteEmployee.cshtml

@using BlazorCosmosDBSQLandMongo.Shared.Models
@page "/deleteemployee/{empId}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper
<h3>Delete Employee</h3>
<p>Are you sure you want to delete this employee with id :<b> @empId</b></p>
<br />
<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@employee.Name</td>
        </tr>
        <tr>
            <td>Address</td>
            <td>@employee.Address</td>
        </tr>
        <tr>
            <td>Gender</td>
            <td>@employee.Gender</td>
        </tr>
        <tr>
            <td>Company</td>
            <td>@employee.Company</td>
        </tr>
        <tr>
            <td>Designation</td>
            <td>@employee.Designation</td>
        </tr>
    </table>
    <div class="form-group">
        <input type="button" value="Delete" onclick="@(async () => await Delete())" class="btn btn-default" />
        <input type="button" value="Cancel" onclick="@Cancel" class="btn" />
    </div>
</div>
@functions {
[Parameter]
string empId { get; set; }
Employee employee = new Employee();
protected override async Task OnInitAsync()
{
    employee = await Http.GetJsonAsync<Employee>
("/api/Employees/Details/" + empId);
}
protected async Task Delete()
{
    await Http.DeleteAsync("api/Employees/Delete/" + empId);
    UriHelper.NavigateTo("/listemployees");
}
void Cancel()
{
    UriHelper.NavigateTo("/listemployees");
}
}

We can modify “NavMenu.cshtml” view under "Shared" folder as well.

NavMenu.cshtml

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">Employee SPA App</a>
    <button class="navbar-toggler" onclick=@ToggleNavMenu>
        <span class="navbar-toggler-icon"></span>
    </button>
</div>
<div class=@(collapseNavMenu ? "collapse" : null) onclick=@ToggleNavMenu>
    <ul class="nav flex-column">
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="" Match=NavLinkMatch.All>
                <span class="oi oi-home" aria-hidden="true"></span> Home
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="/listemployees">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Employee Details
            </NavLink>
        </li>
    </ul>
</div>
@functions {
bool collapseNavMenu = true;
void ToggleNavMenu()
{
    collapseNavMenu = !collapseNavMenu;
}
}

We can modify “Index.cshtml” also.

Index.cshtml

@page "/"
<h4>Connecting Same Cosmos DB Database in Local Emulator using SQL API and Mongo API</h4>
<p>This is an experimental approach to connect same Cosmos DB database in a local emulator with SQL API and Mongo API </p>
<p>We are using Cosmos DB Local Emulator to create the database without any cost.</p>

We have completed all the coding part. Let us run the application and verify the features.

 

We can add a new Employee now.

 

 

After saving the data, we can check the document in Emulator.

 

 

Please note our “EmployeeSQL” collection was created with current employee data.

We can now switch our application to Mongo API mode by simply changing the service in Startup class.

 

We can run the application and create one employee data again.

We can see that “EmployeeMongo” collection is also created with one document.

 

 

 

In Mongo API, we can see there is one additional $oid (ObjectId) created.

Please look at the below image to compare the different collection details.

 

Conclusion

In this article, we saw how to create a Blazor app in Visual Studio Community edition and created a single Cosmos DB account using different .NET SDKs. Then, we connected this database with the SQL API and MongoDB API. We created separate Collections for SQL API and Mongo API.

This is an experimental approach; if you like the article, please feel free to give feedback.