SQL Server 2008 introduced the concepts of User-Defined Table Types (UDTTs) and Table-Valued Parameters (TVPs).
These allow you to pass a
DataTable as a parameter.
There are a few articles about these here.
The articles I've seen pass
DataTables to Stored Procedures, but you can use them in embedded SQL as well.
One of the simplest uses for a Table-Valued Parameter is with the use of the
IN clause.
Many times a user may specify a set of values and you want to select some data based on those values.
The simple solution to this is to format an SQL statement with the provided values and execute it; this is a problematic technique (I won't list the reasons).
These problems are avoided by passing a
DataTable that contains the values to use.
For the included demo, I created and populated a table that contains an ID (
int) and a Name (
varchar). I want to select the rows with particular IDs.
0) Define the User-Defined Table Type. I suggest you keep it simple and generic.
For my example, I just need a table of integers:
CREATE TYPE [dbo].[IntID] AS TABLE ( [ID] [int] NOT NULL )
I also created one that uses GUIDs:
CREATE TYPE [dbo].[GuidID] AS TABLE ( [ID] [uniqueidentifier] NOT NULL )
You may also define a User-Defined Table Type with multiple columns.
Ideally you could define the types in one database and access them from any other database on the server, but that is not currently supported.
1) Write the SQL to use it.
SELECT * FROM Account WHERE ID IN ( SELECT ID FROM @IDs )"
@IDs will be the Table-Valued Parameter, because it's a table, we can select from it.
2) When writing the code, simply add the
DataTable as a parameter. The important step is to tell ADO.NET what type the parameter is:
cmd.Parameters.AddWithValue ( "@IDs" , dt ) ;
cmd.Parameters [ "@IDs" ].TypeName = "IntID" ;
3) Execute the command; ADO.NET and SQL Server will handle the rest.
The demo program supports three SQL statements that do similar things.
You choose a statement via a command-line parameter.
/**************************************************************************************************************/
/* */
/* UDTTdemo.cs */
/* */
/* Demonstrates usage of a User Defined Table Type in SQL Server 2008 */
/* */
/* Modification history: */
/* 2010-07-11 Sir John E. Boucher Created */
/* */
/**************************************************************************************************************/
namespace UDTTdemo
{
public static class UDTTdemo
{
/* Added bonus: use of enum and Dictionary rather than a switch on string */
private enum Choice { Help , In , Exists , Join } ;
private static readonly System.Collections.Generic.Dictionary<Choice,string> command ;
static UDTTdemo
(
)
{
command = new System.Collections.Generic.Dictionary<Choice,string>() ;
command.Add ( Choice.Help , "SELECT 'Syntax:' , 'UDTTdemo [ IN | EXISTS | JOIN ]'" ) ;
command.Add ( Choice.In , "SELECT * FROM Account WHERE ID IN ( SELECT ID FROM @IDs )" ) ;
command.Add ( Choice.Exists , "SELECT * FROM Account WHERE EXISTS ( SELECT ID FROM @IDs IDs WHERE IDs.ID=Account.ID )" ) ;
command.Add ( Choice.Join , "SELECT * FROM Account INNER JOIN @IDs IDs ON Account.ID=IDs.ID" ) ;
return ;
}
[System.STAThreadAttribute()]
public static int
Main
(
string[] args
)
{
int result = 0 ;
try
{
/* Instantiate and populate a DataTable with the desired values */
System.Data.DataTable dt = new System.Data.DataTable() ;
dt.Columns.Add ( "ID" , typeof(int) ) ;
dt.Rows.Add ( 5 ) ;
dt.Rows.Add ( 3 ) ;
dt.Rows.Add ( 1 ) ;
/* Instantiate a Connection */
using
(
System.Data.SqlClient.SqlConnection con
=
new System.Data.SqlClient.SqlConnection
( "Integrated Security=SSPI;Server=localhost\\sqlexpress;Database=Rubbish" )
)
{
/* Instantiate and setup a Command */
System.Data.SqlClient.SqlCommand cmd = con.CreateCommand() ;
Choice choice = Choice.Help ;
if ( args.Length > 0 )
{
/* System.Enum.TryParse<T> is new for .net 4 */
System.Enum.TryParse<Choice> ( args [ 0 ] , true , out choice ) ;
}
cmd.CommandText = command [ choice ] ;
/* Create and set the Table-Valued Parameter (TVP) */
cmd.Parameters.AddWithValue ( "@IDs" , dt ) ;
cmd.Parameters [ "@IDs" ].TypeName = "IntID" ;
/* Get and read a DataReader */
con.Open() ;
System.Data.IDataReader dr = cmd.ExecuteReader
( System.Data.CommandBehavior.CloseConnection ) ;
while ( dr.Read() )
{
System.Console.WriteLine ( "{0} {1}" , dr [ 0 ] , dr [ 1 ] ) ;
}
/* Clean up */
dr.Close() ;
con.Close() ;
}
}
catch ( System.Exception err )
{
System.Console.WriteLine ( err ) ;
}
return ( result ) ;
}
}
}
Compile it at the DOS prompt with csc UDTTdemo.cs (if you have added the .NET 4 directory to your path).
