Scrollable ODBC cursor in C#

Question

I'm a C++ programmer and I'm not familiar with the .NET database model. I usually use IDataReader (OdbcDataReader, OledbDataReader or SqlDataReader) to read data from database. Sometimes when I need a bulk of data I use DataAdapter, but what should I do to achieve the functionality of scrollable cursors that exists in native libraries like ODBC?

---

Thanks all of you for your answers, but I am in a situation that I can't accept them, of course this is my fault that didn't explain my problem completely. I explain it as a comment in one of answers that now removed.

I have to write a program that will act as a proxy between client side program and MSSQL, for this library I have following requirements:

• My program should be compatible with MSSQL2000
• I don't know all the tables and queries that will be sent by the user, I should simply add some information to it, make a log, ... and then execute it against MSSQL, so it is really hard to use techniques that based on ordered field(s) of the query or primary key of the table(All my works are in one database but that database is huge and may change over time).
• Only a part of data is needed by the client, most DBMS support LIMIT OFFSET, unfortunately MSSQL do not support it, and ROW_NUMBER does not exist in the MSSQL2000 and if it supported, then again I need to understand program logic and that need a parse of SQL command(Actually I write a parsing library with boost::spirit but that's native code and beside that I'm not yet 100% sure about its functionality).
• I may have multiple clients but most of queries that will be sent by them are one of a few predefined queries(of course users still send custom queries but its about 30% of all queries), So I think I can open some scrollable cursors and respond to clients using that cursors and a custom cache.
• Server machine and its MSSQL will be dedicated to my program, so I really want to use all of the power of the server and DBMS to achieve my functionality.

So now:

• What is the problem in using scrollable cursors and why I should avoid them?
• How can I use scrollable cursors in .NET?

Answer 1

In SQL Server you can create queries paged thus. The page number you handle it easily from the application. You do not need to create cursors for this task.

For SQL Server 2005 o higher

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ROW FROM TABLEA ) AS ALIAS 
WHERE ROW > 40 
AND ROW <= 49

For SQL Server 2000

SELECT TOP 10 T.* FROM TABLA AS T WHERE T.ID NOT IN
    ( SELECT TOP 39 id from tabla order by id desc )
ORDER BY T.ID DESC

PD: edited to include support for SQL Server 2000