Stream records to a TVP

TVP.cs

/*****
CREATE TYPE [dbo].[BigintCollection] AS TABLE(
	[Value] [bigint] NOT NULL,
	PRIMARY KEY CLUSTERED 
(
	[Value] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)


ALTER PROCEDURE [dbo].[yourSpName] 
	-- Add the parameters for the stored procedure here
	@inputIds dbo.BigintCollection READONLY
AS
-- STUFF
******/

private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<long> ids)
{
	// http://stackoverflow.com/a/10779567

	// the enumerable is not unrolled until just when the data is sent to SQL server
	// so we can load millions of records with no memory penalty
	SqlMetaData[] metaData = new SqlMetaData[1];
	metaData[0] = new SqlMetaData("[Value]", SqlDbType.BigInt);
	SqlDataRecord record = new SqlDataRecord(metaData);
	foreach (long id in ids)
	{
		record.SetInt64(0, id);
		yield return record;
	}
}

public static List<DataType> lookupInfo(IEnumerable<long> inputIds)
{
	var results = new List<DataType>();

	try
	{
		using (SqlConnection conn = /* stuff */)
		{
			SqlCommand cmd = new SqlCommand("yourSpName", conn);
			cmd.CommandType = System.Data.CommandType.StoredProcedure;
			cmd.CommandTimeout = 1000;

			var parameter = cmd.Parameters.AddWithValue("@inputIds", CreateSqlDataRecords(inputIds));
			parameter.SqlDbType = SqlDbType.Structured;
			parameter.TypeName = "dbo.BigintCollection";

			SqlDataReader rdr = cmd.ExecuteReader();

			while (rdr.Read())
			{
				results.Add(new DataType {
					/* stuff */
				});
			}
		}
	}
	catch (Exception e)
	{
		/* log */
		throw;
	}


	return results;
}