ADO.NET not exposing maxlength even though the data exists

11 Dec 2006 8:35 PM

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?

15 Dec 2006 6:48 PM

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

Show quote

On 11 Dec 2006 12:35:50 -0800, "PCRmike" <nordan***@aol.com> wrote:

>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?

16 Dec 2006 8:24 PM

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] wrote:
Show quote

> 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
>
> On 11 Dec 2006 12:35:50 -0800, "PCRmike" <nordan***@aol.com> wrote:
>
> >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?

19 Dec 2006 5:14 PM

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 :)

Show quote

On 16 Dec 2006 12:24:28 -0800, "PCRmike" <nordan***@aol.com> wrote:

>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] wrote:
>> 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
>>
>> On 11 Dec 2006 12:35:50 -0800, "PCRmike" <nordan***@aol.com> wrote:
>>
>> >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?