Question about datareaders... vs2005

[Ian]

I wish to use a datareader to loop through the result set of a stored
procedure. (Several resultsets actually.)

It's for rendering HTML, so I don't need to ever keep this data, just
stream through it.

If I create a dataset, I can create a queryadapter, and then add
sprocs. It will only let me create that sproc as a return a value
(which I guess is the old RETURN_VALUE param) or return nothing.

It won't let me return a datareader.

If I create a sproc as the select statement as a table adapter, it
won't let me return anything other than a table, or a filltable. I
can't get at the datareader there either.

Further, if I add to the class via the partial class mechanism, I can't
see the _commandcollection object.

Any ideas?

[Robin Tucker]

Why not just execute the SP and then read back the results with the data
reader? You are just streaming through it after all, so no need for any of
these fancy .NET components.

Dim DataReader As SqlDataReader

' Guard.

Try

Dim Command As New SqlCommand

Command.Connection = m_Connection
Command.CommandText = "myStoredProcedure"
Command.CommandType = CommandType.StoredProcedure

Command.Parameters.Add("@some_parameter", SqlDbType.NText).Value =
some_parameter.value
Command.Parameters("@some_parameter").Direction =
ParameterDirection.Input

DataReader = Command.ExecuteReader()

If DataReader.HasRows = True Then

While DataReader.Read()

End While

End If

Catch sqlEx As SqlException

Finally

If Not DataReader Is Nothing Then
DataReader.Close()
End If

End Try


"Ian" <dr*****@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I wish to use a datareader to loop through the result set of a stored
procedure. (Several resultsets actually.)

It's for rendering HTML, so I don't need to ever keep this data, just
stream through it.

If I create a dataset, I can create a queryadapter, and then add
sprocs. It will only let me create that sproc as a return a value
(which I guess is the old RETURN_VALUE param) or return nothing.

It won't let me return a datareader.

If I create a sproc as the select statement as a table adapter, it
won't let me return anything other than a table, or a filltable. I
can't get at the datareader there either.

Further, if I add to the class via the partial class mechanism, I can't
see the _commandcollection object.

Any ideas?

[Ian]

Skip that rubbish I just wrote. I was trying to view the adapter's
internals from code which was actually the partial dataset class.

If you include
Namespace AdapterName
partial public class usbdoittableadapter
... then you can add a property which returns a datareader.

[Ian]

Robin Tucker wrote:

Why not just execute the SP and then read back the results with the data
reader? You are just streaming through it after all, so no need for any of
these fancy .NET components.

Oh I can do this no problem, but I spend my life these days assessing
new bits of code, so junior programmers can do it without typing
anything.

Dragging a sproc onto a form used to write all the parameters for you.
Some sprocs may have thirty parameters.

I love the way the sproc calling now just looks like a class
invocation, but to complete the picture it would have been brilliant
had it been implemented as an executereader via iterator as well,
instead of just being executenoquery and executescalar...

Eg,
dim myClass as new myDataset.MyQueryAdapter.

For each datarow as DataRow in myClass.mySproc.(Param1, Param2,
Param3).... ' execute reader call

This way, all the working out of how to set the thing up would have
been automated.

My reasons for doing this is to stop devs wasting a week writing code
to set up the parameters etc.

This may be actually possible, I just haven't found it yet.

Regards,
Ian

(ps Thanks for your answer anyway.)

Dim DataReader As SqlDataReader

' Guard.

Try

Dim Command As New SqlCommand

Command.Connection = m_Connection
Command.CommandText = "myStoredProcedure"
Command.CommandType = CommandType.StoredProcedure

Command.Parameters.Add("@some_parameter", SqlDbType.NText).Value =
some_parameter.value
Command.Parameters("@some_parameter").Direction =
ParameterDirection.Input

DataReader = Command.ExecuteReader()

If DataReader.HasRows = True Then

While DataReader.Read()

End While

End If

Catch sqlEx As SqlException

Finally

If Not DataReader Is Nothing Then
DataReader.Close()
End If

End Try


"Ian" <dr*****@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I wish to use a datareader to loop through the result set of a stored
procedure. (Several resultsets actually.)

It's for rendering HTML, so I don't need to ever keep this data, just
stream through it.

If I create a dataset, I can create a queryadapter, and then add
sprocs. It will only let me create that sproc as a return a value
(which I guess is the old RETURN_VALUE param) or return nothing.

It won't let me return a datareader.

If I create a sproc as the select statement as a table adapter, it
won't let me return anything other than a table, or a filltable. I
can't get at the datareader there either.

Further, if I add to the class via the partial class mechanism, I can't
see the _commandcollection object.

Any ideas?