How to get ADO.NET DataTable off a SqlServer SProc?

[A Traveler]

Help! Ive been searching google, msdn, trying every which way i can and
cannot get this to work.
I am new to SQL Svr, coming from an Oracle background.

I have a stored proc which given an input param of state returns the records
in that state. I know the proc works, because if i run it from Query
Analyzer, i get the proper results, so somehow, im doing something wrong in
my ADO.NET code.

The only thing i can find online is "it can be done", but not how. If anyone
can tell me how, and maybe include some links to some good doc on this
subject, id be grateful. Thanks. Code follows below.

<stored proc>
CREATE PROCEDURE PEOPLE_BY_STATE
(
@STATE VARCHAR(3)
)
AS
SET NOCOUNT ON

SELECT * FROM PEOPLE WHERE STATE = @STATE
GO
</stored proc>

 

[W.G. Rowland]

This is off the top of my head, so appologize for any mistakes.. I think
this should at least point you in the right direction.. (Using VB.Net..
Others will be similar, just diff syntax)

Dim con As New SqlConnection("ConString")

Dim cmd As New SqlCommand

Dim adp As New SqlDataAdapter

Dim ds As New DataSet

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[StoredProcedureName]"

cmd.Connection = con

adp.SelectCommand = cmd

adp.Fill(ds)


You could shorten that up by using the overloaded constructors of the
various parts (connection, command, adapter).. You could also skip the
adapter and use the cmd.ExecuteReader to get a datareader for the results
and read them into yout dataset or table that way..

Hope that helps.

WGRowland

 

[A Traveler]

W.G., thank you for the expedient reply, i will have to give that a shot
tomorrow. Can i ask though, how would using the Fill method, vs. say, the
ExecuteNonQuery (which is how it is done in Oracle), affect any additional,
outbound parameters that you might have? Would they still get filled
correctly? Say for example, you had a sproc which returned a recordset, and
also returned an OUT param with the number of records (not a practical
application i know, but just for arguments sake). Would using the
DataAdapter.Fill method still populate those out params?

Thanks in advance.

This is off the top of my head, so appologize for any mistakes.. I think
this should at least point you in the right direction.. (Using VB.Net..
Others will be similar, just diff syntax)

Dim con As New SqlConnection("ConString")

Dim cmd As New SqlCommand

Dim adp As New SqlDataAdapter

Dim ds As New DataSet

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[StoredProcedureName]"

cmd.Connection = con

adp.SelectCommand = cmd

adp.Fill(ds)


You could shorten that up by using the overloaded constructors of the
various parts (connection, command, adapter).. You could also skip the
adapter and use the cmd.ExecuteReader to get a datareader for the results
and read them into yout dataset or table that way..

Hope that helps.

WGRowland

Help! Ive been searching google, msdn, trying every which way i can and
cannot get this to work.
I am new to SQL Svr, coming from an Oracle background.

 

[W.G. Rowland]

I wish I had the answer to that. Though I imagine it would be easy enough
to test. Simply create the SqlParameter objects for the input and output
fields. Add them to the select command for the adapter.. Run the
adapter.Fill command and when it's finished see if the value of your output
parameter holds a value or not. Sorry I'm not more help, but let me know if
that actually works.. (I haven't spent a lot of time - read, "any" - working
with output parameters.)

WGR

W.G., thank you for the expedient reply, i will have to give that a shot
tomorrow. Can i ask though, how would using the Fill method, vs. say, the
ExecuteNonQuery (which is how it is done in Oracle), affect any
additional, outbound parameters that you might have? Would they still get
filled correctly? Say for example, you had a sproc which returned a
recordset, and also returned an OUT param with the number of records (not
a practical application i know, but just for arguments sake). Would using
the DataAdapter.Fill method still populate those out params?

Thanks in advance.

This is off the top of my head, so appologize for any mistakes.. I think
this should at least point you in the right direction.. (Using VB.Net..
Others will be similar, just diff syntax)

Dim con As New SqlConnection("ConString")

Dim cmd As New SqlCommand

Dim adp As New SqlDataAdapter

Dim ds As New DataSet

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[StoredProcedureName]"

cmd.Connection = con

adp.SelectCommand = cmd

adp.Fill(ds)


You could shorten that up by using the overloaded constructors of the
various parts (connection, command, adapter).. You could also skip the
adapter and use the cmd.ExecuteReader to get a datareader for the results
and read them into yout dataset or table that way..

Hope that helps.

WGRowland

Help! Ive been searching google, msdn, trying every which way i can and
cannot get this to work.
I am new to SQL Svr, coming from an Oracle background.

 

[Sushil Chordia]

The only difference when using Fill method, is that you get a ResultSet in
addition to the Output parameters. There is no affect on the parameters as
such, the parameter objects should have the results. Jus to make sure you
know:they will not be filled in the DataTable/DataSet.
--
HTH,
Sushil Chordia.
This posting is provided "AS IS" with no warranties, and confers no rights.

W.G., thank you for the expedient reply, i will have to give that a shot
tomorrow. Can i ask though, how would using the Fill method, vs. say, the
ExecuteNonQuery (which is how it is done in Oracle), affect any additional,
outbound parameters that you might have? Would they still get filled
correctly? Say for example, you had a sproc which returned a recordset, and
also returned an OUT param with the number of records (not a practical
application i know, but just for arguments sake). Would using the
DataAdapter.Fill method still populate those out params?

Thanks in advance.

This is off the top of my head, so appologize for any mistakes.. I think
this should at least point you in the right direction.. (Using VB.Net..
Others will be similar, just diff syntax)

Dim con As New SqlConnection("ConString")

Dim cmd As New SqlCommand

Dim adp As New SqlDataAdapter

Dim ds As New DataSet

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[StoredProcedureName]"

cmd.Connection = con

adp.SelectCommand = cmd

adp.Fill(ds)


You could shorten that up by using the overloaded constructors of the
various parts (connection, command, adapter).. You could also skip the
adapter and use the cmd.ExecuteReader to get a datareader for the results
and read them into yout dataset or table that way..

Hope that helps.

WGRowland

Help! Ive been searching google, msdn, trying every which way i can and
cannot get this to work.
I am new to SQL Svr, coming from an Oracle background.