*VERY* Simple DbDataAdapter.Update - Incorrect MSDN Documentation????

  • Thread starter Thread starter Sahil Malik
  • Start date Start date
S

Sahil Malik

Okay so one of the two overloads of DbDataAdapter.Update,

Update(DataSet)
Update(DataSet,sourceTable)

What exactly do these do?

Per the documentation the first will call the various commands on each row
in the dataset (#1), and each row in the specified datatable (#2).

Funny though, in reality the first overload ends up calling only the various
commands on the VERY FIRST table in the DataAdapter. I was very surprised to
see this. This has been confirmed by reflected code --

public override int Update(DataSet dataSet)
{
return this.Update(dataSet, "Table");
}
Now needless to say that is QUITE pathetic for two reasons.a) The
documentation is incorrect.b) I can't do a hierarchical update using
SqlDataAdapter.Update? C'mon !!!Can anyone tell me then if there is any way
at all I can use SqlDataAdapter.Update(DataSet) to persist *hierarchical*
changes? (Don't say use SqlXml or execute commands directly, because I know
that answer)- Sahil Malikhttp://codebetter.com/blogs/sahil.malik/
 
Sahil,

I made another message however it was for fun so I changed it because those
are often misunderstood. Now I made it more for real.

In my opinion is it this.

When you have done this.
da1(Select a, b, c from Table1,conn)
da2(Select d, e, f from Table2, conn)

Than you can do
da1.Fill(table1, "mytable1")
da2.Fill(table2, "mytable2")

And need
da1.Update(ds, "mytable1")
da2.Update(ds, "mytable2")

However with this
da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

Or did I misunderstood you?

Cor
 
Yes I was not understood correctly. I don't want to fill table by table and
update table by table. I want to fill a dataset, and update a dataset.

Most of all, when I call DataAdapter.Update(DataSet), per the MSDN doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is not
true, it doesn't do that for the entire dataset (which would be wrong too).
It really calls it for the FIRST table in the dataset.

In an ideal case, I should be able to communicate to the stored procedure
what table within the dataset this insert/update/delete was done upon - and
then call the respective command. It is SIMPLY IMPOSSIBLE as far as I
understand to persist hierarchical changes using
SqlDataAdapter.Update(DataSet). (You could do
SqlDataAdapter.Update(DataRow[]), or other overloads).

Which means, the overload SqlDataAdapter.Update(DataSet) <--- is useless.
SqlDataAdapter.Update(DataTable) <--- was good enough.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
"Sahil Malik"
Most of all, when I call DataAdapter.Update(DataSet), per the MSDN doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is not
true, it doesn't do that for the entire dataset (which would be wrong
too). It really calls it for the FIRST table in the dataset.
Click to expand...

Are you sure of that because in your "update insert and delete" command you
name your table to use. That should in my opinion conflict directly.

Cor
 
Are you sure of that because in your "update insert and delete" command
you name your table to use. That should in my opinion conflict directly.
Click to expand...

If my u/i/d commands command text was "Kill the monkey", the command will
still get called - I am not talking what is in the stored proc - just the
ado.net logic. Run profiler and verify yourself.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
Sahil,

SqlDataAdapter.Update(DataSet).(and all other adapters) takes in
consideration table mappings of an adapter. "Table" mapping is the default
mapping.
IOW it will run on the table set by table mapping.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info


Sahil Malik said:
Yes I was not understood correctly. I don't want to fill table by table
and update table by table. I want to fill a dataset, and update a dataset.

Most of all, when I call DataAdapter.Update(DataSet), per the MSDN doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is not
true, it doesn't do that for the entire dataset (which would be wrong
too). It really calls it for the FIRST table in the dataset.

In an ideal case, I should be able to communicate to the stored procedure
what table within the dataset this insert/update/delete was done upon -
and then call the respective command. It is SIMPLY IMPOSSIBLE as far as I
understand to persist hierarchical changes using
SqlDataAdapter.Update(DataSet). (You could do
SqlDataAdapter.Update(DataRow[]), or other overloads).

Which means, the overload SqlDataAdapter.Update(DataSet) <--- is useless.
SqlDataAdapter.Update(DataTable) <--- was good enough.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/







Cor Ligthert said:
Sahil,

I made another message however it was for fun so I changed it because
those are often misunderstood. Now I made it more for real.

In my opinion is it this.

When you have done this.
da1(Select a, b, c from Table1,conn)
da2(Select d, e, f from Table2, conn)

Than you can do
da1.Fill(table1, "mytable1")
da2.Fill(table2, "mytable2")

And need
da1.Update(ds, "mytable1")
da2.Update(ds, "mytable2")

However with this
da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

Or did I misunderstood you?

Cor
Click to expand...
Click to expand...
 
Miha,

No it won't.

It takes into consideration the TableMappings for only the very first table,
and conviniently ignores the rest.
Anyway, all this is going to be covered in my upcoming book. What shocks me
though is, not one book out there in the market addresses hierarchical
updates aptly, most of them just give a theoretical overview, with maybe
code excerpts out of an ideal situation - not one good example or real code.

mm hmm !!!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


Miha Markic said:
Sahil,

SqlDataAdapter.Update(DataSet).(and all other adapters) takes in
consideration table mappings of an adapter. "Table" mapping is the default
mapping.
IOW it will run on the table set by table mapping.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info


Sahil Malik said:
Yes I was not understood correctly. I don't want to fill table by table
and update table by table. I want to fill a dataset, and update a
dataset.

Most of all, when I call DataAdapter.Update(DataSet), per the MSDN doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is not
true, it doesn't do that for the entire dataset (which would be wrong
too). It really calls it for the FIRST table in the dataset.

In an ideal case, I should be able to communicate to the stored procedure
what table within the dataset this insert/update/delete was done upon -
and then call the respective command. It is SIMPLY IMPOSSIBLE as far as I
understand to persist hierarchical changes using
SqlDataAdapter.Update(DataSet). (You could do
SqlDataAdapter.Update(DataRow[]), or other overloads).

Which means, the overload SqlDataAdapter.Update(DataSet) <--- is useless.
SqlDataAdapter.Update(DataTable) <--- was good enough.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/







Cor Ligthert said:
Sahil,

I made another message however it was for fun so I changed it because
those are often misunderstood. Now I made it more for real.

In my opinion is it this.

When you have done this.
da1(Select a, b, c from Table1,conn)
da2(Select d, e, f from Table2, conn)

Than you can do
da1.Fill(table1, "mytable1")
da2.Fill(table2, "mytable2")

And need
da1.Update(ds, "mytable1")
da2.Update(ds, "mytable2")

However with this
da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

Or did I misunderstood you?

Cor
Click to expand...
Click to expand...
Click to expand...
 
Hi Sahil,

I am not sure anymore what are you talking about.
Isn't an adapter supposed to update one table? Do you think that an adapter
should update more than one table?
As for the hierarhical update, just invoke adapters (an adapter per
DataTable) in right order (for both adapters and operations).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info

Sahil Malik said:
Miha,

No it won't.

It takes into consideration the TableMappings for only the very first
table, and conviniently ignores the rest.
Anyway, all this is going to be covered in my upcoming book. What shocks
me though is, not one book out there in the market addresses hierarchical
updates aptly, most of them just give a theoretical overview, with maybe
code excerpts out of an ideal situation - not one good example or real
code.

mm hmm !!!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


Miha Markic said:
Sahil,

SqlDataAdapter.Update(DataSet).(and all other adapters) takes in
consideration table mappings of an adapter. "Table" mapping is the
default mapping.
IOW it will run on the table set by table mapping.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info


Sahil Malik said:
Yes I was not understood correctly. I don't want to fill table by table
and update table by table. I want to fill a dataset, and update a
dataset.

Most of all, when I call DataAdapter.Update(DataSet), per the MSDN doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is not
true, it doesn't do that for the entire dataset (which would be wrong
too). It really calls it for the FIRST table in the dataset.

In an ideal case, I should be able to communicate to the stored
procedure what table within the dataset this insert/update/delete was
done upon - and then call the respective command. It is SIMPLY
IMPOSSIBLE as far as I understand to persist hierarchical changes using
SqlDataAdapter.Update(DataSet). (You could do
SqlDataAdapter.Update(DataRow[]), or other overloads).

Which means, the overload SqlDataAdapter.Update(DataSet) <--- is
useless. SqlDataAdapter.Update(DataTable) <--- was good enough.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/







Sahil,

I made another message however it was for fun so I changed it because
those are often misunderstood. Now I made it more for real.

In my opinion is it this.

When you have done this.
da1(Select a, b, c from Table1,conn)
da2(Select d, e, f from Table2, conn)

Than you can do
da1.Fill(table1, "mytable1")
da2.Fill(table2, "mytable2")

And need
da1.Update(ds, "mytable1")
da2.Update(ds, "mytable2")

However with this
da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

Or did I misunderstood you?

Cor
Click to expand...
Click to expand...
Click to expand...
 
LOL I confused the whole issue.

Here is what I was saying ---

MSDN documentation says about DataAdapter.Update(dataSet) "It will call
insert/update/command on each changed row in the dataset".
That is incorrect, it will do so, on each changed row, of only the first
table in the dataset.

Thats it .. that is all I was saying.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/




Miha Markic said:
Hi Sahil,

I am not sure anymore what are you talking about.
Isn't an adapter supposed to update one table? Do you think that an
adapter should update more than one table?
As for the hierarhical update, just invoke adapters (an adapter per
DataTable) in right order (for both adapters and operations).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info

Sahil Malik said:
Miha,

No it won't.

It takes into consideration the TableMappings for only the very first
table, and conviniently ignores the rest.
Anyway, all this is going to be covered in my upcoming book. What shocks
me though is, not one book out there in the market addresses hierarchical
updates aptly, most of them just give a theoretical overview, with maybe
code excerpts out of an ideal situation - not one good example or real
code.

mm hmm !!!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


Miha Markic said:
Sahil,

SqlDataAdapter.Update(DataSet).(and all other adapters) takes in
consideration table mappings of an adapter. "Table" mapping is the
default mapping.
IOW it will run on the table set by table mapping.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info


Yes I was not understood correctly. I don't want to fill table by table
and update table by table. I want to fill a dataset, and update a
dataset.

Most of all, when I call DataAdapter.Update(DataSet), per the MSDN doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is
not true, it doesn't do that for the entire dataset (which would be
wrong too). It really calls it for the FIRST table in the dataset.

In an ideal case, I should be able to communicate to the stored
procedure what table within the dataset this insert/update/delete was
done upon - and then call the respective command. It is SIMPLY
IMPOSSIBLE as far as I understand to persist hierarchical changes using
SqlDataAdapter.Update(DataSet). (You could do
SqlDataAdapter.Update(DataRow[]), or other overloads).

Which means, the overload SqlDataAdapter.Update(DataSet) <--- is
useless. SqlDataAdapter.Update(DataTable) <--- was good enough.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/







Sahil,

I made another message however it was for fun so I changed it because
those are often misunderstood. Now I made it more for real.

In my opinion is it this.

When you have done this.
da1(Select a, b, c from Table1,conn)
da2(Select d, e, f from Table2, conn)

Than you can do
da1.Fill(table1, "mytable1")
da2.Fill(table2, "mytable2")

And need
da1.Update(ds, "mytable1")
da2.Update(ds, "mytable2")

However with this
da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

Or did I misunderstood you?

Cor
Click to expand...
Click to expand...
Click to expand...
 
Well as the song says..."It's a long, long road that leads us to who
knows where..." but I finally got it. Being a newbie I was afraid to
jump in but your conclusion is very important for everyone to
understand. I would have thought that an Update on the DataSet would
update all tables. But instead you have to call an update for each
table and specifiy it in the command. Yes, that sucks! But as long as
we understand it we can live with it.

BTW...a plug...just finished your book "Pro ADO.Net with VB.Net 1.1"
and can recommend it highly.

John


LOL I confused the whole issue.

Here is what I was saying ---

MSDN documentation says about DataAdapter.Update(dataSet) "It will call
insert/update/command on each changed row in the dataset".
That is incorrect, it will do so, on each changed row, of only the first
table in the dataset.

Thats it .. that is all I was saying.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/




Miha Markic said:
Hi Sahil,

I am not sure anymore what are you talking about.
Isn't an adapter supposed to update one table? Do you think that an
adapter should update more than one table?
As for the hierarhical update, just invoke adapters (an adapter per
DataTable) in right order (for both adapters and operations).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info

Sahil Malik said:
Miha,

No it won't.

It takes into consideration the TableMappings for only the very first
table, and conviniently ignores the rest.
Anyway, all this is going to be covered in my upcoming book. What shocks
me though is, not one book out there in the market addresses hierarchical
updates aptly, most of them just give a theoretical overview, with maybe
code excerpts out of an ideal situation - not one good example or real
code.

mm hmm !!!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Sahil,

SqlDataAdapter.Update(DataSet).(and all other adapters) takes in
consideration table mappings of an adapter. "Table" mapping is the
default mapping.
IOW it will run on the table set by table mapping.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info


Yes I was not understood correctly. I don't want to fill table by table
and update table by table. I want to fill a dataset, and update a
dataset.

Most of all, when I call DataAdapter.Update(DataSet), per the MSDN doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is
not true, it doesn't do that for the entire dataset (which would be
wrong too). It really calls it for the FIRST table in the dataset.

In an ideal case, I should be able to communicate to the stored
procedure what table within the dataset this insert/update/delete was
done upon - and then call the respective command. It is SIMPLY
IMPOSSIBLE as far as I understand to persist hierarchical changes using
SqlDataAdapter.Update(DataSet). (You could do
SqlDataAdapter.Update(DataRow[]), or other overloads).

Which means, the overload SqlDataAdapter.Update(DataSet) <--- is
useless. SqlDataAdapter.Update(DataTable) <--- was good enough.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/







Sahil,

I made another message however it was for fun so I changed it because
those are often misunderstood. Now I made it more for real.

In my opinion is it this.

When you have done this.
da1(Select a, b, c from Table1,conn)
da2(Select d, e, f from Table2, conn)

Than you can do
da1.Fill(table1, "mytable1")
da2.Fill(table2, "mytable2")

And need
da1.Update(ds, "mytable1")
da2.Update(ds, "mytable2")

However with this
da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

Or did I misunderstood you?

Cor
Click to expand...
Click to expand...
Click to expand...
 
Thank you John !! :-) I am glad you liked it. Hey feel free to post a review
on Amazon.com if you wish !!

The next book (ADO.NET 2.0) is shaping up pretty well too. We just decided
on a very very capable review team, I am quite excited to work with such
folks (who may identify themselves but I leave that upto them). The book is
due to come out around the same time as Visual Studio 2005 release.

And yes I agree, DbDataAdapter.Update(DataSet) is not what you'd think it is
... but I have covered that aptly in Chapter #10 of my upcoming book (writing
that chapter right this minute LOL). It is actually quite hard to persist
hierarchical changes back to a relational database model. I remember someone
posting a message here regarding doing GetChanges on a 40 table dataset and
complaining about GetChanges being too slow.

Well GetChanges was the smaller problem .. I'd hate to ask his updation
logic considering 40 tables in a dataset .. it's frankly near impossible to
write. I bet he thought DbDataAdapter.Update(dataset) will "magically take
care of it all" .. oh don't we wish :-).

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/




J L said:
Well as the song says..."It's a long, long road that leads us to who
knows where..." but I finally got it. Being a newbie I was afraid to
jump in but your conclusion is very important for everyone to
understand. I would have thought that an Update on the DataSet would
update all tables. But instead you have to call an update for each
table and specifiy it in the command. Yes, that sucks! But as long as
we understand it we can live with it.

BTW...a plug...just finished your book "Pro ADO.Net with VB.Net 1.1"
and can recommend it highly.

John


LOL I confused the whole issue.

Here is what I was saying ---

MSDN documentation says about DataAdapter.Update(dataSet) "It will call
insert/update/command on each changed row in the dataset".
That is incorrect, it will do so, on each changed row, of only the first
table in the dataset.

Thats it .. that is all I was saying.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/




Miha Markic said:
Hi Sahil,

I am not sure anymore what are you talking about.
Isn't an adapter supposed to update one table? Do you think that an
adapter should update more than one table?
As for the hierarhical update, just invoke adapters (an adapter per
DataTable) in right order (for both adapters and operations).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info

Miha,

No it won't.

It takes into consideration the TableMappings for only the very first
table, and conviniently ignores the rest.
Anyway, all this is going to be covered in my upcoming book. What
shocks
me though is, not one book out there in the market addresses
hierarchical
updates aptly, most of them just give a theoretical overview, with
maybe
code excerpts out of an ideal situation - not one good example or real
code.

mm hmm !!!

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Sahil,

SqlDataAdapter.Update(DataSet).(and all other adapters) takes in
consideration table mappings of an adapter. "Table" mapping is the
default mapping.
IOW it will run on the table set by table mapping.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info


Yes I was not understood correctly. I don't want to fill table by
table
and update table by table. I want to fill a dataset, and update a
dataset.

Most of all, when I call DataAdapter.Update(DataSet), per the MSDN
doc,
"Calls the respective INSERT, UPDATE, or DELETE statements for each
inserted, updated, or deleted row in the specified DataSet." That is
not true, it doesn't do that for the entire dataset (which would be
wrong too). It really calls it for the FIRST table in the dataset.

In an ideal case, I should be able to communicate to the stored
procedure what table within the dataset this insert/update/delete was
done upon - and then call the respective command. It is SIMPLY
IMPOSSIBLE as far as I understand to persist hierarchical changes
using
SqlDataAdapter.Update(DataSet). (You could do
SqlDataAdapter.Update(DataRow[]), or other overloads).

Which means, the overload SqlDataAdapter.Update(DataSet) <--- is
useless. SqlDataAdapter.Update(DataTable) <--- was good enough.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/







Sahil,

I made another message however it was for fun so I changed it
because
those are often misunderstood. Now I made it more for real.

In my opinion is it this.

When you have done this.
da1(Select a, b, c from Table1,conn)
da2(Select d, e, f from Table2, conn)

Than you can do
da1.Fill(table1, "mytable1")
da2.Fill(table2, "mytable2")

And need
da1.Update(ds, "mytable1")
da2.Update(ds, "mytable2")

However with this
da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

Or did I misunderstood you?

Cor
Click to expand...
Click to expand...
Click to expand...
 
Sahil,
If my u/i/d commands command text was "Kill the monkey", the command will
still get called - I am not talking what is in the stored proc - just the
ado.net logic. Run profiler and verify yourself.
Click to expand...

You want to say that instead of these instructions as I showed you

da1.Fill(ds)
da2.Fill(ds)

You can use this.
da1.Update(ds)
da2.Update(ds)

With a select, insert, delete, commands from table2 in da1, that it is than
updating table1 without any error, while you did not even wrote the update
etc. commands for that?

Strange is there some inbuilt magic?.

Cor
 
Well GetChanges was the smaller problem .. I'd hate to ask his updation
logic considering 40 tables in a dataset .. it's frankly near impossible
to write. I bet he thought DbDataAdapter.Update(dataset) will "magically
take care of it all" .. oh don't we wish :-).
Click to expand...

Since a single DbDataAdapter holds only one set of Insert/Update/Delete
commands I really don't see how it should update the whole dataset at once.
Call me dense :-)
 
Actually Miha, you are right, it does hold only one set of I/U/D statements,
but what I meant in that comment was, say you have a table structure of

Animals
Pet (references AnimalID)
PetBelonging (references PetID).

Now if the user while remaining disconnected adds a new animal, a new pet,
and a new belonging, all of these related, you have to go in this order

Insert Animal, get AnimalID
Insert Pet with new Animal ID, get new PetID
Insert PetBelonging with new PetID, get animal ID.

Now the code for the above, in inserts only, becomes quite lengthy and
tiresome. Now throw in Updates and Deletes, and you have 5 pages to write to
save a dataset with 3 tables. (Don't forget the stored procedures).

Question - How many pages of code will you have to write with 40 tables,
where the relationship might not be strictly hierarchical i.e. there might
be criss cross relationships?
Answer - Quite a bit !!

Thats what I meant .. 40 tables in a dataset is not practical ..

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
This link has an interesting example of how to handle relational data
with the DataAdapter...I think...

http://msdn.microsoft.com/msdnmag/issues/02/02/data/default.aspx

His example is the standard one...Customer table, Order table and
OrderDetail table.

What he does is create a base class that handles the "raw" activities
of getting data and updating/saving data on a single DataAdapter.

He then creates a "business services" class that does the following:

1. Creates an object for each of the 3 tables Customer, Order and
OrderDetail. Each has its own DataDapater and I/U/D commands. Each
inherits from the base class so it has the "raw" GetData and SaveData
functions.

2. He creates a CustomerOrder object which has as local objects, one
each of the Customer, Order and OrderDetail objects. He uses each
object's DataAdapter to create a separate DataSet with one table in
it. He then adds the table from each of individual object into a
composite CustomerOrder dataset. So the composite DataSet has no
DataAdapter. It is used for the UI only. He adds the relationships and
contstraints between the table to this composite data set so they are
useable by the UI.

3. When the program is ready to update, he passes the composite
dataset to each individual object's DataAdapter and does an update
with a reference to which table to use...in pseudo code:

m_specific_object_DataAdapter.Update(o_composite_DataSet, "specific
table name")

So my conclusion is that the data adapter stores one set of I/U/D
commands and the Update method will apply those against any table
referenced in any data set passed to it.

Is my understanding correct? If so, this makes a very logical way of
building a hierarchical Data Access Layer. My only criticism of his
code is that the Business Services layer still has provider dependent
code. I would remove that dependency to the Base class. For example,
he does this:

Dim oSelCmd As SQLComand
Dim oInsCmd As SQLCommand
Dim oDelCmd As SQLCommand

I would do this

Dim oSelCmd As IDbCommand
Dim oInsCmd As IDbCommand
Dim oDelCmd As IDbCommand

oSelCmd = GetCommand()
oInsCmd = GetCommand()
oDelCmd = GetCommand()

where GetCommand is in the base class and simply returns an SQLCommand
or OleDBCommand object depending on the configuration settings.

Does that make sense? Is there a better way to do all of this?

John
 
Ah, so the lengthy code bothers you.
It means that ado.net is very flexible which is, of course, an excellent
approach.
So, since we are programmers, we can write generic procedures or even
generate the code necessary.
And actually, I almost never write any such code.
Check out .net utilities tab on my web page www.rthand.com (I would give you
a link but the server seems down right now) - look out for save adapters
template.
And feel free to contact me via e-mail if you have questions.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info
 
Miha,

The more code you have,
the more mistakes you have.

That's just my motto. Now it doesn't matter who wrote that code, I did, you
did, or a code generator.
I'll check out that link soon's the server is up.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/



Miha Markic said:
Ah, so the lengthy code bothers you.
It means that ado.net is very flexible which is, of course, an excellent
approach.
So, since we are programmers, we can write generic procedures or even
generate the code necessary.
And actually, I almost never write any such code.
Check out .net utilities tab on my web page www.rthand.com (I would give
you a link but the server seems down right now) - look out for save
adapters template.
And feel free to contact me via e-mail if you have questions.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info


Sahil Malik said:
Actually Miha, you are right, it does hold only one set of I/U/D
statements, but what I meant in that comment was, say you have a table
structure of

Animals
Pet (references AnimalID)
PetBelonging (references PetID).

Now if the user while remaining disconnected adds a new animal, a new
pet, and a new belonging, all of these related, you have to go in this
order

Insert Animal, get AnimalID
Insert Pet with new Animal ID, get new PetID
Insert PetBelonging with new PetID, get animal ID.

Now the code for the above, in inserts only, becomes quite lengthy and
tiresome. Now throw in Updates and Deletes, and you have 5 pages to write
to save a dataset with 3 tables. (Don't forget the stored procedures).

Question - How many pages of code will you have to write with 40 tables,
where the relationship might not be strictly hierarchical i.e. there
might be criss cross relationships?
Answer - Quite a bit !!

Thats what I meant .. 40 tables in a dataset is not practical ..

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
Click to expand...
Click to expand...
 
JL,

My view is that there are many ways to do this. This being one possible way.
And there is no good way, or bad way, just the way that suits you the best.

Specifically about database independent provider layer, well - that is a
dream. Even by doing IDB*.*, you still have to write db specific SQL or
Stored procs - which is 75% of the effort. Plus someone could argue, that he
didn't need multiple databases supported, so why take the penalty of using
an interface.

So .. well .. there isn't one right way .. just ways that suit you better.
Hopefully my next book will give this matter good enough coverage.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
Sahil Malik said:
Miha,

The more code you have,
the more mistakes you have.

That's just my motto. Now it doesn't matter who wrote that code, I did,
you did, or a code generator.
Click to expand...


So what's the big deal. If you want high level procedures somebody has to
write them.
From your motto, even if MS had written them and included in framework, you
would have mistakes :-)
I'll check out that link soon's the server is up.
Click to expand...

Hm, yes, those providers...
 
Back
Top