Update dataset/datatable disconnected?

  • Thread starter Thread starter Pipo
  • Start date Start date
P

Pipo

Hi,

Is it possible to update a dataset/dattable/dataview disconnected, without
looping?
Situation:
I'm performing a conversion.
I read a bulkdata from a SQL server-database, process the data en write that
back to a Oracle-Database.
I'm not allowed to change the 'core' data (SQL-server).
But I need to convert some values before writing it to Oracle because of
contraints.
I can't modify the Oracle database either.
For now I'm looping through the dataset replacing everything I need.
Is there a way to do this at ones?

I mean it is a disconnected datasource with it's relations etc, right, so
something like: 'UPDATE MyDataset SET MyDBValue = x WHERE MyDBValue = y'
,etc

TIA,

Pipo
 
Pipo,

Not that I know however how many milliseconds you think it takes to loop
through a dataset table which contains 100000 rows on a normal PC. (Not
deleting because that is terrible slow)

When there was a command by the way, than it would be done behind the scene
in a loop, in a dataset is the smallest item an item, what is itself an
object.

However what you can do of course is create first a dataview where you have
set a rowfilter for that

In VBNet code roughly typed here.

\\\
dim dv as ds.Tables("myTable")
dv.rowfilter = "myvalue = '" & y & "'"
For each drv as datarowview in dv
drv("myDBValue") = x 'where you probably have to cast that
Next
///

I hope this gives an idea?

Cor
 
Thanks Cor,

You're right, I do it now already with the datarowview.
But I wanted to be possitive that there wasnt an other (easier/better) way
of doing this.

Thanks for your time, Cor
 
Pipo,

Instead of passing in each row, row by row, You could instead pass rows as
Binary (Oracle only), or XML (Both Oracle & SQL Server)

In either case, you end up making your solution very database specific. As
far as SQL Server goes, check out SqlXml that comes with MDAC in .NET 1.1
(Well it's really com interoped), and System.Data.SqlXml with .NET 2.0).

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

Serious,

What is in your opinion than the advantage over those 4 or 5 rows (depending
on the program language) of code?

Keep in mind that datarow items are objects on there own.

I don't see it however maybe you can enlighten me.

Cor
 
Cor,

It really depends on how many rows. If you go row by row, as in pure SQL,
then you are hitting the database multiple times, and the logs of the
database are getting bigger by an order of a lot. So the small perf hit you
pay to hold the larger objects, is worth it in my opinion.

The downside obviously is that your solution now becomes very very db
specific, which in most enterprise wide projects is not a big deal since
they are married to a major db anyway (oracle/sql server/db2).

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
Hi Sahil,
It really depends on how many rows. If you go row by row, as in pure SQL,
then you are hitting the database multiple times, and the logs of the
database are getting bigger by an order of a lot. So the small perf hit
you pay to hold the larger objects, is worth it in my opinion.

The downside obviously is that your solution now becomes very very db
specific, which in most enterprise wide projects is not a big deal since
they are married to a major db anyway (oracle/sql server/db2).
Click to expand...

It is not that late in the US.

See the topic, we are talking about a disconnected datatable, what do you
mean

:-)

Cor
 
Back
Top