Sql Bulk Write

Performs a bulk insert operation from a DataTable into a specified SQL Server table using SqlBulkCopy .

This function maps each column from the DataTable to the destination SQL table by column name. If a known SqlException related to column length mismatch occurs, the error message will include the column name and maximum allowed length. This function uses reflection to extract internal metadata from SqlBulkCopy to provide detailed error diagnostics.

    Public Function SqlBulkWrite(MyConnection As SqlConnection, 
                                 ByVal DataTable As DataTable, 
                                 ByVal DestinationTbl As String, 
                                 Optional ByVal BatchSize As Integer = 1000) As String

        Dim szMsg As String = String.Empty

        If Not MyConnection.State = ConnectionState.Open Then MyConnection.Open()

        Using sbc As New SqlBulkCopy(MyConnection)
            Try
                sbc.DestinationTableName = DestinationTbl
                sbc.BatchSize = BatchSize

                ' Map Columns
                For Each cColumn As DataColumn In DataTable.Columns
                    sbc.ColumnMappings.Add(cColumn.ColumnName, cColumn.ColumnName)
                Next

                sbc.WriteToServer(DataTable)

            Catch exSql As SqlException

                If exSql.Message.ToLower.Contains("received an invalid column length from the bcp client for colid") Then
                    Dim pattern As String = "\d+"
                    Dim match As Match = Regex.Match(exSql.Message.ToString(), pattern)
                    Dim index As Integer = Convert.ToInt32(match.Value) - 1

                    Dim fi As FieldInfo = GetType(SqlBulkCopy).GetField("_sortedColumnMappings",
                              BindingFlags.NonPublic Or BindingFlags.Instance)
                    Dim sortedColumns As Object = fi.GetValue(sbc)
                    Dim items As Object() = CType(sortedColumns.GetType().GetField("_items",
                                       BindingFlags.NonPublic Or BindingFlags.Instance).GetValue(sortedColumns), Object())

                    Dim itemData As FieldInfo = items(index).GetType().GetField("_metadata", 
                                 BindingFlags.NonPublic Or 
                                 BindingFlags.Instance)
                    Dim metadata As Object = itemData.GetValue(items(index))

                    Dim column As Object = metadata.GetType().GetField("column", 
                             BindingFlags.Public Or 
                             BindingFlags.NonPublic Or 
                             BindingFlags.Instance).GetValue(metadata)
                    Dim length As Object = metadata.GetType().GetField("length", 
                             BindingFlags.Public Or 
                             BindingFlags.NonPublic Or 
                             BindingFlags.Instance).GetValue(metadata)

                    szMsg = $"Column: {column} contains data with a length greater than: {length}"
                Else
                    szMsg = exSql.Message
                End If

            Catch ex As Exception
                szMsg = ex.Message
            End Try

        End Using

        Return szMsg
    End Function

    Public Function SqlBulkWrite(MyConnection As SqlConnection, 
                                 ByVal DataTable As DataTable, 
                                 ByVal DestinationTbl As String, 
                                 Optional ByVal BatchSize As Integer = 1000) As String

        Dim szMsg As String = String.Empty

        If Not MyConnection.State = ConnectionState.Open Then MyConnection.Open()

        Using sbc As New SqlBulkCopy(MyConnection)
            Try
                sbc.DestinationTableName = DestinationTbl
                sbc.BatchSize = BatchSize

                ' Map Columns
                For Each cColumn As DataColumn In DataTable.Columns
                    sbc.ColumnMappings.Add(cColumn.ColumnName, cColumn.ColumnName)
                Next

                sbc.WriteToServer(DataTable)

            Catch exSql As SqlException

                If exSql.Message.ToLower.Contains("received an invalid column length from the bcp client for colid") Then
                    Dim pattern As String = "\d+"
                    Dim match As Match = Regex.Match(exSql.Message.ToString(), pattern)
                    Dim index As Integer = Convert.ToInt32(match.Value) - 1

                    Dim fi As FieldInfo = GetType(SqlBulkCopy).GetField("_sortedColumnMappings",
                              BindingFlags.NonPublic Or BindingFlags.Instance)
                    Dim sortedColumns As Object = fi.GetValue(sbc)
                    Dim items As Object() = CType(sortedColumns.GetType().GetField("_items",
                                       BindingFlags.NonPublic Or BindingFlags.Instance).GetValue(sortedColumns), Object())

                    Dim itemData As FieldInfo = items(index).GetType().GetField("_metadata", 
                                 BindingFlags.NonPublic Or 
                                 BindingFlags.Instance)
                    Dim metadata As Object = itemData.GetValue(items(index))

                    Dim column As Object = metadata.GetType().GetField("column", 
                             BindingFlags.Public Or 
                             BindingFlags.NonPublic Or 
                             BindingFlags.Instance).GetValue(metadata)
                    Dim length As Object = metadata.GetType().GetField("length", 
                             BindingFlags.Public Or 
                             BindingFlags.NonPublic Or 
                             BindingFlags.Instance).GetValue(metadata)

                    szMsg = $"Column: {column} contains data with a length greater than: {length}"
                Else
                    szMsg = exSql.Message
                End If

            Catch ex As Exception
                szMsg = ex.Message
            End Try

        End Using

        Return szMsg
    End Function

Posted by ipercube