Schema Collections

The Schema collections return metadata for the Advanced SQL Engine objects. The notable metadata columns for the JSON data type are:

Schema Table

The TdDataReader.SchemaTable returns result set metadata. The notable metadata columns for the Clob data type are:

Configuring Parameters

The following example shows how to configure a Json parameter using DbType type declaration, Size property and a BCL value. The value is set to a System.String instance.

TdParameter biography = new TdParameter();
biography.ParameterName = "Name";

biography.DbType = DbType.String;
biography.Size = 64001;

biography.Value = "{\"First\": \"John\", \"Last\": \"Smith\"}";

Note
The Data Provider maps DbType.String to the SQL Engine VarChar Data Type when Size is less than or equal to 64,000 characters. The Data Provider maps DbType.String to the SQL Engine CLOB Data Type when Size is greater than 64,000.

The following example shows how to configure a Json parameter using TdType type declaration and a BCL value.

TdParameter biography = new TdParameter();
biography.ParameterName = "Name";

biography.TdType = TdType.Json;

biography.Value = "{\"First\": \"John\", \"Last\": \"Smith\"}";

The TdParameter.Value should be set to an Array of Characters (Char[]), a String object or an instance of System.IO.TextReader.

Set TdParameter.Size Property for InputOutput or Output Parameters

The TdParameter.Size property must be set to a number greater than Zero for InputOutput or Output Parameters. The Data Provider will throw an exception when TdParameter.Direction property is set to ParameterDirection.Output or ParameterDirection.InputOutput and TdParameter.Size property is set to Zero. The TdParameter.Size property specifies the maximum number of Unicode Characters the Data Provider will send to the SQL Engine and/or receive from the SQL Engine. We recommend to set the TdParameter.Size property to the corresponding SQL Engine object size (e.g. Column or Stored Procedure Parameter size) for Input, InputOutput or Output parameters.

Retrieving Json Data

The following methods return the column or parameter value as a System.Char[] object.

1. TdDataReader.GetChars
2. TdDataReader.GetFieldValue<Char[]>
3. TdParameter.GetValue<Char[]>

The following methods and properties return the column or parameter value as a System.String object.

1. TdDataReader.GetString
2. TdDataReader.GetValue
3. TdDataReader.GetFieldValue<String>
4. TdParameter.Value
5. TdParameter.GetValue<String>

The following methods and properties return the column or parameter value as a TdClob class.

Note
The CommandBehavior (see TdCommand.ExecuteReader(CommandBehavior)) must not include CommandBehavior.SequentialAccess.

1. TdDataReader.GetTdClob
2. TdDataReader.GetProviderSpecificValue
3. TdDataReader.GetFieldValue<TdClob>
4. TdParameter.ProviderSpecificValue
5. TdParameter.GetValue<TdClob>

The following method returns the column or parameter value as a System.TextReader object.

1. TdDataReader.GetTextReader
2. TdDataReader.GetFieldValue<TextReader>
3. TdParameter.GetValue<TextReader>

Note
The CommandBehavior.SequentialAccess can potentially result in best overall performance. However in this mode JSON data cannot be transformed to a TdClob object. The CommandBehavior.SequentialAccess reduces the overall messages between the Data Provider and the SQL Engine because LOBs are transmitted to the Data Provider in Inline-mode.

See Also

Data Type Mappings

Accessor Methods for Retrieving Data

Configuring Parameters and Parameter Data Types

Large Object (LOB) Data Types