Classic ADO and Table-Valued Parameters in Stored Procedure

Question

Is there some way to pass a table-valued parameter to a stored procedure in SQL Server via classic ADO?

Answer 1

Yes it is possible:

DataTable loans; loans = new DataTable();
loans.Columns.Add("loan_nbr", typeof(int));
loans.Columns.Add("loan_date", typeof(System.DateTime));
loans.Columns.Add("loan_amount", typeof(decimal)); 

using(SqlCommand cmd = new SqlCommand("InsertLoans", conn)
{  
cmd.CommandType = CommandType.StoredProcedure;  
cmd.Parameters.AddWithValue("Loans", loans);  
cmd.ExecuteNonQuery();
}

Note this does require framework 3.5 or later.

see: http://pratchev.blogspot.com/2008/04/table-valued-parameters.html

Answer 2

I thought they were new in 2008?

Anyway, I think the answer is going to be no, I doubt there's a DataTypeEnum value that you'll be able to bend to your needs.

So if I may suggest an alternative, I guess what you want to do is pass some sort of structured data into the stored procedure. I have done this before in ADO using XML:

• define the parameter in the stored proc as type xml
• define the parameter in ADO as type adLongVarChar with a length = len(xml) + 1

I know it's not what you wanted, but it's a method that works

Answer 3

Classic ADO is COM and OLE and the SQL Native Client supports Table Valued Parameters over OleDB, see Table-Valued Parameters (OLE DB). One would have to get its hand dirty and code straight to the OleDB interfaces (in C/C++).

Also TVPs are only in SQL 2008, so you won't be able to use them in SQL 2005.

BTW, for completness here is the Table Valued Parameters (ODBC) reference, for the ODBC nostalgics out there...