How to pass a table-value parameter

Question

I am trying to pass a table-value parameter to a stored procedure, but I keep getting an exception (see below).

SqlCommand c = new SqlCommand("getPermittedUsers", myConn) { CommandType = CommandType.StoredProcedure };  c.Parameters.AddWithValue("@intNotifyingUserId", notifyingUserId); c.Parameters.AddWithValue("@tSelectedPdfIds", sharedPdfs).SqlDbType = SqlDbType.Structured;  SqlDataReader dr = c.ExecuteReader();

The type is defined on the server like this:

CREATE TYPE [dbo].[IdList] AS TABLE(     [Id] [int] NOT NULL )

I have tried passing sharedPdfs as a List<int>, and IQueryable<int>, but keep getting the following exception:

Object must implement IConvertible.

Anyone know what I am doing wrong? The documentation implies that I should be able to pass a list as a TVP but doesn't give any examples.

Thank you.

Answer 1

The following example illustrates using either a DataTable or an IEnumerable<SqlDataRecord>:

SQL Code

CREATE TABLE dbo.PageView (     PageViewID BIGINT NOT NULL CONSTRAINT pkPageView PRIMARY KEY CLUSTERED,     PageViewCount BIGINT NOT NULL ); CREATE TYPE dbo.PageViewTableType AS TABLE (     PageViewID BIGINT NOT NULL ); CREATE PROCEDURE dbo.procMergePageView     @Display dbo.PageViewTableType READONLY AS BEGIN     MERGE INTO dbo.PageView AS T     USING @Display AS S     ON T.PageViewID = S.PageViewID     WHEN MATCHED THEN UPDATE SET T.PageViewCount = T.PageViewCount + 1     WHEN NOT MATCHED THEN INSERT VALUES(S.PageViewID, 1); END 

C# Code

private static void ExecuteProcedure(bool useDataTable, string connectionString, IEnumerable<long> ids) {     using (SqlConnection connection = new SqlConnection(connectionString)) {         connection.Open();         using (SqlCommand command = connection.CreateCommand()) {             command.CommandText = "dbo.procMergePageView";             command.CommandType = CommandType.StoredProcedure;              SqlParameter parameter;             if (useDataTable) {                 parameter = command.Parameters.AddWithValue("@Display", CreateDataTable(ids));             }             else {                 parameter = command.Parameters.AddWithValue("@Display", CreateSqlDataRecords(ids));             }             parameter.SqlDbType = SqlDbType.Structured;             parameter.TypeName = "dbo.PageViewTableType";              command.ExecuteNonQuery();         }     } }  private static DataTable CreateDataTable(IEnumerable<long> ids) {     DataTable table = new DataTable();     table.Columns.Add("ID", typeof(long));     foreach (long id in ids) {         table.Rows.Add(id);     }     return table; }  private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<long> ids) {     SqlMetaData[] metaData = new SqlMetaData[1];     metaData[0] = new SqlMetaData("ID", SqlDbType.BigInt);     SqlDataRecord record = new SqlDataRecord(metaData);     foreach (long id in ids) {         record.SetInt64(0, id);         yield return record;     } } 

Answer 2

You can pass the parameter as a DataTable, IEnumerable<SqlDataRecord>, or DbDataReader.