datareader and varchar(4000)

[Dan Holmes]

I have a SqlServer table with a column defined as VARCHAR(4000). I am reading the data with a datareader. When i see
the data in the debugger, i am not getting all the data from the column. Using SQL (SSMS) i can see it all; using the
code below i only get the first 996 bytes. Is there a limitation to the amount of data in a column? Certainly not. I
searched for this and stuff about SequentialAccess. That doesn't work either. The cmd is a simple SELECT.

using (SD.SqlClient.SqlConnection con = new SD.SqlClient.SqlConnection(_connectionString))
{
con.Open();
cmd.Connection = con;
SD.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult |
System.Data.CommandBehavior.SequentialAccess);

if (dr.HasRows)
result = new List<CostFunctionParameterInfo>();
while (dr.Read())
{
CostFunctionParameterInfo cpi = new CostFunctionParameterInfo();
cpi.Id = (int?)dr["ID"];
cpi.Name = dr["ParameterName"].ToString();
cpi.FunctionDefinitionID = functionID;
cpi.DataType = dr.GetInt32(3);
cpi.Description = cpi.Name;
cpi.FunctionDefinitionID = (int?)dr["FunctionCallParameterID"];
//this is the value that isn't returning all the data
cpi.DefaultValue = dr.IsDBNull(6) ? null : dr.GetString(6);

cpi.DomainType = dr.GetInt32(7);
cpi.ReferenceValue = dr.IsDBNull(8) ? null : dr["Referenceid"].ToString();
result.Add(cpi);
}
}

 

[Gregory A. Beamer]

I have a SqlServer table with a column defined as VARCHAR(4000). I am
reading the data with a datareader. When i see the data in the
debugger, i am not getting all the data from the column.

It is a buffer size issue, most likely. If you need to see the full value
of the column, consider putting it into a variable and then reading from
the variable. You can set up a debug only section for setting the variable
and pull directly from the DataReader (firehose cursor) in the production
application.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************