How to create a table-value parameter for text command

I have a generated block of SQL query that takes a table-value parameter as an input.

I have a DataTable data input parameter, then:

// add the table-value parameter
var tvp = com.Parameters.AddWit开发者_如何学PythonhValue("data", data);
tvp.SqlDbType = SqlDbType.Structured;
tvp.TypeName = "???";

com.ExecuteNonQuery();

The problem is that if the command is not a stored procedure then the TypeName needs to be declared.

The number and type of columns in the data parameter could vary a lot, so I don't have a SQL type that I can use.

Is there any way that I can use a TVP in this way?

You have to have a defined TypeName on the server end in order to pass a table valued parameter. So if non exists, then no... you cannot use a table valued parameter in this way. Read more on limitations and requirements here:

http://msdn.microsoft.com/en-us/library/bb675163.aspx

I haven't ever tried it but there shouldn't be any reason that you couldn't generate the create TypeName statement prior to this block of code based on structure of your datatable. You could pick any name you want and then drop it when you're done.

It would take a little coding to build the create typename statement based on the structure of your datatable but do-able.