Skip Hidden Columns in Export to DataTable

Welcome Guest! To enable all features please Login or Register.

vb-dev
Member Topic Starter

2022-10-01T17:21:15Z

Is there a more efficient way to exclude hidden columns from a row?

I am producing a DataTable that contains the grid's columns and rows. I don't want the table to include hidden columns. To inspect every cell, as in the below example, doesn't perform very well. Is there a more efficient way?

    <Extension>
    Friend Function ToDataTable(ByVal grid As iGrid) As DataTable
        Dim dt As New DataTable

        For Each col As iGCol In grid.Cols
            If col.Visible Then
                dt.Columns.Add(col.Text.ToString)
            End If
        Next

        For Each row As iGRow In grid.Rows
            Dim newRow = dt.NewRow

            For Each cell As iGCell In row.Cells
                If IsColumnHeaderVisible(grid, cell.ColIndex) Then
                    newRow(cell.Col.Text.ToString) = cell.Value
                End If
            Next

            dt.Rows.Add(newRow)
        Next

        Return dt
    End Function

Edited by moderator 2022-10-04T04:42:58Z | Reason: Igor/10Tec corrected subject

Angelos
Newbie

2022-10-03T08:25:29Z

Hello vb-dev,
I had the exact same issue. Try the code below. It is tested on an iGrid with 3912 rows and 27 columns (real data). I had no issues. Actually, the code ran instantly in debug mode.


        Dim dt As New DataTable
        For Each col As iGCol In IGrid1.Cols
            dt.Columns.Add(If(col.Text, "col" & col.Index))
        Next

        For Each row As iGRow In IGrid1.Rows
            Dim newRow As DataRow = dt.NewRow
            For i = 0 To dt.Columns.Count - 1
                newRow(i) = row.Cells(i).Value
            Next
            dt.Rows.Add(newRow)
        Next

        For Each col As iGCol In IGrid1.Cols
            If col.Visible = False Then
                dt.Columns.Remove(If(col.Text, "col" & col.Index))
            End If
        Next

Note: If you're using hidden columns without text in you iGrid as I do, then the "If(col.Text, "col" & col.Index)" is necessary. It replaces "nothing" or empty text with a text of your choise.

The logic behind the above code is that I add every column (hidden or not) in the data table, and when the transfer is finished, I delete the non-visible columns.
The problem is not that you inspect every column; the problem is that the rows are added one-by-one and not as a collection. But this is how DataTables work.
You can save some time if you don't create a new row in the loop. You can try to add a new row, passing array values as an object. Check this link: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/adding-data-to-a-datatable
For example:


        Dim ar As New List(Of String)
        For Each row As iGRow In IGrid1.Rows
            ar.Clear()
            For i = 0 To dt.Columns.Count - 1
                ar.Add(row.Cells(i).Value.ToString)
            Next
            dt.Rows.Add(ar.ToArray)
        Next

Igor/10Tec
Administration

2022-10-03T14:04:44Z

I also think that the main problem is in the performance of the DataTable object. You add row-by-row, and all this is done in an object manner, which is traditional for .NET but can drastically slow down performance.

The only alternative to this is to populate the DataTable in one call with the DataTable.Load() method. It expects an object implementing the IDataReader interface. We could create a wrapper for iGrid and feed it to the DataTable.Load() method. The whole construction would look like this:

<Extension>
Friend Function ToDataTable(ByVal grid As iGrid) As DataTable
    Dim dt As New DataTable

    Using reader As New iGridReader(grid)
        dt.Load(reader)
    End Using

    Return dt
End Function

Class iGridReader : Implements IDataReader

    Private sourceGrid As iGrid

    Public Sub New(ByVal grid As iGrid)
        sourceGrid = grid
    End Sub

    Public ReadOnly Property Depth As Integer Implements IDataReader.Depth
        Get
            '...
        End Get
    End Property

    Public ReadOnly Property IsClosed As Boolean Implements IDataReader.IsClosed
        Get
            '...
        End Get
    End Property

    ' Implementation of other members of IDataReader

End Class

But after looking at the source code of DataTable.Load, I am not sure the result will be significantly faster because, it seems, the implementation is also based on creation and addition of the DataRow objects one-by-one. If you have time to experiment, you can try to finish this approach - but you will need to implement about 30 members of IDataReader.

What I would recommend that you try in your original implementation is to call the DataTable.BeginLoadData() method before you start to add data to the DataTable and then DataTable.EndLoadData() in the very end before you return the result. Try it for your real data and tell us the result.

vb-dev
Member Topic Starter

2022-10-04T17:40:49Z

I thank you both for your suggestions. After a lot of testing, I have found that the performance isn't all that bad, really. If we encounter larger resultsets, we'll definitely try these suggestions.

Important Information:

The 10Tec Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close