How to populate a table valued parameter using a custom DbDataReader?

Question

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

I wrote the following code which populate a table valued paremeter using IEnumerable<SqlDataRecord> objects :

static void Main()
{       
    List<int> idsToSend = ...
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();    
        SqlCommand command = new SqlCommand(@"SELECT ...
                                              FROM ... 
                                              INNER JOIN @ids mytable ON ...", connection);

        command.Parameters.Add(new SqlParameter("@ids", SqlDbType.Structured)
        {
            TypeName = "int_list_type",
            Direction = ParameterDirection.Input,
            Value = GetSqlDataRecords(idsToSend) //returns IEnumerable<SqlDataRecord> 
        });

        SqlDataReader reader = command.ExecuteReader();
        //consume reader...
    }
}

It works perfectly.

The MSDN page says:

You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter.

I would like to use a custom DbDataReader instead to pass the row data. Something like this:

public class CustomDataReader : DbDataReader
{
    public CustomDataReader(IEnumerable<int> values)
    {           
    }

    //implementation of other abstract methods and fields required by DbDataReader
    //...
}

I have changed the following line in the initial code example :

Value = GetSqlDataRecords(idsToSend)
=>
Value = new CustomDataReader(idsToSend)

If I run the code, it give me the following exception during execution of command.ExecuteReader() :

An unhandled exception of type 'System.NotSupportedException' occurred in System.Data.dll Additional information: Specified method is not supported.

I have put a breakpoint on ALL methods and properties of my CustomDbDataReader class : none of them are called before I got the exception so its probably not related to the implementation of the DbDataReader

EDIT : here is the stack trace, as requested :

   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, 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 asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at ConsoleApplication211.Program.Main() in Program.cs:line 51
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

Can you edit the question to show the stack trace of the exception — stuartd
– stuartd, Commented Mar 20, 2017 at 16:31
The only difference I can see is that it use a stored procedure, is this a requirement to use a DbDataReader ? — tigrou
– tigrou, Commented Mar 20, 2017 at 16:48

Lucero · Accepted Answer · 2017-03-20 16:50:36Z

5

You need to implement GetSchemaTable, which is not marked abstract.

See also: How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C# .NET

answered Mar 20, 2017 at 16:50

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to populate a table valued parameter using a custom DbDataReader?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related