C# copy data between databases

Question

What is the fastest method for copying data between databases in c#? I want to create c# database procedure than accepts query string as parameter and inserts data to another database (sql query and destination table will have the same columns).

I use SQLBulkCopy right now but causes some problems.

can you tell us what problems? because i wrote such project and used SQLBulkCopy . it works great. — AEMLoviji, Apr 29 '11 at 10:42
"causes some problems" - what problems? that approach is pretty good, so if you explain the problem maybe we can fix that — Marc Gravell, Apr 29 '11 at 10:42
"I use SQLBulkCopy right now but causes some problems." - Really? and what might they be? (Crystal ball is broken) — Mitch Wheat, Apr 29 '11 at 10:42
Have you tried to backup the DB and restore it to the second DB? or ar the DB systems different? — Gabriel, Apr 29 '11 at 10:43
The problem is when 2 users try to execute procedure in the same time the procedure crashes and it dose not work until next dll deployment (restart does not help). — spdro, Apr 29 '11 at 10:48
Backup and restore wont help. I need to copy data automatically every 30 min — spdro, Apr 29 '11 at 10:51

score 0 · Answer 1 · edited May 23 '17 at 10:24

I know you've asked about C# in your post, but you might want to consider a couple of other methods, using the stored procedure you are about to create. All of the below would be in the stored procedure itself.

Have you considered a T-SQL approach using OPENROWSET() That way, you never have to pull the data back over to C# and send it back over to SQL server. You will find some recommendations from Microsoft here and here is the specific link using OPENROWSET()

If you want to use C# still, you can also use SSIS, which has an ability to copy data in bulk mode between servers, and you can call it from C# using the appropriate package you'd have to create (visual workflow). Please see this thread for details.

C# copy data between databases

1 Answers1