Using OleDBDataReader (C#) to retrieve stored proc return code and then resultset

[dshiel]

Hi there,

I am trying to first retrieve a stored proc Return Value to determine
what happened in the proc, and then if the value = 0 (Success in my
case) I want to iterate through the resultset to show my data to the
user.

Is this possible??

I'm using OleDB in ASP.NET. Win2000, .NET 1.1.

Heres my code so far:

// Connect to me database
m_DB = new OleDbConnection(CConfigInfo.DBConnectionString);
m_DB.Open();

// Run my stored proc
OleDbCommand m_dbCmd
m_dbCmd = new OleDbCommand({ ? = CALL sp_GUI_PedsGET }, m_DB);
m_dbCmd.CommandType = CommandType.Text;

OleDbParameter cParam = m_dbCmd.Parameters.Add("@RETURN_VALUE",
OleDbType.Integer);
cParam.Direction = ParameterDirection.ReturnValue;

OleDbDataReader cDataReader = m_dbCmd.ExecuteReader();
cDataReader.Close();

// at the moment I have to close the DataReader to read the return
value, but I dont know how to reopen it so I can iterate through
// the resultset. If I dont close the DataREader I can iterate through
the resultset but cant read the return value. Arrrggghhhhh!

switch ((int)(cParam.Value))
{
case 0:
ProcessResultset(cDataReader);
break;
....
}

I have done this without any problems in C++ so it must be possible.

Thanx in advance,
Dave.

 

[William Ryan eMVP]

You need to open the reader and have it closed to get the value. Since you
are using the 1.1 framework, you can use DataReader.HasRows to determine if
you have rows, I don't know if that' sequal to success or failure here but
datareaders are connected objects so you are going to have to do somethign
Open to get what you need.

Cheers,

Bill

 

[Dave Shiel]

Hi there Bill,

Thanx for getting back to me.

The return value is designed to provide me with additional info, like
@@ERROR in the event of a SQL error, or if say one of my input params
was used to do a lookup on another table and that lookup returned no
results. This info is basically used for logging purposes so that user
has a better chance at determining what went wrong.

Actually, when I think of it, if an error occurs I wont have any rows in
my resultset, so your suggestion will work.

Nice one!

Coming from C++/ATL world, I still think that having to close the
DataReader to check return values and output parameters of a stored
procedure (that also returns a resultset) is a bit of a flaw. Maybe the
lads at Microsoft will improve on this and make this possible in the
future ;-)

Thanx again.
Later,
Dave.

 

[William Ryan eMVP]

Dave:

It's not really the fault of ADO.NET. When you use an Output Param or
ReturnVlaue, the server can't process those values until everything is
processed and returned to the client. As such, it's returned after the
actual rows are. Positionally, the data is stored at the end so it's like
reading a file and having some text you are searching for at the end. You
may be interested in the stuff before it, but if you want to find out what
the last 4 bytes are for instance, you have to read until the end. You have
to open the file which is roughly analogous to calling .Read on the
datareader. Let's say though that they could pack it in the front (which
arthitecturally in SQL Server would be difficult to say the least), when you
did a While dr.Read the data from the output params or return value would be
there in the resultset in front of your records. Since OutputParams and
RetVal aren't used more than they are not used, this would inconvenience
many people. If they gave you an option, it would require the server to
have to process data in two different ways which would be really ugly..

I know it's a bit inconvenient, but all things considered, I can't really
think of any other viable options.

BTW, Bill Vaughn has a superb discussion if you go to
www.betav.com ->Articles-> MSDN Retrieving the Gozoutas.

HTH,

bill