.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> C# >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Exploring 5 Steps For Executing Bulk Copy Operations

Posted By:Ravi Ranjan Kumar       Posted Date: June 24, 2011    Points: 200    Category: C#    URL: http://www.dotnetspark.com  

This article enable you to learn the basic steps need to create BulkCopy operation using C#
 

Introduction

The simplest approach to perform a SQl server bulk copy operation is to perform a single bulk copy operation against a database. by default, a bulk copy operation is performed as an isloated operation; the copy operation occurs in a non-trusted way with no opportunity for rolling it back.

Steps For Performing BukCopy 
To Perform the Bulk copy operation you need to perform the following steps :
1) Connect to the source server to get the data to be copied. this can be done with the following code 
using(SqlConnection Sourceconnection = new SqlConnection(connectionstring))
            {
                Sourceconnection.Open();
SqlCommand sourcecomand= new SqlCommand("your Command for select values from sourcetable", Scon); SqlDataReader dr = sourcecomand.ExecuteReader(); //----- }
In the preceding code snippet sourcetable is the name of the source table from which the data is to be copied.

2) Connect to the destination server. This can be done with the following code 
using (SqlConnection Destinationconnection = new SqlConnection(ConnectionString))
                {
                    Destinationconnection.Open();
                }
3) Create a SqlBulkCopy object. This can be done with the following code 
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(Destinationconnection))
                    
4) Set the Destinationtablename property to the name of the target table. This can be done withe following code
bulkcopy.DestinationTableName = "Destinationtable";
5) Call the WriteToServer() method. This can be done with following code :
bulkcopy.WriteToServer(dr);
In the preceding code snipper, dr is an object of the SqlDataReader class.

Final Illustration :- 
using(SqlConnection Scon = new SqlConnection("Data Source=.;Initial Catalog = YourDatabaseName;uid =sa;pwd = yourPassword"))
            {
                Scon.Open();
                SqlCommand Scmd = new SqlCommand("Select * from Sourcetable", Scon);
                SqlDataReader dr = Scmd.ExecuteReader();
                using (SqlConnection Dcon = new SqlConnection("Data Source =.;Initial Catalog = YourDatabaseName;uid =sa;pwd = yourPassword"))
                {
                    Dcon.Open();
                    using (SqlBulkCopy bulkcopy = new SqlBulkCopy(Dcon))
                    {
                        bulkcopy.DestinationTableName = "Destinationtable";
                        try
                        {
                            bulkcopy.WriteToServer(dr);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message.ToString());

                        }
                        finally
                        {
                            dr.Close();
                        }
                    }
                }

Information 
sorry for inconvenience about Source Code. its coming soon   :)

Point to Be Noted :
Before performing bulkcopy operation be confirmed and you need to ensure that the structure of the table has been created as same as Destination table name. otherwise you will get an error messages. because its recommended that the source and target column datatype must matches. the the datatypes not match, SqlBulkCopy tried to convert each source value to the target datatype. such conversion can affect the performance of the application. 
 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend