Pulling Information using DataAdapter with ADO.NET - How to retrieve several rows returned by a stored procedure using "DataAdapter" in ADO.NET from ASP.NET
(Page 2 of 4 )
Until now, we have seen only "DataReader." Now, let us try to do the same things using "DataAdapter."
I will try to use the same stored procedure available in the previous section to retrieve more than one row.
The steps will be a bit different from what we examined earlier in this series. The following are the steps involved in working with "DataAdapter."
- Create and open a SQL Server connection (using an "SQLConnection" object).
- Create a "SQLCommand" Object and specify its properties.
- Create a SQL Server adapter object based on the "SQLCommand" object.
- Assign the "SQLConnection" object to "SQLDataAdapter" object.
- Create a new "DataTable" object ( to hold all the rows).
- Execute the stored procedure using the "Fill" method and assign the result to a "DataTable" object.
- Dispose of "SQLDataAdapter" and release all other memory resources.
You can observe that the steps are quite different from any of my earlier methods. Try to create the same form (with a different name) as I mentioned in the previous section. After designing the form, switch to the code and add the following line at the top.
Imports System.Data.SqlClient
Add the following code to your "Retrieve Employees" button:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim name As String
Dim cn As New SqlConnection("Data Source=.;initial
catalog=Northwind;user id=sa")
Dim cmd As New SqlCommand
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_emp_getAllEmployees"
.Connection = cn
End With
Dim dt As New DataTable
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
da.Dispose()
cmd.Dispose()
cn.Close()
Me.DataGrid1.DataSource = dt
Me.DataGrid1.DataBind()
dt.Dispose()
End Sub
Set the start page, execute your application (by pressing F5) and click on the button "Retrieve Employee Details." Once it executes successfully, you should be able to view the list of all employees in the data grid.
Next: How to retrieve only one row returned by a stored procedure using "DataAdapter" in ADO.NET from ASP.NET >>
More MS SQL Server Articles
More By Jagadish Chaterjee
