SQL Bulk Copy with Mysql Datatable to MsSql Database

SQL Bulk Copy with Mysql Datatable to MsSql Database

SQLClient and MYSQLCLient Class are used for This Sample 

Add namespace for SqlBulkCopy

App Config File Changes

 

     

 

Form level Changes to be Done

Public Class frmMySQLDataImportTest

    Dim cnMySQL As New MySql.Data.MySqlClient.MySqlConnection

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

       Dim sqlTrn As SqlTransaction = Nothing

        Try

                    'Connection String is Stored in Application Config File

Dim strConnection As String =  System.Configuration.ConfigurationManager.

ConnectionStrings("MySql").ToString

            cnMySQL = New MySqlConnection(strConnection)

            cnMySQL.Open()

            sqlTrn = cnSql.BeginTransaction

            Dim dt As New DataTable

            Dim Cmd As New MySqlCommand("Select * from City", cnMySQL)

            dt.Load(Cmd.ExecuteReader)

            Dim dr As New DataTableReader(dt)

            If dt.Rows.Count <> 0 Then

                'To Work with Bulk Copy Concept Table Structure has to be Same. 

                Dim bulkCopy As New SqlBulkCopy(cnSql, SqlBulkCopyOptions.KeepIdentity, sqlTrn)

                bulkCopy.DestinationTableName = "dbo.MySql_Data_City"

                Try

                    ' ******  Write from the source to the destination. ******

                    bulkCopy.WriteToServer(dr)

                Catch ex As Exception

                    Console.WriteLine(ex.Message)

                Finally

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

                 dr.Close()

                End Try

            End If

            sqlTrn.Commit() 

            MessageBox.Show("Records Add and updated")

        Catch ex As MySqlException

            If IsNothing(sqlTrn.Connection) = False Then sqlTrn.Rollback()

            MessageBox.Show("Error connecting to the server: " + ex.Message)

        Catch ex1 As Exception

            If IsNothing(sqlTrn.Connection) = False Then sqlTrn.Rollback()

            MessageBox.Show("Error connecting to the server: " + ex1.Message)

        End Try

    End Sub

End Class