How to Prepare an ADO.NET Statement which includes

Question

I'm updating an existing application which uses a factory to build prepared statements, and then execute them later, which works for the rest of the system, but I've been tasked with adding a call to a statement which uses an NText, and I cannot figure out how to properly prepare the statement. I am getting a runtime error of "SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size." but I'm not sure what value to use for the Length

Obligatory Code Sample:

SqlCommand update = new SqlCommand("UPDATE Log.Response_File_Log " +
  "SET File = @File " +
  ", DateFileReceived = GETDATE() " +
  "WHERE RunID = @RunID", _connection);
update.Parameters.Add(new SqlParameter("@File", SqlDbType.NText));
update.Parameters.Add(new SqlParameter("@RunID", SqlDbType.Int));
update.Prepare();

Answer 1

Don't use NTEXT, is a deprecated type. If your database has an NTEXT column change it to VARBINARY(MAX). Use -1 for MAX type parameters length: (...,SqlDbType.Varbinary, -1).

Answer 2

Can you set it to the length of the data you're passing in to @File parameter? This is just a suggestion.