User Defined Tables (UDT) Service API

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string Table = "udt_custom_orders";               // UDT table name
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// Insert a row
var payload = new
{
    table = Table,
    rows = new object[]
    {
        new
        {
            columns = new object[]
            {
                new { name = "order_ref", value = "ORD-2026-001" },
                new { name = "customer_name", value = "ACME Supply Company" },
                new { name = "order_total", value = "1250.00" },
                new { name = "status", value = "pending" },
            },
            conditions = Array.Empty<object>(),
        },
    },
};

var resp = await client.PostAsync(
    $"{BaseUrl}/udtservice/api/udtdata/insertudtdata",
    new StringContent(JsonSerializer.Serialize(payload), Encoding.UTF8, "application/json"));
var result = JsonDocument.Parse(await resp.Content.ReadAsStringAsync()).RootElement;

if (result.GetProperty("errorNo").GetInt32() == 0)
{
    Console.WriteLine($"Insert: {result.GetProperty("errorMessage").GetString()}");
}
else
{
    Console.WriteLine(
        $"Error {result.GetProperty("errorNo").GetInt32()}: {result.GetProperty("errorMessage").GetString()}");
}

// Read-back — errorNo == 0 does not prove the row landed; confirm via OData.
var checkResp = await client.GetAsync(
    $"{BaseUrl}/odataservice/odata/table/{Table}?$filter=order_ref eq 'ORD-2026-001'");
checkResp.EnsureSuccessStatusCode();
var checkResult = JsonDocument.Parse(await checkResp.Content.ReadAsStringAsync()).RootElement;
Console.WriteLine(
    $"Read-back: {checkResult.GetProperty("value").GetArrayLength()} row(s) found for order_ref=ORD-2026-001");

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string Table = "udt_custom_orders";               // UDT table name
const string UidColumn = "udt_custom_orders_uid";       // the table's primary key: udt_{tablename}_uid
const int RowUid = 12345;                               // that key's value for the row to update
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

var payload = new
{
    table = Table,
    rows = new object[]
    {
        new
        {
            columns = new object[]
            {
                new { name = "status", value = "completed" },
                new { name = "order_total", value = "1300.00" },
            },
            // rowuid takes the primary-key value; a name/value pair is ignored.
            conditions = new object[]
            {
                new { rowuid = RowUid },
            },
        },
    },
};

var resp = await client.PutAsync(
    $"{BaseUrl}/udtservice/api/udtdata/updateudtdata",
    new StringContent(JsonSerializer.Serialize(payload), Encoding.UTF8, "application/json"));
var body = await resp.Content.ReadAsStringAsync();
var result = JsonDocument.Parse(body).RootElement;

// Read the [N] count: errorNo is 0 even when no row matched.
if (resp.IsSuccessStatusCode && result.TryGetProperty("errorNo", out var errorNo) && errorNo.GetInt32() == 0)
{
    Console.WriteLine($"Update: {result.GetProperty("errorMessage").GetString()}");
}
else
{
    Console.WriteLine($"Error (HTTP {(int)resp.StatusCode}): {body}");
}

// Read-back — confirm the new values actually landed via OData.
var checkResp = await client.GetAsync(
    $"{BaseUrl}/odataservice/odata/table/{Table}?$filter={UidColumn} eq {RowUid}");
checkResp.EnsureSuccessStatusCode();
var checkResult = JsonDocument.Parse(await checkResp.Content.ReadAsStringAsync()).RootElement;
var checkRows = checkResult.GetProperty("value");
if (checkRows.GetArrayLength() > 0)
{
    var row = checkRows[0];
    Console.WriteLine(
        $"Read-back: status={row.GetProperty("status").GetString()}, order_total={row.GetProperty("order_total").GetString()}");
}
else
{
    Console.WriteLine($"Read-back: no row with {UidColumn} = {RowUid}");
}

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string Table = "udt_custom_orders";               // UDT table name
const string UidColumn = "udt_custom_orders_uid";       // the table's primary key: udt_{tablename}_uid
const int RowUid = 12345;                               // that key's value for the row to delete
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// conditions is TOP-LEVEL for delete, and rowuid takes the primary-key value.
var payload = new
{
    table = Table,
    conditions = new object[]
    {
        new { rowuid = RowUid },
    },
};

var request = new HttpRequestMessage(
    HttpMethod.Delete, $"{BaseUrl}/udtservice/api/udtdata/deleteudtdata")
{
    Content = new StringContent(JsonSerializer.Serialize(payload), Encoding.UTF8, "application/json"),
};
var resp = await client.SendAsync(request);
var body = await resp.Content.ReadAsStringAsync();
var result = JsonDocument.Parse(body).RootElement;

// Check the row count in errorMessage — errorNo == 0 lies when 0 rows were deleted.
if (resp.IsSuccessStatusCode && result.TryGetProperty("errorNo", out var errorNo) && errorNo.GetInt32() == 0)
{
    Console.WriteLine($"Delete: {result.GetProperty("errorMessage").GetString()}");
}
else
{
    Console.WriteLine($"Error (HTTP {(int)resp.StatusCode}): {body}");
}

// Read-back — confirm the row is actually gone via OData.
var checkResp = await client.GetAsync(
    $"{BaseUrl}/odataservice/odata/table/{Table}?$filter={UidColumn} eq {RowUid}");
checkResp.EnsureSuccessStatusCode();
var checkResult = JsonDocument.Parse(await checkResp.Content.ReadAsStringAsync()).RootElement;
Console.WriteLine(
    $"Read-back: {checkResult.GetProperty("value").GetArrayLength()} row(s) remain with {UidColumn} = {RowUid}");

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string Table = "udt_custom_orders";               // UDT table name
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(5) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

var rows = new List<Dictionary<string, string>>
{
    new() { ["order_code"] = "A1", ["description"] = "First order", ["qty"] = "1.5" },
    new() { ["order_code"] = "A2", ["description"] = "Second order", ["qty"] = "2.5" },
};

// Usage — count before and after, because "isSuccessful" does not mean "inserted"
var before = await RowCountAsync(client, Table);
var message = await BulkUploadAsync(client, Table, rows);
var after = await RowCountAsync(client, Table);
Console.WriteLine($"{message} — rows inserted: {after - before}");

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Bulk-inserts rows into a UDT from an in-memory CSV, built by hand (no CSV package —
// this repo's docs examples run with zero package installs). The header row is
// mandatory — without it the service returns success and inserts nothing. Column
// names are case-sensitive and must match the UDT.
static async Task<string> BulkUploadAsync(
    HttpClient client, string table, IReadOnlyList<Dictionary<string, string>> rows)
{
    if (rows.Count == 0) throw new ArgumentException("no rows to upload", nameof(rows));

    var columns = rows[0].Keys.ToList();
    var csv = new StringBuilder();
    csv.AppendLine(string.Join(",", columns));   // REQUIRED — omitting it silently inserts 0 rows
    foreach (var row in rows)
        csv.AppendLine(string.Join(",", columns.Select(c => row[c])));

    using var content = new MultipartFormDataContent();
    var file = new ByteArrayContent(Encoding.UTF8.GetBytes(csv.ToString()));
    file.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
    // The form field MUST be named "file" — any other name is rejected.
    content.Add(file, "file", "upload.csv");

    var response = await client.PostAsync($"{BaseUrl}/udtservice/api/bulkupload/{table}", content);
    response.EnsureSuccessStatusCode();
    return await response.Content.ReadAsStringAsync();
}

// Read-back — the upload response carries no row count.
static async Task<int> RowCountAsync(HttpClient client, string table)
{
    var response = await client.GetAsync(
        $"{BaseUrl}/odataservice/odata/table/{table}?$count=true&$top=0");
    response.EnsureSuccessStatusCode();
    var json = JsonDocument.Parse(await response.Content.ReadAsStringAsync()).RootElement;
    return json.TryGetProperty("@odata.count", out var count) ? count.GetInt32() : 0;
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string Table = "udt_custom_orders";               // UDT table name
const string UidColumn = "udt_custom_orders_uid";       // the table's primary key: udt_{tablename}_uid
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

// Query UDT data via OData
var resp = await client.GetAsync(
    $"{BaseUrl}/odataservice/odata/table/{Table}?$filter=status eq 'pending'");
resp.EnsureSuccessStatusCode();
var data = JsonDocument.Parse(await resp.Content.ReadAsStringAsync()).RootElement;

foreach (var row in data.GetProperty("value").EnumerateArray())
{
    Console.WriteLine($"Order: {row.GetProperty("order_ref")}, Total: {row.GetProperty("order_total")}");
    // The primary key is the rowuid value for update and delete.
    Console.WriteLine($"  {UidColumn}: {row.GetProperty(UidColumn)}");
}

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}

using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;

// ---- EDIT THESE -----------------------------------------------------------
const string BaseUrl = "https://play.example.com";   // your P21 server
const string Username = "apiuser";
const string Password = "your-password";
const string Table = "udt_custom_orders";               // UDT table name
// ---------------------------------------------------------------------------

var handler = new HttpClientHandler
{
    // Test tenants often present a self-signed cert. Delete this line in production.
    ServerCertificateCustomValidationCallback =
        HttpClientHandler.DangerousAcceptAnyServerCertificateValidator,
};
using var client = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(2) };
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

var token = await GetTokenAsync(client);
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);

var payload = new
{
    table = Table,
    rows = new object[]
    {
        new
        {
            columns = new object[]
            {
                new { name = "order_ref", value = "ORD-2026-002" },
                new { name = "customer_name", value = "ACME Manufacturing" },
                new { name = "order_total", value = "875.50" },
                new { name = "status", value = "pending" },
            },
            conditions = Array.Empty<object>(),
        },
    },
};

var resp = await client.PostAsync(
    $"{BaseUrl}/udtservice/api/udtdata/insertudtdata",
    new StringContent(JsonSerializer.Serialize(payload), Encoding.UTF8, "application/json"));
var result = JsonDocument.Parse(await resp.Content.ReadAsStringAsync()).RootElement;

if (result.GetProperty("errorNo").GetInt32() == 0)
{
    // Success — errorMessage contains the success description
    Console.WriteLine($"OK: {result.GetProperty("errorMessage").GetString()}");
}
else
{
    // Error — errorNo is non-zero
    Console.WriteLine(
        $"Failed (code {result.GetProperty("errorNo").GetInt32()}): {result.GetProperty("errorMessage").GetString()}");
    if (result.TryGetProperty("sqlErrorMessage", out var sqlErr) && sqlErr.ValueKind == JsonValueKind.String)
    {
        Console.WriteLine($"SQL Error: {sqlErr.GetString()}");
    }
}

// --- helpers ---------------------------------------------------------------

// v2 token endpoint — credentials go in the body, never in headers.
static async Task<string> GetTokenAsync(HttpClient client)
{
    var payload = JsonSerializer.Serialize(new { username = Username, password = Password });
    var response = await client.PostAsync(
        $"{BaseUrl}/api/security/token/v2",
        new StringContent(payload, Encoding.UTF8, "application/json"));
    response.EnsureSuccessStatusCode();
    return ReadField(await response.Content.ReadAsStringAsync(), "AccessToken");
}

// Some middleware answers this endpoint in XML even when asked for JSON.
static string ReadField(string payload, string field)
{
    try
    {
        var value = JsonDocument.Parse(payload).RootElement.GetProperty(field).GetString();
        if (!string.IsNullOrEmpty(value)) return value;
    }
    catch (Exception ex) when (ex is JsonException or KeyNotFoundException) { }

    var match = System.Text.RegularExpressions.Regex.Match(payload, $"<{field}>([^<]+)</{field}>");
    if (!match.Success)
        throw new InvalidOperationException(
            $"No {field} in response: {payload[..Math.Min(200, payload.Length)]}");
    return match.Groups[1].Value;
}