Pass Table Valued Parameter Using Ado.Net

Pass table valued parameter using ADO.NET

1. Create type in SQL Server:

   CREATE TYPE [dbo].[MyDataType] As Table
   (
       ID INT,
       Name NVARCHAR(50)
   )
   

2. Create Procedure:

   CREATE PROCEDURE [dbo].[MyProcedure]
   (
       @myData As [dbo].[MyDataType] Readonly
   )
   AS
   
   BEGIN
       SELECT * FROM @myData
   END
   

3. Create DataTable in C#:

   DataTable myDataTable = new DataTable("MyDataType");
   myDataTable.Columns.Add("Name", typeof(string));
   myDataTable.Columns.Add("Id", typeof(Int32));
   myDataTable.Rows.Add("XYZ", 1);
   myDataTable.Rows.Add("ABC", 2);
   

4. Create SQL Parameter:

   SqlParameter parameter = new SqlParameter();
   parameter.ParameterName = "@myData";
   parameter.SqlDbType = System.Data.SqlDbType.Structured;
   parameter.Value = myDataTable;
   command.Parameters.Add(parameter); 
   

Can I pass table-valued parameters agnostically?

Is there a way I can pass a table-valued parameter through ADO.NET without resorting to a concrete SQL ADO implementation

No.

You'll have to downcast to SqlParameter when doing SQL Server-specific stuff, like Table-Valued Parameters. eg

var param = command.AddParameter("@table", dt);
var sqlParam = (SqlParameter) param;
sqlParam.SqlDbType = SqlDbType.Structured;  
sqlParam.TypeName = "dbo.CategoryTableType";  

Of course you could use Reflection, but that would just be a waste of time.

Populate a table-valued parameter in ado.net with ONE insert statement for multiple values?

Profiler shows you not what the client actually sent, but a kind of TSQL reproduction batch that you could run to do the same thing.

Table-Valued Parameters are a feature of the TDS network protocol, as well as the SQL Server Database Engine. And clients will send the data directly using TDS, and not in seperate INSERT statements.

If you run Profiler, you'll see that there aren't separate SQL:StmtCompleted events for inserting each row into the TVP.

C# Passing Table Valued Parameter to SqlCommand not Working

You are not setting your SqlCommand object to be a stored procedure. You should do a couple of things:

1. Remove the EXEC prefix from the string ~(it's not needed)

2. Set command to be a stored procedure:

   command.CommandType = CommandType.StoredProcedure;
   

3. Not sure how the square braces around the DataTable column names will affect this either, but I suspect it's better with them removed.

Pass test data to table-valued parameter within SQL

You can't use a temp table - you have to use a table variable:

declare @t [MyNameSpace].[MyTypeTable]
insert into @t (/*columns*/) values
(/* first row */),
(/* second row */)

EXECUTE MyNameSpace.MyStoredProc @MyTypeTableVar = @t;

(You can populate it with either INSERT ... VALUES as shown above or INSERT ... SELECT if you have an existing table containing the data you care about)

---

Related Topics