Postgres JSONB Data Type Mapping using C# and Dapper [duplicate]

Question

I have a system that audits DB row add/edit/delete operations via a Postgres function with signature:

 func_audit_record(p_table_name  TEXT,
                   p_action_name TEXT,
                   p_old_row     JSONB,
                   p_new_row     JSONB,
                   p_edited_by   INTEGER)

This function has been executed directly via other Postgres functions up until this point, but now I need to execute it via C# directly.

How do I go about mapping a C# data type to the Postgres JSONB data-type while maintaining the above signature?

public async Task AuditAddAsync(string tableName, string actionName, JsonDocument oldRow, JsonDocument newRow, int appUserId)
    {
      var obj = new
      {
        p_audit_table_name = tableName,
        p_audit_action_name = actionName,
        p_old_row_state_as_text = oldRow, //JsonDocument object is too complex here
        p_new_row_state_as_text = newRow, //JsonDocument object is too complex here
        p_edited_by_user_id = appUserId,
        p_system_action = isSystemAction,
        p_notes = auditNotes
      };
      await _dapperAdapter.ExecuteAsync("func_audit_record", obj);
    }

I've tried with JsonDocument above (which seems to have the correct structure in the RootElement property), but that's too complex. Also, running JsonDocument.RootElement.ToString() throws an error since the function func_audit_record is expecting JSONB, not TEXT.

I really don't want to need to create a duplicate func_audit_record with TEXT parameters.

Ideas?

You should leverage Postgres extensions for doing data audit, instead of trying to manually do them via C# - it will be so inefficient. Look at Temporal table extension, by creating triggers you can also capture the user that made the data change or delete. — Anand Sowmithiran
– Anand Sowmithiran, Commented Jan 10, 2022 at 9:26
If you insist on making your code work and want to pass jsonb to your plpgsql function, then you can use Dapper's ICustomQueryParameter, as shown in this example — Anand Sowmithiran
– Anand Sowmithiran, Commented Jan 10, 2022 at 9:34

Amit Joshi · Accepted Answer · 2022-01-10 10:57:13Z

1

You can make your code work. To pass json to your plpgsql function, use Dapper's ICustomQueryParameter, as shown in this example

edited Jan 10, 2022 at 10:57

Amit Joshi

16.6k23 gold badges88 silver badges154 bronze badges

answered Jan 10, 2022 at 10:16

Anand Sowmithiran

2,9683 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres JSONB Data Type Mapping using C# and Dapper [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related