Moving Data with DataSets/ADO.NET - looking for examples

[kellygreer1]

I'm looking for some examples of:
1) Loading a DataSet with one dataadapter and connection.
2) Reconnecting that DataSet to another dataadapter / new connection
3) Pushing the DataRows back to the database as if they were new rows.

For my application of this... the Tables are identical in design (same
DDL), but are in seperate SQL Servers. As another small issue I am
wondering what the correct way to get the "target table" to accept my
primary key field also.... which in this case is an auto-incrementing
int. Do you issue a SET IDENTITY_INSERT tablename OFF command to the
database?

On a side note any good ADO.NET Tips and Tricks / Hacks books this
group would recommend? Any that would maybe cover my question?

Thanks in advance,

Kelly Greer
(e-mail address removed)
replace nospam with yahoo

 

[Cowboy \(Gregory A. Beamer\)]

You can set IDENTIY_INSERT on, but the larger problem is one of
archtiecture. Is this going to be your primary method of moving new records
to another database? If so, I would seriously consider something like DTS
(Integration Services in SQL 2005) or even replication. Using a dataset to
transfer is a bit kludgy.

Since what you are attempting goes a bit against the grain, your best bet,
if you must go this route, is to set up a stored procedure that does the
identity insert on for you. You then attempt the insert. As long as this
database is read only from the app perspective, this will solve your
problem.

NOTE: It is not a simple matter of connecting a dataset to each side, as the
DataSet from the old DB will have the rows marked OLD, which is incorrect.
You can kludge this by making the UPDATE proc actually insert, but that
means you end up with a "real" sproc for update and a "fake"/transfer sproc
for update (if you are updating normally in the second DB and not having it
as read only). This means you have to move rows from one dataset to another
to flag them as new, if you do not want to kludge things. Is replication
sounding simpler?

Your other option is a custom update mechanism, where you fire off the
updates yourself. One option is to strip the xmlns="" portion of the DataSet
header and then use OPEN XML in a sproc to create an in memory "temp" table
for your inserts. You can then run IDENTITY_INSERT ON and cover yourself
that way.

NOTE: NO matter what road you take, if the second DB is ever inserted
outside of your mechanism, you will eventually end up with ID clashes, which
is not good. Make sure you have some form of exception handling for this
eventuality.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Guest]

Take a look at http://msdn2.microsoft.com/en-us/library/ms187042.aspx.
This gives an overview of bulk Import in sql server.
Also, http://msdn2.microsoft.com/en-us/library/ms190923.aspx, which
describes Copying Data between sql server instances.
Finally, http://msdn2.microsoft.com/en-us/library/ms186335.aspx, titled
Keeping Identity Values When Bulk Importing Data .
Finally, finally, if you are using ADO.NET 2.0, you could consider the
SQLBulkCopy class, described at
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx.

 

[kellygreer1]

Thanks this should give me some ideas. I'll read these tonight.
I was hoping there was a way to do it via DataSets ... because I won't
also mind being able to sync through a firewall via port 80 using a web
service. Maybe passing 5K or 10K records at a time. Most tables (80%)
are 100 records to 300 records.

Any suggestions for using a DataSet (or not)... but still transfering
the data via a web service?

Thanks for your help,
Kelly Greer
(e-mail address removed)
replace nospam with yahoo

 

[kellygreer1]

Maybe I got lucky here....
1) I am using .NET 2.0 locally and on a remote server, so BulkCopy is
an option.
2) looks like the WriteToServer method will accept a DataTable as a
parameter. Should be able to pass the dataset and then process
datatables on the server side..... sweet!

I'll let you guys know how it goes,
Kelly