CODEWITHSUNDEEP
c#.netsql-servercursor
BigBoss
BigBoss

Reputation: 6914

Scrollable ODBC cursor in C#

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:

So now:

Upvotes: 2

Views: 2162

Answers (4)

Arturo Hernandez
Arturo Hernandez

Reputation: 2869

I don't think cursors will work for you particular case. The main reason is that you have 3 tiers. But let's take two steps back.

Most 3 tier applications have a stateless middle tier (your c++ code). Caching is fine since it really just an optimization and does not create any real state in the middle tier. The middle tier normally has a small number of open sessions to the database. Because opening a db session is expensive for the processor, and after the db session is open a set amount of RAM is reserved at the database server. When a request is received by the middle tier, the request is processed and handed on to the SQL database. An algorithm may be used to pick any of the open sessions, or it can even be done at random. In this model it is not possible to know what session will receive the next request. Cursors belong to the session that received the original query request. So you can't really expect that the receiving session will be the one that has your open cursor.

The 3 tier model I described is used mainly for web applications so they can scale to hundreds or thousands of clients. Were SQL servers would never be able to open that many sessions. Microsoft ADO.NET already has many features to support the kind of architecture I described, so it is not very hard to implement. And the same is used even in non Web applications depending on the circumstance. You could potentially keep track of your sessions so you could open a single session per client, I would first make sure that the use case justifies that. Know that open cursors can take up a lot of resources as well.

Cursors still have a place within a single transaction, it's just hard to keep them open so that the client application can fetch/update values within the result set.

What I would suggest its that you do the following within the query transaction. Store in a separate table the primary key values of the main table in your query. On the separate table include other values like sessionid and rownumber. Return a few of the first rows by linking to the new table in the original query. And in subsequent calls just query the corresponding rows again by linking to your new table. You will need an equivalent to a caching mechanism to purge old data, and to refresh the result set according to your needs.

Upvotes: 2

Christian Hayter
Christian Hayter

Reputation: 31071

When Microsoft designed the ADO.NET API, they made the decision to expose only firehose cursors (IDataReader etc). This may or may not actually pose a problem for you. You say that you want "functionality of scrollable cursors", but that can mean all sorts of things, not just paging, and each particular use case can be tackled in a variety of ways. For example:

Requirement: The user should be able to arbitrarily page up and down the resultset.

  • Retrieve only one page of data at a time, e.g. using the ROW_NUMBER() function. This is more efficient than scrolling through a cursor.

Requirement: I have an extremely large data set and I only want to process one row at a time to avoid running out of memory.

  • Use the firehose cursor provided by ADO.NET. Note that this is only practical if (a) you don't need to hit the database at all during the loop, or (b) you have MARS configured in your connection string.
  • Simulate a keyset cursor by retrieving the set of unique identifiers into an array, then loop through the array and read one row of data at a time.

Requirement: I am doing a complicated calculation that involves moving forwards and backwards through the resultset.

  • You should be able to re-write your algorithm to eliminate this requirement. For example, read one set of rows, process them, read another set of rows, process them, etc.

UPDATE (more information provided in the question)

Your business requirements are asking too much. You have to handle arbitrary queries that assume the presence of scrollable cursors, but you can't provide scrollable cursors, and you can't re-write the client code to not use scrollable cursors. That's an impossible position to be in. I recommend you stick with what you currently have (C++ and ODBC) and don't bother trying to re-write it in .NET.

Upvotes: 2

ronpy
ronpy

Reputation: 262

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

Upvotes: 3

jgauffin
jgauffin

Reputation: 101150

I usually use DataReader.Read() to skip all rows that I do not want to use when doing paging on a DB which do not support paging.

If you don't want to build the SQL paged query yourself you are free to use my paging class: https://github.com/jgauffin/Griffin.Data/blob/master/src/Griffin.Data/BasicLayer/Paging/SqlServerPager.cs

Upvotes: 2

Related Questions