Simple task, can't do in ADO.NET

Switching from ADO to ADO.net and I am totally lost and and frustrated. What
I'm trying to do is so dirt simple: At RUNTIME, load an arbitrary database
table into a datagrid for editing. Simple: create a SqlDataAdapter,
dynamically constructing the SQL command string based on the the name of the
table supplied at runtime; create a CommandBuilder; call Fill, supplying a
new blank DataSet; call Update when complete. So far, so good.

The table that I'm editing has lookups that the user needs to be able to see
as they are editing. However, there's not updatable view concept in ADO.NET.
If I do a JOIN to any lookup tables, CommandBuilder flatlines and can't
generate the necessary update statements. Is the only alternative here to
generate the update statement by hand? That just seems incredibly tedious and
unnecessary.

Another weird thing is that it's a mystery as to how CommandBuilder works.
If you trace through the code you'll see that it doesn't actually modify the
UpdateCommand property in the DataAdapter. Somehow it's doing it's magic in
the background. This prevented me from kludging up update statements from a
non-lookup version of the SelectCommand and using that in the lookup version.

Another problem is now to specify a primary key for the DataSet. Supposedly
the SqlDataAdapter uses a DataReader in the background to generate schema for
the DataSet. But the IsKey fields are all null. So somewhere between there
and the DataSet it somehow decides what the primary key is, but it's not
accessible to the programmer. I guess what I'm wonder is can I specify a
primary key directly to the DataSet after it has been filled?

Sahil
Mon Jan 03 22:12:11 CST 2005

> However, there's not updatable view concept in ADO.NET.
> If I do a JOIN to any lookup tables, CommandBuilder flatlines and can't
> generate the necessary update statements. Is the only alternative here to
> generate the update statement by hand? That just seems incredibly tedious
> and
> unnecessary.

CommandBuilder is hyper inefficient for flat tables too. Update Statements
by hand is the best, not so tedious and fairly necessary.

> Another weird thing is that it's a mystery as to how CommandBuilder works.
> If you trace through the code you'll see that it doesn't actually modify
> the
> UpdateCommand property in the DataAdapter. Somehow it's doing it's magic
> in
> the background. This prevented me from kludging up update statements from
> a
> non-lookup version of the SelectCommand and using that in the lookup
> version.

CommandBuilder works based upon the schema of your table and tries and
generates the best possible one size fits all kinda SQL Query, as mentioned
above it is okay for a quick and dirty job, but not for a heavy load
enterprise app.

> Another problem is now to specify a primary key for the DataSet.
> Supposedly
> the SqlDataAdapter uses a DataReader in the background to generate schema
> for
> the DataSet. But the IsKey fields are all null. So somewhere between there
> and the DataSet it somehow decides what the primary key is, but it's not
> accessible to the programmer. I guess what I'm wonder is can I specify a
> primary key directly to the DataSet after it has been filled?

SqlDataAdapter uses DataReader - that's a comment I have never been able to
validate, maybe the MS Gurus can shed light on that. Though I have heard the
same many times over, I'm just not sure if it's air talk or has weight to
it.
DataSet can hold schema information if you call a FillSchema on it first. In
my opinion FillSchema executes a fairly heavy query on the master db so it
should be avoided in heavy duty operations.
Yes you can emulate PK in a DataTable after or before it has been filled.
There are properties on the relevant datacolumn object that allow you to do
this very easily.

.. and don't be frustrated, in the long run ADO.NET is way better than ADO.
I recommend buying a book :-) (hint hint google for my name).

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik




"pearsons_11114" <pearsons11114@discussions.microsoft.com> wrote in message
news:074318A5-8C13-453F-8C32-6E972B359FBB@microsoft.com...
> Switching from ADO to ADO.net and I am totally lost and and frustrated.
> What
> I'm trying to do is so dirt simple: At RUNTIME, load an arbitrary database
> table into a datagrid for editing. Simple: create a SqlDataAdapter,
> dynamically constructing the SQL command string based on the the name of
> the
> table supplied at runtime; create a CommandBuilder; call Fill, supplying a
> new blank DataSet; call Update when complete. So far, so good.
>
> The table that I'm editing has lookups that the user needs to be able to
> see
> as they are editing. However, there's not updatable view concept in
> ADO.NET.
> If I do a JOIN to any lookup tables, CommandBuilder flatlines and can't
> generate the necessary update statements. Is the only alternative here to
> generate the update statement by hand? That just seems incredibly tedious
> and
> unnecessary.
>
> Another weird thing is that it's a mystery as to how CommandBuilder works.
> If you trace through the code you'll see that it doesn't actually modify
> the
> UpdateCommand property in the DataAdapter. Somehow it's doing it's magic
> in
> the background. This prevented me from kludging up update statements from
> a
> non-lookup version of the SelectCommand and using that in the lookup
> version.
>
> Another problem is now to specify a primary key for the DataSet.
> Supposedly
> the SqlDataAdapter uses a DataReader in the background to generate schema
> for
> the DataSet. But the IsKey fields are all null. So somewhere between there
> and the DataSet it somehow decides what the primary key is, but it's not
> accessible to the programmer. I guess what I'm wonder is can I specify a
> primary key directly to the DataSet after it has been filled?
>

William
Mon Jan 03 22:16:53 CST 2005

You aren't the first to find that ADO.NET is not ADO classic. Many of the
limitations of ADO.NET are also limitations of ADO classic as well. The
principle difference between the two in regard to updating is that ADO.NET
does not try to figure out how to construct the action SQL
(INSERT/UPDATE/DELETE) statements--it delegates that responsibility to the
(very lame) CommandBuilder. I discuss the issues in an article published
some time ago (see http://www.betav.com/msdn_magazine.htm). I explain the
"magic" in the article. Basically, when you invoke the CB, ADO.NET knows
that when the Update is executed it needs to retrieve the command to use
from the CB. This requires (at least) another round trip to the server to
resolve the action commands. There are no additional properties to "tune"
how it does its job.
ADO.NET is designed to let the developer provide whatever update SQL is
required for the situation. No, the CB is incapable of dealing with any but
the simplest situations. It does not get any better in ADO 2.0 due out later
this year. Yes, I agree, there are many situations that require you to write
SQL to do the updates--which can be a PIA, but the generic one-size-fits-all
update logic in ADO classic did not do much better for JOINed rowset
products.
I discuss the PK issue in another article on identity issues.

I discuss the whole magilla in my book on ADO.NET.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"pearsons_11114" <pearsons11114@discussions.microsoft.com> wrote in message
news:074318A5-8C13-453F-8C32-6E972B359FBB@microsoft.com...
> Switching from ADO to ADO.net and I am totally lost and and frustrated.
> What
> I'm trying to do is so dirt simple: At RUNTIME, load an arbitrary database
> table into a datagrid for editing. Simple: create a SqlDataAdapter,
> dynamically constructing the SQL command string based on the the name of
> the
> table supplied at runtime; create a CommandBuilder; call Fill, supplying a
> new blank DataSet; call Update when complete. So far, so good.
>
> The table that I'm editing has lookups that the user needs to be able to
> see
> as they are editing. However, there's not updatable view concept in
> ADO.NET.
> If I do a JOIN to any lookup tables, CommandBuilder flatlines and can't
> generate the necessary update statements. Is the only alternative here to
> generate the update statement by hand? That just seems incredibly tedious
> and
> unnecessary.
>
> Another weird thing is that it's a mystery as to how CommandBuilder works.
> If you trace through the code you'll see that it doesn't actually modify
> the
> UpdateCommand property in the DataAdapter. Somehow it's doing it's magic
> in
> the background. This prevented me from kludging up update statements from
> a
> non-lookup version of the SelectCommand and using that in the lookup
> version.
>
> Another problem is now to specify a primary key for the DataSet.
> Supposedly
> the SqlDataAdapter uses a DataReader in the background to generate schema
> for
> the DataSet. But the IsKey fields are all null. So somewhere between there
> and the DataSet it somehow decides what the primary key is, but it's not
> accessible to the programmer. I guess what I'm wonder is can I specify a
> primary key directly to the DataSet after it has been filled?
>

pearsons11114
Mon Jan 03 23:25:05 CST 2005

> Yes you can emulate PK in a DataTable after or before it has been filled.
> There are properties on the relevant datacolumn object that allow you to do
> this very easily.

So I can update the columns individually rather than the PrimaryKey array in
the Table?

thx.

Sahil
Mon Jan 03 23:32:26 CST 2005

You can update with whatever update statement you specify. So if the update
statement includes a PK - then yes you can update based on the PK, but if
your UpdateCommand reads --- "Update MyTable Set MyColumn = 1" .. it'll
update every single row.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik




"pearsons_11114" <pearsons11114@discussions.microsoft.com> wrote in message
news:C9ACD705-EA91-444C-A6E4-FDC174CAFAD3@microsoft.com...
>> Yes you can emulate PK in a DataTable after or before it has been filled.
>> There are properties on the relevant datacolumn object that allow you to
>> do
>> this very easily.
>
> So I can update the columns individually rather than the PrimaryKey array
> in
> the Table?
>
> thx.
>

pearsons11114
Tue Jan 04 13:23:01 CST 2005

Sorry, that not what I meant by update. In the documentation, it says to set
a primary key in a DataSet by supplying an array of DataCo.umns to the
PrimaryKey property of the Table. It sounds like you are saying I can also
set it by setting properties of each DataColumn individually, analogous to
the ReadOnly property in a SchemaTable. However, I couldn't see how to do
that.

"Sahil Malik" wrote:

> You can update with whatever update statement you specify. So if the update
> statement includes a PK - then yes you can update based on the PK, but if
> your UpdateCommand reads --- "Update MyTable Set MyColumn = 1" .. it'll
> update every single row.
>
> - Sahil Malik
> http://dotnetjunkies.com/weblog/sahilmalik
>
>
>
>
> "pearsons_11114" <pearsons11114@discussions.microsoft.com> wrote in message
> news:C9ACD705-EA91-444C-A6E4-FDC174CAFAD3@microsoft.com...
> >> Yes you can emulate PK in a DataTable after or before it has been filled.
> >> There are properties on the relevant datacolumn object that allow you to
> >> do
> >> this very easily.
> >
> > So I can update the columns individually rather than the PrimaryKey array
> > in
> > the Table?
> >
> > thx.
> >
>
>
>

pearsons11114
Tue Jan 04 13:45:07 CST 2005



"William (Bill) Vaughn" wrote:

> You aren't the first to find that ADO.NET is not ADO classic. Many of the
> limitations of ADO.NET are also limitations of ADO classic as well. The
> principle difference between the two in regard to updating is that ADO.NET
> does not try to figure out how to construct the action SQL
> (INSERT/UPDATE/DELETE) statements--it delegates that responsibility to the
> (very lame) CommandBuilder. I discuss the issues in an article published
> some time ago (see http://www.betav.com/msdn_magazine.htm). I explain the
> "magic" in the article. Basically, when you invoke the CB, ADO.NET knows
> that when the Update is executed it needs to retrieve the command to use
> from the CB. This requires (at least) another round trip to the server to
> resolve the action commands. There are no additional properties to "tune"
> how it does its job.

Yeah, I finally figured out that the CommandBuilder exposes the statements
as properties. As you say, a pretty weak effort.

> ADO.NET is designed to let the developer provide whatever update SQL is
> required for the situation. No, the CB is incapable of dealing with any but
> the simplest situations. It does not get any better in ADO 2.0 due out later
> this year. Yes, I agree, there are many situations that require you to write
> SQL to do the updates--which can be a PIA, but the generic one-size-fits-all
> update logic in ADO classic did not do much better for JOINed rowset
> products.

True, but the nice feature they took away was the ability for all this to
happen at dynamically at runtime. With ADO.NET I essentially have to write my
own CommandBuilder that's a bit less brain-dead. The logic of updatable views
is fairly straightforward. Can't see why it defeated them.

<rant>
The factoring they did in ADO.NET makes sense , but not the reduction in
functionality. They've turned the design on it head, making it revolve around
performance issues that only arise in a heavy-load environment. This is still
a relatively rare use case, unless one has been drinking the tool vendor
marketing koolaid. Classic early optimization error. OTOH, in terms of
dollars spent on tools, it makes economic sense for them.
</rant>
> I discuss the PK issue in another article on identity issues.
>
> I discuss the whole magilla in my book on ADO.NET.

Will check it out. Thanks!