ADO Net Database ASP.Net Tutorial

  1. Home
  2. ASP.Net Tutorial
  3. ADO Net Database
  4. Bulk loading data from one table to another database (VB)
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

    Sub btnBulkCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBulkCopy.Click
        Dim YourDatabaseConString As String
        Dim NorthWindConString As String
        Dim YourDatabaseCon As SqlConnection = New SqlConnection()
        Dim NorthwindCon As SqlConnection = New SqlConnection()
        Dim YourDatabaseCom As SqlCommand = New SqlCommand()
        Dim YourDatabaseReader As SqlDataReader
        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()
        Dim NorthWindBulkOp As SqlBulkCopy
        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")
        Dim JobTitleColMap As SqlBulkCopyColumnMapping
        JobTitleColMap = New SqlBulkCopyColumnMapping("Source", "Title")
        NorthWindBulkOp.ColumnMappings.Add(JobTitleColMap)
        NorthWindBulkOp.BulkCopyTimeout = 500000000
        AddHandler NorthWindBulkOp.SqlRowsCopied, _
                    New SqlRowsCopiedEventHandler(AddressOf OnSqlRowsCopied)
        NorthWindBulkOp.NotifyAfter = 1000
        YourDatabaseReader = YourDatabaseCom.ExecuteReader()
        Try
            NorthWindBulkOp.WriteToServer(YourDatabaseReader)
        Catch ex As Exception
            ' Write error handling code here
            lblResult.Text = ex.Message
        Finally
            YourDatabaseReader.Close()
        End Try
    End Sub
    Private Sub OnSqlRowsCopied(ByVal sender As Object, _
            ByVal args As SqlRowsCopiedEventArgs)
        lblCounter.Text += args.RowsCopied.ToString() + " rows are copied
"
    End Sub



    Bulk Loading Large Volume Data


    
    

         
        
        
        
            
    

    


File: Web.config

  
                     connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
             providerName="System.Data.SqlClient" />
                     connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=True"
             providerName="System.Data.SqlClient" />