SqlBulkCopy Wcf Rest Moving Data between 2 sql server tables

Question

I am trying to move data from some tables from one sql server database to another sql server database. I am planning to write a wcf rest service to do that. I am also trying to implement this using SQLBulkCopy. I am trying to implement the below functionality on button click. Copy Table1 data from source sql server to Table 1 in destination sql server. Same as for table 2 and table 3. I am blocked on couple of things. Is sql bulk copy a best option with wcf rest service to transfer data. I was asked not to use ssis in this task. If there is any exception while moving data from source to destination, then the destination data should be reverted back. It is something like transaction. How do I implement this transaction functionality. Any pointer would help.

My the rest service as this is not even using a service to copy tha data — 3dd, Jul 27 '15 at 15:33
Well you can wrap your code inside a `TransactionScope`.That might help.And also, why a `REST` service? — Amit Kumar Ghosh, Jul 29 '15 at 05:53

DDan · Accepted Answer · 2015-08-01T00:30:53.410

Based on the info you gave, your solution would be something like the sample code I inserted. Pointers for the SQLBulkCopy: BCopyTutorial1, BCopyTutorial2 SQLBulkCopy with transaction scope examples: TrasactionBulkCopy

My version is a simplified version of these, based on your question.

Interface:

[ServiceContract]
public interface IYourBulkCopyService {

    [OperationContract]
    void PerformBulkCopy();
}

Implementation:

public class YourBulkCopyService : IYourBulkCopyService {

    public void PerformBulkCopy() {
        string sourceCs =  ConfigurationManager.AppSettings["SourceCs"];
        string destinationCs =  ConfigurationManager.AppSettings["DestinationCs"];

        // Open a sourceConnection to the AdventureWorks database. 
        using (SqlConnection sourceConnection = new SqlConnection(sourceCs) {
            sourceConnection.Open();

            //  Get data from the source table as a SqlDataReader.         
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT * FROM YourSourceTable", sourceConnection);
            SqlDataReader reader = commandSourceData.ExecuteReader();

            //Set up the bulk copy object inside the transaction.  
            using (SqlConnection destinationConnection = new SqlConnection(destinationCs)) {
                destinationConnection.Open();

                using (SqlTransaction transaction = destinationConnection.BeginTransaction()) {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                               destinationConnection, SqlBulkCopyOptions.KeepIdentity,
                               transaction)) {
                        bulkCopy.BatchSize = 10;
                        bulkCopy.DestinationTableName =
                            "YourDestinationTable";

                        // Write from the source to the destination. 
                        try {
                            bulkCopy.WriteToServer(reader);
                            transaction.Commit();
                        } catch (Exception ex) {
                            // If any error, rollback
                            Console.WriteLine(ex.Message);
                            transaction.Rollback();
                        } finally {
                            reader.Close();
                        }
                    }
                }
            }
        }
    }
}

I don't think the questioner wants to expose an endpoint that directly connects to both databases. — LSU.Net, Aug 04 '15 at 14:50
Expose where? It fully depends on architecture of the system. It could be a business layer consisting of multiple application servers, where this operation has to be shared between them. That would mean no exposure of it to the presentation layer whatsoever. I agree it shouldn't be a user interface where the end-user can directly invoke this operation. The OP asked for a WCF service which starts this operation, I set it in a simplified frame (as I stated in my answer) which gives space for that. — DDan, Aug 04 '15 at 16:42

SqlBulkCopy Wcf Rest Moving Data between 2 sql server tables

1 Answers1