C# XTDB Connector

DotNetXtdbConnector.cs

public abstract class AbstractXtdb
{
	public Dictionary<string, object> ToJsonLd(object obj)
	{
		if (obj is DateTime dateTime)
		{
			return new Dictionary<string, object>
			{
				["@value"] = dateTime.ToString("o"),
				["@type"] = "xt:timestamp"
			};
		}

		if (obj is DateTime date)
		{
			return new Dictionary<string, object>
			{
				["@value"] = date.ToString("yyyy-MM-dd"),
				["@type"] = "xt:date"
			};
		}

		throw new InvalidOperationException("Unsupported type for JSON-LD serialization");
	}
}

public class Xtdb : AbstractXtdb
{
	public string Url { get; }
	public string Accept { get; }
	public string Txtimeout { get; }

	public Xtdb(string url = "http://localhost:3000", string accept = "application/json", string txtimeout = null)
	{
		Url = url;
		Accept = accept;
		Txtimeout = txtimeout;
	}

	public List<Dictionary<string, object>> SqlQuery(string q, List<object> p = null, bool m = false, string accept = null, bool? explain = null, long? basisAtTxId = null, DateTime? basisAtSystemTime = null)
	{
		accept ??= Accept;
		var headers = new WebHeaderCollection
			{
				{ "Accept", accept },
				{ "Content-Type", "application/json" }
			};

		//var argsValue = p == null ? new List<object>() : JsonSerializer.Serialize(p, new JsonSerializerOptions { Converters = { new CustomConverter(this) } });

		var payload = new Dictionary<string, object>
		{
			["sql"] = q,
			["queryOpts"] = new Dictionary<string, object>
			{
				["txTimeout"] = Txtimeout,
				["keyFn"] = "SNAKE_CASE_STRING",
				["explain"] = explain,
				["basis"] = new Dictionary<string, object>
				{
					["currentTime"] = basisAtSystemTime?.ToString("o"),
					["atTx"] = new Dictionary<string, object>
					{
						["txId"] = basisAtTxId,
						["systemTime"] = basisAtSystemTime?.ToString("o")
					}
				}
			}
		};

		RemoveNoneAndEmptyDictValues(payload);

		var data = Newtonsoft.Json.JsonConvert.SerializeObject(payload);
		using var webClient = new WebClient { Headers = headers };
		var response = webClient.UploadData(new Uri(Url + "/query"), "POST", Encoding.UTF8.GetBytes(data));
		var responseBody = Encoding.UTF8.GetString(response);

		 try
        {
            var array = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(responseBody);
            return array;
        }
        catch (JsonException ex)
        {
            Console.WriteLine($"JSON Deserialization error: {ex.Message}");
            return null;
        }
	}

	public List<Dictionary<string, object>> SqlTx(string q, List<object> p = null, bool m = false, string accept = null, DateTime? importSystemTime = null)
	{
		accept ??= Accept;
		var headers = new WebHeaderCollection
			{
				{ "Accept", accept },
				{ "Content-Type", "application/json" }
			};

		//var argsValue = p == null ? new List<object>() : JsonSerializer.Serialize(p, new JsonSerializerOptions { Converters = { new CustomConverter(this) } });

		var payload = new Dictionary<string, object>
		{
			["txOps"] = new List<Dictionary<string, string>> { new Dictionary<string, string> { ["sql"] = q[..^1] } },
			["opts"] = new Dictionary<string, object> { ["systemTime"] = importSystemTime?.ToString("o") }
		};

		RemoveNoneAndEmptyDictValues(payload);

		var data = JsonConvert.SerializeObject(payload);
		using var webClient = new WebClient { Headers = headers };
		var response = webClient.UploadData(new Uri(Url + "/tx"), "POST", Encoding.UTF8.GetBytes(data));
		var responseBody = Encoding.UTF8.GetString(response);

		return JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(responseBody);
	}

	public List<Dictionary<string, object>> SqlStatus(string accept = null)
	{
		accept ??= Accept;
		var headers = new WebHeaderCollection
			{
				{ "Accept", accept }
			};

		using var webClient = new WebClient { Headers = headers };
		var response = webClient.DownloadData(new Uri(Url + "/status"));
		var responseBody = Encoding.UTF8.GetString(response);

		return JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(responseBody);
	}
}

private class XtdbConsole
{
	private readonly Xtdb _xtdb;

	public XtdbConsole(string url)
	{
		_xtdb = new Xtdb(url);
	}

	public void CmdLoop()
	{
		while (true)
		{
			Console.Write("xtdb> ");
			var input = Console.ReadLine();
			if (input == "exit")
			{
				break;
			}

			try
			{
				HandleCommand(input);
			}
			catch (Exception ex)
			{
				Console.WriteLine($"Error: {ex.Message}");
			}
		}
	}

	private void HandleCommand(string input)
	{
		var parts = input.Split(' ');
		var command = parts[0].ToLower();
		var args = parts.Length > 1 ? string.Join(' ', parts[1..]) : string.Empty;

		switch (command)
		{
			case "status":
				var status = _xtdb.SqlStatus();

				break;
			case "query":
				var query = args;
				var result = _xtdb.SqlQuery(query);
			
				break;
			case "tx":
				var txQuery = args;
				var txResult = _xtdb.SqlTx(txQuery);
				
				break;
			default:
				Console.WriteLine($"Unknown command: {command}");
				break;
		}
	}
}

public static Dictionary<TValue, TKey> TransposeDictionary<TKey, TValue>(Dictionary<TKey, TValue> originalDictionary)
{
	var transposedDictionary = new Dictionary<TValue, TKey>();

	foreach (var kvp in originalDictionary)
	{
		// Check if the value already exists as a key in the transposed dictionary
		if (transposedDictionary.ContainsKey(kvp.Value))
		{
			throw new ArgumentException("The dictionary cannot be transposed because it contains duplicate values.");
		}

		transposedDictionary[kvp.Value] = kvp.Key;
	}

	return transposedDictionary;
}

public static DataTable ConvertDictionaryListToDataTable(List<Dictionary<string, object>> dictionaryList)
{
	DataTable dataTable = new DataTable();
	// Add columns to the DataTable
	foreach (var dictionary in dictionaryList)
	{
		foreach (var key in dictionary.Keys)
		{
			if (!dataTable.Columns.Contains(key))
			{
				dataTable.Columns.Add(key, typeof(object));
			}
		}
	}
	// Add rows to the DataTable
	foreach (var dictionary in dictionaryList)
	{
		DataRow dataRow = dataTable.NewRow();
		foreach (var kvp in dictionary)
		{
			dataRow[kvp.Key] = kvp.Value;
		}
		dataTable.Rows.Add(dataRow);
	}
	return dataTable;
}

private static DateTime ParseIsoDatetime(string dateString)
{
	dateString = dateString.Replace('T', ' ');

	string[] datetimeFormats =
	{
			"yyyy-MM-dd HH:mm:ss.ffffffzzz", // Full timestamp with microseconds and timezone
            "yyyy-MM-dd HH:mm:ss.ffffff'Z'", // Timestamp without timezone Z
            "yyyy-MM-dd HH:mm:ss.ffffff",    // Timestamp without timezone
            "yyyy-MM-dd HH:mm:ss'Z'",        // Timestamp without microseconds Z
            "yyyy-MM-dd HH:mm:ss",           // Timestamp without microseconds
            "yyyy-MM-dd HH:mm'Z'",           // Without seconds Z
            "yyyy-MM-dd HH:mm",              // Without seconds
            "yyyy-MM-dd HH'Z'",              // Only date and hour Z
            "yyyy-MM-dd HH",                 // Only date and hour
            "yyyy-MM-dd",                    // Only date
            "yyyy-MM",                       // Only year and month
            "yyyy"                           // Only year
        };

	foreach (var format in datetimeFormats)
	{
		if (DateTime.TryParseExact(dateString, format, null, DateTimeStyles.None, out var result))
		{
			return result;
		}
	}

	return DateTime.MaxValue;
}

private static void RemoveNoneAndEmptyDictValues(Dictionary<string, object> d)
{
	List<string> keysToDelete = new();
	foreach (var kvp in d)
	{
		if (kvp.Value == null || (kvp.Value is Dictionary<string, object> dict && dict.Count == 0))
		{
			keysToDelete.Add(kvp.Key);
		}
	}

	foreach (var key in keysToDelete)
	{
		d.Remove(key);
	}

	List<string> subKeysToDelete = new();
	foreach (var kvp in d)
	{
		if (kvp.Value is Dictionary<string, object> dict)
		{
			RemoveNoneAndEmptyDictValues(dict);
			if (dict.Count == 0)
			{
				subKeysToDelete.Add(kvp.Key);
			}
		}
	}

	foreach (var key in subKeysToDelete)
	{
		d.Remove(key);
	}
}

USE:

var x = new Xtdb("http://localhost:6543");
var dic = x.SqlQuery("SELECT _id, price FROM contracts");

var data = ConvertDictionaryListToDataTable(dic).Dump("Data");

data.Select("price > 101").Select(d =>d.Field<long>(1)).ToList().Dump("sql");