Update without Fill first (DataAdapter)

[Calvin Lai]

Hi all,

Could someone tell me if a Fill has to be called first before a DataAdapter
can Update a DataTable object? Is that a must? Isn't that kind of waste of
time?

Say, I have a DataTable already filled from before. For some reasons (coz I
am doing ASP.NET app), the adapter object is gone after a round trip. I
stored my DataTable somewhere that's persistent (ViewState). So the data in
DataTable can be modified and ready to be update. However, whenever I try to
call oAdapter.Update(oDataTable), it fails because it said missing a select
command. All I have to do, is build a adapter object, and wrapped it up w/ a
CommandBuilder, then call some Fill method (which I think is NOT necessary),
then it works..

Anyone can point me I am wrong?

Calvin

 

[Marina]

You don't need a call to Fill. You just need to have update/insert/delete
commands on the dataadapter, or alternatively a commandbuilder,which means a
select command on your dataadapter.

 

[William Ryan]

The CommandBuilder Infers the update/delete and modify commands from the
Select Statement...so technically the answer is NO, you don't need to fire a
select statement first provided you aren't using a CommandBuilder. AFAIK,
if you are using a CB, then you must call fill first.

While CommandBuilders can be convenient in a pinch, there's a ton of
limitations with them. As long as your table rows are marked as
modified/deleted/inserted etc, and you have the corresponding commands added
to your dataadapter, you'll be fine if you don't use the CB

 

[Calvin Lai]

Thanks for your reply. Is there any easy way to implement the SQL statements
from the DataTable's state w/o using the CommandBuilder? Coz I think MS has
provide such a convenient object, why wouldn't I make full of it instead of
building my own.

Besides, the second reason why CB seems more reasonably to me is that, I
have to modify BOTH the DataTable AND construct my own SQL statements in
ADO.NET, whereas in ADO, I just work with on single RecordSet object...I am
very confuse here. Why would ADO.NET seem to have more redundant work here?

Calvin

 

[Marina]

The real issue is that a dataset is an object completely disconnected from
its data source. In fact, it doesn't have anything to do with database acess
as such - it can be used to store any kind of data, or be used to get data
from any data source.

The recordset on the other hand, was strictly for database access, and thus
completely dependent on being tied to a specific data source.

All this makes the dataset much more flexible and ubiquotous, but also makes
it harder to use to do common tasks that were easier before.

 

[William Ryan]

Check out Bill Vaughn's article on Weaning developers from the
CommandBuilder http://www.betav.com/msdn_magazine.htm . The DataAdapter
Configuration Wizard will do a lot for you.. and while the CommandBuilder is
Convenient, that convenience comes at a price. You can't control
concurrency for one thing, it's performance is less than stellar and less
than if you rolled out your own logic, you can't control the commands it
generates b/c it's inferred from the Select statment and most importantly,
You can't use Stored Procs. This alone is reason enough to shun it

As far as the 'redundancy' that's the core of ADO.NET. For one thing, you
don't need a database to use a DataTable or DataSet. YOu can read stuff from
an XML file and use a DataAdapter to submit that data to a DB... so if your
update failed b/c a database was temporarily down, you could work offline
and serialize your changes, then submit them when the DB was available.
MOreoever, you can grab data from an Oracle Database and turn around and
submit that exact same data to Access/SqlServer/whatever else. So things
scale and port much much better now. And most importantly, using the
ADO.NET Disconnected objects like the one you mention (DataTable) you open a
connection and immediately close it. This takes a Lot of stress off of your
DB and is much less resource intensive on the server.

Also, if you only want readonly access, the DataReader (SqlDataReader,
OleDbDataReader etc) is the closest thing to old school ADO that I know of,
but it's only a one way street (you can read data but not write it) and it's
only forward moving (like the old firehouse cursor). Depending on your
needs, you may want to mix and match.

HTH,

Bill

 

[Calvin Lai]

Thanks for all you guys help! They all make sense. The only reason I asked
is because I want to create a "Generic" DB access object, which could
retrieve all schema and do update/insert/delete command on the fly instead
of writing one for each table/view I accessed. With the proper solutions you
provided, it seems this couldn't be done. But I guess that's a trade off. So
it's okay.

Calvin

Check out Bill Vaughn's article on Weaning developers from the
CommandBuilder http://www.betav.com/msdn_magazine.htm . The DataAdapter
Configuration Wizard will do a lot for you.. and while the CommandBuilder is
Convenient, that convenience comes at a price. You can't control
concurrency for one thing, it's performance is less than stellar and less
than if you rolled out your own logic, you can't control the commands it
generates b/c it's inferred from the Select statment and most importantly,
You can't use Stored Procs. This alone is reason enough to shun it

As far as the 'redundancy' that's the core of ADO.NET. For one thing, you
don't need a database to use a DataTable or DataSet. YOu can read stuff from
an XML file and use a DataAdapter to submit that data to a DB... so if your
update failed b/c a database was temporarily down, you could work offline
and serialize your changes, then submit them when the DB was available.
MOreoever, you can grab data from an Oracle Database and turn around and
submit that exact same data to Access/SqlServer/whatever else. So things
scale and port much much better now. And most importantly, using the
ADO.NET Disconnected objects like the one you mention (DataTable) you open a
connection and immediately close it. This takes a Lot of stress off of your
DB and is much less resource intensive on the server.

Also, if you only want readonly access, the DataReader (SqlDataReader,
OleDbDataReader etc) is the closest thing to old school ADO that I know of,
but it's only a one way street (you can read data but not write it) and it's
only forward moving (like the old firehouse cursor). Depending on your
needs, you may want to mix and match.

HTH,

Bill

Thanks for your reply. Is there any easy way to implement the SQL statements
from the DataTable's state w/o using the CommandBuilder? Coz I think MS has
provide such a convenient object, why wouldn't I make full of it instead of
building my own.

Besides, the second reason why CB seems more reasonably to me is that, I
have to modify BOTH the DataTable AND construct my own SQL statements in
ADO.NET, whereas in ADO, I just work with on single RecordSet object...I am
very confuse here. Why would ADO.NET seem to have more redundant work here?

Calvin

The CommandBuilder Infers the update/delete and modify commands from the
Select Statement...so technically the answer is NO, you don't need to

fire

a
select statement first provided you aren't using a CommandBuilder. AFAIK,
if you are using a CB, then you must call fill first.

While CommandBuilders can be convenient in a pinch, there's a ton of
limitations with them. As long as your table rows are marked as
modified/deleted/inserted etc, and you have the corresponding commands added
to your dataadapter, you'll be fine if you don't use the CB
"Calvin Lai" <clai[at]qdata[dot]com> wrote in message
Hi all,

Could someone tell me if a Fill has to be called first before a
DataAdapter
can Update a DataTable object? Is that a must? Isn't that kind of

waste

of
time?

Say, I have a DataTable already filled from before. For some reasons (coz
I
am doing ASP.NET app), the adapter object is gone after a round

trip.

I I
try up

 

[William Ryan]

Remember though, you can do stuff generically, but the more generic, in this
regard, the more you lose, and I think with ADO.NET in particular, what you
lose greatly outweighs what you gain. The configuration wizard can do quite
a bit for you, as can strongly typed datasets...but the more you play around
with it, the more I think you'll like it.

HTH,

Bill

Thanks for all you guys help! They all make sense. The only reason I asked
is because I want to create a "Generic" DB access object, which could
retrieve all schema and do update/insert/delete command on the fly instead
of writing one for each table/view I accessed. With the proper solutions you
provided, it seems this couldn't be done. But I guess that's a trade off. So
it's okay.

Calvin

Check out Bill Vaughn's article on Weaning developers from the
CommandBuilder http://www.betav.com/msdn_magazine.htm . The DataAdapter
Configuration Wizard will do a lot for you.. and while the

CommandBuilder

is
Convenient, that convenience comes at a price. You can't control
concurrency for one thing, it's performance is less than stellar and less
than if you rolled out your own logic, you can't control the commands it
generates b/c it's inferred from the Select statment and most importantly,
You can't use Stored Procs. This alone is reason enough to shun it

As far as the 'redundancy' that's the core of ADO.NET. For one thing, you
don't need a database to use a DataTable or DataSet. YOu can read stuff from
an XML file and use a DataAdapter to submit that data to a DB... so if your
update failed b/c a database was temporarily down, you could work offline
and serialize your changes, then submit them when the DB was available.
MOreoever, you can grab data from an Oracle Database and turn around and
submit that exact same data to Access/SqlServer/whatever else. So things
scale and port much much better now. And most importantly, using the
ADO.NET Disconnected objects like the one you mention (DataTable) you

open

a
connection and immediately close it. This takes a Lot of stress off of your
DB and is much less resource intensive on the server.

Also, if you only want readonly access, the DataReader (SqlDataReader,
OleDbDataReader etc) is the closest thing to old school ADO that I know of,
but it's only a one way street (you can read data but not write it) and it's
only forward moving (like the old firehouse cursor). Depending on your
needs, you may want to mix and match.

HTH,

Bill

Thanks for your reply. Is there any easy way to implement the SQL statements
from the DataTable's state w/o using the CommandBuilder? Coz I think

MS
has

provide such a convenient object, why wouldn't I make full of it

instead
of

building my own.

Besides, the second reason why CB seems more reasonably to me is that, I
have to modify BOTH the DataTable AND construct my own SQL statements in
ADO.NET, whereas in ADO, I just work with on single RecordSet

object...I
am

very confuse here. Why would ADO.NET seem to have more redundant work here?

Calvin


The CommandBuilder Infers the update/delete and modify commands from the
Select Statement...so technically the answer is NO, you don't need

to
fire

a
select statement first provided you aren't using a CommandBuilder. AFAIK,
if you are using a CB, then you must call fill first.

While CommandBuilders can be convenient in a pinch, there's a ton of
limitations with them. As long as your table rows are marked as
modified/deleted/inserted etc, and you have the corresponding commands
added
to your dataadapter, you'll be fine if you don't use the CB
"Calvin Lai" <clai[at]qdata[dot]com> wrote in message
Hi all,

Could someone tell me if a Fill has to be called first before a
DataAdapter
can Update a DataTable object? Is that a must? Isn't that kind of waste
of
time?

Say, I have a DataTable already filled from before. For some reasons
(coz
I
am doing ASP.NET app), the adapter object is gone after a round

trip.

I
stored my DataTable somewhere that's persistent (ViewState). So

the
data

in
DataTable can be modified and ready to be update. However,

whenever

I it
up