Pulling Information using DataAdapter with ADO.NET
(Page 1 of 4 )
This is the fourth article in a series focusing on working with stored procedures in ADO.NET. In this article we mainly focus on retrieving multiple rows from a stored procedure using DataReader, doing the same thing with DataAdapter, and using DataAdapter to retrieve multiple sets of data from a stored procedure.
A downloadable file for this article is available
here.
If you are new to stored procedures (or in accessing them using ADO.NET) in SQL Server, I strongly suggest you go through my first article in this series before proceeding further.
To work with the stored procedures in this article, you need the simple extra tables "emp" and "dept" within the "Northwind" database. The structures of those two tables are available in the first article of this series.
How to retrieve several rows returned by a stored procedure using "DataReader" in ADO.NET from ASP.NET
In my previous article, we saw several examples that covered working with DataReader. Before proceeding with DataAdapter, let us complete the matter of working with several rows using DataReader.
Before going to the ADO.NET code, we need to create simple stored procedure in SQL Server which returns some value. Using "Query Analyzer," execute the following script in the "Northwind" database.
CREATE PROCEDURE dbo.sp_emp_getAllEmployees
AS
SELECT * FROM emp
RETURN
The above stored procedure simply uses a SELECT statement to retrieve all employee rows from the table "EMP." Even though I used a simple SELECT, you can use any join or sub-query or correlated queries along with several conditions (according to your needs). The stored procedure is named "sp_Emp_getAllEmployees." Now, we need to go to ADO.NET to access the same in ASP.NET.
The steps will look very similar to the ones I specified in my previous article. I shall work directly with the code now. Add a new web form (call it "SeveralRowsUsingDataReader") with a single button captioned "Retrieve Employees" and a data grid (simply "datagrid1").
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
.Connection.Open()
Dim dr As SqlDataReader = .ExecuteReader
Me.DataGrid1.DataSource = dr
Me.DataGrid1.DataBind()
'release resources
.Connection.Close()
.Dispose()
End With
End Sub
Set the start page, execute your application (by pressing F5) and click on the button "Retrieve Employees." Once it executes successfully, you should be able to view the list of all employees in the data grid.
There is nothing special in the above code (you can refer to my previous article for explanations) except that I simply assigned the "DataReader" to a "DataGrid."
Next: How to retrieve several rows returned by a stored procedure using "DataAdapter" in ADO.NET from ASP.NET >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 12 
