正在从表值参数c#检索数据

Question

我以前没有使用过TVP或存储过程，我希望能澄清一下如何在c#中检索与第一个查询等效的数据集。我创建了一个似乎可以工作的自定义类型和过程，每当我声明一个变量并在SSMS中执行存储过程时，它都可以工作，我只是不知道如何在代码中复制它。

SELECT ServLoc.ID, Meters.MeterID, MeterHistory.Stuff
FROM ServLoc 
INNER JOIN Meters ON ServLoc.ID = Meters.ServLocID
INNER JOIN MeterHistory ON Meters.MeterID = MeterHistory.MeterID
WHERE ServLoc.ID IN (1,2,3...40,000)

CREATE TYPE dbo.ServLocMeters 
AS TABLE (ServLocID int NOT NULL)

CREATE PROCEDURE [dbo].[sp_ServLocMeters]
@newServLocMeters ServLocMeters READONLY
AS
SELECT ServLocID, Meters.MeterID, MeterHistory.Stuff
FROM Meters 
INNER JOIN MeterHistory ON Meters.MeterID = MeterHistory.MeterID
WHERE ServLocID IN (SELECT ServLocID FROM @newServLocMeters)
ORDER BY ServLocID

declare @table as ServlocMeters
insert into @table values (1)
insert into @table values (2)
insert into @table values (31)
exec sp_servlocmeters @table

Answer 1

不久之后我发现了一个例子，哈哈。感谢marc提供的前缀heads。

DataTable table = new DataTable("ServLoc");
table.Columns.Add("ServLocID", typeof(int));
for (int i = 0; i < 3000; i++)
{
table.Rows.Add(1 + i);
}
DataSet dbDatasets = new DataSet();
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("sp_ServLocMeters", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@newServLocMeters", table);
cmd.CommandTimeout = 0;
sqlParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP  
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dbDatasets);
}