Table Valued Parameters, A Short, Complete Example

January 5, 2011

Table Valued Parameters, A Short, Complete Example

Filed under: SQL Scripts,SQLServerPedia Syndication,Technical Articles — Tags: "sql server", ado.net, c#, table valued parameters — Michael J. Swart @ 12:00 pm

After surfing for a while I had trouble finding an example of an application that shows how to use table valued parameters (tvp) from beginning to end using C#, ado.net, and SQL Server 2008. Official docs covering TVPs are found at Table-Valued Parameters in SQL Server 2008 (ADO.NET).

So this is my own TVP example, it consists of a SQL Script, a C# script and a batch file that runs and executes the program.

The DB Setup (a SQL Script)

Run this on your SQL Server 2008 (or later) database that you can test on:

use tempdb;
 
CREATE TYPE BigIntList
    AS TABLE (i bigint)
GO
 
CREATE PROCEDURE ReturnEvenNumbers (@list BigIntList READONLY) AS
SELECT i
FROM @list
WHERE i % 2 = 0
GO

The C# program

Aptly called Program.cs, this is the definition of a program that calls the new procedure with a list of seven numbers and prints the list of numbers that comes back (i.e. the even numbers).
Edit the connection string here and then save this as Program.cs in some directory.

using System.Data.SqlClient;
using System.Data;
 
namespace TVParameterTest {
    class Program {
        static void Main( ) {
            // build table
            DataTable dt = new DataTable();
            dt.Columns.Add( "i", typeof( long ) );
            foreach( long l in new long[] {1,2,3,4,5,6,7} )
                dt.LoadDataRow( new object[] { l }, true );
 
            // build connection and command
            SqlCommand cmd = new SqlCommand(
                "ReturnEvenNumbers",
                new SqlConnection() );
            cmd.Connection = new SqlConnection( @"Data Source=.\sql2k8;Initial Catalog=tempdb;Integrated Security=True" );
            cmd.Connection.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add( "list", SqlDbType.Structured).Value = dt;
 
            // execute and output
            SqlDataReader reader = cmd.ExecuteReader();
            while( reader.Read() )
                System.Console.WriteLine( reader[0].ToString());
            cmd.Connection.Close();
 
        }
    }
}

Running it!

First make sure you’ve got Visual Studio on your machine, then you should be able to open a command prompt (or powershell!) and see this:

E:\Temp>csc program.cs
Microsoft (R) Visual C# 2008 Compiler version 3.5.30729.1
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.

E:\Temp>Program.exe
2
4
6

-- Comments (1)

1 Comment »

[…] ad hoc TVP query itself. It was a bit of a modification of my example at Table Valued Parameters, A Short Complete Example. The only difference is that the command is a query instead of a stored […]

Pingback by Follow up on Ad Hoc TVP contention | Michael J. Swart — April 5, 2013 @ 2:14 pm

RSS feed for comments on this post. TrackBack URL

Michael J. Swart

January 5, 2011