CODEWITHSUNDEEP
vb.netdatagridviewdatatablesql-server-2012
Hoh
Hoh

Reputation: 1184

Filling DataTable as source of DataGridView is very slow

So, here is a case where I have more than 40.000 rows in my database.
When I start Select * from Table it gets me result set in less than a second.
But, when I want to fill DataTable with those rows and than show it in DataGridView it takes forever to show (Around 15-20 seconds).

Why is that?

My code: 

Public Shared Function FillDTwithSQL(ByVal StoredProc As String, ByVal table As DataTable) As DataTable
        Dim cmd As SqlCommand = CreateCommand(SProcedura)
        Dim dt As New DataTable("dt")
        dt = table
        dt.Rows.Clear()
        Try
            If adoConnection.State = ConnectionState.Closed Then adoConnection.Open()
            dt.Load(cmd.ExecuteReader(CommandBehavior.Default))
        Catch ex As Exception
            MsgBox("Greska: " & ex.ToString)
            Error = True
            Error_text = ex.ToString
        End Try
        adoConnection.Close()
        Return dt
    End Function 
Public Shared Function CreateCommand(ByVal SProcedura As String) As SqlCommand
        CreateConnection(ConnectionSetup.ConnectionString)
        Dim cmd As New SqlCommand(ConnectionSetup.DataBaseName & ".dbo." & SProcedura, adoConnection)
        cmd.CommandType = CommandType.StoredProcedure
        Return cmd
    End Function 
Private sub FillDGV()
DataBaseLayer.FillDTwithSQL("SelectProc", ds_Tables.Table)
            Me.DataGridView1.DataSource = dds_Tables.Table
            Me.DataGridView1.ClearSelection()
End Sub

Upvotes: 0

Views: 2192

Answers (1)

DMAN
DMAN

Reputation: 471

For large amounts of data it is recommended to use the DataGridView's virtual mode.

Have a look at this link: How to: Implement Virtual Mode in the Windows Forms DataGridView Control

To summarize the link: You have to implement your own data caching by implementing the DataGridView's CellValueNeeded event and set Me.DataGridView1.VirtualMode = true.

Upvotes: 2

Related Questions