MSSQL Output Cursor Parameter

  • Thread starter Thread starter Yusuf INCEKARA
  • Start date Start date
Y

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,"",DataRowVersion.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,"",DataRowVersion.Default,my_DBNull);

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

Regards.
 
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?
 
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]" <[email protected]>
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]
- (e-mail address removed)

Yusuf INCEKARA said:
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,"",DataRowVersion.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,"",DataRowVersion.Default,my_DBNull);

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

Regards.
Click to expand...
Click to expand...
 
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]
- (e-mail address removed)

Yusuf INCEKARA said:
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]" <[email protected]>
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]
- (e-mail address removed)

Yusuf INCEKARA said:
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,"",DataRowVersion.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,"",DataRowVersion.Default,my_DBNull);

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

Regards.
Click to expand...
Click to expand...
Click to expand...
 
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.StoredProcedure, 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.StoredProcedure, 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.
 
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]
- (e-mail address removed)




Yusuf INCEKARA said:
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.StoredProcedure, 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.StoredProcedure, 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.


in
message news:#[email protected]...
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?
Click to expand...
Click to expand...
 
Nicholas Paldino said:
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.
Click to expand...
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 .
 
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 said:
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]
- (e-mail address removed)




Yusuf INCEKARA said:
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.StoredProcedure, 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.StoredProcedure, 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.


in
message news:#[email protected]...
Yusef,

If you want transparency, then you aren't going to be able to
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top