System.Data.Odbc.OdbcDataReader.GetBytes() always returns 0 when you use with ExecuteReader(CommandBehavior.SequentialAccess)

  • Article

Consider a sample which reads an image from the SQL Server table (Adventure Works database), whose column is VARBINARY(MAX). The code sample goes like this,

 

Odbc.CommandText = "select ProductPhotoID,LargePhoto from Production.ProductPhoto where ProductPhotoID = '70'"

Dim dr As Odbc.OdbcDataReader = cm.ExecuteReader(CommandBehavior.SequentialAccess) à Happens with the ODBC SQL Server driver and the SQL Native client driver

 

Dim bufferSize As Int32 = 5000

Dim blob(bufferSize - 1) As Byte

Dim retval As Long = 0

Dim startIndex As Int64 = 0

 

retval = dr.GetBytes(1, startIndex, blob, 0, bufferSize)

With VS.NET 2003:

=================

retval = dr.GetBytes(1, startIndex, blob, 0, bufferSize)

The above statement always returns the right number of bytes and the image is properly fetched.

With VS.NET 2005:

=================

retval = dr.GetBytes(1, startIndex, blob, 0, bufferSize)

The above statement always returns 0.

When I change the Dim dr As Odbc.OdbcDataReader = cm.ExecuteReader(CommandBehavior.SequentialAccess) to CommandBehavior.CloseConnection then everything starts working.

When I use SqlClient everything works fine as expected.

 

RESOLUTION:

 

  1. This is a known issue with .NET 2.0 and it is fixed in VS 2008 SP 1.
  2. To work around the issue, either you've to use CommandBehavior.CloseConnection or use System.Data.SqlClient instead of System.Data.ODBC.