Rajat Srivastava

Senior Dot Net Developer specializing in building robust restful api's

Building Restful CRUD APIs with HarperDB and .Net Core

Published Feb 14, 2023

Build a set of restful APIs using .Net Core and HarperDB. We will be utilizing the latest version of the HarperDB.Net.Client Nuget package to communicate with the cloud server instance of HarperDB via an Asp.Net Core Web API project

The main focus of this guide will be on the service layer which actually deals with the implementation. Code for the rest of the project will be shared and briefly explained including the models, controllers, etc. as it would be similar to any other .Net Core app.

Head over to HarperDB Studio and create your free account
Once created, log in using your credentials and create a new organization and enter the details

Wait for the process to complete and then select the newly created organization and click on Create New HarperDB Cloud Instance tab

Next, select the AWS or Verizon instance option

Choose the cloud partner, I'll be going with AWS for now, and then click on Instance Info

Provide the instance name and a username and password to access the instance as well

Select the free variant in the details section as it should be more than enough for the purpose of this demo and confirm the details

That's it for now, the server should be up in a few minutes but we don't need it right away and we will come back to it once required

The HarperDb.Net.Client package exposes various useful methods through which we can easily perform all CRUD operations to our HarperDB server.

Let's first list down the operations supported

Schema Creation
Schema Definition
Schema Deletion
Table Creation
Table Definition
Table Deletion
Attribute Creation
Attribute Deletion
Create Record
Get Record by ID
Update Record
Execute SQL Query
Bulk CSV Operations (insert, update & upsert)

Both the synchronous and asynchronous variants of these generic methods are available and can be consumed as per the requirement. IHarperClient and IHarperClientAsync are two interfaces for the same.

Classes implementing these two respective interfaces have two constructors, one which accepts the table name and the other without it. We will be looking at the actual methods and configuration settings later.

namespace HarperNetClient
{ public interface IHarperClientAsync { Task<IRestResponse> BulkOperationAsync<T>(string dataSource, string operationType = "csv_url_load", string actionType = "insert"); Task<IRestResponse> CreateAttributeAsync(string schema, string table, string attribute); Task<IRestResponse> CreateRecordAsync<T>(T itemToCreate); Task<IRestResponse> CreateSchemaAsync(string schema); Task<IRestResponse> CreateTableAsync(string table, string schema, string hashAttribute = "id"); Task<IRestResponse> DescribeSchemaAsync(string schema); Task<IRestResponse> DescribeTableAsync(string table, string schema); Task<IRestResponse> DropAttributeAsync(string table, string schema, string attribute); Task<IRestResponse> DropSchemaAsync(string schema); Task<IRestResponse> DropTableAsync(string table, string schema); Task<IRestResponse> ExecuteQueryAsync(string sqlQuery); Task<IRestResponse> GetByIdAsync(string id); Task<IRestResponse> UpdateRecordAsync<T>(T itemToUpdate);
    }
}

Our end goal is to create a set of rest APIs using Asp.Net Core and HarperDB. The API's we are building will consist of two main entities Posts & Comments.

A post will have a few properties like Content, PostedBy, etc. Each post can have one or multiple comments having some properties of its own. Their can be replies to comments as well. The final object will be something like below

[{ "id": "668e65fe-133b-44ef-a3db-b488b5990c90", "content": "Will Eric Ten Hag be a success at Manchester United", "postedBy": "mufc_fan", "postedAt": "2022-05-11T15:16:05.5078365+05:30", "imageURL": "https://cdn.footballfancast.com/wp-content/uploads/2019/06/Erik-ten-Hag-11-600x400.jpg", "commentsThread": [ { "id": "370189e5-d613-43f9-a9a5-7f72f6b0c7d1", "comment": "Yes he will be", "postedAt": "2022-05-11T15:17:54.4697461+05:30", "postedBy": "always_Red", "postId": "668e65fe-133b-44ef-a3db-b488b5990c90", "parentCommentId": null, "commentReplies": [ { "id": "33653eb2-5e8f-4554-81f9-b079fafc36a5", "comment": "Totally rebuilt his squad at was great in UCL", "postedAt": "2022-05-11T15:19:29.4973336+05:30", "postedBy": "mufc_always", "postId": null, "parentCommentId": "370189e5-d613-43f9-a9a5-7f72f6b0c7d1" }, { "id": "86a1692a-ac8b-4266-93ae-198efb8bfd34", "comment": "Totally agree, his philosophy at Ajax proves he can be successful", "postedAt": "2022-05-11T15:19:00.6457227+05:30", "postedBy": "mufc_Red", "postId": null, "parentCommentId": "370189e5-d613-43f9-a9a5-7f72f6b0c7d1" }] }, { "id": "9636c45e-3156-4cfc-aa60-41da72ce6c7d", "comment": "Hopefully !!!", "postedAt": "2022-05-11T15:20:01.289686+05:30", "postedBy": "string", "postId": "668e65fe-133b-44ef-a3db-b488b5990c90", "parentCommentId": null, "commentReplies": []
      }
    ]
  }
]

For this, we will need a Visual Studio solution with two projects. One is a simple Asp.Net Core Web API-based project and the other is an optional class library app to have common models and files etc.

Along with the HarperDB package, we will also be using a few other packages, all of which are listed below and can be added using the NuGet Package Manager in Visual Studio

AutoMapper 11.0.1
AutoMapper.Extensions.Microsoft.DependencyInjection 11.0.0
HarperDB.Net.Client 1.1.0
Newtonsoft.Json 13.0.1
Swashbuckle.AspNetCore 5.6.3

In order to connect to the HarperDB server, we will need the InstanceURl along with the AuthToken. Let us head back to the config section of our HarperDB server to get both these details.

HarperDb.Net.Client package has a class named HarperDbConfiguration which contains the properties required to connect to the server.

We will be updating the appsetting.json file to add the above-mentioned parameters in a new property called ConnectionString and then create a few methods to get these anywhere required

"ConnectionString": { "InstanceUrl": "https://mozab-comments-storage.harperdbcloud.com", "AuthToken": "YOUR_AUTH_TOKEN_HERE"
  }

Note: Ideally one should use a secret manager service like Azure KeyVault, AWS KMS etc to store credentials/tokens in actual applications. We are directly using the AppSettings file here for the purpose of this demo only.

Now, create an Interface named IHarperConfiguration and a class implementing it named HarperConfiguration

using HarperNetClient.models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks; namespace Mozab.Service.Comments.Services
{ public interface IHarperConfiguration {
        HarperDbConfiguration GetHarperConfigurations();
    }
}

using HarperNetClient.models;
using Microsoft.Extensions.Configuration; namespace Mozab.Service.Comments.Services
{ public class HarperConfigurations : IHarperConfiguration { private IConfiguration _config; public HarperConfigurations(IConfiguration configs) { _config = configs; } public HarperDbConfiguration GetHarperConfigurations() { var dbConfigs = _config.GetSection("ConnectionString").Get<HarperDbConfiguration>(); return dbConfigs;
        }
    }
}

The purpose of this Interface is to expose the GetHarperConfiguration method to all services via DependencyInjection. This method reads the configurations stored in the appsettings.json file earlier.

The HarperClientAsync class is the actual class containing the various methods we will be using. There are two constructors available for this, one without table name that can be used in the Schema service when we dont know the table name before hand and other in all other services when we know the destination tables to be affected.

public HarperClientAsync(HarperDbConfiguration config);
public HarperClientAsync(HarperDbConfiguration config, string table);

The first parameter to this constructor is the one returned by the GetHarperConfiguration method implemented earlier.

There are two classes implemented one containing the list of constants that we will be using throughout the project and another being a custom ExceptionHandler

namespace Mozab.SharedUtilities
{ public class Constants { public const string CommentCannotBeEmpty = "Cannot add an empty comment"; public const string UserNotFound = "No User found for the given id"; public const string PostNotFound = "No Posts found for the given id"; public const string InvalidCommentId = "No Comment found for the given id"; public const string InvalidParentCommentId = "No parent comment found for the given id"; public const string NoCommentForTheJab = "No Comment found for the given Jab"; public const string InvalidTableOrSchemaName = "Table or Schema name cannot be empty"; public const string SchemaAlreadyExists = "Schema with the same name already exists"; public const string TableAlreadyExists = "Table with the same name already exists"; public const string TableNotFound = "No Table found for the given name in the schema"; public const string SchemaNotFound = "No Schema found for the given name";
    }
}

using System; namespace Mozab.SharedUtilities
{ public class CustomException: Exception { public CustomException() { } public CustomException(string message) : base(message)
        {

        }
    }
}

Although there are only two main entities involved, we will be having multiple variants of these created in our project. The idea is to have different models which are used internally and different ones which are exposed to end-user for requests and responses.

I have the model classes created in the console project but they can be easily part of the web API one as well.

using System;
using System.Collections.Generic; namespace Mozab.SharedUtilities.Models
{ public class Posts { public string id { get; set; } public string Content { get; set; } public string PostedBy { get; set; } public DateTime PostedAt { get; set; } public string ImageURL { get; set; } } public class PostVM { public string Content { get; set; } public string PostedBy { get; set; } public string ImageURL { get; set; } } public class PostComment { public string id { get; set; } public string Comment { get; set; } public DateTime PostedAt { get; set; } public string PostedBy { get; set; } public string PostId { get; set; } public string ParentCommentId { get; set; } } public class PostCommentVM { public string Comment { get; set; } public string PostedBy { get; set; } public string PostId { get; set; } public string ParentCommentId { get; set; } } public class PostsWithComments { public Posts Post { get; set; } public List<CommentsThread> PostComments { get; set; } } public class PostResponse { public string id { get; set; } public string Content { get; set; } public string PostedBy { get; set; } public DateTime PostedAt { get; set; } public string ImageURL { get; set; } public List<CommentResponse> CommentsThread { get; set; } } public class CommentsThread { public PostComment Comment { get; set; } public List<PostComment> CommentReplies { get; set; } } public class CommentResponse { public string id { get; set; } public string Comment { get; set; } public DateTime PostedAt { get; set; } public string PostedBy { get; set; } public string PostId { get; set; } public string ParentCommentId { get; set; } public List<PostComment> CommentReplies { get; set; }
    }
}

We will be using the AutoMapper package to automatically map all entities from one type to another. We will need this as ViewModel and the actual Models are different.

For example, in order to create a new Post later on the API request will use the PostVM model while the HarperDB entity will be of type Post with added parameters like Id and PostedAt which we don't want as input from the user.

using AutoMapper;
using Mozab.SharedUtilities.Models; namespace Mozab.Service.Comments
{ public class ProfileMapping: Profile { public ProfileMapping() { CreateMap<PostVM, Posts>().ReverseMap(); CreateMap<PostCommentVM, PostComment>().ReverseMap(); CreateMap<Posts, PostResponse>().ReverseMap(); CreateMap<PostComment, CommentResponse>().ReverseMap();

        }
    }
}

We just need to create a new class and configure the Maps and ReverseMaps for the object types we want AutoMapper to convert for us.

Also, the class needs to be added in startup.cs so that it is available everywhere for injection.

var mapperConfig = new MapperConfiguration(m => { m.AddProfile(new ProfileMapping()); }); IMapper mapper = mapperConfig.CreateMapper();
    services.AddSingleton(mapper);

Our project has three Controllers classes for Schema, Posts, and Comments. In the Schema controller, we will be creating some endpoints which can be used to create/delete schemas and tables in the database server.

The Post & Comment Controllers will have the respective methods to perform CRUD operations on the entities. Below is the swagger documentation screenshot of the final list of APIs which we will have at the end of this implementation

using Microsoft.AspNetCore.Mvc;
using Mozab.Service.Services;
using Mozab.SharedUtilities;
using System;
using System.Threading.Tasks; namespace Mozab.Service.Controllers
{ [Route("api/[controller]")] [ApiController] public class SchemaController : ControllerBase { private ISchemaService _service; public SchemaController(ISchemaService service) { _service = service; } [HttpPost] public async Task<IActionResult> CreateSchema(string schema) { try { var response = await _service.CreateSchema(schema); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpPost("table")] public async Task<IActionResult> CreateTable(string table, string schema) { try { var response = await _service.CreateTable(table, schema); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpDelete("table")] public async Task<IActionResult> DeleteTable(string table, string schema) { try { var response = await _service.DeleteTable(table, schema); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpDelete] public async Task<IActionResult> DeleteSchema(string schema) { try { var response = await _service.DeleteSchema(schema); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpPost("table/attribute")] public async Task<IActionResult> CreateAttribute(string table, string schema, string attribute) { try { var response = await _service.CreateAttribute(table, schema, attribute); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpDelete("table/attribute")] public async Task<IActionResult> DeleteAttribute(string table, string schema, string attribute) { try { var response = await _service.DeleteAttribute(table, schema, attribute); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!");
            }
        }
    }
}

PostController

using Microsoft.AspNetCore.Mvc;
using Mozab.Service.Services;
using Mozab.SharedUtilities;
using Mozab.SharedUtilities.Models;
using System;
using System.Threading.Tasks; namespace Mozab.Service.Controllers
{ [Route("api/[controller]")] [ApiController] public class PostsController : ControllerBase { private IPostService _service; public PostsController(IPostService service) { _service = service; } [HttpGet] public async Task<IActionResult> GetAllPosts() { try { var response = await _service.GetAllPosts(); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpGet("{id}")] public async Task<IActionResult> GetPostById(string id) { try { var response = await _service.GetPostById(id); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpPost] public async Task<IActionResult> AddNewPost(PostVM postToAdd) { try { var response = await _service.AddNewPost(postToAdd); return Created("", response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpPut("{id}")] public async Task<IActionResult> UpdatePost(string id, PostVM postToUpdate) { try { var response = await _service.UpdatePostById(id, postToUpdate); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpDelete("{id}")] public async Task<IActionResult> DeletePostById(string id) { try { var response = await _service.DeletePostById(id); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!");
            }
        }

    }
}

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Mozab.Service.Comments.Services;
using Mozab.SharedUtilities;
using Mozab.SharedUtilities.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks; namespace Mozab.Service.Comments.Controllers
{ [Route("api/[controller]")] [ApiController] public class CommentsController : ControllerBase { private ICommentsService _service; public CommentsController(ICommentsService service) { _service = service; } [HttpGet("{id}")] public async Task<IActionResult> GetCommentById(string id) { try { var response = await _service.GetCommentById(id); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpGet("post/{id}")] public async Task<IActionResult> GetCommentsByPost(string id) { try { var response = await _service.GetAllCommentsByPost(id); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpPost] public async Task<IActionResult> CreateComment(PostCommentVM commentToAdd) { try { var response = await _service.AddNewComment(commentToAdd); return Created("", response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpPut("{id}")] public async Task<IActionResult> UpdateComment(string id, PostCommentVM postComment) { try { var response = await _service.UpdateComment(id, postComment); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpDelete("{id}")] public async Task<IActionResult> DeleteCommentById(string id) { try { var response = await _service.DeleteCommentById(id); return Ok(response); } catch (CustomException ex) { return BadRequest(ex.Message); } catch (Exception ex) { return BadRequest("Something went wrong!"); } } [HttpDelete("post/{id}")] public async Task<IActionResult> DeleteCommentByPost(string id) { try { var response = await _service.DeleteCommentByPost(id); return Ok(response); } catch(CustomException ex) { return BadRequest(ex.Message); } catch(Exception ex) { return BadRequest("Something went wrong!");
            }
        }
    }
}

We will be creating three services, one each for the three controllers defined above. The interface for all these will list down the operations that can be performed. Lets us first create the interfaces and then look one by one at each implementation.

using System.Threading.Tasks; namespace Mozab.Service.Services
{ public interface ISchemaService { public Task<string> CreateSchema(string schemaName); public Task<string> CreateTable(string tableName, string schemaName); public Task<string> DeleteSchema(string schema); public Task<string> DeleteTable(string table, string schema); public Task<string> CreateAttribute(string schema, string table, string attribute); public Task<string> DeleteAttribute(string table, string schema, string attribute);
    }
}

IPostService

using Mozab.SharedUtilities.Models;
using System.Collections.Generic;
using System.Threading.Tasks; namespace Mozab.Service.Services
{ public interface IPostService { Task<List<PostResponse>> GetAllPosts(); Task<PostResponse> GetPostById(string zabId); Task<SharedUtilities.Models.Posts> AddNewPost(PostVM zabToAdd); Task<SharedUtilities.Models.Posts> UpdatePostById(string id, PostVM zabToUpdate); Task<bool> DeletePostById(string id);
    }
}

using Mozab.SharedUtilities.Models;
using System.Collections.Generic;
using System.Threading.Tasks; namespace Mozab.Service.Comments.Services
{ public interface ICommentsService { public Task<PostComment> AddNewComment(PostCommentVM comment); public Task<List<CommentResponse>> GetAllCommentsByPost(string postId); public Task<CommentResponse> GetCommentById(string commentId); public Task<PostComment> UpdateComment(string id, PostCommentVM comment); public Task<bool> DeleteCommentById(string commentId); public Task<bool> DeleteCommentByPost(string postId);
    }
}

Let us now implement each of the services and interact with our HarperDB Cloud Instance configured earlier and configure the below dependencies in startup.cs

services.AddSingleton<IHarperConfiguration, HarperConfigurations>();
services.AddSingleton<ICommentsService, CommentsService>();
services.AddSingleton<ISchemaService, SchemaService>();
services.AddSingleton<IPostService, PostService>();

using HarperNetClient;
using HarperNetClient.models;
using Mozab.Service.Comments.Services;
using Mozab.SharedUtilities;
using Newtonsoft.Json;
using System.Threading.Tasks;
using Constants = Mozab.SharedUtilities.Constants; namespace Mozab.Service.Services
{ public class SchemaService : ISchemaService { private IHarperClientAsync _client; private IHarperConfiguration _config; private HarperDbConfiguration _dbConfigs; public SchemaService(IHarperConfiguration configs) { _config = configs; _dbConfigs = _config.GetHarperConfigurations(); _client = new HarperClientAsync(_dbConfigs);
        }
}

Firstly, we will create three local variables to store the client, config, and dbConfig details. Each of these three will be available via the constructor method using dependency injection.

The _dbConfig object will use the HarperConfiguration service created earlier to get the HarperDB instance details from the appsettings.json file.

The _client object will be the actual object for the HarperClientAsync class which will expose all the async methods required to interact with HarperDB. This class accepts a HarperDbConfiguration object specifying the instance details to which the connection needs to be established.

public async Task<string> CreateSchema(string schemaName) { try { if (string.IsNullOrEmpty(schemaName)) throw new CustomException(Constants.InvalidTableOrSchemaName); var schemaExists = await CheckIfSchemaExists(schemaName); if (schemaExists) throw new CustomException(Constants.SchemaAlreadyExists); var response = await _client.CreateSchemaAsync(schemaName); if (response.IsSuccessful) _dbConfigs.Schema = schemaName; return JsonConvert.DeserializeObject<Content>(response.Content).Message; } catch { throw; } } private async Task<bool> CheckIfSchemaExists(string schema) { try { var response = await _client.DescribeSchemaAsync(schema); return response.StatusCode == System.Net.HttpStatusCode.OK ? true : false; } catch { throw;
            }
        }

In Order to create a new schema we will be using methods from the package. One will be used to check if a schema already exists with the same name or not and the other which actually creates the schema
The DescribeSchemaAsync method accepts a string specifying the schema name and returns details about it.
Following is the screenshot of the response for HarperDB for both the scenarios, when schema exists and when it doesn't

When the schema is not found, we get a NotFound status code back from HarperDB with a proper message in the Content property. If it's found we get back a StatusCode of Ok
If the schema is not found then we will use the CreateSchemaAsync method to create a new schema.
The response of a successful transaction, in this case, would be something like below

The NuGet package already contains a class name Content which can be used to parse the response and get the actual message returned by the HarperDB API.

Following the same logic, endpoints are exposed to create Tables and attributes as well. The only difference is that Table creation also expects a Schema name in which the table has to be created.

Methods CheckIfTableExists and CreateTableAsync will be used for the same.

using HarperNetClient;
using HarperNetClient.models;
using Mozab.Service.Comments.Services;
using Mozab.SharedUtilities;
using Newtonsoft.Json;
using System.Threading.Tasks;
using Constants = Mozab.SharedUtilities.Constants; namespace Mozab.Service.Services
{ public class SchemaService : ISchemaService { private IHarperClientAsync _client; private IHarperConfiguration _config; private HarperDbConfiguration _dbConfigs; public SchemaService(IHarperConfiguration configs) { _config = configs; _dbConfigs = _config.GetHarperConfigurations(); _client = new HarperClientAsync(_dbConfigs); } public async Task<string> CreateSchema(string schemaName) { try { if (string.IsNullOrEmpty(schemaName)) throw new CustomException(Constants.InvalidTableOrSchemaName); var schemaExists = await CheckIfSchemaExists(schemaName); if (schemaExists) throw new CustomException(Constants.SchemaAlreadyExists); var response = await _client.CreateSchemaAsync(schemaName); if (response.IsSuccessful) _dbConfigs.Schema = schemaName; return JsonConvert.DeserializeObject<Content>(response.Content).Message; } catch { throw; } } public async Task<string> CreateTable(string tableName, string schemaName) { try { if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(schemaName)) throw new CustomException(Constants.InvalidTableOrSchemaName); _dbConfigs.Schema = schemaName; var tableExists = await CheckIfTableExists(tableName, schemaName); if (tableExists) throw new CustomException(Constants.TableAlreadyExists); var response = await _client.CreateTableAsync(tableName, schemaName); return JsonConvert.DeserializeObject<Content>(response.Content).Message; } catch { throw; } } public async Task<string> CreateAttribute(string schema, string table, string attribute) { try { if (string.IsNullOrEmpty(table) || string.IsNullOrEmpty(schema)) throw new CustomException(Constants.InvalidTableOrSchemaName); _dbConfigs.Schema = schema; if (string.IsNullOrEmpty(attribute)) throw new CustomException("Attribute name cannot be empty"); var tableExists = await CheckIfTableExists(table, schema); if (!tableExists) throw new CustomException(Constants.TableNotFound); var response = await _client.CreateAttributeAsync(table, schema, attribute); return JsonConvert.DeserializeObject<Content>(response.Content).Message; } catch { throw; } } public async Task<string> DeleteAttribute(string table, string schema, string attribute) { try { if (string.IsNullOrEmpty(table) || string.IsNullOrEmpty(schema)) throw new CustomException(Constants.InvalidTableOrSchemaName); _dbConfigs.Schema = schema; if (string.IsNullOrEmpty(attribute)) throw new CustomException("Attribute name cannot be empty"); var tableExists = await CheckIfTableExists(table, schema); if (!tableExists) throw new CustomException(Constants.TableNotFound); var response = await _client.DropAttributeAsync(table, schema, attribute); return JsonConvert.DeserializeObject<Content>(response.Content).Message; } catch { throw; } } public async Task<string> DeleteSchema(string schema) { try { if (string.IsNullOrEmpty(schema)) throw new CustomException(Constants.InvalidTableOrSchemaName); _dbConfigs.Schema = schema; var schemaExists = await CheckIfSchemaExists(schema); if (!schemaExists) throw new CustomException(Constants.SchemaNotFound); var response = await _client.DropSchemaAsync(schema); return JsonConvert.DeserializeObject<Content>(response.Content).Message; } catch { throw; } } public async Task<string> DeleteTable(string table, string schema) { try { if (string.IsNullOrEmpty(table) || string.IsNullOrEmpty(schema)) throw new CustomException(Constants.InvalidTableOrSchemaName); _dbConfigs.Schema = schema; var tableExists = await CheckIfTableExists(table, schema); if (!tableExists) throw new CustomException(Constants.TableNotFound); var response = await _client.DropTableAsync(table, schema); return JsonConvert.DeserializeObject<Content>(response.Content).Message; } catch { throw; } } private async Task<bool> CheckIfSchemaExists(string schema) { try { var response = await _client.DescribeSchemaAsync(schema); return response.StatusCode == System.Net.HttpStatusCode.OK ? true : false; } catch { throw; } } private async Task<bool> CheckIfTableExists(string table, string schema) { try { var response = await _client.DescribeTableAsync(table, schema); return response.StatusCode == System.Net.HttpStatusCode.OK ? true : false; } catch { throw;
            }
        }

    }
}

We can use the endpoints to create a Schema named Mozabs and tables named posts and comments which we will be using in our other two services. (These names can be updated in the constructor method of their corresponding service class)

This service exposes various methods which can be used to perform CRUD operations on the comment entity.

Comment can be made directly to the post in which case the PostId property of the comment will be populated. Another use case is to reply to an existing comment in which case, the PostId field will be null or empty and the ParentCommentId field will have the value

The initial configuration of both the CommentService and the later PostService remains the same as the SchemaService as it also needs all those three properties defined.

The only difference here will be that we define the Schema and Table names as well. The schema will be part of the HarperDbConfiguration class while the Table name can be passed as a parameter to the constructor of the HarperClientAsync class

using AutoMapper;
using HarperNetClient;
using HarperNetClient.models;
using Mozab.SharedUtilities;
using Mozab.SharedUtilities.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Constants = Mozab.SharedUtilities.Constants; namespace Mozab.Service.Comments.Services
{ public class CommentsService : ICommentsService { private IHarperClientAsync _client; private IHarperConfiguration _config; private HarperDbConfiguration _dbConfigs; private string Table_Name = "comments"; private IMapper _mapper; public CommentsService(IHarperConfiguration configs, IMapper mapper) { _config = configs; _dbConfigs = _config.GetHarperConfigurations(); _dbConfigs.Schema = "Mozabs"; _client = new HarperClientAsync(_dbConfigs, Table_Name); _mapper = mapper;

        }
}

public async Task<PostComment> AddNewComment(PostCommentVM commentToAdd) { try { if (string.IsNullOrEmpty(commentToAdd.Comment)) throw new CustomException(Constants.CommentCannotBeEmpty); if (!string.IsNullOrEmpty(commentToAdd.ParentCommentId)) { if (CheckIfCommentExists(commentToAdd.ParentCommentId) == null) throw new CustomException(Constants.InvalidParentCommentId); } var comment = _mapper.Map<PostComment>(commentToAdd); comment.PostedAt = DateTime.Now; var response = await _client.CreateRecordAsync<PostComment>(comment); var insertedCommentId = JsonConvert.DeserializeObject<Content>(response.Content).Inserted_Hashes[0]; comment.id = insertedCommentId; return comment; } catch { throw;
            }
        }

The generic method CreateRecordAsync is used to create a new record in the database.
The response returned contains the id of the newly created post along with the Ok status code.
Id can be retrieved from the inserted_hashes property of the content in the response object

public async Task<CommentResponse> GetCommentById(string commentId) { try { if (string.IsNullOrEmpty(commentId)) throw new CustomException(Constants.InvalidCommentId); string query = $"SELECT * FROM {_dbConfigs.Schema}.{Table_Name} WHERE id = \"{commentId}\" OR ParentCommentId = \"{commentId}\" "; var response = await _client.ExecuteQueryAsync(query); var commentsResponse = JsonConvert.DeserializeObject<List<PostComment>>(response.Content); if (commentsResponse != null && commentsResponse.Count > 0) { var parentComment = commentsResponse.Where(x => x.id == commentId).ToList()[0]; var childComments = commentsResponse.Where(x => x.id != commentId).ToList(); var comments = _mapper.Map<CommentResponse>(parentComment); comments.CommentReplies = childComments; return comments; } else { return new CommentResponse(); } } catch { throw;
            }
        }

Now when we pass a comment ID we need to get back the actual comment and any replies to that comment as well.
Although we have the GetRecordByID method exposed since we have a custom requirement, we can use the power of HarperDB to use SQL query on JSON type schema and get desired records.
The ExecuteQueryAsync method is used here where we pass on a simple query to fetch the comments with the given id and all other comments which have its ParentCommentID as it as well.

public async Task<PostComment> UpdateComment(string id, PostCommentVM commentToUpdate) { try { var comment = _mapper.Map<PostComment>(commentToUpdate); if (CheckIfCommentExists(id) == null) throw new CustomException(Constants.InvalidCommentId); comment.id = id; var response = await _client.UpdateRecordAsync<PostComment>(comment); if (response.IsSuccessful) { return comment; } else { return null; } } catch { throw; } } public async Task<PostComment> CheckIfCommentExists(string commentId) { try { var response = await _client.GetByIdAsync(commentId); var comment = (JsonConvert.DeserializeObject<List<PostComment>>(response.Content)); if (comment != null && comment.Count > 0) return comment[0]; else return null; } catch { throw;
            }

        }

Before updating the comment we will make use of the available method GetByIdAsync to make sure a comment exists for the given id
If true, we will move on to the UpdateRecordAsync method and pass on the modified object which will be updated in the database
If successful, the Content part in response will have the ID of the comment updated in the Update_Hashes property

public async Task<bool> DeleteCommentById(string commentId) { try { if (CheckIfCommentExists(commentId) == null) throw new CustomException(Constants.InvalidCommentId); string queryToDeleteMainComment = $"DELETE FROM {_dbConfigs.Schema}.{Table_Name} WHERE id = \"{commentId}\""; var response = await _client.ExecuteQueryAsync(queryToDeleteMainComment); if (response.IsSuccessful) { string queryToDeleteCommentReplies = $"DELETE FROM {_dbConfigs.Schema}.{Table_Name} WHERE ParentCommentId = \"{commentId}\""; response = await _client.ExecuteQueryAsync(queryToDeleteCommentReplies); } return response.IsSuccessful; } catch { throw;
            }
        }

There is no direct method exposed to delete an entity in HarperDB. Instead, we can make use of the ExecuteQueryAsync method again and pass on the delete query to it.
Here we are first executing a query to delete the comment by the ID and then another query to delete all the replies to it as well.

We have two more methods implemented in the CommentService which use the ExecuteQueryAsync method to Get all comments by the PostId and the other to Delete all comments by PostId. Both of these methods reuse the GET and DELETE functionality created above.

using AutoMapper;
using HarperNetClient;
using HarperNetClient.models;
using Mozab.SharedUtilities;
using Mozab.SharedUtilities.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Constants = Mozab.SharedUtilities.Constants; namespace Mozab.Service.Comments.Services
{ public class CommentsService : ICommentsService { private IHarperClientAsync _client; private IHarperConfiguration _config; private HarperDbConfiguration _dbConfigs; private string Table_Name = "comments"; private IMapper _mapper; public CommentsService(IHarperConfiguration configs, IMapper mapper) { _config = configs; _dbConfigs = _config.GetHarperConfigurations(); _dbConfigs.Schema = "Mozabs"; _client = new HarperClientAsync(_dbConfigs, Table_Name); _mapper = mapper; } public async Task<PostComment> AddNewComment(PostCommentVM commentToAdd) { try { if (string.IsNullOrEmpty(commentToAdd.Comment)) throw new CustomException(Constants.CommentCannotBeEmpty); if (!string.IsNullOrEmpty(commentToAdd.ParentCommentId)) { if (CheckIfCommentExists(commentToAdd.ParentCommentId) == null) throw new CustomException(Constants.InvalidParentCommentId); } var comment = _mapper.Map<PostComment>(commentToAdd); comment.PostedAt = DateTime.Now; var response = await _client.CreateRecordAsync<PostComment>(comment); var insertedCommentId = JsonConvert.DeserializeObject<Content>(response.Content).Inserted_Hashes[0]; comment.id = insertedCommentId; return comment; } catch { throw; } } public async Task<bool> DeleteCommentById(string commentId) { try { if (CheckIfCommentExists(commentId) == null) throw new CustomException(Constants.InvalidCommentId); string queryToDeleteMainComment = $"DELETE FROM {_dbConfigs.Schema}.{Table_Name} WHERE id = \"{commentId}\""; var response = await _client.ExecuteQueryAsync(queryToDeleteMainComment); if (response.IsSuccessful) { string queryToDeleteCommentReplies = $"DELETE FROM {_dbConfigs.Schema}.{Table_Name} WHERE ParentCommentId = \"{commentId}\""; response = await _client.ExecuteQueryAsync(queryToDeleteCommentReplies); } return response.IsSuccessful; } catch { throw; } } public async Task<bool> DeleteCommentByPost(string postId) { try { if (CheckIfCommentsExistsForPost(postId) == null) throw new CustomException(Constants.NoCommentForTheJab); string queryToGetMainComments = $"SELECT id FROM {_dbConfigs.Schema}.{Table_Name} WHERE PostId = \"{postId}\" "; var response = await _client.ExecuteQueryAsync(queryToGetMainComments); var mainComments = (JsonConvert.DeserializeObject<List<PostComment>>(response.Content)); foreach (var pc in mainComments) { await DeleteCommentById(pc.id); } return response.IsSuccessful; } catch { throw; } } public async Task<List<CommentResponse>> GetAllCommentsByPost(string postId) { try { string query = $"SELECT * FROM {_dbConfigs.Schema}.{Table_Name} WHERE PostId = \"{postId}\""; var response = await _client.ExecuteQueryAsync(query); var mainComments = (JsonConvert.DeserializeObject<List<PostComment>>(response.Content)); var threads = new List<CommentResponse>(); foreach (var pc in mainComments) { threads.Add(await GetCommentById(pc.id)); } return threads; } catch { throw; } } public async Task<CommentResponse> GetCommentById(string commentId) { try { if (string.IsNullOrEmpty(commentId)) throw new CustomException(Constants.InvalidCommentId); string query = $"SELECT * FROM {_dbConfigs.Schema}.{Table_Name} WHERE id = \"{commentId}\" OR ParentCommentId = \"{commentId}\" "; var response = await _client.ExecuteQueryAsync(query); var commentsResponse = JsonConvert.DeserializeObject<List<PostComment>>(response.Content); if (commentsResponse != null && commentsResponse.Count > 0) { var parentComment = commentsResponse.Where(x => x.id == commentId).ToList()[0]; var childComments = commentsResponse.Where(x => x.id != commentId).ToList(); var comments = _mapper.Map<CommentResponse>(parentComment); comments.CommentReplies = childComments; return comments; } else { return new CommentResponse(); } } catch { throw; } } public async Task<PostComment> UpdateComment(string id, PostCommentVM commentToUpdate) { try { var comment = _mapper.Map<PostComment>(commentToUpdate); if (CheckIfCommentExists(id) == null) throw new CustomException(Constants.InvalidCommentId); comment.id = id; var response = await _client.UpdateRecordAsync<PostComment>(comment); if (response.IsSuccessful) { return comment; } else { return null; } } catch { throw; } } public async Task<PostComment> CheckIfCommentExists(string commentId) { try { var response = await _client.GetByIdAsync(commentId); var comment = (JsonConvert.DeserializeObject<List<PostComment>>(response.Content)); if (comment != null && comment.Count > 0) return comment[0]; else return null; } catch { throw; } } public async Task<PostComment> CheckIfCommentsExistsForPost(string postId) { try { string query = $"SELECT * FROM {_dbConfigs.Schema}.{Table_Name} WHERE PostId = \"{postId}\""; var response = await _client.ExecuteQueryAsync(query); var comment = (JsonConvert.DeserializeObject<List<PostComment>>(response.Content)); if (comment != null && comment.Count > 0) return comment[0]; else return null; } catch { throw;
            }
        }

    }
}

PostService

using AutoMapper;
using HarperNetClient;
using HarperNetClient.models;
using Mozab.Service.Comments.Services;
using Mozab.SharedUtilities;
using Mozab.SharedUtilities.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Constants = Mozab.SharedUtilities.Constants; namespace Mozab.Service.Services
{ public class PostService: IPostService { private IHarperClientAsync _client; private IHarperConfiguration _config; private HarperDbConfiguration _dbConfigs; private string Table_Name = "posts"; private IMapper _mapper; private ICommentsService _comments; public PostService(IHarperConfiguration configs, IMapper mapper, ICommentsService service) { _config = configs; _dbConfigs = _config.GetHarperConfigurations(); _dbConfigs.Schema = "Mozabs"; _client = new HarperClientAsync(_dbConfigs, Table_Name); _mapper = mapper; _comments = service;
        }
}

Create a Post

public async Task<SharedUtilities.Models.Posts> AddNewPost(PostVM postToAdd) { try { if (string.IsNullOrEmpty(postToAdd.Content)) throw new CustomException("Posts content cannot be empty"); var post = _mapper.Map<Posts>(postToAdd); post.PostedAt = DateTime.Now; var response = await _client.CreateRecordAsync<Posts>(post); var insertedCommentId = JsonConvert.DeserializeObject<Content>(response.Content).Inserted_Hashes[0]; post.id = insertedCommentId; return post; } catch { throw;
            }
        }

The generic method CreateRecordAsync is used to create a new record in the database.
The response returned contains the id of the newly created post along with the Ok status code.
Id can be retrieved from the inserted_hashes property of the content in the response object

All other CRUD operations are similar to the implementation done in the CommentService in terms of the communication with HarperDB. The only change is in the business logic as we call a few methods of CommentService from the PostService to Get comments on a particular post

using AutoMapper;
using HarperNetClient;
using HarperNetClient.models;
using Mozab.Service.Comments.Services;
using Mozab.SharedUtilities;
using Mozab.SharedUtilities.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Constants = Mozab.SharedUtilities.Constants; namespace Mozab.Service.Services
{ public class PostService: IPostService { private IHarperClientAsync _client; private IHarperConfiguration _config; private HarperDbConfiguration _dbConfigs; private string Table_Name = "posts"; private IMapper _mapper; private ICommentsService _comments; public PostService(IHarperConfiguration configs, IMapper mapper, ICommentsService service) { _config = configs; _dbConfigs = _config.GetHarperConfigurations(); _dbConfigs.Schema = "Mozabs"; _client = new HarperClientAsync(_dbConfigs, Table_Name); _mapper = mapper; _comments = service; } public async Task<SharedUtilities.Models.Posts> AddNewPost(PostVM postToAdd) { try { if (string.IsNullOrEmpty(postToAdd.Content)) throw new CustomException("Posts content cannot be empty"); var post = _mapper.Map<Posts>(postToAdd); post.PostedAt = DateTime.Now; var response = await _client.CreateRecordAsync<Posts>(post); var insertedCommentId = JsonConvert.DeserializeObject<Content>(response.Content).Inserted_Hashes[0]; post.id = insertedCommentId; return post; } catch { throw; } } public async Task<bool> DeletePostById(string id) { try { if (CheckIfPostExist(id) == null) throw new CustomException(Constants.PostNotFound); string query = $"DELETE FROM {_dbConfigs.Schema}.{Table_Name} WHERE id = \"{id}\""; var response = await _client.ExecuteQueryAsync(query); if (response.IsSuccessful) { await _comments.DeleteCommentByPost(id); } return response.IsSuccessful; } catch { throw; } } public async Task<List<PostResponse>> GetAllPosts() { try { string query = $"SELECT * FROM {_dbConfigs.Schema}.{Table_Name}"; var response = await _client.ExecuteQueryAsync(query); var postResponse = JsonConvert.DeserializeObject<List<Posts>>(response.Content); if (postResponse != null && postResponse.Count > 0) { var posts = new List<PostResponse>(); foreach (var pr in postResponse) { posts.Add(await GetPostById(pr.id)); } return posts; } else { return new List<PostResponse>(); } } catch { throw; } } public async Task<PostResponse> GetPostById(string postId) { try { if (string.IsNullOrEmpty(postId)) throw new CustomException(Constants.PostNotFound); string query = $"SELECT * FROM {_dbConfigs.Schema}.{Table_Name} WHERE id = \"{postId}\" "; var response = await _client.ExecuteQueryAsync(query); var postResponse = JsonConvert.DeserializeObject<List<Posts>>(response.Content); if (postResponse != null && postResponse.Count > 0) { var commentThread = await _comments.GetAllCommentsByPost(postResponse[0].id); var postsData = _mapper.Map<PostResponse>(postResponse[0]); postsData.CommentsThread = commentThread; return postsData; } else { return new PostResponse(); } } catch { throw; } } public async Task<SharedUtilities.Models.Posts> UpdatePostById(string id, PostVM postToUpdate) { try { var post = _mapper.Map<Posts>(postToUpdate); if (CheckIfPostExist(id) == null) throw new CustomException(Constants.PostNotFound); post.id = id; var response = await _client.UpdateRecordAsync<Posts>(post); if (response.IsSuccessful) { return post; } else { return null; } } catch { throw; } } public async Task<SharedUtilities.Models.Posts> CheckIfPostExist(string id) { try { var response = await _client.GetByIdAsync(id); var post = (JsonConvert.DeserializeObject<List<Posts>>(response.Content)); if (post != null && post.Count > 0) return post[0]; else return null; } catch { throw;
            }
        }
    }
}

The HarperDB.Net.Client Nuget package exposes various methods which have been utilized to create a basic CRUD application.

The great part of this package and HarperDB is that we can ignore all the methods and utilize only the ExecuteQueryAsync method in order to achieve our tasks if SQL Queries are something we are more familiar with.

The detailed response object from HarperDB makes it easier to understand the issues encountered if any and lists down everything we need to know about the request we made.

Github Repository

Nuget Package

HarperDB documentations and Code Examples

Postman Collection

Restful Api C#Asp.net core Web api 2

Report

Enjoy this post? Give Rajat Srivastava a like if it's helpful.

Rajat Srivastava

Senior Dot Net Developer specializing in building robust restful api's

With around 8 years of experience working in Dot Net technologies, I have gained hands on experience in working with C#, SQL Server, Rest API, NUnit for unit test cases and various others modules, Nuget etc. which are commonly use...

Discover and read more posts from Rajat Srivastava

get started