Pass table value parameter using ADO.Net

Question

How to pass a table parameter to a stored procedure using ADO.Net?

Answer 1

• Create type in SQL Server :

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

• Create procedure :

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

• Create a 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); 

• Create SQL parameter :

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

Answer 2

I tried this and got an exception:

The table type parameter "@MyDataType" must have a valid type name.

I had to set the TypeName property for SqlParameter:

 parameter.TypeName = "MyDataType"; 

Answer 3

This question is a duplicate of How to pass table value parameters to a stored procedure from .net code . See this question for an example illustrating the use of either a DataTable or an IEnumerable<SqlDataRecord> .

Answer 4

You can prefix with Exec

 using( SqlConnection con = new SqlConnection( "Server=.;database=employee;user=sa;password=12345" ) ) { SqlCommand cmd = new SqlCommand( " exec ('drop table '+@tab)" , con ); cmd.Parameters.AddWithValue( "@tab" ,"Employee" ); con.Open( ); cmd.ExecuteNonQuery( ); }