Copying data in bulk (using SqlBulkCopy)

by Jagadish Pulakhandam on 9/6/2011 12:33:45 PM
Rated 0 from 0 votes
Brief: Demonstrates on using "SqlBulkCopy" to copy data in bulk
Posted to: Manipulating (insert/update/delete) Data using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Following code demonstrates on copying data from one server to another in bulk using ADO.NET's SqlBulkCopy class.  Following is the source code:

01.using System;
02.using System.Collections.Generic;
03.using System.ComponentModel;
04.using System.Data;
05.using System.Drawing;
06.using System.Linq;
07.using System.Text;
08.using System.Windows.Forms;
09.using System.Data.SqlClient;
10. 
11.namespace CSCopyingInBulk
12.{
13.    public partial class Form1 : Form
14.    {
15.        string CONNSTR_SRC = "Data Source=.\\sql2k8; Initial Catalog=Sample; User Id=sa; Password=eXpress2008";
16.        string CONNSTR_DEST = "Data Source=.\\sql2k8; Initial Catalog=Northwind; User Id=sa; Password=eXpress2008";
17. 
18.        public Form1()
19.        {
20.            InitializeComponent();
21.        }
22. 
23.        private void btnCopy_Click(object sender, EventArgs e)
24.        {
25.            try
26.            {
27.                using (SqlCommand cmd = new SqlCommand("SELECT * FROM emp", new SqlConnection(CONNSTR_SRC)))
28.                {
29.                    using (SqlConnection cnDest = new SqlConnection(CONNSTR_DEST))
30.                    {
31.                        using (SqlBulkCopy bc = new SqlBulkCopy(cnDest))
32.                        {
33.                            bc.DestinationTableName = "Emp";
34.                            cmd.Connection.Open();
35.                            SqlDataReader rdr = cmd.ExecuteReader();
36.                            cnDest.Open();
37.                            bc.WriteToServer(rdr);
38.                        }
39.                    }
40.                }
41.                MessageBox.Show("Copied succesfully!");
42.            }
43.            catch (Exception ex)
44.            {
45.                MessageBox.Show(ex.Message);
46.            }
47.        }
48.    }
49.}

Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following