How to fill DataSet from stored procedure?

[Nikolay Petrov]

How to fill DataSet from stored procedure?

 

[One Handed Man \( OHM - Terry Burns \)]

It goes a bit like this . . .

Dim cmd As New OleDb.OleDbCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "MyStoredProcedure"

cmd.Connection = MyConnection


--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

 

[Nikolay Petrov]

I know this, but how actually to read the data returned by the stored
procedure and put it in the dataset?

 

[Shiva]

I assume you are using MS SQL Server.

-- Code Start --
Dim sqlCon As New SqlConnection (<connection string>)
Dim sqlAdp As New SqlDataAdapter
Dim sqlCmd As New SqlCommand
Dim ds As New DataSet

sqlCmd.Connection = sqlCon
sqlCmd.CommandText = <sp name>
sqlCmd.CommandType = CommandType.StoredProcedure
' Add any parameters to the stored proc here using sqlCmd.Parameters
collection
sqlAdp.SelectCommand = sqlCmd
sqlAdp.Fill (ds)
-- Code End --

How to fill DataSet from stored procedure?

 

[Jeff Johnson [MVP: VB]]

How to fill DataSet from stored procedure?

Create a xxxCommand object around the stored procedure and then use that
object when creating the xxxDataAdapter.

Air code:

Dim cnn As SqlConnection = New SqlConnection("Data Source=MySrv;Initial
Catalog=MyDB;Integrated Security=SSPI")
cnn.Open()
Dim cmd As SqlCommand = New SqlCommand("MySP", cnn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet

da.Fill(ds, "MyTable")

 

[Matt S]

Try this.....

' Open db connection

Dim cnn As New SqlConnection(CONNECTION_STRING)

cnn.Open()

' Set command for the stored procedure

Dim cmd As New SqlCommand

With cmd

..CommandType = CommandType.StoredProcedure

..Connection = cnn

..CommandText = "CustOrderHist"

..Parameters.Add("@CustomerID", "ALFKI")

End With

' Create data adapter

Dim da As New SqlDataAdapter(cmd)

' Fill dataset from adapter

Dim ds As New DataSet

da.Fill(ds)



Hope this helps.

 

[Nikolay Petrov]

Maybe my question was wrong.
I needed to know does any data will be returned from stored procedure select
statment to dataset.
I mean when the sqlcommand.commandType is text and .CommandText is the SQL
statemen everything works ok, but when the same sql statment is in stored
procedure does the DataSet iss filled properly?

 

[Matt S]

Whatever the stored procedure returns will be populated in the dataset -
exactly the same as if the commandType is Text and a SQL statement is
provided. Make sure the stored procedure you are using is returning what
you are expecting in query analyser or similar then use the code posted
previously - it should work.

Hope this helps.

 

[Nikolay Petrov]

Thank you all guys

Create a xxxCommand object around the stored procedure and then use that
object when creating the xxxDataAdapter.

Air code:

Dim cnn As SqlConnection = New SqlConnection("Data Source=MySrv;Initial
Catalog=MyDB;Integrated Security=SSPI")
cnn.Open()
Dim cmd As SqlCommand = New SqlCommand("MySP", cnn)
cmd.CommandType = CommandType.StoredProcedure
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet

da.Fill(ds, "MyTable")

 

[Jeff Johnson [MVP: VB]]

Maybe my question was wrong.
I needed to know does any data will be returned from stored procedure select
statment to dataset.
I mean when the sqlcommand.commandType is text and .CommandText is the SQL
statemen everything works ok, but when the same sql statment is in stored
procedure does the DataSet iss filled properly?

Yes, IF the stored procedure returns a resultset. If it's a stored procedure
that only sends back a return value and/or fills output parameters then it
won't fill the DataSet, but then you wouldn't choose such a stored procedure
in the first place, would you?