Just in Chronicles

Life as a Voyage

Handling Large Data Values for System.Data.SqlClient.SqlParameter


Data manipulation in web applications, regardless of whatever languages used for their development, is inevitable. In order to handle data through ADO.NET, SqlParameter class is the key object to define data types. It requires both data type and data length.

Defining data length is not necessary when its ParameterDirection is either ParameterDirection.Input or ParameterDirection.ReturnValue. However, if the ParameterDirection is set as ParameterDirection.Output or ParameterDirection.InputOutput, data length must be defined. Otherwise, it shows the following error.

the Size property has an invalid size of 0.

Since MS-SQL 2005, VARCHAR, NVARCHAR and VARBINARY data types have had MAX as their data length. In order to set a particular data length for these data types, the following code lines are typical.

SqlParameter param = new SqlParameter("@value", SqlDbType.NVarChar, 100);
param.Direction = ParameterDirection.Input;
param.Value = "Certain Value";

But, how can it set its data length, if the NVARCHAR parameter has MAX data length? Simply set it as –1. –1 is the magic number for this data length. Therefore, the following code lines are possible.

SqlParameter param = new SqlParameter("@value", SqlDbType.NVarChar, -1);

데이타 길이 처리를 할 때 MAX 인 경우에는 -1로 세팅하면 된다. -1이 만능일세~


Written by Justin Yoo

15/08/2009 at 12:41