How can i copy data table records of different field name based on mapping list evaluating condition on source data table?

Question

I have a Source Data table of type System.Data.DataTable from which i have to generate Destination Data Table of mapped column(Consider same SqlDBType for mapped columns.)

I have a list of MappingInfo class in which each Source Datatable Column mapped with New Column Name which will be in destination data table.

Public Class MappingInfo

    Public Property SourceFieldName As String
    Public Property DestinationFieldName As String

End Class

I have to evaluate a condition in source datatable to allow row data copy in destination table.

I did this using following code snippet:

''Prepare destination table.

For Each oMapping In oMappingInfo  

            DestinationDataTable.Columns.Add( _
                New DataColumn(oMapping.DestinationFieldName))
        Next

        For Each oRow In SourceDataTable.Rows   ''Copy data.
            If oRow("IsActive") Then
                oDataRow = DestinationDataTable.NewRow
                For Each oMapping In oMappingInfo
                    oDataRow(oMapping.DestinationFieldName) = _
                        oRow(oMapping.SourceFieldName)
                Next
                DestinationDataTable.Rows.Add(oDataRow)
            End If
        Next

The main drawback is that here i have minimum 40k records in source datatable and data is not possible to fetch from database as all changes with data committed only when user save his work. The generated destination table is been assigned as data source to grid control and to report for preview.

How can i achieve this efficiently using Linq or do anyone please suggest me best way to achieve this requirement.

please first correct your question.grammar is poor for asking question — BhavikKama, Sep 30 '12 at 08:22
@BhavikKama - I doubt OP is a native English speaker, and while it's not the best grammar I've read far worse here on SO. His question is understandable, IMO. — Tim, Sep 30 '12 at 08:34
LINQ is not necessarily going to be faster. It provides shortcuts so you don't have to write out the For loop, but there's still a for loop iterating in the generated byte code. — JDB, Oct 08 '12 at 16:46

score 0 · Answer 1 · answered Oct 08 '12 at 16:52

I've not tried this, so I can't say for sure that it'll be faster, but it seems to me that you'd get much better speed using something like the following:

Dim l_destinationTable As DataTable
' This creates a copy of the structure and content
l_destinationTable = SourceTable.Copy()

For Each l_column As DataColumn in l_destinationTable.Columns
    Dim l_columnMap = oMappingInfo.FirstOrDefault( _
        Function (c) c.SourceFieldName = l_column.ColumnName )
    If l_columnMap IsNot Nothing Then
        ' Rename the column if it is mapped
        l_column.ColumnName = l_columnMap.DestinationFieldName
    Else
        ' Drop the column if it is not mapped
        l_destinationTable.Columns.Remove( l_column )
    End If
Next

NOTE: This method will fail if an unmapped column is part of a relationship or another column's expression depends on this column. Also, if you are swapping the name of two columns (for example, A will be named B and B will be named A) then you will get an exception as two columns may not have the same name at the same time.

How can i copy data table records of different field name based on mapping list evaluating condition on source data table?

1 Answers1