Moving to record X in ASP.NET

In ASP.Net, if you binding dataset, you can loop through dataset values.
Like - http://forums.asp.net/t/1323999.aspx/1

Using this logic you can move to 20th record in dataset.

Raj

I dont understand your sample Raj,

This is how Im accesing the table.  

Dim conString = ConfigurationManager.ConnectionStrings("MyDataBase").ConnectionString
Dim con As New SqlConnection(conString)
Dim strQuery As String = "SELECT ................................."
con.Open()
Dim cmd As New SqlCommand(strQuery, con)
Dim RS As SqlDataReader = cmd.ExecuteReader
if rs.read() then
   .....
end if

I need to move to a specific record to paginate a list of thousands of records. For example, if show 20 records per page, and if the user clicks on PAGE 5 I know I have to move the pointer to (5x20)+1 (record 101) and show only the next 20.

I dont want to loop and unnecesary go thru 100 records till I reach record 101. This is fine for just 100 records, but I need to list thousands of them.

I remember I did this with MOVE in ASP, but I dont know how to do it in .NET

Now I understand your question right. This question is already answered.
Please check this question - https://www.experts-exchange.com/questions/22941122/Moving-to-the-nth-record-of-a-DataReader.html?anchorAnswerId=20221968#a20221968

Raj

Hi again Raj,

Thanks for the comment, I checked out the question and the link (http://msdn.microsoft.com/en-us/library/bh8kx08z(VS.71).aspx) but I still couldnt figure out how to move the pointer straight to an X number of records.

The problem unfortunately is the use of a datareader. I have no problem with datareaders (I like them alot actually) but it is their nature to read only one line of the results at a time in sequence. For some reason there are no methods added to the datareader to advance multiple records forward or backward (it would have been a good thing to do).

So you are forced to re-examine how you want to deal with this:

1. store your data return in an in memory construct such as a dataset and then you should be able to reach in and grab any records you would like at will. The downside of this is it is potentially more resource intensive than a datareader.

2. Probably the more conventionally "professional" approach would be to continue to use a datareader or even a dataset but limit and choose the records you want through your sql statement. This would give you a more streamlined paging mechanism.

NOTE: Depending on what you are paging through a simplistic approach can be had using a sql dataadapter. This often gives you the option to page automatically, cache if you want, and or use ajax to minimize individual data reads.

I don't suppose this is the kind of answer you want. Its certainly not as easy as "just use this method and your done", but I believe it is a good example of the type of options you will have to consider to get this functioning the way you want (unless you are going to loop forward). Loading all that data from sql each time with a datareader with or without a method sounds like it would be kindof "heavy lifting" in your application anyway.

Good Luck

@pvg1975:
ddayx10 clearly explained everything, I believe, there is no further explanation on this. I suggest you to use paging in SQL as ddayx10 mentioned. That would be more efficient, since it loads only required data and would be 'light-weight'

If you want to proceed with this way, let me know - I will provide you an example.

Raj