C# & ADO.NET

Pass a DataTable as a table-valued parameter to a SQL Server stored procedure

C# & ADO.NET · By the Postback Notes editorial team · Published 2026-08-30

Inserting 500 order lines one INSERT at a time means 500 round trips. A table-valued parameter (TVP) sends all of them in a single call, as a parameter shaped like a table. SQL Server has supported TVPs since 2008; ADO.NET sends a DataTable as one with almost no extra code.

1. Create a table type

CREATE TYPE dbo.OrderLineList AS TABLE
(
    ProductId int           NOT NULL,
    Quantity  int           NOT NULL CHECK (Quantity > 0),
    UnitPrice decimal(10,2) NOT NULL,
    PRIMARY KEY (ProductId)
);

2. A procedure that accepts it

CREATE PROCEDURE dbo.AddOrderLines
    @OrderId int,
    @Lines   dbo.OrderLineList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.OrderLines (OrderId, ProductId, Quantity, UnitPrice)
    SELECT @OrderId, ProductId, Quantity, UnitPrice
    FROM @Lines;
END

Table-valued parameters are always READONLY: the procedure can read them, join them, but not modify them.

3. Pass a DataTable from C#

var lines = new DataTable();
lines.Columns.Add("ProductId", typeof(int));
lines.Columns.Add("Quantity", typeof(int));
lines.Columns.Add("UnitPrice", typeof(decimal));
foreach (var item in cart.Items)
{
    lines.Rows.Add(item.ProductId, item.Quantity, item.UnitPrice);
}

using (var con = new SqlConnection(cs))
using (var cmd = new SqlCommand("dbo.AddOrderLines", con) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.Add("@OrderId", SqlDbType.Int).Value = orderId;
    SqlParameter p = cmd.Parameters.Add("@Lines", SqlDbType.Structured);
    p.TypeName = "dbo.OrderLineList";
    p.Value = lines;
    con.Open();
    cmd.ExecuteNonQuery();
}

Without a DataTable

For large volumes, streaming SqlDataRecord objects avoids building the whole table in memory:

using Microsoft.SqlServer.Server; // System.Data.dll on .NET Framework

static IEnumerable<SqlDataRecord> ToRecords(IEnumerable<CartItem> items)
{
    var meta = new[]
    {
        new SqlMetaData("ProductId", SqlDbType.Int),
        new SqlMetaData("Quantity", SqlDbType.Int),
        new SqlMetaData("UnitPrice", SqlDbType.Decimal, 10, 2)
    };
    foreach (var i in items)
    {
        var r = new SqlDataRecord(meta);
        r.SetInt32(0, i.ProductId);
        r.SetInt32(1, i.Quantity);
        r.SetDecimal(2, i.UnitPrice);
        yield return r;
    }
}
// p.Value = ToRecords(cart.Items);  (the sequence must contain at least one record; pass null for an empty set)

Typical uses

Official documentation