How to pass a table-value parameter

user_307960 0
Viewed 46851

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.

ado.net .net c#
3 Answers

Adding a new answer with updated links.

According to the documentation (docs.microsoft.com), you can use one of the following parameter types:

SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable \ SqlDataRecord objects. You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. The TypeName must match the name of a compatible type previously created on the server.

Not included in the docs but important for high-performance apps, a sample using IEnumerable<SqlDataRecord> (.NET Core 3.1, async):

  using var timeout = new CancellationTokenSource(TimeSpan.FromSeconds(30));
  using SqlConnection connection = this.GetConnection();
  await connection.OpenAsync(timeout.Token);

  using SqlCommand command = connection.CreateCommand();
  command.CommandType = CommandType.StoredProcedure;
  command.CommandText = "Mycommand";

  IEnumerable<SqlDataRecord> records = // ...

  SqlParameter parameter = command.Parameters.Add("@MyObjects", SqlDbType.Structured);
  parameter.TypeName = "MyCustomTableType";
  parameter.Value = records;

  await command.ExecuteNonQueryAsync(timeout.Token);

Example using a DataTable:

  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();

Example using DbDataReader:

 // Assumes connection is an open SqlConnection.  
 // Retrieve data from Oracle.  
 OracleCommand selectCommand = new OracleCommand(  
     "Select CategoryID, CategoryName FROM Categories;",  
     oracleConnection);  
 OracleDataReader oracleReader = selectCommand.ExecuteReader(  
     CommandBehavior.CloseConnection);  

  // Configure the SqlCommand and table-valued parameter.  
  SqlCommand insertCommand = new SqlCommand(  
      "usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam =  
      insertCommand.Parameters.AddWithValue(  
      "@tvpNewCategories", oracleReader);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();
Chris_Gillum_20690
Related