Pass Arrays to SQL Functions from .NET C#

Question

Hi,

There is any chance to pass arrays to SQL Functions from .NET C#.

Thanks in advance.

Sincerely.

And Could you show me how ?

Answer 1

With SQL Server 2008, yes, using table-valued parameters. With SQL Server 2005, you'd have to wrap the data in XML and decompose it using XQuery in your T-SQL function.

NB: you can only pass table-valued parameters to stored procs--not to functions.

From the link:

 // Configure the SqlCommand and table-valued parameter.
 SqlCommand insertCommand = new SqlCommand(
   "usp_InsertCategories", connection);
 insertCommand.CommandType = CommandType.StoredProcedure;
 SqlParameter tvpParam = 
    insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", dataReader);
 tvpParam.SqlDbType = SqlDbType.Structured;

Answer 2

Erland Sommarskog has two excellent articles on how to do this in SQL Server 2000 and 2005:

http://www.sommarskog.se/arrays-in-sql-2005.html

http://www.sommarskog.se/arrays-in-sql-2000.html

But with SQL Server 2008 and table-valued parameters, it's much nicer and easier, so if you're on 2008, definitely use TVP !

Marc