SQLBulkCopy does not work

sqlbulkcopy vb.net


This is my first attempt to use sqlbulkcopy class. When I run it, nothing happens, no errors whatsoever. Please help me.

Here is my code:-

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration.ConfigurationManager

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim connectionString As String = GetConnectionString()
    ' Open a connection 
    Using sourceConnection As SqlConnection = New SqlConnection(connectionString)

        ' Perform an initial count on the destination table.
        Dim commandRowCount As New SqlCommand("SELECT COUNT(*) FROM dbo.BRANCH;", sourceConnection)
        Dim countStart As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
        Console.WriteLine("Starting row count = {0}", countStart)

        ' Get data from the source table as a SqlDataReader.
        Dim commandSourceData As SqlCommand = New SqlCommand("select * from BRANCH", sourceConnection)
        Dim reader As SqlDataReader = commandSourceData.ExecuteReader

    End Using
End Sub

Private Function GetConnectionString() As String
    Return "Data Source=;Initial Catalog=SOURCEDB;User ID=sa;Password="
End Function

Private Function GetDestString() As String
    Return "Data Source=;Initial Catalog=DESTINATIONDB;User ID=sa;Password="
End Function

Public Sub UpdateHQDB(ByVal reader)
    Dim DestConString As String = GetDestString()
    ' Open a connection 
    Using DestinationConnection As SqlConnection = New SqlConnection(DestConString)

        ' Perform an initial count on the destination table.
        Dim DestcommandRowCount As New SqlCommand("SELECT COUNT(*) FROM dbo.BRANCH;", DestinationConnection)
        Dim DestcountStart As Long = System.Convert.ToInt32(DestcommandRowCount.ExecuteScalar())
        Console.WriteLine("Starting row count at destination = {0}", DestcountStart)

        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(DestinationConnection)
            bulkCopy.DestinationTableName = "dbo.BRANCH"

                ' Write from the source to the destination.

            Catch ex As Exception

                ' Close the SqlDataReader. The SqlBulkCopy
                ' object is automatically closed at the end
                ' of the Using block.
            End Try
        End Using

        ' Perform a final count on the destination table
        ' to see how many rows were added.
        Dim countEnd As Long = _
        Console.WriteLine("Ending row count = {0}", countEnd)
        Console.WriteLine("{0} rows were added.", countEnd - DestcountStart)

        Console.WriteLine("Press Enter to finish.")
    End Using
End Sub
End Class

Popular Answer

i would say that use NotifyAfter and SqlRowsCopied event of SqlBulkCopy to troubleshoot

following code in c#

        sb.NotifyAfter = 1;
        sb.SqlRowsCopied += new SqlRowsCopiedEventHandler(sb_SqlRowsCopied);

    void sb_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
       // See if this event fired

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why