Bulk loading data from one table to another database (C#) : SqlBulkCopy « ADO.net Database « ASP.NET Tutorial

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

<script runat="server">
    protected void btnBulkCopy_Click(object sender, EventArgs e)
    {
        String YourDatabaseConString;
        String NorthWindConString;
        SqlConnection YourDatabaseCon = new SqlConnection();
        SqlConnection NorthwindCon = new SqlConnection();

        SqlCommand YourDatabaseCom = new SqlCommand();
        SqlDataReader YourDatabaseReader;

        YourDatabaseConString = ConfigurationManager.ConnectionStrings["DSN_YourDatabase"].ConnectionString;
        
        NorthWindConString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;

        YourDatabaseCon.ConnectionString = YourDatabaseConString;
        YourDatabaseCom.Connection = YourDatabaseCon;
        YourDatabaseCom.CommandText = " SELECT ID, First_Name, Last_Name, 'YourDatabase' as Source FROM MailingList_Temp ";
        YourDatabaseCom.CommandType = CommandType.Text;

        YourDatabaseCom.Connection.Open();

        SqlBulkCopy NorthWindBulkOp;
        NorthWindBulkOp = new SqlBulkCopy(NorthWindConString, 
                                SqlBulkCopyOptions.UseInternalTransaction);

        NorthWindBulkOp.DestinationTableName = "Employees";

        NorthWindBulkOp.ColumnMappings.Add("Id", "EmployeeID");
        NorthWindBulkOp.ColumnMappings.Add("First_Name", "FirstName");
        NorthWindBulkOp.ColumnMappings.Add("Last_Name", "LastName");

        SqlBulkCopyColumnMapping JobTitleColMap;
        JobTitleColMap = new SqlBulkCopyColumnMapping("Source", "Title");

        NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap);
        NorthWindBulkOp.BulkCopyTimeout = 500000000;

        NorthWindBulkOp.SqlRowsCopied += 
            new SqlRowsCopiedEventHandler(OnRowsCopied);

        NorthWindBulkOp.NotifyAfter = 1000;

        YourDatabaseReader = YourDatabaseCom.ExecuteReader();

        try
        {
            NorthWindBulkOp.WriteToServer(YourDatabaseReader);
        }
        catch (Exception ex)
        {
            lblResult.Text = ex.Message;
        }
        finally
        {
            YourDatabaseReader.Close();
        }
    }

    private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
    {
        lblCounter.Text += args.RowsCopied.ToString() + " rows are copied<Br>";
    }

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Bulk Loading Large Volume Data</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnBulkCopy" Runat="server" Text="Start Bulk Copy" />&nbsp;
        <br />
        <br />
        <asp:Label ID="lblResult" Runat="server"></asp:Label>
        <br />
        <br />
        <asp:Label ID="lblCounter" Runat="server"></asp:Label>    
    </div>
    </form>
</body>
</html>

File: Web.config

<configuration>

  <connectionStrings>
        <add name="DSN_Northwind" 
             connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
             providerName="System.Data.SqlClient" />

        <add name="DSN_YourDatabase" 
             connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=True"
             providerName="System.Data.SqlClient" />    </connectionStrings>

</configuration>
18.53.SqlBulkCopy
	18.53.1.	Bulk loading data from one table to another database (C#)
	18.53.2.	Bulk loading data from one table to another database (VB)