search

dotnet / SqlClient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.
MIT License
817 stars 271 forks source link

JSON datatype support in SqlClient #2622

Open saurabh500 opened 4 days ago

saurabh500 commented 4 days ago

The support for the JSON data type is in preview in Azure SQL DB https://techcommunity.microsoft.com/t5/azure-sql-blog/native-json-type-amp-json-aggregates-are-now-in-private-preview/ba-p/3830753

To enable applications to leverage JSON effectively, the SqlClient driver will require changes as well.

When it comes to the APIs of the JSON support, the fundamental APIs which are foundational are

  1. Enhancement of SqlDbType to offer an enum value for JSON type. This will be offered by SqlDbType.Json enum.
  2. Providing a SqlType like to work with Json data. The type here will be SqlJson.

System.Data.SqlDbType ships with dotnet/runtime, and changes to SqlDbType will be made in the runtime repo. There is an issue open in dotnet/runtime at https://github.com/dotnet/runtime/issues/103925

SqlJson unlike other SqlTypes will ship in Microsoft.Data.SqlClient.

Related EF issue at https://github.com/dotnet/efcore/issues/32150

APIs

The APIs that have been identified for modification are

  1. SqlDataReader.GetFieldValue<string>() -> Returns a JSON string.
  2. SqlDataReader.GetFieldValue<JsonDocument>() -> Returns a System.Text.JsonDocument.
  3. We need to support all the above Generics for SqlDataReader.GetFieldValueAsync<T>() async equivalents as well.
  4. SqlDbType.Json: This would allow the SqlParameters to be qualified as Json types.
  5. SqlDataReader.GetString(int colOrdinal): This should return a JSON string.
  6. SqlDataReader.GetFieldValue<byte[]>(int colOrdinal). The byte[] returned should be usable by Utf8JsonReader to create a JSON reader. This is the high performance scenario in .Net.
  7. SqlDataReader.GetStream() should support a TextStream which can be serialized as a JSON text. We will need to check if the COLTYPE is JSON, then we stream the data according to the payload sent by the server.
  8. SqlDataReader.GetDataTypeName(Int32) : Returns The string representing the data type of the specified column. Should return JSON in this case.
  9. SqlDataReader.GetFieldType(Int32) : Returns the ‘type’ for JSON
  10. SqlDataReader.GetSqlJson(Int32) : Returns a SqlJson that contains the JSON stored within the corresponding field.
  11. SqlParameter will not have any new APIs exposed. The behavior of existing APIs will take, so that they can work with SqlDbType and SqlJson type.

Since SqlDbType enhancement can be added at the earliest to dotnet/runtime 9.0, M.D.S for .Net runtime, will support JSON for dotnet/runtime 9.0 and above only.

The support for JSON in netfx version of M.D.S is TBD. One of the hurdles is that System.Data.SqlDbType which is available from System.Data.dll, cannot be enhanced in .Net framework.

Backward compat with server without JSON support

Backward compat of new client with old server. What happens when SqlDbType.Json/SqlJson is used with a server, which doesn't have Json support. This is pending discussions.

Technical details

  1. Json support will be enabled with a feature extension negotiation, which means that the client will handshake the feature support with the server. The feature extension is versioned and will start with v1. The Feature extension identifier is 0x0D
  2. Feature negotiation would mean that the server can send the Json specific type information in the metadata of the result set. The tdsType for JSON is planned to be 244
  3. JSON payload will be exchanged between the client and server as VARCHAR(Max) for v1 of the Feature extension negotiated.

Dependencies

  1. TDS documentation changes with the protocol changes mentioned above.
  2. dotnet/runtime getting the SqlDbType enum update.
  3. Migrate SqlClient builds to use dotnet 9 SDK.
  4. A SQL Server with the protocol changes, which can be used for testing the client changes.
saurabh500 commented 4 days ago

cc @roji @uc-msft @apoorvdeshmukh @deepaksa1 @imasud00 @David-Engel @cheenamalhotra

Wraith2 commented 4 days ago

Adding GetFieldValue(Async) adds a hard dependency on the System.Text.Json library.

roji commented 4 days ago

@Wraith2 System.Text.Json is part of .NET in modern versions, so a package reference is only needed on old TFMs. Even for those old TFMs, a system dependency such as System.Text.Json is generally not a problem.

Wraith2 commented 4 days ago

Yup, it's just missing from the dependencies list in the first post is all.

roji commented 4 days ago

@Wraith2 oh I see, thanks!