Cannot use DBDataReader as Structured SQL Parameter with 2.0 Update

[Petermarcu]

@Coder3333 commented on Thu Sep 21 2017

This article details how to use a DBDataReader as a Structured input parameter to a SQL query:

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

This worked just fine with Core 1.1, but since I updated to Core 2.0, it no longer works. If I use DataTable as input, instead of a DBDataReader, the exact same code works.

The error I get is:
"Specified method is not supported"
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

---

@nil4 commented on Sun Oct 08 2017

Confirmed, same here.

[danmoseley]

@corivera

[corivera]

CC @saurabh500

[corivera]

@Coder3333 do you have an isolated repro for this?

[nil4]

@corivera @saurabh500 isolated repro at https://gist.github.com/nil4/a30e16bae327d3271bd774749b340c1d

Running that program throws:

Unhandled Exception: System.NotSupportedException: Specified method is not supported.
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Repro.Program.Main(String[] args)

[nil4]

@corivera @saurabh500 the gist I provided above targets net47. Targeting netcoreapp2.0 and adding a NuGet reference to System.Data.SqlClient version 4.4.0, results in the same exception.

Here is the project adjusted for netcoreapp2.0:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.4.0" />
  </ItemGroup>
</Project>

[corivera]

I get the same NotSupportedException in a Framework project. This exception is coming from GetSchemaTable(), which is not implemented in DbDataReader, so this is expected. I had no issues when using a SqlDataReader as a parameter.

System.NotSupportedException: Specified method is not supported.
at System.Data.Common.DbDataReader.GetSchemaTable()
at System.Data.SqlClient.SqlParameter.GetActualFieldsAndProperties(List1& fields, SmiMetaDataPropertyCollection& props, ParameterPeekAheadValue& peekAhead) at System.Data.SqlClient.SqlParameter.MetaDataForSmi(ParameterPeekAheadValue& peekAhead) at System.Data.SqlClient.TdsParser.WriteSmiParameter(SqlParameter param, Int32 paramIndex, Boolean sendDefault, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource1 completion, Int32 startRpc, Int32 startParam)

[nil4]

That's not what the docs at https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters#configuring-a-sqlparameter-example say though:

Configuring a SqlParameter Example

System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or System.Collections.Generic.IEnumerable<SqlDataRecord> objects.

They go on to say:

You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter, as shown in this fragment:

// Configure the SqlCommand and table-valued parameter.  
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
insertCommand.CommandType = CommandType.StoredProcedure;  
SqlParameter tvpParam =  insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);  
tvpParam.SqlDbType = SqlDbType.Structured;  

[corivera]

Correct, but the DbDataReader-derived reader implementation needs to have the necessary methods implemented. If using a custom reader implementation, then GetSchemaTable() is one of the required methods that must be overridden & implemented in this case (along with many others).

[nil4]

That makes sense.

What doesn't make sense is that the stack trace I get (https://github.com/dotnet/corefx/issues/24502#issuecomment-336710134) does not have any reference to GetSchemaTable, or in fact to the DbDataReader-derived class for that matter.

In contrast, the stack trace you posted (https://github.com/dotnet/corefx/issues/24502#issuecomment-337014523) does. Had I seen the same behavior, it would have been clear that I needed to implement this method, and then potentially others that may be needed.

[Coder3333]

Can anyone explain why the dependence on GetSchemaTable was added with .Net Core 2.0? My application worked great prior to that. GetSchemaTable appears to be a rather convoluted method to implement, being that it has to return a table with 31 very specific columns. Is there a trivial way to implement GetSchemaTable that I have just not thought of or can the dependence be removed?

[corivera]

GetSchemaTable() was added back in 2.0, so references to it in Core were added back to match Framework System.Data code. This same NotSupportedException is thrown in a Framework project, so the fact that this scenario worked before was just a coincidence of the GetSchemaTable workarounds.

Framework code: https://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlParameter.cs,1270

Current code in master: https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlParameter.cs#L1309

1.1.0 code: https://github.com/dotnet/corefx/blob/release/1.1.0/src/System.Data.SqlClient/src/System/Data/SqlClient/SqlParameter.cs#L1207