Next, Previous, Last, First in ADO.NET

[mark1110]

I am rewriting a VB6 application that is using ADO to C# using ADO.NET. What I would like to do seems simple enough, I have a form with several textboxes, comboboxes, and checkboxes. I would like to open a Access database, read its contents, then populate the form. I also need to go to the next record, go to the previous record etc. Here is part of the code I wrote:


private void frmClient_Load(object sender, EventArgs e)
{

OleDbDataAdapter daClient;
DataTable dtClient;
string strClientSQL;



string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + Utilities.stdDir;
strClientSQL = "SELECT * FROM Client ORDER BY casename";
daClient = new OleDbDataAdapter(strClientSQL,Utilities.connectionString);
dtClient = new DataTable("ClientInfo");
daClient.Fill(dtClient);


This works, however since I have over 500,000 records it takes 10 minutes to load. I am not sure if this is correct way to do this. Is there a better way?

Thanks,

Mark

 

[jbenson001]

500,000 rows is way to many to get back and show to end users. Even if it took 10 seconds to get all the data, it would be too overwhelming for the user. I would suggest having a page that lets the user filter the data by some criteria. Then display the results in a grid, with custom paging. This way there is less stress on the DB. Then allow the user to select a row from the grid, and display the results as you want.

 

[ca8msm]

Also, try not to use Access as a database for web applications. It has a hard time coping with multiple connections.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[mark1110]

Unfortunately I have to use access as my database. The current code which uses ADO is:
Set cnn1 = New Connection

cnn1.CursorLocation = adUseClient

strCnn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & gcMDB

cnn1.Open strCnn

Set adoPrimaryRS = New Recordset

adoPrimaryRS.CursorLocation = adUseServer

strPrimarySQL = "select * from Client "
adoPrimaryRS.Open strPrimarySQL, cnn1, adOpenStatic, adLockOptimistic

This works fine. I can go to the next record, previous record, even the last record in just seconds.

There has to be a similar way of doing this in ADO.NET. The form I have just has about 15 textboxes that need to be populated when I press the previous or next button. Any help you can give me will be greatly appreciated.

Thanks,

Mark

 

[ca8msm]

This works fine. I can go to the next record, previous record, even the last record in just seconds.

The difference is that VB6 is a windows programming environment and therefore the actual data can be stored in memory on the client. Web based applications don't work like that and you'll find that working with such large datasets isn't a good idea. Have a look at some of the ASP.NET examples such as this one from Scott Mitchell, as these should guide you in the right direction:

http://www.asp.net/learn/dataaccess/tutorial14cs.aspx?tabid=63

Unfortunately I have to use access as my database

Any reasons for this?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.