DataReader with a return parameter and an OUTPUT parameter.

  • Thread starter Thread starter Kevin Burton
  • Start date Start date
K

Kevin Burton

I have a stored procedure and I set up the command type
to be StoredProcedure with the return value and the
output parameter set to InputOutput. This StoredProcedure
also returns a set of rows. From the best that I can tell
if I use ExecuteReader() the first time it returns the
OUTPUT parameter and it takes a second time to actually
get the data. Is this the default behavior or is there
some special setup to get this to return the data AND the
OUTPUT parameter in one call to ExecuteReader()?

Thank you for your suggestions.

Kevin
(e-mail address removed)
 
See my article on this subject at www.betav.com\articles (it's one of the
MSDN articles).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thank you for your help. This article was very
enlightening but unfortunately my problem was not solved.
The first data read from the DataReader.Read method
returned false and the NextResult also returned false.
Here is a snippet of code:

using(SqlDataReader reader = queryCommand.ExecuteReader
(CommandBehavior.CloseConnection))
{
.. . .
int rowCount = 0;
ArrayList rows = new ArrayList();
do
{
while(reader.Read())
{
// Copy the results
.. . .
}
} while(reader.NextResult());
}

The header of the SP looks like:

CREATE PROCEDURE dbo.pphtran_get_trans
(
@User_Transaction_Type varchar(10), -- Posted,
Pending, Authorized, Denied
@Alias_ID bigint,
@Tran_DateTime varchar(20), -- date
format 'yyyy-mm-dd hh:mm:ss'
@Rows_On_Page smallint,
@Page int,
@Row_Count int OUTPUT
)

Kevin
 
Just to be complete I tried:

SqlCommand queryCommand = connection.CreateCommand();
SqlDataAdapter da = new SqlDataAdapter
("pphtran_get_trans", connection);
da.SelectCommand =
queryCommand =
this.TransactionType.InitializeCommand(queryCommand,
this.TransactionType.Type.ToString(),
this.StartRow/this.MaxRows, this.MaxRows, ref
returnValueParameter, ref rowCountParameter);

DataSet ds = new DataSet();
int fillRows = da.Fill(ds);

And the fillRows return from the Fill method was zero yet
the output parameter returned 7 which is the number of
rows that I expected.

Kevin
 
Maybe it is the way I am setting up the parameters:

public SqlCommand InitializeCommand
(SqlCommand cmd, string type, long startPage, long
rowsOnPage, ref SqlParameter returnValue, ref
SqlParameter rowCount)
{
cmd.CommandType =
CommandType.StoredProcedure;

cmd.Parameters.Add
("@User_Transaction_Type", SqlDbType.VarChar, 10);
cmd.Parameters
["@User_Transaction_Type"].Value = type;
cmd.Parameters.Add("@Alias_ID",
SqlDbType.BigInt);
cmd.Parameters["@Alias_ID"].Value
= this.AliasId;
cmd.Parameters.Add
("@Tran_DateTime", SqlDbType.VarChar, 20);
cmd.Parameters
["@Tran_DateTime"].Value = this.TransactionDate.ToString
("yyyy-MM-dd hh:mm:ss");
cmd.Parameters.Add
("@Rows_On_Page", SqlDbType.SmallInt);
cmd.Parameters
["@Rows_On_Page"].Value = rowsOnPage;
cmd.Parameters.Add("@Page",
SqlDbType.Int);
cmd.Parameters["@Page"].Value =
startPage;
rowCount = cmd.Parameters.Add
("@Row_Count", SqlDbType.Int);
cmd.Parameters
["@Row_Count"].Direction = ParameterDirection.InputOutput;
if(startPage == 0)
{
cmd.Parameters
["@Row_Count"].Value = 0;
}
else
{
cmd.Parameters
["@Row_Count"].Value = 1;
}
returnValue = cmd.Parameters.Add
("@ReturnValue", SqlDbType.Int);
cmd.Parameters
["@ReturnValue"].Direction =
ParameterDirection.ReturnValue;
cmd.CommandText
= "pphtran_get_trans";
return cmd;
}

I have tried this stored procedure with the Query
Analyzer and it works just fine. I have included a simple
test script to give you an idea of the calling procedure.

declare @RowCount int,
@alias_id int,
@tran_datetime varchar(20),
@Row_On_Page int,
@Page int

SET @RowCount = 0
SET @Row_On_Page = 10
SET @Page = 1
SET @tran_datetime = convert(varchar
(20),current_timestamp,120)

SET @alias_id = 307
SET @RowCount = 0
EXEC dbo.pphtran_get_trans 'Posted', @alias_id,
@tran_datetime, @Row_On_Page, @Page, @RowCount OUTPUT
print @RowCount

Thanks again.

Kevin
 
So, if a rowset returned it would be in one of the DataTable objects in the
Tables collection. What does Tables.Count return?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Which means there were no resultsets that contain rowsets returned.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top