fill dataset from Sql Server stored procedure?

[Guest]

I have a stored procedure on Sql Server2k. I can fill a data table which I
can append to a dataset using an ADODB recordset object which gets populated
from a command object that runs the sp. I was hoping to use a DataAdapter.
But I think the data adapter only uses select statements. I could write the
sp in my vb.net app, but the sp references UDF's I wrote in the Sql Sever. I
will guess that I will need to stick with the ADODB recordset object for
this. But I am open to any better suggestions on how to fill the
datatable/dataset with the data from the stored procedure.

Thanks,
Rich

 

[Guest]

OK. Now I remember my dilema. ADO.Net doesn't use recordset objects. So
how can I fill my data table/dataset from a stored procedure?

 

[Guest]

Of course, the SqlDataReader. It's all coming back to me. Just haven't done
this for a while. Actually, I want to populate a datagrid with this. So I
am thinkin I will populate the datatable from the Reader object and set the
datagrid datasource to the datatable. Am I on track here? can a reader be
used as a datasource?

 

[Roger]

how can I fill my data table/dataset from a stored procedure?
Hi,
I am not an expert, but set the CommandType
to StoredProcedure.
CommandText to your StoredProc name.

I was hoping to use a DataAdapter.
But I think the data adapter only uses select statements

DataAdapter has Select, Insert and Delete statements.

Then, DataAdapter.Fill(DataTable)
Of course, Connection must be open.
Hope that helps,
Roger

 

[Steve]

The following code will connect to an SQL Server 2000 database, retrieve
records from a table, fill a dataset, and bind the dataset to a datagrid
object.

dim conn as SqlConnection = new SqlConnection("Data
Source=(local);Integrated Security=SSPI; Initial Catalog=northwind")

dim da as SqlDataAdapter = new SqlDataAdapter("SELECT CustomerID,
ContactName FROM Customers", thisConnection)

dim ds as dataset = new DataSet

da.Fill(ds, "Customers")
datagrid1.datasource = ds
datagrid1.databind

It isn't necessary to call the databind method if you're creating a Windows
Forms application, but is required for ASP.NET.

 

[Steve]

Oops.. when converting this code from C# to VB.NET, I missed changing
"thisconnection" to "conn". Change that and the code should work.

 

[Cor Ligthert]

Rich,

When you combine the answers from Roger and Steve, than you have your
answer,

I hope this helps,

Cor

 

[Guest]

Actually, I ended up using a datareader to load the data from the stored
procedure and then loaded a datatable in a dataset from the datareader and
binded that to the datagrid. My question is if it is possible to use a
dataAdapter with a stored procedure? I don't think it is, is it?

 

[Guest]

Rich,

Here is an example. Note that this particular stored procedure requires a
parameter. Also note that this code uses OleDb objects, but you can easily
change it to use SQLClient:

Public Function GetRentedTapesByCustomerID(ByVal CustomerID As Integer)
As DataTable

Dim cn As OleDb.OleDbConnection = Settings.GetConnection()
Dim cmd As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "RentedTapesByCustomerID"
cmd.Parameters.Add("@CustomerID", CustomerID)
cn.Open()
cmd.Connection = cn
da.SelectCommand = cmd
da.Fill(dt)
cn.Close()

Return dt

End Function

Kerry Moorman

 

[Brian Henry]

why recode for what is already coded? use a data adapter it will do all the
reader work and such for you... using a reader to do it alone is dangerous
because you can only have one open at once and have to make sure you close
it when done, etc...

 

[Guest]

Thanks all for your replies. I didn't think the datareader was the way to
go. Also, I broke down and used the SqlDataAdapter wizard. It had stored
procedures as an optioin (I am such a lamo). Anyway, now the explanations
all make sense. Everything working OK, finally.

Thanks all for your help.