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.