当我的源是 DataReader 时，如何指定在表值参数中使用服务器定义的值?

Question

我的TVP看起来像这样：

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

我有一些数据读取器可以生成一个双精度列表(不是实际执行的 SQL)：

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();
}
}

我得到的错误是我没有足够的参数。由于我定位的 TVP 有 2 列。如果我添加一个虚拟列，我得到的错误是我无法标识插入到表值参数中。

我无法重新定义TVP。

另一个问题指定DataTable作为来源，答案使用IEnumerable<SqlDataRecord>。不幸的是，两者都不能解决我有DbDataReader的情况。如有必要，我可以转换为SqlDataRecord，但我想避免额外的步骤，因为我无法轻松控制DbDataReader源。

Answer 1

正如 Fabio 在对该问题的评论中指出的那样，您可以使用IEnumerable<SqlDataRecord>通过SqlDataRecord设置自定义数据结构，然后将结果从SqlDataReader流式传输到 TVP。

在以下示例中，TVP 的"id"列使用允许将useServerDefault设置为true的SqlMetaData构造函数。如果未从原始查询返回任何行，则yield break应安全退出。

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;
}
}

因此，与其传入rdr作为 TVPSqlParameter的"值"，不如传入：SendRowsToProc(rdr)。

---

如果您确实想将rdr作为 TVP 值传入，请考虑的另一个选项：

1. 使用不同的 UDTT，该 UDTT 具有相同的ID INT NOT NULL列，但未标记为IDENTITY

2. 在SELECT语句(在源位置)中，列列表的开头为：

   ROW_NUMBER() OVER (ORDER BY @@MICROSOFTVERSION) AS [ID]
   

这应该提供与IDENTITY列当前提供的相同功能。当然，如果您正在执行存储过程，它将不起作用，但对于问题中给出的情况，它应该没问题。