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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 12:56:12 -0500

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
"Dave Shiel" <dshiel@flexicom.com> wrote in message
news:OOUDotLGEHA.1012@TK2MSFTNGP11.phx.gbl...
> 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.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!