Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to perform bulk copy from one database table to another in ASP.NET

Author: Alanna Kremer
Download Source Code : 1323_BulkCopy.zip

You can use SqlBulkCopy object to copy data from one SQL Server database table to another table. This article will show how we can perform this task.

 
SQLBulkCopy class is used to write data only to SQL Server database tables. The source database table can be from same SQL Server database or from different Server. For example, you can copy bulk data from Oracle database table to SQL Server database table using SQLBulkCopy object. You need to load data into a DataTable, IDataReader or DataRow[] array instance to copy it to SQL Server database table.
 
We will use PUBS Sample database for our example to copy data. I will get data from “publishers” table of PUBS database and copy it in another table “BulkCopyPublishers”. You have to create “BulkCopyPublishers” table and make sure that this table must have same schema as “publishers” table. You can download PUBS Sample database and use for this example or follow example and do it for another database. You can use any other Database Server to get data and copy this data to SQL Server database table using SQL Server table. 

Let’s start our example. You can download source code.

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add code below in your Web Form

    <asp:Button ID="Button1" runat="server" Text="Copy Data"
                onclick="Button1_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" ></asp:Label>

     
  6. Open code behind file and include following namespace.

    C#

    using System.Data.SqlClient;
     
    VB.NET

    Imports System.Data.SqlClient
     
  7. Now write code below in Button click event method in code behind file

    C#

    protected void Button1_Click(object sender, EventArgs e) {
        string sqlQuery = "Select * From publishers";
        string connString = "Data Source=Local;Initial Catalog=PUBS;Integrated Security=True";
        SqlConnection sourceConn= new SqlConnection(connString);
        SqlConnection destConn = new SqlConnection(connString);
        sourceConn.Open();
        destConn.Open();
        SqlCommand command = new SqlCommand(sqlQuery, sourceConn);
        SqlDataReader reader = command.ExecuteReader();
     
        SqlBulkCopy bulkCopy = new SqlBulkCopy(destConn);
        bulkCopy.DestinationTableName = "BulkCopyPublishers";
        bulkCopy.WriteToServer(reader);
        bulkCopy.Close();
        sourceConn.Close();
        destConn.Close();
     
        Label1.Text = "Data copied successfully";
     
    }
     
    VB.NET
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sqlQuery As String = "Select * From publishers"
        Dim connString As String = "Data Source=Local;Initial Catalog=PUBS;Integrated Security=True"
        Dim sourceConn As New SqlConnection(connString)
        Dim destConn As New SqlConnection(connString)
        sourceConn.Open()
        destConn.Open()
        Dim command As New SqlCommand(sqlQuery, sourceConn)
        Dim reader As SqlDataReader = command.ExecuteReader()
     
        Dim bulkCopy As New SqlBulkCopy(destConn)
        bulkCopy.DestinationTableName = "BulkCopyPublishers"
        bulkCopy.WriteToServer(reader)
        bulkCopy.Close()
        sourceConn.Close()
        destConn.Close()
     
        Label1.Text = "Data copied successfully"
    End Sub
     
    First I have queried for entire data from publishers table then I have written connection string. You have to write your own connection string here. If your source and destination Servers are different then you need to write connection strings separately for both. I have created source and destination connections for SQL Server by passing same connection string. You need to create source and destination connections separately no matter you want to copy data from same Database Server or from different Database Servers.

    I have created a SQLCommand instance and then I have created a SqlDataReader instance and fetched data from source database table. SqlDataReader is better option to in our case because it is read-only forward-only object and it is fast way to forward data.

    Now I have created a SqlBulkCopy instance by providing destination SqlConnection object. You need to provide the destination table name to SqlBulkCopy object. Now I have called the WriteToServer() method of SqlBulkCopy object by passing SqlDataReader object as parameter.

    Now close SqlBulkCopy object and source and destination connections. 
     
  8. Press Ctrl+F5 to see website in browser. Click on button and see the result in SQL Server. Open BulkCopyPublishers Table of PUBS Database. You will see copied data in the table.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to Allow Alphanumeric only in TextBox in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net