CODEWITHSUNDEEP
c#sql-server-2008t-sqlado.netcursor
Steve M
Steve M

Reputation: 53

Cursor as an output parameter

I have a stored procedure in SQL Server 2008 R2 that returns a Cursor as an OUTPUT value. Is it possible to return this Cursor to a C# application using the .NET 4.0 framework?

My Stored procedure has the following signature:

CREATE PROCEDURE [dbo].[MyProcedure]
      @nuserid    int,
      @nfetchtype int,
      @returncursor CURSOR VARYING OUTPUT
AS
...

At the end of the stored procedure I have

...
    set @returncursor = CURSOR FORWARD_ONLY STATIC for SELECT * FROM MyTable WHERE          column=@Value
    open @returncursor
END

In my C# Web Application, I use a wrapper that utilizes System.Data.DbType and I set my return value/parameter as DbType.Object. When I execute the query against the database, I get the following error:

Operand type clash: sql_variant is incompatible with cursor

This DbType.Object parameter will work for a REFCURSOR in Oracle but I'm curious to see if there is a way to do the same for SqlServer. My goal is to have stored procedures in both SqlServer and Oracle that have the exact same signature.

Upvotes: 1

Views: 4072

Answers (2)

Mohan
Mohan

Reputation: 1

You are not supposed to use cursors in database APIs such as OLE DB, ODBC, ADO, and DB-Library etc.

Please go through this article before using the cursors.

Upvotes: 0

Mr. TA
Mr. TA

Reputation: 5359

You can have a CURSOR parameter in SQL, but ADO.NET can't use it. Instead, in .NET, you have to use the IDataReader returned by IDbCommand.ExecuteReader(), and use NextResult() to iterate over your result sets.

Upvotes: 2

Related Questions