datareader vs output parameters

  • Thread starter Thread starter John A Grandy
  • Start date Start date
J

John A Grandy

when wishing to read around 5-10 col values from a specific result row of a
sp ...

what are the trade-offs of the following two approaches:

1. write SP to return rowset, and use SQLDataReader to extract specific col
values

2. build the SP with an output type parameter for each col and then call
SQLCommand.ExecuteNonQuery and read each output parameter
 
I would think using output parameters is better because then you don't get
the DataReader back to the client code and iterate it there, but database
can run the query and return only results (not any cursor like system used &
provided for the client code)..

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist


"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
when wishing to read around 5-10 col values from a specific result row of a
sp ...

what are the trade-offs of the following two approaches:

1. write SP to return rowset, and use SQLDataReader to extract specific col
values

2. build the SP with an output type parameter for each col and then call
SQLCommand.ExecuteNonQuery and read each output parameter
 
John:

You can have it both ways. You can define Output Params and load set their
values in your proc even though you are using ExecuteReader. One caution
though, if you are using SQL Server, you need to Close the Reader before the
OutParams will be visible.

Overall, any approach with output params is probably better than grabbing a
rowset
 
ok, output params win from an efficient use of internal resources &
performance point-of-view.

but from a size-of-code & time-to-development point-of-view , it sure is
easier to write the data-reader code than it is to properly configure all
the output params and then read them.

did you see my other post regarding auto-building SQLCommand.Parameters ?
thanks.
 
John:

I would have to respectfully disagree, if you are using Stored Procs
already. You can create and use output params with the exact same number of
lines of code.

Also, and this is just MHO, the code issue would have to be pretty big to
negate the performance benefit. If you get a lot more users and your app is
sluggish you'll have to perform major surgery to fix it in most cases this
is a killer.

I didn't see your other post, but I'll take a look at it now.

Cheers,

Bill
 
Back
Top