Best approach with ADO.net

[meyvn77]

I’m new at .NET but have been programming for a while.
I am rebuilding an application in .NET using ADO.NET. I have a few questions.

The DB for the app for one client will be in Access the other in SQL Server. Let’s keep this conversation to access for now. The App will be migrated to ASP.NET in a little while so I want to take full advantage of the disconnected nature of ADO.NET

First, I have a large database table that’s around 200,000 records that the user will be querying and filtering. The end user will be seeing subsets of this table in a datagridview for the main part of the app. The app has other functionality but it is all driven from the subset in the datagrid

So… the best way to do this..?

First, I thought I would just do a dataset and let the user filter the binding source but the .fill() takes forever. Also, the limitation of not being able to querying the dataset with SQL is a pain because I may need that capability. I looked into LINQ to Dataset but it looks pretty taxing to learn so I would like to avoid it.. I’m still learning .NET.

Second, I though of using a datareader where I can use SQL statements and feed it into a datatable then bind to datagridview. The problem is I saw no speed increase from the dataset. Because I am going to go to ASP.NET in the future it seems that the datareader is the way to go? I guess what frustrates me about the datareader is the inability to loop through it and do meaningful things like you could with good old ADO.

?Do I need to load all 200000 records? No, not really only as a result to the users query or filter.

I guess these are the only two options I have been able to think of. I know there are many ways to do things in .NET so I thought I would ask and maybe it would save me some pain down the road.

Charles

[eclipsed4utoo]

How about post your code that you are using? You can loop through a datareader.

What kind of performance are you expecting when returning 200,000 records? Does the table(s) have indexes set up? If not, I would look into setting up indexes.(if Access supports indexing).

And no, you should not return on 200,000 records. You could either use paging or use filters to limit the number of records returning from the database.

[meyvn77]

This code is loading through the dataset using the datasources tab

Me.GIS_EVENTSTableAdapter.Fill(Me.CDMSDataSet.GIS_EVENTS)
Me.DataGridView1.DataSource = Me.CDMSDataSet.GIS_EVENTS


This is the code I am using to put the Datareader in to a datatable then datagridview
I also tried to loop through and add it by row to the grid to see if it was faster but it didn't work

Dim Conn As New OleDb.OleDbConnection
Dim CMD As New OleDb.OleDbCommand
Dim DR As OleDb.OleDbDataReader
Dim i As Long

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TOA\Crash\_Active_CDMS\Collier_County\CDMS.mdb"
Conn.Open()

CMD.Connection = Conn
CMD.CommandText = "SELECT * FROM GIS_EVENTS"

DR = CMD.ExecuteReader
Me.DataGridView1.DataSource = DR

Dim DataTable1 As New DataTable()
DataTable1.Load(DR)
Me.DataGridView1.DataSource = DataTable1

'This code does not work
'I am trying to loop through each
'row and Add it to the DataGridview
'i = 0
'While DR.Read
' Me.DataGridView1.Rows.Add()
' With Me.DataGridView1.Rows(i)
' .Cells(0).Value = DR!On_STREET
' i = i + 1
' End With
'End While

Conn.Close()

I guess what I don't understand is that ADO.NET is great for the disconnected datasets but if I can't query the dataset then I need to use a reader but each time I use the datareader it connects to the database? I'm I misunderstanding this? It would seem to me that what they need is a datareader for Datasets?

Thanks,
Charles

[Alsvha]

A DataReader is a connected reader, so it needs the connection to read from it. That's also why always remembering to close and dispose a reader is important.

If you want to run through the data disconnected, you should place it over into a dataset, because when the dataset is populated, the connection to the database can be closed, and the data will exists disconnected in your application.

[meyvn77]

Thanks for the relpys.
I guess I am not being clear or not asking the correct question.

Right now I am migrating the App. from VB to VB.NET because it is also a desktop App. Because I am going to have to move the VB.NET app to ASP.NET this year I am trying to have all my ADO.NET code optimized for ASP.NET but still work OK for the desktop app.
Also, the App will be hitting an access DB for one department and SQL Server for another.

Questions:

1) My dataset is large and the FILL command takes forever so it just doesn't work well. So.. can I incrementally fill the dataset to avoid the waiting when the program opens? and is that approch really good for ASP.NET anyhow?

2) If no then, would it be good practice to use a datareader to just query the database with the query to get the subset I want and set that to the datagridview? This would be about 10 users and they would be running a query lets say every minute? Then when I need to update a field just use the Command object (this wont happen much in the desktop app and never in the ASP.net app)

Also, Does anyone have some code to loop through a data reader and fill a datagridview with all the columns and rows? I am guessing that that will be faster than filling a datatable then binding it to the datagridview?

Thanks,

Charles

[Alsvha]

I'd properly use "paging" if the data amount is so big that it's too slow to put into a dataset, and thus limit the amount of data, so you don't have to pull the entire data out at once, but only the data currently needed.
So if you only display say 100 rows of the data at any given time, you can just pull out only those 100 rows of data.


But for your last question
The datagridview has an item collection. You can make a loop while reading your datareader and manually add new items to that collection based on the values from your reader.
How much you'll save (if anything) is impossible here.

[eclipsed4utoo]

Thanks for the relpys.
I guess I am not being clear or not asking the correct question.

Right now I am migrating the App. from VB to VB.NET because it is also a desktop App. Because I am going to have to move the VB.NET app to ASP.NET this year I am trying to have all my ADO.NET code optimized for ASP.NET but still work OK for the desktop app.
Also, the App will be hitting an access DB for one department and SQL Server for another.

Questions:

1) My dataset is large and the FILL command takes forever so it just doesn't work well. So.. can I incrementally fill the dataset to avoid the waiting when the program opens? and is that approch really good for ASP.NET anyhow?

2) If no then, would it be good practice to use a datareader to just query the database with the query to get the subset I want and set that to the datagridview? This would be about 10 users and they would be running a query lets say every minute? Then when I need to update a field just use the Command object (this wont happen much in the desktop app and never in the ASP.net app)

Also, Does anyone have some code to loop through a data reader and fill a datagridview with all the columns and rows? I am guessing that that will be faster than filling a datatable then binding it to the datagridview?

Thanks,

Charles

You have a misunderstanding. ADO.Net works with .Net. It doesn't matter if it's for a windows app, console app, or web app.

1. Yes, incremental loading is a great idea in ADO.Net. A number of the "viewing" controls(ListView, GridView, etc.) allow paging, which means it loads X number of records on page 1, and then has more page numbers on the bottom.

You may be able to bind the datareader directly to the datagridview.

Also, it doesn't matter how you try binding, returning, looping, and binding 200,000 records is not going to happen instantanously.