Either UserType 19 or 36 is required for 'text', 'text_locator' or 'unitext' typed input parameter in the stored proc

[ngvtien]

Describe the bug If neither UserType36 nor 19 is set for when the byte count for the text value is greater than 16384 then we will get an exception thrown The token datastream length was not correct. This is an internal protocol error.

To Reproduce Please provide C#/SQL necessary to reproduce the issue, and steps to follow: SQL:

create procedure [dbo].[sp_test_text_type_coloumn]
  @text_locator_input text_locator,
  @text_input text,
  @output int output
as
begin
  set @output = 10
end

C#

public void TextTypeColumn_Procedure_Failed_When_Incorrect_UserType_Set()
{
    using (var connection = new AseConnection(ConnectionStrings.Pooled))
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "sp_test_text_type_coloumn";
            command.CommandType = CommandType.StoredProcedure;

            var text_locator = new string('x', 16384);
            var just_text = new string('y', 1024);

            var p = command.CreateParameter();
            p.ParameterName = "@text_locator_input";
            p.Value = text_locator;
            p.DbType = DbType.String;
            command.Parameters.Add(p);

            p = command.CreateParameter();
            p.ParameterName = "@text_input";
            p.Value = just_text;
            p.DbType = DbType.String;
            command.Parameters.Add(p);

            var pOut = command.CreateParameter();
            pOut.ParameterName = "@output";
            pOut.Value = DBNull.Value;
            pOut.DbType = DbType.Int32;
            pOut.Direction = ParameterDirection.Output;
            command.Parameters.Add(pOut);

            command.ExecuteNonQuery();
        }
    }
}

If you break into the code and set UserTypefor the 1st parameter to either 19 or 36 then it would work but not when 35 is set

Expected behavior It shouldn't throw any exception.

Environment

• .NET Framework or Core version (all versions)
• AdoNetCore.AseClient nuget package version 0.18.0

Additional context One of the work-around for me at this stage is to allow AseParameter class to have an additional internal property to over write the user type and that can be set at the time when the parameter is created. eg something like this

var p = command.CreateParameter();
p.ParameterName = "@text_locator_input";
p.Value = text_locator;
p.DbType = DbType.String;
p.OverrideUserType = 36; //19;
command.Parameters.Add(p);

and when it comes to creating the FormatItem we would check for any previous value set like so

var format = new FormatItem
{
    ParameterName = parameter.ParameterName,
    IsOutput = parameter.IsOutput,
    IsNullable = parameter.IsNullable,
    Length = length,
    DataType = TypeMap.GetTdsDataType(dbType, parameter.SendableValue, length, parameter.ParameterName),
    UserType = parameter.OverrideUserType ?? TypeMap.GetTdsUserType(dbType)
};

[formicas]

@ngvtien the workaround breaks the drop-in replacement paradigm. GetTdsUserType is missing the full context to work properly. It should probably be based on the output of GetTdsDataType, not just the DbType

[ngvtien]

Actually, with the recent commit I've addressed this issue already, similar to to how we're dealing with UserType 34 and 35