Unable to pass an empty table-valued parameter through Dapper

[burns47]

The following code will throw an exception on 1.50.2.0, with the message "to send a table-valued parameter with no rows, use a null reference for the value instead."

Connection.Execute("TestTVP", new { Table = Enumerable.Empty<SqlDataRecord>() }, 
        transaction: Transaction, commandType: CommandType.StoredProcedure);

Following that advice, I used this code and got this message: "DBNull value for parameter 'Table' is not supported. Table valued parameters cannot be DBNull"

Connection.Execute("TestTVP", new { Table = null as IEnumerable<SqlDataRecord> }, 
        transaction: Transaction, commandType: CommandType.StoredProcedure);

I know this is probably SqlClient's fault, but shouldn't dapper handle this?

[NickCraver]

We don't use TVPs much so I'm not sure exactly where this is thrown - we'll have to dig. Can you get some more info for when we're able to do this? Are you using the full SQL client, on full framework, against SQL Server? That'll save some time.

[burns47]

No problem -- I'm targeting .NET 4.5.2 in a class library (so I guess full SqlClient and full framework), and I'm running against SQL Server 2016 LocalDB (maybe that's it? I could've sworn this has worked in the past). For reference, here's the stack trace when you run with an empty IEnumerable:

System.ArgumentException was unhandled by user code
  HResult=-2147024809
  Message=There are no records in the SqlDataRecord enumeration. To send a table-valued parameter with no rows, use a null reference for the value instead.
  Source=System.Data
  StackTrace:
       at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, 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, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)

Here's the stack trace for when the TVP is null:

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=DBNull value for parameter 'Table' is not supported. Table-valued parameters cannot be DBNull.
  Source=System.Data
  StackTrace:
       at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
       at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
       at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
       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, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)
       at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
       at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)

[bmeredith]

This is something I'm encountering as well.

If I try to pass an empty IEnumerable<SqlDataRecord> as a table-valued parameter, it throws the same exception. This occurs whether it is passed in as a parameter through an anonymous object like above. Or if it is passed in using the AsTableValuedParameter() method.