Preparing a command with Structured Parameters

Question

I have this ADO.NET command object and I can set some parameters and execute it successfully.

_mergecommand.Parameters.Add(new SqlParameter("values", SqlDbType.Structured)); _mergecommand.Parameters["values"].TypeName = "strlist"; _mergecommand.Parameters["values"].Direction = ParameterDirection.Input;

_mergecommand.Parameters["values"].Value = valuelist; _mergecommand.ExecuteNonQuery();

This works fine. But I want to prepare this command before executing it because I need to run this millions of times. I am using SQL Server 2008. I get this error if I try to prepare it

SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size.

Any idea how to do this?

Answer 1

If you have to do it millions of times using a command like this is probably not a good strategy.

Can you serialize your data into an XML string and pass that as a single argument? That will be considerably less load on your network and SQL Server.... although it will probably hit your client a lot harder.

If you are dead set on doing it that way, maybe what you are looking for is an overload of the SqlCommand.Parameters.Add method:

_mergecommand.Parameters.Add("@values", System.Data.SqlDbType.NVarChar, 100).Value = foo; 

is that more like what you wanted?

Answer 2

This is old, but there does appear to be a correct answer which is to use -1 as the size, e.g.:

_mergecommand.Parameters.Add(new SqlParameter("values", SqlDbType.Structured, -1));