ADO/ADO.NET - Passing Dataset to Stored Procedure

Asked By Naresh Kumar on 12-Jan-10 07:35 AM
Hi All,

Is it possible to send a Dataset or DataTable to a StoredProcedure using Command object? If so please tell me how to do this.

Thanks in advance.

Table-Valued Parameter - F Cali replied to Naresh Kumar on 12-Jan-10 07:58 AM

In SQL Server 2008, yes you can pass a DataTable to a stored procedure.  You will be using table-valued parameter.  Here's an example from the following link:

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

Dim table As New DataTable("temp")
Dim col1 As New DataColumn("col1", System.Type.GetType("System.Int32"))
Dim col2 As New DataColumn("col2", System.Type.GetType("System.String"))
Dim col3 As New DataColumn("col3", System.Type.GetType("System.DateTime"))
table.Columns.Add(col1)
table.Columns.Add(col2)
table.Columns.Add(col3)

'Create a command object that calls the stored proc
Dim command As New SqlCommand("usp_AddRowsToMyTable", conn)
command.CommandType = CommandType.StoredProcedure

'Create a parameter using the new type
Dim param As SqlParameter = command.Parameters.Add("@MyTableParam", SqlDbType.Structured)
param.Value = table

If your database is not SQL Server 2008, then it is not possible.

Regards,
http://www.sql-server-helper.com/sql-server-2008/table-valued-parameters.aspx