none
Best Way to Copy Data From Server A Table A1 to Server B Table B1 w/ C# and ADO.NET RRS feed

  • Question

  • Hello ... new guy here ... thank you for your patience.  I've seen lots of ADO.NET examples (including the 101 C# examples), but I have not found one "exactly" like this...and I'm looking for the best practice...

    Here's my environment:

    SQL Server A has a database that contains table A1
    SQL Server B has a database that contains table B1

    Table A1 and B1 have an identical structure (columns, datatypes, etc.)

    Rows periodically flow into table A1 from various SQL Server sources.

    I would like to create a C# application (VS 2005) using ADO.NET that simply copies the rows in A1 (the source) and appends them into Table B1 (the target) on SQL Server B.  Note:  for various reasons I don't want to use SSIS.

    =====

    I get the general drift of setting up two connection managers ... ConnectionManagerA for the SQL Server A source and ConnectionManagerB for the SQL Server B target databases.

    I also presume I'll need to set up a DataAdapterA for ConnectionManagerA.

    I suppose I'll need a strongly typed DataSetA with a table in it representing Table A1.  I think I can fill the table in DataSetA with all the latest rows in SQL Server A Table A1.

    OK, so now at this point, what's the best "plumbing" strategy to take those rows, as is, and have them all inserted into SQL Server B, Table B1?

    --  Is there any way to take those rows in DataSetA Table A1 and have them directly inserted into the target SQL Server Table B1 via ConnectionManagerB?

    --  Should I copy all of the DataSetA Table A1 rows into another DataSet and/or table representing Table B1, followed by some type of update command?  If so, how?

    -- Should I give in and try to push this one down to the intern?  : )

    Enquiring minds want to know the best practice!

    Thanks so much!

    Doug B
    Monday, October 31, 2005 10:04 PM

Answers

  • Hi,

    Well, I kinda overlooked the two-servers scenario (i assumed that you meant two databases). Sorry for that. I guess you can achieve that by having a datareader on your TableA and populate a newly created datatable with the same schema. Add a DataAdapter in which the INSERT statement would insert new values into the TableB. you can then use this adapter to update the newly added values in the datatable thus adding the data to the second server. Can't seem to find any other appropriate approach in this scenario...

     

     

    cheers,

    Paul June A. Domag

    Wednesday, November 2, 2005 4:58 AM

All replies

  • Hello

    This looks like a good sceario for replication. Maybe leave the processing to SQL Server?
    Tuesday, November 1, 2005 12:41 AM
  • Good point, but that won't do the trick for a number of reasons (that are not that exciting so I won't go into them ...)

    I'm just looking for "the best way" to go from A to B via C# and ADO.NET. 

    But thank you so much for your suggestion!

    Doug

    Tuesday, November 1, 2005 1:22 AM
  • Doug - I moved this post to the .NET Framework Data Access and Storage forum.  They should be able to help you with your ADO.net question.

    Thanks!
    Karen
    Visual C#
    Tuesday, November 1, 2005 4:31 AM
  • OK, any help is appreciated.

    Doug
    Tuesday, November 1, 2005 5:53 PM
  • Hi,

    You can just execute an sql statement to do this.

    INSERT INTO B.dbo.B1 SELECT * FROM A.dbo.A1

    You can execute this in code by using a command object...

     

    cheers,

    Paul June A. Domag

    Wednesday, November 2, 2005 1:41 AM
  • OK, I read up on the SqlCommand class and have a better understanding on executing a command without the need to use a DataSet, DataAdapter etc.  This looks helpful in a lot of situations.  Cool!

    I may be reading this incorrectly, but it *looks* like the SqlCommand only allows for a single connection manager to a single SQL Server engine.  In my scenario, I have two different SQL Servers ... A and B, running on two different hosts.  I'm presuming that I would need two different connection managers to handle this.  So, I'm not quite sure, but it seems that SqlCommand can only execute against one Connection at a time.  The SQL you suggest looks like it would work great if A and B were databases on the same SQL Server.  I can't get away with setting up any links between the SQL Server tables, etc.

    So, I think I'm still looking for the silver bullet strategy .... move rows from a table in one SQL Server to a similar table located in a second SQL Server using C#.  From reading the docs I can come up with a way to brute force it, but I was hoping to find the "best practice" for this.

    Thanks everybody for helping the new guy!

    Doug

    Wednesday, November 2, 2005 2:25 AM
  • Hi,

    Well, I kinda overlooked the two-servers scenario (i assumed that you meant two databases). Sorry for that. I guess you can achieve that by having a datareader on your TableA and populate a newly created datatable with the same schema. Add a DataAdapter in which the INSERT statement would insert new values into the TableB. you can then use this adapter to update the newly added values in the datatable thus adding the data to the second server. Can't seem to find any other appropriate approach in this scenario...

     

     

    cheers,

    Paul June A. Domag

    Wednesday, November 2, 2005 4:58 AM
  • Paul ....

    Thank you so much for your suggestions!  I'm new at some of this, and your ideas led me on the research trail to the right documentation. 

    It looks like there's a new feature in .NET 2.0 called a SQLBulkCopy Class that lets you take data directly from a source .NET DataTable (from Server A) and use the high speed bcp library calls to load it directly into the target Server B.  This is much better than the brute force method I had in mind.

    I think I can take some of your insight and put it together with this new feature and do the job.

    Thanks again so much for your helpful input.

    Doug B  Big Smile
    Wednesday, November 2, 2005 5:29 PM