DataGridView problems

[Jase1000]

Hello all,

I have been using VB.NET 2005 for the past month or so, and created a project which utilises the Datagridview.

The project reads a number of access tables and allows the user to edit, update, add etc...

All works without problem until I decide to read a table with a number of records in it (20k onwards). The connection to the database is made, the DataAdapter fills a dataset and the dataset populates the datagridview.

The dataset gets populated within a second, but the problem lies in populating the datagridview.

If the dataset is populated so quickly, why does it take forever to populate the dataset (roughly 4 minutes). I know that it is not down to access because if I filter the data down to 100 records it is almost instantaneous.

Other than painting the number of records only shown in view in the first instance - and then redrawing each time the user scrolls down, I am drawing a blank.

 

[vis781]

4 minutes sounds like a long time. The longest part should be getting the data from the database. There must be something else going on here. Can you show some relavent code.

 

[Jase1000]

Dim objDbStock As OleDb.OleDbConnection = new OleDb.OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source='" & objSettings.DatabaseLocation & "';jet oledb:database password=")

Dim objDaStock As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("Select * from Stock_File where Supplier=" & "'" & cboSupplier.text & "'", objDbStock)

Dim objDsStock As New DataSet

Try

objDsStock = New DataSet("StockRecords")
objDaStock.Fill(objDsStock, "StockRecords")
objDaStock.Dispose()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "")
Exit Sub
End Try



* dgvStockList.DataSource = objDsStock.Tables("StockRecords")

* It is here that it takes forever to load the dataset into the datagridview.

Populating the dataset takes probably 5 to 10 seconds.

Thanks for the help

Jason

 

[vis781]

Have you tried using a binding source object instead. I know it's another object to instantiate but it's a very efficient component. Drop one in the designer or create one in code, either way. Just for my info how many records are in this table?

 

[Jase1000]

Binding Source object?

Sorry a bit new to ADO.NET, well VB in general really. Do you have an example I could use.

 

[vis781]

Just drop one from the toolbox onto the form, or create one in code...

Private MyBindingSource as new BindingSource

Or you can declare it with events if you think you will need any events that it raises.

 Me.MyBindingSource.DataSource = YourDatatable
 
Your DGV.Datasource = me.MyBindingSource

I'm still confused by the length of time it is taking though, 4 minutes is a very long time...

 

[vis781]

Something else to think about. Seeing as you are disposing of the dataadpater after you have finished with it, i assume you only need t display this information and aren't intending on it being modifed? If so then you can populate a DGV really fast with a datareader. Here's a snippet that works with the northwind database but you can modify it to what you need...

[LEFT]Private Sub LoadDataGridView()
‘Populate a read-only DataGridView control with an SqlDataReader
Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
Try
Dim strSql As String = "SELECT * FROM Customers"
Dim cmdGrid As New SqlCommand(strSql, cnnNwind)
cmdGrid.CommandType = CommandType.Text
cnnNwind.Open()
Dim sdrGrid As SqlDataReader = cmdGrid.ExecuteReader
Dim intCol As Integer
With sdrGrid
If .HasRows Then
dgvCusts.Rows.Clear()
‘Add column definition: FieldName, and ColumnName
For intCol = 0 To .FieldCount - 1
dgvCusts.Columns.Add(.GetName(intCol), .GetName(intCol))
Next
‘Base column width on header text width
dgvCusts.AutoSizeColumnsMode = _
DataGridViewAutoSizeColumnsMode.ColumnHeader
While .Read
‘Get row data as an Object array
Dim objCells(intCol) As Object
.GetValues(objCells)
‘Add an entire row at a time
dgvCusts.Rows.Add(objCells)
End While
.Close()
End If
End With
Catch exc As Exception
MsgBox(exc.Message)
Finally
cnnNwind.Close()
End Try
End Sub[/LEFT]

 

[Jase1000]

Tried using the binding source object, and it reduces 21000 records down to a minute and a half to process. I will have a go at the data reader, as you say, I will not be using it. This is primarily for display purposes only.

Trouble is because I am new to this, I never know what is deemed satisfactory in terms of speed.

 

[aBsOlUt]

a dataReader would do the job a bit faster but maybe not so fast as I believe it has to do with the dataGridView object itself. Thats why it takes 5 seconds for the dataAdapter and 4 minutes for the actual datagridview.

Try the dataReader first and see if that works first.

I think the problem has to do that the dataGridview object is a bit badly implemented in .NET 2.0. Try setting a maximum of some rows to be shown and then while the user scrolls keep adding more...

I am not in my computer to check such a solution but I will soon...I might be wrong.

 

[Jase1000]

Thanks for the help - I will give it a try and see what difference it makes!