Generic data transfer

[Vijay]

Hi,
I need to transfer data from various external sources to any database
(Oracle, SQL Server) generically. The database name, field names &
table names will be in a configuration file. The possible SQL
statements would be Inserts, Updates and deletes.


Which is the best way to go about it using ADO.NET? Kindly advise.

Thanks,
VJ

 

[Mark Rae]

Which is the best way to go about it using ADO.NET? Kindly advise.

Is this something which needs to be done regularly? If so, I'd consider
writing Data Transformation Services packages for each transfer and
scheduling them as required.

 

[Cor Ligthert]

Vijay,

When you are lazy and performance is not real important use OLEDB when that
is not than create for every database use the best class for that.

http://msdn.microsoft.com/library/d...en-us/cpref/html/frlrfsystemdatasqlclient.asp

http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOracleClient.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDb.asp

I hope this helps,

Cor

 

[Sahil Malik [MVP]]

ADO.NET is not the right tool for this job - especially if you have tonnes
of data to be transferred. The only thing within ADO.NET that comes close to
being suitable is SqlBulkCopy and that is only in ADO.NET 2.0.

DTS is a better choice IMO.

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

 

[Vijay]

Hi guys,

Thanks for the quick response.

This component has to be build generic such that it will only work on
configurations for DB, Table, Column names & mappings.

The Sql commands used for transfer would be Insert/Update/Delete with
conditions wherever appropriate (e.g. Where <Column Name> = ... for
Updates/Deletes). (conditions also have to b configurable !!! (it's
manageable though))

I intend to use OLEDB provider for Oracle & .NET SQLCLient provider
for SQL server depending upon target DB specified in configuration.

To perform the transfer operations I was contemplating using .NET
DataSet. e.g. loading empty datatable(s) from the target table(s) so
as to have the table layout in memory & then add these to dataset &
use dataset's intrinsic's ability to fire Insert/Update/Delete
commands (via. SQLDataAdapter/OLEDBDataAdapter). The Dataset might
prove useful even while performing cascading updates/Deletes for
relations.

Kindly comment on the above proposed solution...

Cheers,
Vijay

 

[Sahil Malik [MVP]]

Again - the answer is the same - DTS is a better choice.

Dataset hierarchical relationship saving is not magic. You can't just call
DataAdapter.Update(dataset) and expect ADO.NET to take care of everything
for you - magically - that is simply impossible to do.

Here would be a typical sequence of events -

Begin Tran
Disable Constraints
DTS Transfer
Enable Constraints
Commit

How much data are we working with BTW?

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

 

[Cor Ligthert]

Vijay,

When you have a generic, however, very selective data transfer to do, than I
would not use a dataset. Datareaders and proper commands for update,
deletes(what I don't understand) and insert looks for me in the way you
describe your problem more in place.

Just my thought on what you wrote ,

Cor