SqlCommandBuilder.DeriveParameters() incorrectly includes the database name in TypeName property values for SqlParameters for table-valued parameters

[Metritutus]

Description

When using SqlCommandBuilder.DeriveParameters() with a SqlCommand instance to execute a stored procedure with a table-valued parameter, the SqlParameter instance it produces (for the aforementioned table-valued parameter) has a TypeName property value which incorrectly includes the database name.

Attempting to execute this SqlCommand will produce an exception message explaining that it is not valid to include the database name in the type name for a table-valued parameter.

Exception details

Exception message: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 1 ("@ids"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified.  Database name is not allowed with a table-valued parameter, only schema name and type name are valid.
Stack trace:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteScalar()
   at ConsoleApp1.Program.Main(String[] args) in C:\Users\<redacted>\Documents\Visual Studio 2019\Projects\ConsoleApp1\ConsoleApp1\Program.cs:line 30

To reproduce

Create the following user-defined table type:

CREATE TYPE [IdList] AS TABLE
(
    [Id] [int] NOT NULL
)

Create the following stored procedure:

CREATE PROCEDURE [spTestTableValuedParameter](@ids [IdList] READONLY)
AS
BEGIN
    SELECT [Id], ([Id] + 1) AS [IdPlusOne]
    FROM @ids
    ORDER BY [Id] DESC
END

Create a console application with the following code (filling in the connection string as appropriate):

using Microsoft.Data.SqlClient;
using System;
using System.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "<redacted>";
            string storedProcedureName = "spTestTableValuedParameter";

            using var connection = new SqlConnection(connectionString);

            connection.Open();

            using var command = new SqlCommand(storedProcedureName, connection) { CommandType = CommandType.StoredProcedure };
            SqlCommandBuilder.DeriveParameters(command);

            var values = new DataTable();
            values.Columns.Add("Id", typeof(int));

            values.Rows.Add(1);
            values.Rows.Add(2);
            values.Rows.Add(3);

            command.Parameters["@ids"].Value = values;

            var result = command.ExecuteScalar();

            Console.WriteLine(result); // Should be 3.
        }
    }
}

Run the above console application.

Expected behavior

The stored procedure should execute without error.

Further technical details

Microsoft.Data.SqlClient version: 2.1.2
.NET target: .NET 5
SQL Server version: Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 20 2021 17:51:58
Operating system: Windows 10

Additional context
From Googling, it looks like this may be a long-standing bug dating back to System.Data.SqlClient that has never been fixed: https://stackoverflow.com/a/10035953

[Wraith2]

Odd, I thought I'd fixed this years ago in dotnet/corefx#35549

[Metritutus]

It does indeed appear to work if I use System.Data.SqlClient instead.

The problem therefore appears to exclusively be with Microsoft.Data.SqlClient.

Perhaps Microsoft.Data.SqlClient lacks the improvements made to System.Data.SqlClient in your referenced pull-request?

[Wraith2]

It looks like that PR didn't get pulled into this repo. I can port it once my outstanding changes to SqlParameter have been merged.

[cheenamalhotra]

@Wraith2

Yes it looks like got missed. Please also consider dotnet/corefx#41008 while porting over the fix.

[Wraith2]

The xml schema was one of the things i wanted to look at so yes i can work on that at the same time.