"I like big data for $400" - Implementing a Jeopardy! Question Explorer using DocumentDB, Azure Search, AngularJS and Electron - Part 1


Introduction

The concept of NoSQL or schema-less databases refers to systems that store data in a schemaless fashion using some form of document notation such as JSON. Examples of NoSQL databases include: MongoDBCouchDBRavenDB and, as we will discuss in this post, Azure DocumentDB.

In this series, we will be creating an application that allows users to quickly browse and search a repository of over 200,000 jeopardy questions. We will use Azure DocumentDB as a data repository, Azure Search to implement full text search functionality and will use AngularJS and Electron to create a desktop client that users can use to browse these questions.

We will implement this functionality in two posts, the first focusing on creating the DocumentDB repository and WebAPI call, the second on creating the client application and enabling the search functionality. You can access the code for this project from this GitHub repository: https://github.com/sigaostudios/nosqljeopardy

We will assume you have a basic understanding of how to use the Azure Portal and that you have an active Azure account. If not, you can set one up by going to www.azure.com. Once that is in place, come back here and we can continue with the walkt-hrough.

The system

To begin, let's look at the architectural layout of the system we plan to create:
Jeopardy Browser Architecture using DocumentDB, Azure Search, Angular and Electron

We have an AngularJS and Electron application that connects to our DocumentDB Collection through a C# WebAPI. Additionally that client connects to Azure search through the use of the Azure Search REST API. This is a simple architecture that in theory, could be massively scalable due to the use of Azure as our back end.

So, now that we know what we are building, let's get started with building.

Configure Azure DocumentDB

Azure DocumentDB is Microsoft's NoSQL database as a service offering. It is a massively scalable solution that allows developers to quickly and easily integrate document based persistence without the overhead of maintaining the underlying infrastructure.

DocumentDB stores data in a series of collections. Collections are similar to SQL tables in that they are used to store data, but unlike SQL tables, they are schema-less. That means you can store any combination of document types in a single collection and can access those documents using query tools such as DocumentDB's SQL implementation or the DocumentDB Linq API.

As you can imagine, this has a huge impact on how you model your application data. For the purposes of this post, we will have a single document type used to model our JeopardyQuestions. This model looks like this:

  • Id : a unique identifier
  • ShowNumber: an integer that represents the show number that the question was used in
  • AirDate: a DateTime that represents the date the question aired
  • Round: a string that contains the value Jeopardy!, Double Jeopardy! or Final Jeopardy! and represents the round the question was used in
  • Category: a string that represents the category of the question
  • Value: a string that represents the dollar value of the question
  • Question: a string that represents the question
  • Answer: a string that represents the answer to the question

We will begin our process by creating a DocumentDB service, Database and Collection.

The DocumentDB service is the primary container of DocumentDB collections. The service provides the base value of the Endpoint URI and providesthe primary security and monitoring functionality for your collections.
To create a new service, access your Azure portal by going to https://portal.azure.com

When there, create a new DocumentDB resource:
Create DocumentDB Account in Azure Portal

The ID field is a globally unique name used to identify your DocumentDB account. This name will also be used to generate the base URI for your collections. We will use cs-sqlsaturday-demo for our account name, but you should use a name that makes sense to you.

You will notice that you have the option to use the MongoDB API. Selecting this allows you to access your DocumentDB repository using any of the available MongoDB tools and APIs . For the purposes of this demo, we will be using the DocumentDB API. If you want to change this at a future date, you will need to create a new service.

Finally, select the resource group and location of your document DB repository and press create.

Once your account is provisioned, you can select that resource from your resource list and view the DocumentDB Management blade:
DocumentDB Management Blade in Azure Portal

After your account is provisioned, we will need to create a collection. To do that, click Add Collection.
DocumentDB Collection Creation
Give your collection a name such as "Questions", select a storage capactiy, and then choose your Throughput Capactiy (RU/s).

RU/s are the base unit of billing in DocumentDB and translates to a 1kb action (either writing or reading) in DocumentDB. The capacity is tracked in RUs per second and the lowest number you can pick is 400, at the time of authoring this post, this translates to roughly $25/month. You can access a calculator that will help determine the number of RUs you will need based on your document size and expected transaction rate here.

Next you should pick a partition key, for the purposes of this demo we will leave this blank. Partition Keys are one of the methods DocumentDB uses to scale your system by separating data based on a value in your documents (ie. Country, State, Customer Group, etc.)

Finally, create a database for your collection. Databases are simply logical groups of collections used for grouping like document sets.

Once you are done entering information, click create and your collection will be built. It is important to note, that everything you can do from the Azure Portal can also be done using the Azure API. We will use the .NET API later on to create a database and collection in the account we just created.

Now that we have the DocumentDB account in place, we can begin writing code to access our collection.

Create the Data Repository in C#

After the Azure DocumentDB collection is in place, we will need to load data into the collection. We will load our collection with data from a CSV file containing over 200,000 Jeopardy Questions provided by Reddit user, trexmatt. (https://www.reddit.com/r/datasets/comments/1uyd0t/200000_jeopardy_questions_in_a_json_file/)

Create the initial project

The following walk through was created using Visual Studio 2015 with the Microsoft .NET Core Tools (Preview 2). I will be using the .NET Core tooling to compile my project, but will target the full .NET Framework. Some of the details have changed with the release of VS 2017, especially the use of project.json. I will create a subsequent blog post soon to address the changes.

To begin, we will create a new blank solution named "NoSQLJeopardy"
Visual Studio - New Solution

Next we will create a new project
Visual Studio - Add New Project

We will use the "Class Library (.NET Core) and call it NoSQLJeopardy.Data.

Edit the project.json file in our new project to look like this:

{
"version": "1.0.0-*",
"dependencies": {
"CsvHelper": "2.16.3",
"Microsoft.Azure.DocumentDB": "1.10.0",
"Newtonsoft.Json": "9.0.1",
"NETStandard.Library": "1.6.0"
},
"frameworks": {
"net452": {
}
}
}

Now, let's begin with our simple domain model:

Create a new class in our data project. Call this file JeopardyQuestion.cs.

JeopardyQuestion.cs:

using System;
namespace NoSqlJeopardy.Data
{
public class JeopardyQuestion
{
public string Id { get; set; }
public int ShowNumber { get; set; }
public DateTime AirDate { get; set; }
public string Round { get; set; }
public string Category { get; set; }
public string Value { get; set; }
public string Question { get; set; }
public string Answer { get; set; }
}
}

Since this class matches the layout of the CSV file, we can easily import this data into a list. To perform that task, we will use the NuGet package "CsvHelper 2.16.3". You can install this tool by running InstallPackage CsvHelper from the Console Manager in Visual Studio.
Once you have the domain model and have installed the CsvHelper package, let's create a basic repository that will extract data from our CSV file.

1. Create the JeopardyRepository Interface: Create a new interface file IJeopardyRepository.cs

using System.Collections.Generic;
using System.Threading.Tasks;
namespace NoSqlJeopardy.Data
{
public interface IJeopardyRepository
{
IEnumerable<JeopardyQuestion> GetQuestionsFromCsv(string file);
}
}

2. Create the implementation of that interface: Create a new class file called JeopardyRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.IO;
using CsvHelper;

namespace NoSqlJeopardy.Data
{
public class JeopardyRepository : IJeopardyRepository
{

public IEnumerable<JeopardyQuestion> GetQuestionsFromCsv(string file)
{
using (StreamReader fileReader = File.OpenText(file))
{
using (CsvReader csvReader = new CsvReader(fileReader))
{
var records = csvReader.GetRecords<JeopardyQuestion>().ToList();
return records;
}
}
}
}
}

Now that we have the capability to load data from a CSV file, let's connect to the Azure DocumentDB and load data into the repository. This code closely follows the getting started documentation from Azure located here: https://docs.microsoft.com/en-us/azure/documentdb/documentdb-get-started
We've changed a little bit to make it easier to work with our repository.

1. Update the IJeopardyRepository with a couple of additional method declarations

using Microsoft.Azure.Documents.Client;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace NoSqlJeopardy.Data
{
public interface IJeopardyRepository
{
string EndpointUri { get; set; }
string PrimaryKey { get; set; }
string DatabaseName { get; set; }
string Collection { get; set; }
DocumentClient Client { get; set; }
Task CreateDocumentClient();
IEnumerable<JeopardyQuestion> GetQuestionsFromCsv(string file);
Task SaveQuestionToDocDb(JeopardyQuestion question);
IEnumerable<JeopardyQuestion> GetQuestionsForGameAndRound(int gameNumber, string round);
}
}

2. Update JeopardyRepository.cs with the appropriate implementations

//Configuration settings
public string EndpointUri { get; set; }
public string PrimaryKey { get; set; }
public string DatabaseName { get; set; }
public string Collection { get; set; }
public DocumentClient Client { get; set; } //Not a setting, but this gives us access to the DocumentClient in case we need to interact directly with the service

The following code creates a connection to the DocumentDB API using the configuration properties from above.

It checks to see if the database exists, if not it creates one. A database in DocumentDB is a logical grouping of DocumentDB Collections that can be used for management and automation purposes.

Once the database is verified, it checks to see if a collection exists, if not, it creates a collection. A collection in DocumentDB is the primary tool used to persist and access documents in DocumentDB. A collection incurs billing, so be careful, here, once this call completes, you will be charged. This API call performs the same steps we completed in the Azure Portal. You can see that you can set the Throughput RUs in teh RequestOptions object ( new RequestOptions { OfferThroughput = 400 }); //used to set RUs

//Connects to DocumentDB using API
//Creates a database and collection if one does not exist
public async Task CreateDocumentClient()
{
Client = new DocumentClient(new Uri(EndpointUri), PrimaryKey);

try
{
await Client.ReadDatabaseAsync(UriFactory.CreateDatabaseUri(DatabaseName));
}
catch (DocumentClientException de)
{
// If the database does not exist, create a new database
if (de.StatusCode == HttpStatusCode.NotFound)
{
await Client.CreateDatabaseAsync(new Database { Id = DatabaseName });
}
else
{
throw;
}
}
//Checks to see if document collection exists, if not it creates it
try
{
await Client.ReadDocumentCollectionAsync(UriFactory.CreateDocumentCollectionUri(DatabaseName, Collection));
}
catch (DocumentClientException de)
{
// If the document collection does not exist, create a new collection
if (de.StatusCode == HttpStatusCode.NotFound)
{
DocumentCollection collectionInfo = new DocumentCollection();
collectionInfo.Id = Collection;
// Configure collections for maximum query flexibility including string range queries.
collectionInfo.IndexingPolicy = new IndexingPolicy(new RangeIndex(DataType.String) { Precision = -1 });
// Here we create a collection with 400 RU/s.
await Client.CreateDocumentCollectionAsync(
UriFactory.CreateDatabaseUri(DatabaseName),
collectionInfo,
new RequestOptions { OfferThroughput = 400 }); //used to set RUs
}
else
{
throw;
}
}
}

The next method will take an object (in this case a JeopardyQuestion object) and will write that object to the DocumentDB service. If that document already exists, the document is updated.

        //Saves an individual question to the server if it does not exist
public async Task SaveQuestionToDocDb(JeopardyQuestion question)
{
try
{
await Client.ReadDocumentAsync(UriFactory.CreateDocumentUri(DatabaseName, Collection, question.Id));
}
catch (DocumentClientException de)
{
if (de.StatusCode == HttpStatusCode.NotFound)
{
await Client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(DatabaseName, Collection), question);
}
else
{
throw;
}
}
}

Finally, we will use Linq and the DocumentDB API to return a list of questions for a given game number and round.

        //Used to perform query using Linq
public IEnumerable<JeopardyQuestion> GetQuestionsForGameAndRound(int gameNumber, string round)
{
FeedOptions queryOptions = new FeedOptions { MaxItemCount = 100 };
IQueryable<JeopardyQuestion> jeopardyQuery = Client.CreateDocumentQuery<JeopardyQuestion>(
UriFactory.CreateDocumentCollectionUri(DatabaseName, Collection), queryOptions)
.Where(q => q.ShowNumber == gameNumber && q.Round == round);
return jeopardyQuery.ToList<JeopardyQuestion>();
}

Our completed JeopardyRepository.CS should now look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.IO;
using CsvHelper;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents;
using System.Net;
namespace NoSqlJeopardy.Data
{

public class JeopardyRepository : IJeopardyRepository
{
//Configuration settings
public string EndpointUri { get; set; }
public string PrimaryKey { get; set; }
public string DatabaseName { get; set; }
public string Collection { get; set; }
public DocumentClient Client { get; set; }
//Connects to DocumentDB using API
//Creates a database and collection if one does not exist
public async Task CreateDocumentClient()
{
Client = new DocumentClient(new Uri(EndpointUri), PrimaryKey);

try
{
await Client.ReadDatabaseAsync(UriFactory.CreateDatabaseUri(DatabaseName));
}
catch (DocumentClientException de)
{
// If the database does not exist, create a new database
if (de.StatusCode == HttpStatusCode.NotFound)
{
await Client.CreateDatabaseAsync(new Database { Id = DatabaseName });
}
else
{
throw;
}
}
//Checks to see if document collection exists, if not it creates it
try
{
await Client.ReadDocumentCollectionAsync(UriFactory.CreateDocumentCollectionUri(DatabaseName, Collection));
}
catch (DocumentClientException de)
{
// If the document collection does not exist, create a new collection
if (de.StatusCode == HttpStatusCode.NotFound)
{
DocumentCollection collectionInfo = new DocumentCollection();
collectionInfo.Id = Collection;
// Configure collections for maximum query flexibility including string range queries.
collectionInfo.IndexingPolicy = new IndexingPolicy(new RangeIndex(DataType.String) { Precision = -1 });
// Here we create a collection with 400 RU/s.
await Client.CreateDocumentCollectionAsync(
UriFactory.CreateDatabaseUri(DatabaseName),
collectionInfo,
new RequestOptions { OfferThroughput = 400 }); //used to set RUs
}
else
{
throw;
}
}
}
//Loads questions from a CSV file
public IEnumerable<JeopardyQuestion> GetQuestionsFromCsv(string file)
{
using (StreamReader fileReader = File.OpenText(file))
{
using (CsvReader csvReader = new CsvReader(fileReader))
{
var records = csvReader.GetRecords<JeopardyQuestion>().ToList();
return records;
}
}
}
//Saves an individual question to the server if it does not exist
public async Task SaveQuestionToDocDb(JeopardyQuestion question)
{
try
{
await Client.ReadDocumentAsync(UriFactory.CreateDocumentUri(DatabaseName, Collection, question.Id));
}
catch (DocumentClientException de)
{
if (de.StatusCode == HttpStatusCode.NotFound)
{
await Client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri(DatabaseName, Collection), question);
}
else
{
throw;
}
}
}
//Used to perform query using Linq
public IEnumerable<JeopardyQuestion> GetQuestionsForGameAndRound(int gameNumber, string round)
{
FeedOptions queryOptions = new FeedOptions { MaxItemCount = 100 };
IQueryable<JeopardyQuestion> jeopardyQuery = Client.CreateDocumentQuery<JeopardyQuestion>(
UriFactory.CreateDocumentCollectionUri(DatabaseName, Collection), queryOptions)
.Where(q => q.ShowNumber == gameNumber && q.Round == round);
return jeopardyQuery.ToList<JeopardyQuestion>();
}
}
}

Load data into DocumentDB

Now that our Repository is in place, let's create a console application to load data into our repository.

Begin by creating a new .NET Core console application called QuestionLoader

Edit the project.json file to look like this

{
"version": "1.0.0-*",
"buildOptions": {
"emitEntryPoint": true,
"copyToOutput": {
"includeFiles": [
"appsettings.json",
"Questions\\jeopardy_csv.csv"
]
}
},
"dependencies": {
"CsvHelper": "2.16.3",
"Microsoft.Extensions.Configuration": "1.1.0",
"Microsoft.Extensions.Configuration.Abstractions": "1.1.0",
"Microsoft.Extensions.Configuration.EnvironmentVariables": "1.1.0",
"Microsoft.Extensions.Configuration.FileExtensions": "1.1.0",
"Microsoft.Extensions.Configuration.Json": "1.1.0",
"Newtonsoft.Json": "9.0.1",
"NoSqlJeopardy.Data": "1.0.0-*",
"System.Runtime.Extensions": "4.0.0"
},
"frameworks": {
"net452": {
}
}
}

Create a folder in that project called "Questions"

Save a copy of the following CSV file to that folder. (Insert link to the JEOPARDY_CSV.csv)

This will be a straightforward application that loads data from the CSV file and writes the questions one at a time into our collection. This is not meant to be a bulk loader. There are methods of bulkloading data into DocumentDB, but that is outside of the scope of this particular blog post. :)

We will use an appsettings.json file and the Microsoft.Extensions framework in order to load our DocumentDB configuration instead of hard coding it.

Our appsettings.json file should look like this:

{
"DocumentDB": {
"EndpointUri": "https://***.documents.azure.com:443/",
"PrimaryKey": "",
"DatabaseName": "jeopardy_sqlsaturday",
"Collection": "questions"
}
}

You can get your EndpointUri and Primary Key by going to Keys in the Azure Portal DocumentDB Service blade:
DocumentDB - Key Management

DocumentDB - Keys

Set your EndpointUri equal to URI and your primary key option to the value in primary key.

Now, set your program.cs file to look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Net;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Newtonsoft.Json;
using NoSqlJeopardy.Data;
using System.Threading;
using Microsoft.Extensions.Configuration;
namespace QuestionLoader
{
public class Program
{
public static IConfigurationRoot Configuration;
public static void Main(string[] args)
{
var builder = new ConfigurationBuilder()
.AddJsonFile($"appsettings.json", true, true)
.AddEnvironmentVariables();
Configuration = builder.Build();
var repo = new JeopardyRepository();
//Configure repository
var docDbConfig = Configuration.GetSection("DocumentDB");
repo.Collection = docDbConfig["Collection"];
repo.DatabaseName = docDbConfig["DatabaseName"];
repo.EndpointUri = docDbConfig["EndpointUri"];
repo.PrimaryKey = docDbConfig["PrimaryKey"];
try
{
repo.CreateDocumentClient().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);
}
//Read CSV
Console.WriteLine("Reading CSV File");
var csvQuestions = repo.GetQuestionsFromCsv(@".\Questions\jeopardy_csv.csv");
Console.Write("Read {0} questions.", csvQuestions.Count());
foreach(var question in csvQuestions)
{
repo.SaveQuestionToDocDb(question);
Thread.Sleep(100);
Console.WriteLine("Saving: {0}", question.Question);
}
Console.WriteLine("End of demo, press any key to exit.");
Console.ReadKey();
}
}
}

Let's examine what this is doing:

This code loads our appsettings file and creates our Configuration builder.

           var builder =newConfigurationBuilder()
.AddJsonFile($"appsettings.json", true, true)
.AddEnvironmentVariables();
Configuration = builder.Build();

This code creates an instance of our JeopardyRepository and loads configuration from the Configuration Builder.

            var repo = new JeopardyRepository();
//Configure repository
var docDbConfig = Configuration.GetSection("DocumentDB");
repo.Collection = docDbConfig["Collection"];
repo.DatabaseName = docDbConfig["DatabaseName"];
repo.EndpointUri = docDbConfig["EndpointUri"];
repo.PrimaryKey = docDbConfig["PrimaryKey"];

This code creates the DocumentDB Client connection:

            try
{
repo.CreateDocumentClient().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);
}

This code loads the questions from the CSV file and reports on how many questions are in that file:

            //Read CSV
Console.WriteLine("Reading CSV File");
var csvQuestions = repo.GetQuestionsFromCsv(@".\Questions\jeopardy_csv.csv");
Console.Write("Read {0} questions.", csvQuestions.Count());

Finally, this code iterates through each of those questions and uses our repository to save those questions to the collection.

            foreach(var question in csvQuestions)
{
repo.SaveQuestionToDocDb(question);
Thread.Sleep(100);
Console.WriteLine("Saving: {0}", question.Question);
}

We use Thread.Sleep here to slow the calls down a little bit so we don't hit our throttle limit. Depending on how high your RUs are set, you might not need this.

Once we have this code in place, we can run our QuestionLoader application and it will load our questions into the DocumentDB collection. Warning, this could take some time depending on how many questions you have in your CSV file. This is not meant to be a bulk loader, but simply a demo on how to load documents into DocumentDB.

Create the API call in C#

Now that we have an API and we've loaded data into our DocumentDB collection, we need to create an API that our client application can use to query data in a format that is useful for displaying a Jeopardy board. We are using a proxy API for two reasons:

  1. We will be using the Azure DocumentDB Master Key to query data from our API. We want to protect this from the public as it can be used to incur charges on our DocumentDB account (not to mention the fact that it gives complete access to the data in our DocumentDB Service.
  2. We want to transform the data from our persistence model to a more appropriate ViewModel. While we can do that easily in JavaScript, the API allows us to abstract the persistence layer further from our client layer.

To begin, create a new .NET Core ASP.NET Web Application called NoSQLJeopardy.Api.
Set the project.json file to look like this:

{
"dependencies": {
"CsvHelper": "2.16.3",
"Microsoft.AspNetCore.Mvc": "1.0.1",
"Microsoft.AspNetCore.Routing": "1.0.1",
"Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
"Microsoft.AspNetCore.Server.Kestrel": "1.0.1",
"Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
"Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
"Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
"Microsoft.Extensions.Configuration.Json": "1.0.0",
"Microsoft.Extensions.Logging": "1.0.0",
"Microsoft.Extensions.Logging.Console": "1.0.0",
"Microsoft.Extensions.Logging.Debug": "1.0.0",
"NoSqlJeopardy.Data": "1.0.0-*"
},
"tools": {
"Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
},
"frameworks": {
"net452": { }
},
"buildOptions": {
"emitEntryPoint": true,
"preserveCompilationContext": true
},
"publishOptions": {
"include": [
"wwwroot",
"**/*.cshtml",
"appsettings.json",
"web.config"
]
},
"scripts": {
"postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
}
}

Now let's create a ViewModel for our Jeopardy Board. We will do this in our NoSqlJeopardy.Data project. Create a folder entitled "ViewModels" in that project and add the following files:

QuestionVm.cs

namespace NoSqlJeopardy.Data.ViewModels
{
public class QuestionVm
{
public string Value { get; set; }
public string Question { get; set; }
public string Answer { get; set; }
}
}

BoardCategoryVm.cs

using System.Collections.Generic;
namespace NoSqlJeopardy.Data.ViewModels
{
public class BoardCategoryVm
{
public string CategoryName { get; set; }
public IEnumerable<QuestionVm> Questions { get; set; }
}
}

JeopardyBoardVm.cs

using System.Collections.Generic;
namespace NoSqlJeopardy.Data.ViewModels
{
public class JeopardyBoardVm
{
public IEnumerable<BoardCategoryVm> Categories { get; set; }
}
}

We now have a view model that will make displaying a Jeopardy Board in our client application easy.

Next, let's create an an options object that will allow our API to easily load the configuration settings from our appsettings.json file.

Create a folder in NoSqlJeopardy.Api called Options. Create a class in that folder entitled NSJDocumentDbSettings.cs

namespace NoSqlJeopardy.Api.Options
{
public class NSJDocumentDbSettings
{
public string EndpointUri { get; set; }
public string PrimaryKey { get; set; }
public string Collection { get; set; }
public string DatabaseName { get; set; }
}
}

Next, let's set our appsettings.json file to look like this (use the same configuration settings we used in the QuestionLoader for the API):

{
"Logging": {
"IncludeScopes": false,
"LogLevel": {
"Default": "Debug",
"System": "Information",
"Microsoft": "Information"
}
},
"DocumentDB": {
"EndpointUri": "https://**.documents.azure.com:443/",
"PrimaryKey": "",
"DatabaseName": "jeopardy_sqlsaturday",
"Collection": "questions"
}
}

After we have the settings saved, let's load those into our application by adding some calls in our Startup object. Open Startup.cs and add the following code:

        public IConfigurationRoot Configuration { get; }
public void ConfigureOptions(IServiceCollection services)
{
services.Configure<NSJDocumentDbSettings>(Configuration.GetSection("DocumentDB"));
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddMvc();
services.AddOptions();
ConfigureOptions(services);
services.AddTransient<IJeopardyRepository, JeopardyRepository>();
}

Now that we have the configuration information loaded, let's update our server to use CORS

        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
loggerFactory.AddConsole(Configuration.GetSection("Logging"));
loggerFactory.AddDebug();
// Shows UseCors with CorsPolicyBuilder.
app.UseCors(builder => builder
.AllowAnyOrigin()
.AllowAnyHeader()
.AllowAnyMethod());
app.UseMvc();
}

Are startup.cs file should now look like this:

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using NoSqlJeopardy.Api.Options;
using NoSqlJeopardy.Data;
namespace NoSqlJeopardy.Api
{
public class Startup
{
public Startup(IHostingEnvironment env)
{
var builder = new ConfigurationBuilder()
.SetBasePath(env.ContentRootPath)
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
.AddEnvironmentVariables();
Configuration = builder.Build();
}
public IConfigurationRoot Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddMvc();
services.AddOptions();
ConfigureOptions(services);
services.AddTransient<IJeopardyRepository, JeopardyRepository>();
}
public void ConfigureOptions(IServiceCollection services)
{
services.Configure<NSJDocumentDbSettings>(Configuration.GetSection("DocumentDB"));
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
loggerFactory.AddConsole(Configuration.GetSection("Logging"));
loggerFactory.AddDebug();
// Shows UseCors with CorsPolicyBuilder.
app.UseCors(builder => builder
.AllowAnyOrigin()
.AllowAnyHeader()
.AllowAnyMethod());
app.UseMvc();
}
}
}

Once our startup.cs file is completed, let's create a QuestionsController in our Controllers folder. We will have a single get call that will take a game and round as parameters and will return the appropriate JeopardyVM for that call.

Our QuestionsController file should look like this:

using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using NoSqlJeopardy.Api.Options;
using NoSqlJeopardy.Data;
using NoSqlJeopardy.Data.ViewModels;
namespace NoSqlJeopardy.Api.Controllers
{
[Route("api/[controller]")]
public class QuestionsController : Controller
{
private IJeopardyRepository _repo;
public QuestionsController(IJeopardyRepository repo, IOptions<NSJDocumentDbSettings> settings)
{
_repo = repo;
repo.EndpointUri = settings.Value.EndpointUri;
repo.PrimaryKey = settings.Value.PrimaryKey;
repo.DatabaseName = settings.Value.DatabaseName;
repo.Collection = settings.Value.Collection;
repo.CreateDocumentClient().Wait();
}
// GET: api/values
[HttpGet]
public JeopardyBoardVm Get([FromQuery]int showNumber, [FromQuery]string round)
{
JeopardyBoardVm board = new JeopardyBoardVm();
var questions = _repo.GetQuestionsForGameAndRound(showNumber, round);
var cats = (from c in questions
select c.Category).Distinct().ToList();
var boardCategories = new List<BoardCategoryVm>();
foreach (var cat in cats)
{
BoardCategoryVm bcat = new BoardCategoryVm();
bcat.CategoryName = cat;
var boardQuestions = new List<QuestionVm>();
var qs = (from q in questions
where q.Category.Equals(cat)
orderby int.Parse(q.Value.Replace("$", "").Replace(",", ""))
select q).ToList();
foreach(var q in qs)
{
QuestionVm question = new QuestionVm();
question.Value = q.Value;
question.Question = q.Question;
question.Answer = q.Answer;
boardQuestions.Add(question);
}
bcat.Questions = boardQuestions;
boardCategories.Add(bcat);
}
board.Categories = boardCategories;
return board;
}
}
}

The pertinent code is found in the Get Function. This code loads a list of questions from our repository using the GetQuestionsForGameAndRound created above. Once the questions are loaded, it reshapes that data into our view model.

You can now debug the API and call the questions route using Postman
Postman Questions Api Call

Conclusion

That concludes day 1 of this series. Tomorrow, we will focus on creating an Angular 2 application that will connect to our API and display the questions in an easy to use JeopardyBoard. Additionally we will connect our DocumentDB to Azure Search and will implement a full text search function for our Jeopardy Question client.

Head on over to part 2 to create an Angular and Electron app!

 

The ultralight angular application setup package – Part 2: Build process for large applications "I Like Big Data for $400" - Implementing a Jeopardy! Question Explorer using DocumentDB, AzureSearch, Angular and Electron - Part 2