C# – Use SqlDbType.Structured for table parameters

To use a table-valued parameter (TVP) with SqlCommand (ADO.NET), pass in a DataTable as a SqlDbType.Structured parameter, like this:

var param = command.Parameters.AddWithValue("@TVP_People", tvpPeople); param.SqlDbType = SqlDbType.Structured; param.TypeName = "dbo.TVP_People";
Code language: C# (cs)

I’ll show a full example, starting with creating a TVP type and then inserting it as a SqlDbType.Structured parameter as shown above.

1 – Create the TVP type in SQL Server

To be able to pass in table-valued parameters, first create a table type in the SQL Server database, like this:

CREATE TYPE TVP_People AS TABLE ( [Id] int NOT NULL, [Name] [nvarchar](50) NOT NULL )
Code language: SQL (Structured Query Language) (sql)

You can add however many columns you need.

2 – Use the TVP in a query

To use the table-valued parameter in a query:

  • Add a DataTable with columns matching the TVP definition.
  • Pass in the DataTable as a SqlDbType.Structured parameter.
  • Set the parameter TypeName to the TVP type name (“dbo.TVP_People” in this example).

Here’s an example. This is using the TVP to insert multiple rows into a table:

using System.Data; using System.Data.SqlClient; //Create DataTable with same columns as TVP var tvpPeople = new DataTable(); tvpPeople.Columns.Add("Id", typeof(int)); tvpPeople.Columns.Add("Name", typeof(string)); //Add data tvpPeople.Rows.Add(1, "Jake"); tvpPeople.Rows.Add(2, "Benny"); tvpPeople.Rows.Add(3, "Bob"); //Add the DataTable as a SqlDbType.Structured parameter using (var con = new SqlConnection(connectionString)) { con.Open(); using var cmd = new SqlCommand("INSERT INTO dbo.People SELECT * FROM @TVP_People", con); var param = cmd.Parameters.AddWithValue("@TVP_People", tvpPeople); param.SqlDbType = SqlDbType.Structured; param.TypeName = "dbo.TVP_People"; cmd.ExecuteNonQuery(); }
Code language: C# (cs)

This successfully passes the rows as a table parameter and inserts them in the table.

Object must implement IConvertible

You must use a DataTable with SqlDbType.Structured. If you try to use another collection type (such as a List or array), you’ll get an exception like this:

System.InvalidCastException: Failed to convert parameter value from a List to IEnumerable
System.InvalidCastException: Object must implement IConvertible.

You’ll need to convert the collection to a DataTable. For example, create the DataTable and then loop through your collection and add rows, like this:

var people = new List<Person>() { new Person() { Id = 1, Name = "Jake"}, new Person() { Id = 2, Name = "Benny"} }; //Create DataTable with same columns as TVP var tvpPeople = new DataTable(); tvpPeople.Columns.Add("Id", typeof(int)); tvpPeople.Columns.Add("Name", typeof(string)); //Convert the List to rows foreach(var person in people) { tvpPeople.Rows.Add(person.Id, person.Name); }
Code language: C# (cs)

Then you can pass in the DataTable as the SqlDbType.Structured parameter.

Use SqlDbType.Structured with a stored procedure

In the examples above, I showed how to use a table-valued parameter with SqlDbType.Structured and a raw SQL query. You can also use this with stored procs.

First, in the stored proc, add the parameter with the TVP type (dbo.TVP_People in this example) and READONLY:

CREATE PROCEDURE InsertPeople @TVP_People dbo.TVP_People READONLY AS BEGIN INSERT INTO dbo.People SELECT * FROM @TVP_People END
Code language: SQL (Structured Query Language) (sql)

Now in the code, add a DataTable that matches the TVP definition and add it as a SqlDbType.Structured parameter. To specify that this is a stored proc, use the stored proc name and CommandType.StoredProcedure. Here’s an example:

using System.Data; using System.Data.SqlClient; //Create DataTable with same columns as TVP var tvpPeople = new DataTable(); tvpPeople.Columns.Add("Id", typeof(int)); tvpPeople.Columns.Add("Name", typeof(string)); //Add data tvpPeople.Rows.Add(1, "Jake"); tvpPeople.Rows.Add(2, "Benny"); using (var con = new SqlConnection(connectionString)) { con.Open(); using var cmd = new SqlCommand("dbo.InsertPeople", con); cmd.CommandType = CommandType.StoredProcedure; var param = cmd.Parameters.AddWithValue("@TVP_People", tvpPeople); param.SqlDbType = SqlDbType.Structured; param.TypeName = "dbo.TVP_People"; cmd.ExecuteNonQuery(); }
Code language: C# (cs)

This successfully passes in the DataTable as a TVP to the stored proc, which then inserts the rows into the table.

Leave a Comment