SqlBulkCopy Wcf Rest Moving Data between 2 sql server tables

c# rest sqlbulkcopy wcf

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. Code in chsarp

Connections

Accepted Answer

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();
                        }
                    }
                }
            }
        }
    }
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why