Stored Procedure as dataset

[patrick24601]

Is it possible to return the results of a stored procedure as an ASp.net dataset? Can I get some hints? I have been looking but cannot seem to find any examples.

[Robby]

Setr the CommandType to Stored Proc, and the SP name in CommandText.

 

As long as you have a resultset coming back from the stored proc it will fill the dataset

[patrick24601]

So are you saying that I could have referred to a dataset in the code below (instead of the way I did it)

 

---

       Dim cmd As New SqlCommand
       Dim conn As New SqlConnection(hpdmdbConnString)
       Dim counter As Integer

       cmd.Connection = conn

       cmd.CommandType = CommandType.StoredProcedure
       cmd.CommandText = "usp_rolelookup"
       cmd.Parameters.Add("@username", currentuser)

       ' Open the connection
       conn.Open()
       ' And run the stored procedure command we have set up in CMD
       objreader = cmd.ExecuteReader
       ' Did we get any rows back? If so then loop through our objreader and add the single column of data
       ' to the arraylist.
       counter = 0
       If objreader.HasRows Then
           While (objreader.Read())
               counter = counter + 1
               getUserRoles.Add(objreader.GetString(0))
           End While
       End If

[a_jam_sandwich]

               Dim MyAdapter As New SqlClient.SqlDataAdapter("usp_rolelookup", conn)
               Dim MyDataset As New DataSet
	' set the command type
               MyAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
	' add the parameters
               MyAdapter.SelectCommand.Parameters.Add("@username", currentuser)
	' Use the dataadapter to fill the dataset
               MyAdapter.Fill(MyDataset)
               If MyDataset.Tables.Count > 0 Then
		' do somthing with the dataset
               End If

 

Nice and easy use the dataadapters fill command to fill a disconnected dataset with the stored procedures results

 

Regards

 

Andy

[patrick24601]

A simple thanks does not do your post justice. So imagine this is a big giant thank you.

[Robby]

Thanks Andy...