API cursors?

Im really confused about cursor stuff that isnt quite explained in the BOL.
What exactly is the difference between a Tsql cursor and an API cursor? When
I access a db from a .NET application is this an API cursor? is an API cursor
the same as a server cursor? does an API cursor/ server cursor hold any locks
on the db? does a .NET application? BOL says to use a 'server' cursor as the
prefered methos to give a web app user a result set from an aribtrary query
defined by the user which might be unpredicably large so that you can fetch n
rows at a time without bringing the entire result set in and without holding
locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
is how and if it differs from a Tsql cursor called by a stored procedure or
something. Can anyone explain this?Hi Charles,
Here is some information...
T-SQL cursors are used in stored production and query development in
applications like Query Analyzer. Since SQL is, for the most part, a batch
processing language, the support for row-by-row processing (using a cursor)
is necessary is some situations. Other than learning about them for
troubleshooting purposes, I would avoid using.
API cursors (which can be server-side or client-side) are used in
application development, like VB, VC, not ADO.NET (due to poor performance,
cursors are not supported in ADO.NET). If you still want to use a cursor, a
previous version of ADO will need to be used. With the new SqlDataReader
object, you can read the data very quickly into a collection and process the
data row-by-row.
API cursors, as with all database transactions, acquire locks. For more
info on locking types and duration, I suggest reading about locking modes and
transaction isolation levels.
A book I suggest...'Inside Microsoft SQL Server 2000' by Delaney/Soukup,
Microsoft Press.
HTH,
Adam
"CharlesT" wrote:
> Im really confused about cursor stuff that isnt quite explained in the BOL.
> What exactly is the difference between a Tsql cursor and an API cursor? When
> I access a db from a .NET application is this an API cursor? is an API cursor
> the same as a server cursor? does an API cursor/ server cursor hold any locks
> on the db? does a .NET application? BOL says to use a 'server' cursor as the
> prefered methos to give a web app user a result set from an aribtrary query
> defined by the user which might be unpredicably large so that you can fetch n
> rows at a time without bringing the entire result set in and without holding
> locks. But I cant find anywhere else in BOL what exactly this 'server cursor'
> is how and if it differs from a Tsql cursor called by a stored procedure or
> something. Can anyone explain this?
>|||okay, well suppose I have an SQL database of availible rental properties.
People in a company update this database frequently. They want to make a .NET
application that will allow user on the internet to search this database -
users would be able to find online what properties are availible and their
rates and pictures etc. Based on whatever search values the user inputs
their result set may be unpredicably large. I could make .NET simply load
the entire database each time the user makes a search into a dataset but this
takes time and resources and it is likely that the user might want to change
their search after seeing that hundreds of properties have met their criteria
etc. Alternatively, I could have the user's search run a stored procedure
that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
user decides to keep looking fetches the next 10 rows etc. I understand how
the cursor in the stored procedure works and I can estimate its effect on
locking and even change its isolation level based on network traffic etc.
Performance wont be a factor at all when dealing with only 10 rows. The
variable which stores how deep the user was in the database could exist in
the application so the cursor can be closed and reopened each time the user
selects next so it doesnt hold locks etc. Or, and this is the thing I dont
get, I could use the ExecuteReader() object from .NET to access the database.
I suppose that since this is using ADO it is not an API cursor, but then what
is it' what is going on in SQL when an outside application runs a cursor in
SQL via ADO. What is the concurrency price of possible simultaneous
SELECT/UPDATE to an SQL DB from inside a company with people accessing it
over the internet with a web application? If .NET doesnt use API cursors
when it reads data from SQL with ADO then what does it use? and if it isnt
API cursors then what is the fuss over API cursors that I see mentioned
everywhere in SQL books and where would they ever concievably appear?
--
Message posted via http://www.sqlmonster.com|||CharlesT via SQLMonster.com wrote:
> okay, well suppose I have an SQL database of availible rental properties.
> People in a company update this database frequently. They want to make a .NET
> application that will allow user on the internet to search this database -
> users would be able to find online what properties are availible and their
> rates and pictures etc. Based on whatever search values the user inputs
> their result set may be unpredicably large. I could make .NET simply load
> the entire database each time the user makes a search into a dataset but this
> takes time and resources and it is likely that the user might want to change
> their search after seeing that hundreds of properties have met their criteria
> etc. Alternatively, I could have the user's search run a stored procedure
> that uses a cursor on the SQL database to fetch the 1st 10 rows, then if the
> user decides to keep looking fetches the next 10 rows etc. I understand how
> the cursor in the stored procedure works and I can estimate its effect on
> locking and even change its isolation level based on network traffic etc.
> Performance wont be a factor at all when dealing with only 10 rows. The
> variable which stores how deep the user was in the database could exist in
> the application so the cursor can be closed and reopened each time the user
> selects next so it doesnt hold locks etc. Or, and this is the thing I dont
> get, I could use the ExecuteReader() object from .NET to access the database.
> I suppose that since this is using ADO it is not an API cursor, but then what
> is it' what is going on in SQL when an outside application runs a cursor in
> SQL via ADO. What is the concurrency price of possible simultaneous
> SELECT/UPDATE to an SQL DB from inside a company with people accessing it
> over the internet with a web application? If .NET doesnt use API cursors
> when it reads data from SQL with ADO then what does it use? and if it isnt
> API cursors then what is the fuss over API cursors that I see mentioned
> everywhere in SQL books and where would they ever concievably appear?
> --
> Message posted via http://www.sqlmonster.com
For your scenario you should take a look at some of the solutions
offered here:
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--