ADO.NET not exposing maxlength even though the data exists

[PCRmike]

I have been trying to understand why the maxLength column is not
populated when a datatable is populated using the
SqlClient.SqlDataAdapter.Fill() command. I do not know why it requires
another round trip to the server via fillschema() when the data is
there. (I used a protocol analyzer to see it)

If you open a client-side cursor on ADO 2.8 (VB6) the definedSize is
transmitted to the client without doing another round trip. The
fillschema() works fine on ADO.NET but why request data that you
already have? Is there a way to expose this raw data from the TDS
format?

 

[Mary Chipman [MSFT]]

You can set the SqlDataAdapter's MissingSchemaAction property to
MissingSchemaAction.AddWithKey before calling Fill. You'll get other
metadata as well, which you may not need.

--Mary

 

[PCRmike]

I'm aware that you can get the data by the MissingSchemaAction
property. When I use this it doubles the number of reads when I do a
trace (SQL Profiler Trace). The old ADO did not require additional
calls to the database. Why does the new ADO require additional DB
calls? It is very inefficient on high volume requests.

 

[Mary Chipman [MSFT]]

I don't think that there's any satisfactory answer to the "why"
question. There are always tradeoffs with new API's where
functionality is implemented in such a way that breaks
backwards/forwards compatibility. Old ADO fetched and saved metadata
as part of each row, which added overhead in other ways. ADO.NET was
architected differently. Best practice is to not request schema to be
returned by the server, but you doubtless already knew that