Blog » Via Michael Swart

Table Valued Parameters, A Short, Complete Example

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

  1. Pretty great post. I simply stumbled upon your weblog and wanted to
    say that I have really enjoyed surfing around your blog posts.

    After all I'll be subscribing to your rss feed and I'm hoping you write again very soon!

  2. Pretty great post. I simply stumbled upon your weblog and wanted to say that I
    have really enjoyed surfing around your blog posts.
    After all I'll be subscribing to your rss feed and I'm hoping you write again very soon!

  3. Every weekend i used to visit this web page, as i wish for enjoyment, as this this website conations in fact fastidious funny data
    too.

  4. Every weekend i used to visit this web page, as i wish for enjoyment, as this this
    website conations in fact fastidious funny data too.

  5. After checking out a number of the blog posts
    on your site, I seriously appreciate your technique of writing a blog.
    I saved it to my bookmark website list and will be checking back in the
    near future. Please visit my web site as well and tell me your opinion.

  6. After checking out a number of the blog posts on your site, I seriously appreciate your
    technique of writing a blog. I saved it to my bookmark website list
    and will be checking back in the near future.

    Please visit my web site as well and tell me
    your opinion.

Speak Your Mind

  • No HTML is allowed.