How do I specify to use server defined values in a Table Valued Parameter when my source is a DataReader?

Question

My TVP looks like this::

create type p_tvp as table
(
   id int identity not null,
   value float not null
)

I have some datareader that yields a list of double (not actual SQL executed):

 using (var conn = new SqlConnection(_srcString)
 using (var cmd = new SqlCommand("select * from (values(1e),(2e),(3e))f(v)", conn))
 {
       await conn.OpenAsync();

       using(var rdr = await _cmd.ExecuteReaderAsync())
       using(var tCon = new SqlConnection(_targetString))
       using(var tcmd = new SqlCommand("MyStoredProcedure",tCon))
       {
         await tCon.OpenAsync();
         tcmd.CommandType = CommandType.StoreProcedure;
         var param = tcmd.Parameters.AddWithValue(@tvp, rdr);
         param.SqlDbType = SqlDbType.Structured;
         param.TypeName = "dbo.p_tpv";
         await tcmd.ExecuteNonQuery();
       }
 }

The error I get is that I don't have enough parameters. As the TVP I'm targeting has 2 columns. If I add a dummy column, the error I get is that I can't identity insert into table valued parameters.

I cannot redefine the TVP.

The other question specifies a DataTable as the source, the answer uses a IEnumerable<SqlDataRecord>. Unfortunately, neither solves my case of having a DbDataReader. I can convert to SqlDataRecord if necessary, but I'd like to avoid the extra steps as I cannot easily control the DbDataReader source.

You can use a reader though as well to enable streaming. If you use a datatable you have to materialize the dataset. My actual source database isn't Sql Server. — Michael B, Apr 23 '19 at 16:10
Yeah I do that. This again isn't my actual code, but I'll update my example. — Michael B, Apr 23 '19 at 19:20
Possible duplicate of [Identity column in a table-valued parameter in procedure, how to define DataTable](https://stackoverflow.com/questions/3210945/identity-column-in-a-table-valued-parameter-in-procedure-how-to-define-datatabl) — Fabio, Apr 23 '19 at 19:53
Again, that's for a datatable (the actual answer implements via an `IEnumerable`. I have a `DbDataReader`. — Michael B, Apr 23 '19 at 20:11
You cannot do this with DbDataReader, instead you can "stream" DbDataReader into `IEnumerable`. With `IEnumerable` you will enumerate rows one at the time, without materialising whole data. — Fabio, Apr 23 '19 at 20:42

Solomon Rutzky · Answer 1 · 2019-05-10T02:27:37.423

As Fabio noted in a comment on the question, you can use IEnumerable<SqlDataRecord> to set up a custom data structure via SqlDataRecord and then stream the results from the SqlDataReader into the TVP.

In the following example, the "id" column of the TVP uses the SqlMetaData constructor that allows for setting useServerDefault to true. The yield break should safely exit if no rows were returned from the original query.

private IEnumerable<SqlDataRecord> SendRowsToProc(SqlDataReader reader)
{
  if (!reader.HasRows)
  {
    yield break;
  }

  SqlDataRecord resultRow = new SqlDataRecord(new SqlMetaData[] {
             new SqlMetaData("id", SqlDbType.Int, true, true, SortOrder.Unspecified, 0),
             new SqlMetaData("value", SqlDbType.Float)
                                                    });

  while (reader.Read())
  {
    resultRow.SetDouble(1, reader.GetDouble(0));

    yield return resultRow;
  }
}

And so, rather than passing in rdr as the "value" of the TVP SqlParameter, you pass in: SendRowsToProc(rdr).

Another option to consider, if you really want to pass rdr in as the TVP value:

Use a different UDTT, one that has the same ID INT NOT NULL column, but that is not marked as being an IDENTITY
In your SELECT statement (at the source), start the column list with:
```
ROW_NUMBER() OVER (ORDER BY @@MICROSOFTVERSION) AS [ID]
```

That should provide the same functionality that the IDENTITY column is currently providing. Of course, it won't work if you are executing a stored procedure, but for the case given in the question, it should be fine.

Yeah, I went down this road. Unfortunately, this felt like a kind of ugly hack that I had to do. Also as an aside, for stylistic purposes, locals and parameters are specified in camelCase. PascalCase is reserved for classes and public members and the `_` usually specifies a private member. — Michael B, May 10 '19 at 01:19
@MichaelB Not sure that I agree with it being _that_ hacky. Maybe slightly, but not that bad. Maybe there is a way, using Reflection, to modify the internal `SqlDataRecord` / `DbDataRecord` being used by the `SqlDataReader`, so that it's definition has `useServerDefault` set to `true` :-). Either way, I added a long-shot second option to my answer. Just something to consider :-). — Solomon Rutzky, May 10 '19 at 02:31

How do I specify to use server defined values in a Table Valued Parameter when my source is a DataReader?

1 Answers1