DataReader.GetChars bug when using SequentialAccess ?

[Cerebrus]

Hi all,

Using : .NET 1.1, SQL Server 2000, Win 2K.

Could anyone confirm if there is a bug in the SqlDataReader.GetChars()
method when using CommandBehavior.SequentialAccess to get a BLOB
string from the database ? I have searched all over, and found many
threads with the same problem as I, but no word from the MS folks that
this is a bug. This problem only seems to occur when using GetChars,
not with GetBytes. But since it is a ntext column I am querying, I
wanted to use GetChars, since that is the recommended way.

The Situation : I am trying to retrieve 3 columns from the database,
the last of which is an ntext datatype. It may contain text upto 10,000
characters. I need it to be ntext, since it would possibly contain
HTML/XML markup.

The Exception : The exception message is
"System.InvalidOperationException: Invalid attempt to read from column
ordinal '2'. With CommandBehavior.SequentialAccess, you may only read
from column ordinal '3' or greater. "

I have checked the KB info for this exception at
"http://support.microsoft.com/kb/q308614". To my knowledge I am
accessing the columns in the order they were retrieved by the
DataReader.

My code is as follows :
---------------------------------

// string ItemID is retrieved from a Session object on Page Load.

private void GetData()
{
try
{
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

string strSQL = "SELECT ItemName, ItemImgUrl, ItemDesc FROM Items
WHERE ItemID = " + ItemID;
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;

conn.Open();
SqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess);

// The following line works fine even with GetChars(),
//but I *do* want to use SequentialAccess because of it's
performance benefits.
// Plus it streams the data directly from the database.
//SqlDataReader dr = cmd.ExecuteReader();

// Read only first row.
dr.Read();
// Read first two columns.
ItemName = dr.GetString(0);
ItemImgUrl = dr.GetString(1);

// Read CLOB column (Col. ordinal is 2)
int bufLen = 100;
int startidx = 0;
StringBuilder sb = new StringBuilder(bufLen);
StringWriter sw = new StringWriter(sb);
char[] buf = new Char[bufLen];
long clob = dr.GetChars(2,startidx,buf,0,bufLen);
while (clob == bufLen)
{
sw.Write(buf);
sw.Flush();
// Reposition the startindex
startidx += bufLen;

// Exception here, because the DataReader seems to have
progressed to the next column.
clob = dr.GetChars(2,startidx,buf,0,bufLen);
}

sw.Close();
ItemDesc = sb.ToString();

conn.Close();
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
}
---------------------------------

Note that if I change the "// Read CLOB column here" part to use
GetBytes as follows, it seems to work.

---------------------------------
// Read CLOB column
int bufLen = 100;
int startidx = 0;
StringBuilder sb = new StringBuilder(bufLen);
UTF8Encoding utf = new UTF8Encoding();
byte[] buf = new byte[bufLen];
long blob = dr.GetBytes(2,startidx,buf,0,bufLen);
while (blob == bufLen)
{
sb.Append(utf.GetString(buf));
startidx += bufLen;
blob = dr.GetBytes(2,startidx,buf,0,bufLen);
}

ItemDesc = sb.ToString();
---------------------------------

Any help will be greatly appreciated.
TIA,

Cerebrus.

 

[Cerebrus]

Update :

GetBytes only *seems* to work. I get back all my data, but each letter
has a space appended to it, so my data looks like :

M y D a t a l o o k s l i k e t h i s .

Someone please help !