.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
How to get ADO.NET DataTable off a SqlServer SProc?
Messages   Related Types
This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
Post a new message to this list...

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>
Reply to this message...
 
    
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" <Click here to reveal e-mail address> wrote in message
news:eNyNS%Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
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.

"W.G. Rowland" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
Sushil Chordia (VIP)
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.
"A Traveler" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
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

"A Traveler" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
 
System.Data.CommandType
System.Data.Common.DataAdapter
System.Data.DataSet
System.Data.DataTable
System.Data.SqlClient.SqlCommand
System.Data.SqlClient.SqlConnection
System.Data.SqlClient.SqlDataAdapter
System.Data.SqlClient.SqlParameter




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification