Add Json to System.Data.DbType

[roji]

With more and more databases supporting JSON types natively, we should add Json to the DbType enum.

/cc @divega @ajcvickers

[saurabh500]

@roji I am trying to understand how would a driver in ADO.net which already has support for JSON, work when DbType.Json is added? Existing drivers likely, already the CLR native type to some DbType What would be the guidance or path forward for them ?

[roji]

On the write side, for Npgsql for example which already has NpgslqDbType.Jsonb (and NpgsqlDbType.Json), DbType.Json would simply be supported as an additional, provider-agnostic way to specify that a parameter should be sent as the JSON type (probably jsonb).

Existing drivers likely, already the CLR native type to some DbType

Here I'm assuming you're referring to what happens when the user reads DbType after having set DbParameter.Value, right?

So first, the primary CLR type mapped to JSON is generally string, and reading DbType after setting Value to a string should already return DbType.String (rather than anything related to JSON), so for this common scenario I don't think there's a problem.

But there's indeed the case where the ADO.NET driver also supports JSON-specific types; for example, Npgsql allows you to read and write System.Text.Json types directly (e.g. JsonDocument), as a sugar to remove the burden of having to serialize/deserialize to string internally (note that the PG wire encoding for json/jsonb is just text, no internal binary format). If you assign e.g. JsonDocument to NpgsqlParameter.Value and then read DbType, you get object at the moment, since there's nothing else in that enum that fits. Changing that to return the new DbType.Json would indeed be a breaking change, but it's a minor one that I think we'd definitely do it in Npgsql. Of course, that decision is up to each and every provider.

What do you think, does that make sense (hopefully I understood your question)?