MSSQL Output Cursor Parameter

[Yusuf INCEKARA]

I have a stored procedure :

CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT
AS
set @RETCUR = CURSOR
FORWARD_ONLY STATIC FOR
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME
OPEN @RETCUR

It has an output cursor variable.

How can i use it in .net?

There is no any sqldbtype.Cursor parameter

object my_DBNull;

my_DBNull = Convert.DBNull;

SqlParameter pm = new SqlParameter ( "@RETCUR",SqlDbType.Variant ,2000,
ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default ,my_DBNull);

And above line is not working also.

I can do it in oracle with

create or replace procedure STP_GETSTORELIST(RETCUR out types.ref_cursor)
AS
begin
open RETCUR for
SELECT ID,STORE_NAME FROM T_INF_STORE;
end STP_GETSTORELIST;
OracleParameter pm = new OracleParameter ( "RETCUR",OracleType.Cursor,2000,
ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default,my_DBNull);

Please send answer also to my e-mail too. (if exist)

Regards.

[Nicholas Paldino [.NET/C# MVP]]

Yusuf,

Do you have to have it return a cursor? Have you considered just
performing the select, and then checking the results of the select?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Yusuf INCEKARA" <yi*******@mikrokom.com> wrote in message
news:eK**************@TK2MSFTNGP12.phx.gbl...

I have a stored procedure :

CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT
AS
set @RETCUR = CURSOR
FORWARD_ONLY STATIC FOR
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME
OPEN @RETCUR

It has an output cursor variable.

How can i use it in .net?

There is no any sqldbtype.Cursor parameter

object my_DBNull;

my_DBNull = Convert.DBNull;

SqlParameter pm = new SqlParameter ( "@RETCUR",SqlDbType.Variant ,2000,
ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default ,my_DBNull);

And above line is not working also.

I can do it in oracle with

create or replace procedure STP_GETSTORELIST(RETCUR out types.ref_cursor)
AS
begin
open RETCUR for
SELECT ID,STORE_NAME FROM T_INF_STORE;
end STP_GETSTORELIST;
OracleParameter pm = new OracleParameter (
"RETCUR",OracleType.Cursor,2000,
ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default,my_DBNull);

Please send answer also to my e-mail too. (if exist)

Regards.

[Yusuf INCEKARA]

My application Architect :

User Interface Layer (UIL)
Business Layer (BL)
Service Layer (SL)
Generic Data Access Layer(GDAL)

GDAL decides which provider to use at runtime.
All sql statements are in stored procedures.
GDAL Can use MSSQL , ORACLE , OLEDB , ODBC ... and any .net managed
provider.
For coding transparency i need to use output cursor. In my codes there is a
line

GDal.ExecCursorStp("STP_GETSTORELIST",CommandType. StoredProcedure, pmi,ref
ds);
where pmi is parameter array and ds is a Dataset.
It works fine with Oracle. And all other parts that are not returning an
output cursor are working fine too with both MSSQL and Oracle.
----- Original Message -----
From: "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
Newsgroups: microsoft.public.dotnet.languages.csharp
Sent: Monday, November 29, 2004 8:45 PM
Subject: Re: MSSQL Output Cursor Parameter

Yusuf,

Do you have to have it return a cursor? Have you considered just
performing the select, and then checking the results of the select?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Yusuf INCEKARA" <yi*******@mikrokom.com> wrote in message
news:eK**************@TK2MSFTNGP12.phx.gbl...

I have a stored procedure :

CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT
AS
set @RETCUR = CURSOR
FORWARD_ONLY STATIC FOR
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME
OPEN @RETCUR

It has an output cursor variable.

How can i use it in .net?

There is no any sqldbtype.Cursor parameter

object my_DBNull;

my_DBNull = Convert.DBNull;

SqlParameter pm = new SqlParameter ( "@RETCUR",SqlDbType.Variant ,2000,
ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default ,my_DBNull);
And above line is not working also.

I can do it in oracle with

create or replace procedure STP_GETSTORELIST(RETCUR out types.ref_cursor) AS
begin
open RETCUR for
SELECT ID,STORE_NAME FROM T_INF_STORE;
end STP_GETSTORELIST;
OracleParameter pm = new OracleParameter (
"RETCUR",OracleType.Cursor,2000,
ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default,my_DBNull);

Please send answer also to my e-mail too. (if exist)

Regards.

[Nicholas Paldino [.NET/C# MVP]]

Yusef,

If you want transparency, then you aren't going to be able to return the
cursor from SQL server like that, as I am not sure it is supported by the
framework.

I still don't understand why you can't just declare your stored
procedure like this:

CREATE PROCEDURE STP_GETSTORELIST
AS
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME

This would make it more universally accessible, no?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Yusuf INCEKARA" <yi*******@mikrokom.com> wrote in message
news:eG**************@TK2MSFTNGP11.phx.gbl...

My application Architect :

User Interface Layer (UIL)
Business Layer (BL)
Service Layer (SL)
Generic Data Access Layer(GDAL)

GDAL decides which provider to use at runtime.
All sql statements are in stored procedures.
GDAL Can use MSSQL , ORACLE , OLEDB , ODBC ... and any .net managed
provider.
For coding transparency i need to use output cursor. In my codes there is
a
line

GDal.ExecCursorStp("STP_GETSTORELIST",CommandType. StoredProcedure, pmi,ref
ds);
where pmi is parameter array and ds is a Dataset.
It works fine with Oracle. And all other parts that are not returning an
output cursor are working fine too with both MSSQL and Oracle.
----- Original Message -----
From: "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
Newsgroups: microsoft.public.dotnet.languages.csharp
Sent: Monday, November 29, 2004 8:45 PM
Subject: Re: MSSQL Output Cursor Parameter

Yusuf,

Do you have to have it return a cursor? Have you considered just
performing the select, and then checking the results of the select?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Yusuf INCEKARA" <yi*******@mikrokom.com> wrote in message
news:eK**************@TK2MSFTNGP12.phx.gbl...

>I have a stored procedure :
>
> CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT
> AS
> set @RETCUR = CURSOR
> FORWARD_ONLY STATIC FOR
> SELECT ID,STORE_NAME FROM T_INF_STORE
> ORDER BY STORE_NAME
> OPEN @RETCUR
>
> It has an output cursor variable.
>
> How can i use it in .net?
>
> There is no any sqldbtype.Cursor parameter
>
> object my_DBNull;
>
> my_DBNull = Convert.DBNull;
>
> SqlParameter pm = new SqlParameter ( "@RETCUR",SqlDbType.Variant ,2000,
> ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default ,my_DBNull); >
> And above line is not working also.
>
> I can do it in oracle with
>
> create or replace procedure STP_GETSTORELIST(RETCUR out types.ref_cursor) > AS
> begin
> open RETCUR for
> SELECT ID,STORE_NAME FROM T_INF_STORE;
> end STP_GETSTORELIST;
>
>
> OracleParameter pm = new OracleParameter (
> "RETCUR",OracleType.Cursor,2000,
> ParameterDirection.Output,true,0,0,"",DataRowVersi on.Default,my_DBNull);
>
> Please send answer also to my e-mail too. (if exist)
>
> Regards.
>
>
>
>
>

[Yusuf INCEKARA]

Well this is the code where i call database :

MWSNS.ParamStruct[] pmi = new MWSNS.ParamStruct[1];
pmi[0].Direction = ParameterDirection.Output ;

pmi[0].ParamName = "@RETCUR";

pmi[0].DataType = DbType.Object ;

pmi[0].Value = DBNull.Value;

DAL Dal=new DAL();

Dal.ExecCursorStp("STP_GETSTORELIST",CommandType.S toredProcedure, pmi,ref
ds);
In GDAL if DataType is object it convert it to Oracle.Cursor (if provider is
Oracle) or to SqlDbType.Variant (if provider is MSSQL)

return true;

It works with Oracle. If it works with MSSQL too i won't need any
customization in my project.

If not i have to customize code like this :

if (provider==EnumProviders.ORACLE)

{

MWSNS.ParamStruct[] pmi = new MWSNS.ParamStruct[1];

pmi[0].Direction = ParameterDirection.Output ;

pmi[0].ParamName = "@RETCUR";

pmi[0].DataType = DbType.Object ;

pmi[0].Value = DBNull.Value;

GDAL Dal=new GDAL();

Dal.ExecCursorStp("STP_GETSTORELIST",CommandType.S toredProcedure, pmi,ref
ds);
}

else

{

GDAL Dal=new GDAL();

dsMenu =Dal.ExecDataSet ("STP_GETSTORELIST",
System.Data.CommandType.Text);

}

Obviously i don't want to do this.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:#r**************@TK2MSFTNGP09.phx.gbl...

Yusef,

If you want transparency, then you aren't going to be able to return the cursor from SQL server like that, as I am not sure it is supported by the
framework.

I still don't understand why you can't just declare your stored
procedure like this:

CREATE PROCEDURE STP_GETSTORELIST
AS
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME

This would make it more universally accessible, no?

[Nicholas Paldino [.NET/C# MVP]]

Yusuf,

Right, but SQL Server doesn't support this construct, so if the oracle
SP was in this format as well, you could just wire up the SelectCommand
property of a DataAdapter with the command for the stored procedure, and
then populate a data set based on that.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com


"Yusuf INCEKARA" <yi*******@mikrokom.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

Well this is the code where i call database :

MWSNS.ParamStruct[] pmi = new MWSNS.ParamStruct[1];
pmi[0].Direction = ParameterDirection.Output ;

pmi[0].ParamName = "@RETCUR";

pmi[0].DataType = DbType.Object ;

pmi[0].Value = DBNull.Value;

DAL Dal=new DAL();

Dal.ExecCursorStp("STP_GETSTORELIST",CommandType.S toredProcedure, pmi,ref
ds);
In GDAL if DataType is object it convert it to Oracle.Cursor (if provider
is
Oracle) or to SqlDbType.Variant (if provider is MSSQL)

return true;

It works with Oracle. If it works with MSSQL too i won't need any
customization in my project.

If not i have to customize code like this :

if (provider==EnumProviders.ORACLE)

{

MWSNS.ParamStruct[] pmi = new MWSNS.ParamStruct[1];

pmi[0].Direction = ParameterDirection.Output ;

pmi[0].ParamName = "@RETCUR";

pmi[0].DataType = DbType.Object ;

pmi[0].Value = DBNull.Value;

GDAL Dal=new GDAL();

Dal.ExecCursorStp("STP_GETSTORELIST",CommandType.S toredProcedure, pmi,ref
ds);
}

else

{

GDAL Dal=new GDAL();

dsMenu =Dal.ExecDataSet ("STP_GETSTORELIST",
System.Data.CommandType.Text);

}

Obviously i don't want to do this.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:#r**************@TK2MSFTNGP09.phx.gbl...

Yusef,

If you want transparency, then you aren't going to be able to return

the

cursor from SQL server like that, as I am not sure it is supported by the
framework.

I still don't understand why you can't just declare your stored
procedure like this:

CREATE PROCEDURE STP_GETSTORELIST
AS
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME

This would make it more universally accessible, no?

[Yusuf INCEKARA]

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:#z**************@TK2MSFTNGP09.phx.gbl...

Yusuf,

Right, but SQL Server doesn't support this construct, so if the oracle
SP was in this format as well, you could just wire up the SelectCommand
property of a DataAdapter with the command for the stored procedure, and
then populate a data set based on that.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

SQL server supports this construct.
CREATE PROCEDURE STP_GETSTORELIST_CURSOR_VERSION
@RETCUR CURSOR VARYING OUTPUT
AS
set @RETCUR = CURSOR
FORWARD_ONLY STATIC FOR
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME
OPEN @RETCUR
it works fine. But this is not working in .net. I am harding to understand
why?
Also Oracle SP is not is this format too in native. You cannot do anyting in
oracle with returning cursor .
It is for supporting Java Developers. And .net is supporting this construct
too.
I just wondering why Microsoft support output cursors in Oracle but not in
their own database product .

[Yusuf INCEKARA]

And also i change my GDAL. If provider is MSSQL
it just ignore cursor output parameters.
Entire project does not need to change. I have solve it in GDAL.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:#z**************@TK2MSFTNGP09.phx.gbl...

Yusuf,

Right, but SQL Server doesn't support this construct, so if the oracle
SP was in this format as well, you could just wire up the SelectCommand
property of a DataAdapter with the command for the stored procedure, and
then populate a data set based on that.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com


"Yusuf INCEKARA" <yi*******@mikrokom.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

Well this is the code where i call database :

MWSNS.ParamStruct[] pmi = new MWSNS.ParamStruct[1];
pmi[0].Direction = ParameterDirection.Output ;

pmi[0].ParamName = "@RETCUR";

pmi[0].DataType = DbType.Object ;

pmi[0].Value = DBNull.Value;

DAL Dal=new DAL();

Dal.ExecCursorStp("STP_GETSTORELIST",CommandType.S toredProcedure, pmi,ref ds);
In GDAL if DataType is object it convert it to Oracle.Cursor (if provider is
Oracle) or to SqlDbType.Variant (if provider is MSSQL)

return true;

It works with Oracle. If it works with MSSQL too i won't need any
customization in my project.

If not i have to customize code like this :

if (provider==EnumProviders.ORACLE)

{

MWSNS.ParamStruct[] pmi = new MWSNS.ParamStruct[1];

pmi[0].Direction = ParameterDirection.Output ;

pmi[0].ParamName = "@RETCUR";

pmi[0].DataType = DbType.Object ;

pmi[0].Value = DBNull.Value;

GDAL Dal=new GDAL();

Dal.ExecCursorStp("STP_GETSTORELIST",CommandType.S toredProcedure, pmi,ref ds);
}

else

{

GDAL Dal=new GDAL();

dsMenu =Dal.ExecDataSet ("STP_GETSTORELIST",
System.Data.CommandType.Text);

}

Obviously i don't want to do this.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:#r**************@TK2MSFTNGP09.phx.gbl...

Yusef,

If you want transparency, then you aren't going to be able to return

the

cursor from SQL server like that, as I am not sure it is supported by

the framework.

I still don't understand why you can't just declare your stored
procedure like this:

CREATE PROCEDURE STP_GETSTORELIST
AS
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME

This would make it more universally accessible, no?

[Nicholas Paldino [.NET/C# MVP]]

Yusef,

Yes, SQL Server supports it, but the provider for SQL Server in .NET
doesn't support it. To that end, you will have to use something else if you
want to access it in .NET.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Yusuf INCEKARA" <yi*******@mikrokom.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:#z**************@TK2MSFTNGP09.phx.gbl...

Yusuf,

Right, but SQL Server doesn't support this construct, so if the
oracle
SP was in this format as well, you could just wire up the SelectCommand
property of a DataAdapter with the command for the stored procedure, and
then populate a data set based on that.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

SQL server supports this construct.
CREATE PROCEDURE STP_GETSTORELIST_CURSOR_VERSION
@RETCUR CURSOR VARYING OUTPUT
AS
set @RETCUR = CURSOR
FORWARD_ONLY STATIC FOR
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME
OPEN @RETCUR
it works fine. But this is not working in .net. I am harding to understand
why?
Also Oracle SP is not is this format too in native. You cannot do anyting
in
oracle with returning cursor .
It is for supporting Java Developers. And .net is supporting this
construct
too.
I just wondering why Microsoft support output cursors in Oracle but not in
their own database product .