Using a normal SqlDataReader from an SqlCommand with SqlConnection, I am wondering: When is a value downloaded from the sql server to my machine? Is it when I call reader.Read() or when I actually fetch the value with reader.GetBytes?
My primary concern is that I have some BLOB fields in a view. Sometimes I will grab data from the view with the BLOB data, and sometimes I'll leave them out to speed up. I am wondering if I should create a different view that doesn't contain the BLOB fields in order to speed up performance (as in preventing the BLOB data to be downloaded to the machine).
Or is it enough to refrain from grabbing the value with reader.GetBytes?
If you just called ExecuteReader() or ExecuteReader(CommandBehavior) and did not set the CommandBehavior.SequentialAccess flag the read will happen when you call reader.Read().
If you used .ExecuteReader(CommandBehavior.SequentialAccess) it will read the values as you request them, but you must request the columns in the same order you specified them in the query and you must fully read out every column before moving to the next row.
Based on your description you should just not include the column with the BLOB when you don't need the blob data, you can keep the schema the same just make your two queries
select Col1, Col2, BlobData from Foo
when you want the blob and
select Col1, Col2, cast(null as varbinary(max)) as BlobData from Foo
when you don't want the blob, it will keep the schema but just pass in nulls.