Hey guys, so ever since I worked on HarperDb with NodeJs a few days back, I wanted to try it out with dot net core. Professionally, I have been working as a .Net developer so checking this integration was a must thing to do.

So the plan was to make a simple CRUD API using HarperDB but eventually, I ended up making a separate project which I believe is now good enough for anyone of you to integrate with your API project and start exploring. You can try it out as a Nuget Package nuget.org/packages/HarperDb_Net_Client/#

dotnet add package HarperDb_Net_Client --version 1.0.0

Operations Supported

• CreateSchema
• CreateTable
• CreateRecord
• CreateBulkRecord>
• GetById
• ExecuteQuery
• UpdateRecord

As of now the first version which I pushed to GitHub earlier today supports the above-stated operations. Let us now try and understand how we can use this client with our API project.

Get the HarperDbClient

The code for the client is available at the linked Github. Client application either can directly add this project in their solution or can build it and add the DLL as a reference

Models

There are two models which you will need in your client app. HarperDbConfiguration and the HarperDbResponse.

The Configuration model is used to pass on the configuration of your instance to the package while the response object is there in case you want to deserialize the RestResponse returned by HarperDb.

HarperDbConfiguration

public class HarperDbConfiguration
    {
        public HarperDbConfiguration()
        {

        }

        public string InstanceUrl { get; set; }

        public string AuthToken { get; set; }

        public string Schema { get; set; }

    }
}

This class has three properties for the setup of the configuration. This will be required to initialize the main class constructor as based on these configuration connections to the HarperDB will be established.

HarperDbResponse

 public class HarperDbResponse
    {
        public HarperDbResponse()
        {
        }

        public string StatusCode { get; set; }

        public Content Content { get; set; }

        public string ResponseStatus { get; set; }

        public string StatusDescription { get; set; }

    }

    public class Content
    {
        public string Message { get; set; }

        public List<string> Inserted_Hashes { get; set; }

        public string SkippedHashes { get; set; }

    }

I have used the RestSharp NuGet package to request the HarperDb. The response is received in this case is of type IRestResponse which this package returns to the calling client. The client can use this class to convert the incoming response into JSON.

HarperDbRequest

This is another class that I have used inside the package for generating the request object which will be sent to Harper. This contains information like the operation type, table, etc. But the client app doesn't need to worry about this.

IHarperClient

This interface contains the method declarations for the operations supported as of now by this package which have been specified earlier

 public interface IHarperClient
    {
        IRestResponse CreateRecord<T>(T itemToCreate);
        IRestResponse CreateBulkRecord<T>(string csvFilePath);
        IRestResponse GetById(string id);
        IRestResponse ExecuteQuery(string sqlQuery);
        IRestResponse UpdateRecord<T>(T itemToUpdate);
        IRestResponse CreateSchema(string schema);
        IRestResponse CreateTable(string table, string schema, string hashAttribute = "id");

    }

HarperClient

This is the main class with all the logic required to perform the CRUD operations with Harper. Let us now see how this is implemented

        private RestClient _client;
        private HarperDbConfiguration _harperDbConfig;
        private string Schema_Table = "";

        public HarperClient(HarperDbConfiguration config)
        {
            _harperDbConfig = config;
            _client = new RestClient(new Uri(_harperDbConfig.InstanceUrl));
        }

        public HarperClient(HarperDbConfiguration config, string table)
        {
            _harperDbConfig = config;
            Schema_Table = table;
            _client = new RestClient(new Uri(_harperDbConfig.InstanceUrl));
        }

         private RestRequest CreateBaseRequest()
        {
            var request = new RestRequest(Method.POST);
            request.AddHeader("Content-Type", "application/json");
            request.AddHeader("Authorization", $"Basic {_harperDbConfig.AuthToken}");

            return request;
        }

The client application will need to refer to this class to execute the method for CRUD operations. Now there are two constructors which have been exposed. In case the schema and table name is already known then the second constructor can be used. In case the Schema/Table is also being created via the package in that case the single parameter constructor needs to be called

The config object is a must and as described above contains the instance details. I then have the initialization for the RestClient using the instance URL of our HarperDb instance.

Post this configuration we have the implementation for the individual operations that can be formed. Here's the snapshot of the entire class.

using System;
using System.Collections.Generic;
using HarperNetClient.models;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using RestSharp;

namespace HarperNetClient
{
    public class HarperClient : IHarperClient
    {

        private RestClient _client;
        private HarperDbConfiguration _harperDbConfig;
        private string Schema_Table = "";
        private ILogger<HarperClient> _logger;
        public HarperClient(HarperDbConfiguration config)
        {
            _harperDbConfig = config;
            _client = new RestClient(new Uri(_harperDbConfig.InstanceUrl));
        }

        public HarperClient(HarperDbConfiguration config, string table)
        {
            _harperDbConfig = config;
            Schema_Table = table;
            _client = new RestClient(new Uri(_harperDbConfig.InstanceUrl));
        }

        private RestRequest CreateBaseRequest()
        {
            var request = new RestRequest(Method.POST);
            request.AddHeader("Content-Type", "application/json");
            request.AddHeader("Authorization", $"Basic {_harperDbConfig.AuthToken}");

            return request;
        }


        public IRestResponse CreateSchema(string schema)
        {
            try
            {
                Console.WriteLine($"Creating new Schema: {schema}");
                var requestBody = new HarperRequest()
                {
                    Operation = "create_schema",
                    Schema = schema,
                };

                var request = CreateBaseRequest();
                request.AddParameter("application/json",
                    JsonConvert.SerializeObject(requestBody), ParameterType.RequestBody);

                var response = _client.Execute(request);
                return response;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }

        public IRestResponse CreateTable(string table, string schema, string hashAttribute = "id")
        {
            try
            {
                Console.WriteLine($"Creating new Table: {schema}.{table}");
                var requestBody = new HarperRequest()
                {
                    Operation = "create_table",
                    Schema = schema,
                    Table = table,
                    Hash_Attribute = hashAttribute
                };

                var request = CreateBaseRequest();
                request.AddParameter("application/json",
                    JsonConvert.SerializeObject(requestBody), ParameterType.RequestBody);

                var response = _client.Execute(request);
                Console.WriteLine($"{response.Content}");
                return response;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }

        public IRestResponse CreateRecord<T>(T itemToCreate)
        {
            try
            {
                Console.WriteLine($"Creating record");
                List<Object> obj = new List<Object>();
                obj.Add(itemToCreate);
                var requestBody = new HarperRequest()
                {
                    Operation = "insert",
                    Schema = _harperDbConfig.Schema,
                    Table = Schema_Table,
                    Records = obj
                };

                var request = CreateBaseRequest();
                request.AddParameter("application/json",
                    JsonConvert.SerializeObject(requestBody), ParameterType.RequestBody);

                var response = _client.Execute(request);
                Console.WriteLine($"{response.Content}");
                return response;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }

        public IRestResponse CreateBulkRecord<T>(string csvFilePath)
        {
            try
            {
                Console.WriteLine($"Bulk import starting from: {csvFilePath}");
              var requestBody = new HarperRequest()
                {
                    Operation = "csv_file_load",
                    Schema = _harperDbConfig.Schema,
                    Table = Schema_Table,
                    File_Path = csvFilePath
                };

                var request = CreateBaseRequest();
                request.AddParameter("application/json",
                    JsonConvert.SerializeObject(requestBody), ParameterType.RequestBody);

                var response = _client.Execute(request);
                Console.WriteLine($"{response.Content}");
                return response;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }



        public IRestResponse GetById(string id)
        {
            try
            {
                Console.WriteLine($"Get by id: {id}");
              var requestBody = new HarperRequest()
                {
                    Operation = "sql",
                    SQL = $"SELECT * FROM {_harperDbConfig.Schema}.{Schema_Table} WHERE id = \"{id}\""
                };

                var request = CreateBaseRequest();
                request.AddParameter("application/json", JsonConvert.SerializeObject(requestBody), ParameterType.RequestBody);
                var response = _client.Execute(request);
                Console.WriteLine($"{response.Content}");
                Console.WriteLine(response.Content);

                return response;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }

        public IRestResponse ExecuteQuery(string sqlQuery)
        {
            try
            {
                Console.WriteLine($"Exwcuting query: {sqlQuery}");
              var requestBody = new HarperRequest()
                {
                    Operation = "sql",
                    SQL = sqlQuery
                };

                var request = CreateBaseRequest();
                request.AddParameter("application/json", JsonConvert.SerializeObject(requestBody), ParameterType.RequestBody);
                var response = _client.Execute(request);
                Console.WriteLine($"{response.Content}");
                return response;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }

        public IRestResponse UpdateRecord<T>(T itemToUpdate)
        {
            try
            {
                Console.WriteLine($"Updating record");
                List<Object> obj = new List<Object>();
                obj.Add(itemToUpdate);
                var requestBody = new HarperRequest()
                {
                    Operation = "update",
                    Schema = _harperDbConfig.Schema,
                    Table = Schema_Table,
                    Records = obj
                };

                var request = CreateBaseRequest();
                request.AddParameter("application/json",
                    JsonConvert.SerializeObject(requestBody), ParameterType.RequestBody);

                var response = _client.Execute(request);
                Console.WriteLine($"{response.Content}");
                return response;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
                return null;
            }
        }
    }
}

Let us now see how we can use this package to let our API interact with Harper DB

Pre-requisite

• Asp.Net Core web API project
• Code or .dll for the above-mentioned package referenced in the API project
• Free cloud instance of HarperDB => You can refer to this link where I have earlier mentioned the step by step process to create our free cloud instance stackup.hashnode.dev/file-uploadresize-usin..

Models

Let us create a separate folder for our models. We will be needing only one model for our entity Domain. The idea is to perform a CRUD operation on this Domain entity. The service which we are building is a part of a large service that allows users to link some domain names which they want to track. We here are saving these domains in the database with a mapping to its corresponding user.

public class DomainTracking
    {
        public DomainTracking()
        {
        }

        [Key]
        [JsonIgnore]
        public string id { get; set; }

        public string DomainName { get; set; }

        public bool isAvailable { get; set; }

        public bool isTracking { get; set; }

        public string LinkedUser { get; set; }
    }

Add HarperDb Configuration

Open the appsettings.json file and create a new section HarperDbConfiguration. Provide the details of your cloud instance here

"HarperDbConfiguration": {
    "InstanceUrl": "YOUR_CLOUD_INSTANCE_URL",
    "AuthToken": "YOUR_AUTH_TOKEN",
    "Schema": "YOUR_SCHEMA_NAME"
  }

Create Configuration Handler

Interface

    public interface IConfigurationHandler
    {
        HarperNetClient.models.HarperDbConfiguration GetHarperDbConfiguration();
    }

Class

public class ConfigurationHandler: IConfigurationHandler
    {
        private IConfiguration config;
        public ConfigurationHandler(IConfiguration configuration)
        {
            config = configuration;
        }

        public HarperNetClient.models.HarperDbConfiguration GetHarperDbConfiguration()
        {
            var dbConfig = config.GetSection("HarperDbConfiguration").Get<HarperNetClient.models.HarperDbConfiguration>();
            return dbConfig;
        }

    }

Now this Configuration handler class will be used to read configurations from our app setting file. Instead of directly reading it from here we have implemented it via interfaces and classes. The GetHarperDbConfiguration when called will return as the model with the details from the appsetting file.

Setup Utilities and Configuration

Create a new Utility folder in the solution. This folder will contain some utility classes which we will use at the project level.

Firstly, let's add a DependencyInjector static class into this folder. Although we can directly add dependency in the Startup file we should follow such an approach and keep it in a separate place

We will be creating an extension method for IServiceCollection, which allows us to use this method easily from the startup class. Let us create that and for now, add the following three mappings here. We will come back here later to update the reference

 public static class DependencyInjector
    {
       public static void AddDependency(this IServiceCollection service)
        {
            service.AddSingleton<IConfigurationHandler, ConfigurationHandler>();
            service.AddSingleton<IDomainRepository, DomainRepository>();
            service.AddSingleton<ISchemaRepository, SchemaRepository>();
        }
    }

Repositories

We will have to separate repositories one to deal with CRUD for Domain and the other which exposes the methods for DDL operations like Schema and Table creation

ISchemaRepository

 public interface ISchemaRepository
    {
        public string CreateTable(string table);
    }

SchemaRepository

public class SchemaRepository: ISchemaRepository
    {
        private HarperNetClient.IHarperClient _harperClient;
        private ConfigurationHandler configHandler;
        private HarperNetClient.models.HarperDbConfiguration _harperDbConfig;
        public SchemaRepository(IConfigurationHandler config)
        {
            configHandler = (ConfigurationHandler)config;
            _harperDbConfig = configHandler.GetHarperDbConfiguration();
            _harperClient = new HarperNetClient.HarperClient(_harperDbConfig);
        }

        public string CreateTable(string table)
        {
            var response = _harperClient.CreateTable(table, _harperDbConfig.Schema);
                return JsonConvert.DeserializeObject<HarperNetClient.models.Content>(response.Content).Message;
        }
    }

Here we first get the object for our Configuration Handler. Then we use this object to get the harper configurations. We then use this configuration to create an object for our HarperClient class from the package.

We are using the single param constructor since this is the schema constructor and we want to create the table. We have a method which calls the CreateTable method on our package with the table name that has to be created.

We get an IRestResponse in return on which we can use JsonConvert to convert it to an object and get the required fields for generating our actual response.

IDomainRepository

public interface IDomainRepository
    {
        public List<string> CreateDomainTrackingFromCSV(string filePath);
        public string DeleteDomainById(string id);
        public string UpdateDomain(DomainTracking domain);
        public string CreateDomain(DomainTracking domainToCreate);
    }

We have all the basic CRUD operations listed for the Domain entity and corresponding methods for the same are declared here.

DomainRepository

public class DomainRepository: IDomainRepository
    {
        private HarperNetClient.IHarperClient _harperClient;
        private ConfigurationHandler configHandler;
        private HarperNetClient.models.HarperDbConfiguration _harperDbConfig;
        private string Schema_Table = "domain";

        public DomainRepository(IConfigurationHandler config)
        {
            configHandler = (ConfigurationHandler)config;
            _harperDbConfig = configHandler.GetHarperDbConfiguration();
            _harperClient = new HarperNetClient.HarperClient(_harperDbConfig, Schema_Table);
        }


        public string DeleteDomainById(string id)
        {
            string query = $"DELETE FROM {_harperDbConfig.Schema}.{Schema_Table} WHERE id = \"{id}\"";
            var response = _harperClient.ExecuteQuery(query);
            return JsonConvert.DeserializeObject<HarperNetClient.models.Content>(response.Content).Message;
        }

        public string UpdateDomain(DomainTracking domain)
        {
            var response = _harperClient.UpdateRecord<DomainTracking>(domain);
            return JsonConvert.DeserializeObject<HarperNetClient.models.Content>(response.Content).Message;

        }

        public List<string> CreateDomainTrackingFromCSV(string filePath)
        {
            filePath = "Users/rajatsrivastava/Projects/harperdb-crud/harperdb-crud/BulkImport.csv";

            var response = _harperClient.CreateBulkRecord<List<DomainTracking>>(filePath);
            return JsonConvert.DeserializeObject<HarperNetClient.models.Content>(response.Content).Inserted_Hashes;
        }

        public string CreateDomain(DomainTracking domainToCreate)
        {
            var response = _harperClient.CreateRecord<DomainTracking>(domainToCreate);
            return JsonConvert.DeserializeObject<HarperNetClient.models.Content>(response.Content).Inserted_Hashes[0];
        }
    }

We follow a similar approach as in SchemaRepository the only difference here being that we will be calling the package class constructor with two params as we know the table we want to deal with here.

Controller

Let us now create two new ApiControllers to expose the endpoints for the operations we have implemented

SchemaController

[Route("api/[controller]")]
    [ApiController]
    public class SchemaController : ControllerBase
    {
        ISchemaRepository _domainRepo;
        public SchemaController(ISchemaRepository repo)
        {
            _domainRepo = repo;
        }

        [HttpPost]
        [Route("table")]
        public IActionResult CreateTable(string table)
        {
            var response = _domainRepo.CreateTable(table);
            return response != null ? Ok(response) : BadRequest("Something went wrong");
        }

    }

DomainController

[Route("api/[controller]")]
    [ApiController]
    public class DomainController : ControllerBase
    {
        IDomainRepository _domainRepo;
        public DomainController(IDomainRepository repo)
        {
            _domainRepo = repo;
        }


        [HttpPost]
        [Route("domain/bulk")]
        public IActionResult CreateDomainFromcsv(string filePath)
        {
            var response = _domainRepo.CreateDomainTrackingFromCSV(filePath);
            return response != null ? Ok(response) : BadRequest("Something went wrong");
        }

        [HttpPost]
        [Route("domain")]
        public IActionResult CreateDomain(DomainTracking domainToCreate)
        {
            var response = _domainRepo.CreateDomain(domainToCreate);
            return response != null ? Ok(response) : BadRequest("Something went wrong");
        }

        [HttpDelete]
        [Route("domain/id")]
        public IActionResult CreateDomain(string id)
        {
            var response = _domainRepo.DeleteDomainById(id);
            return response != null ? Ok(response) : BadRequest("Something went wrong");
        }

        [HttpPut]
        [Route("domain/id")]
        public IActionResult CreateDomain(string id, DomainTracking domainToCreate)
        {
            domainToCreate.id = id;
            var response = _domainRepo.UpdateDomain(domainToCreate);
            return response != null ? Ok(response) : BadRequest("Something went wrong");
        }
    }

Finally, let us update our Startup.cs file to call our extension method to resolve the dependencies we have created.

  services.AddDependency();

Conclusion

That's it, we have our client code ready for performing the CRUD operations with HarperDB. Let us now run this project and try it from swagger UI.

So the API is successful and returning the ID of the newly created domain object. Let us now head over to the dashboard and see on the HarperDB side

As we can see the package we have implemented can be easily used to perform an interaction with HarperDB from a .Net core project. I would be glad if you could give this a try and share your feedback.

Link to the package: github.com/rajat-srivas/HarperDbClient_DotN.. Link to the sample client app: github.com/rajat-srivas/HarperDB_Crud_With_..