DataGridView Display

I’m currently facing a trouble when executing the following procedure :

    Private Sub PosHistory()
        Dim DateFrom As Date = CDate(tsFrom.Text & " 00:00:00")
        Dim DateTo As Date = CDate(tsTo.Text & " 23:59:59")

        Dim dtList As DataTable
        dtList = GetDataTable(ConnString, CommandType.StoredProcedure, "Pos_History", New SqlParameter("@DateFrom", DateFrom), New SqlParameter("@DateTo", DateTo))
        With DataGridViewList
            .DataSource = Nothing
            .Columns.Clear()
            .DataSource = dtList
        End With
    End Sub

Open in new window


DataTable contains approx. 1000 records.
When executing it for the first time, datagridview values are showed instantly.
At the next execution, after having change a parameter value, we must wait for approx. 1 minute to have the result (same amount of records)
Waiting time is during the execution of the line : « .DataSource = dtList »
Does someone have an idea on how to optimize the code so it’s taking the same time as if it was run for the first time ?

Thanks for your help !
mmorelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't think you need to call Columns.Clear. Setting the Datasource to Nothing will do what you need on that.

What does GetDataTable look like?
0
Shaun KlineLead Software EngineerCommented:
Do you need the .Columns.Clear line? If you are filling the DataGridViewList with data that has the same columns, this line would seem to be not needed. Some of the delay you are seeing may be the grid rebuilding the columns.

Also, if you have the columns set to auto-resize, there will be some delay there as the grid needs to fill before it can determine how big each column needs to be.
0
mmorelleAuthor Commented:
I try without "columns.clear".
Maybe some seconds better, but it take always a long time...
Thanks
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

mmorelleAuthor Commented:
It's not a problem of "auto-resize" because we have the problem only after the first execution...
Thanks
0
mmorelleAuthor Commented:
What does GetDataTable look like? :
GetDataTable make a call to a DLL
This DLL make the connection with SQL Server, send the parameters and the name of the stored-procedure, and receive the datatable.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If removing the .Columns.Clear method doesn't help, then perhaps you could temporarily bypass the call to the DLL, and create your own Connection and Command instead, just for testing purposes:

Using con As New SQLConnection(ConnString)
  con.Open
  Using cmd As New SQLCommand
    cmd.Connection = con
    '/ set your CommandText, Parameters, etc here
    Using dt As New Database
      dt.Load(cmd.ExecuteReader)
      DataGridViewList.Datasource = dt
    End Using
  End Using
End Using

If you have the same performance issues with this method, then I'd assume the trouble lies in the connection to the server, or perhaps in the Stored Procedure. You can use various methods to determine how SQL Server executes that query, but you'd be best to get that information from a SQL Server Expert.
0
mmorelleAuthor Commented:
I find the source of the problem!

The full procedure is

 Private Sub PosHistory()
        Dim DateFrom As Date = CDate(tsFrom.Text & " 00:00:00")
        Dim DateTo As Date = CDate(tsTo.Text & " 23:59:59")

        Dim dtList As DataTable
        dtList = GetDataTable(ConnString, CommandType.StoredProcedure, "Pos_History", New SqlParameter("@DateFrom", DateFrom), New SqlParameter("@DateTo", DateTo))
        With DataGridViewList
            .DataSource = Nothing
            .Columns.Clear()
            .DataSource = dtList
            InitializeDg(dgList)
        End With
    End Sub

Open in new window


And the procedure InitializeDg(dgList) is :

    Public Sub InitializeDg(ByVal dg As DataGridView)

        With dg
            ' Initialize basic DataGridView properties.
            .BackgroundColor = bcApplication
            .BorderStyle = BorderStyle.None

            ' Set property values appropriate for read-only display and limited interactivity. 
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToOrderColumns = True
            .ReadOnly = True
            .SelectionMode = DataGridViewSelectionMode.FullRowSelect
            .MultiSelect = False

            If .RowCount < 100 Then
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
            Else
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCells
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            End If

            .AllowUserToResizeColumns = True
            .AllowUserToResizeRows = True

            ' Set the selection background color for all the cells.
            .DefaultCellStyle.SelectionBackColor = bcTxtOn
            .DefaultCellStyle.SelectionForeColor = Color.Black

            ' Set the background color for all rows and for alternating rows. 
            ' The value for alternating rows overrides the value for all rows. 
            .RowsDefaultCellStyle.BackColor = Color.White
            .AlternatingRowsDefaultCellStyle.BackColor = Color.LightYellow

            ' Set the row and column header styles.
            .ColumnHeadersVisible = True
            .ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
            .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .ColumnHeadersDefaultCellStyle.ForeColor = Color.White
            .ColumnHeadersDefaultCellStyle.BackColor = Color.Black

            .RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
            .RowHeadersDefaultCellStyle.BackColor = Color.Black
            .RowHeadersDefaultCellStyle.SelectionBackColor = Color.FloralWhite
        End With

    End Sub

Open in new window


With the call to this procedure : problem
Without : no problem!!!

Do you understand where is the problem in this procedure "InitializeDG"?

Thanks a lot
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shaun KlineLead Software EngineerCommented:
In your PosHistory method, you are using a WITH statement for DataGridViewList, but you are passing dgList into InitializeDG. Shouldn't those objects be the same?
0
mmorelleAuthor Commented:
The problem was in an other place!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.