C# – Use SqlDbType.Structured for table parameters

by

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 (as an alternative to doing a bulk insert):

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. If you want to do slightly less work, you can use Dapper with TVPs.

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.

Related Articles