Pulling Information using DataAdapter with ADO.NET
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."
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.
I already introduced "DataAdapter" in my previous section. We shall further extend it to get only a single row!
Let us consider a simple stored procedure as follows:
CREATE PROCEDURE dbo.sp_emp_getEmployeeDetails
(
@empno int
)
AS
SELECT * FROM emp
WHERE empno = @empno
RETURN
From the above stored procedure, it is quite certain that it returns only one row (or even none). I would like to read that row and present it in the text boxes!
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_getEmployeeDetails "
.Parameters.Add("@empno",1001)
.Connection = cn
End With
Dim dt As New DataTable
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
da.Dispose()
cmd.Dispose()
cn.Close()
Dim dr As DataRow = dt.Rows(0)
Me.txtEname.text = dr("ename")
Me.txtSal.text = dr("sal")
Me.txtDeptno.text = dr("deptno")
dt.Dispose()
End Sub
We need to concentrate only on the highlighted rows (in red). You can observe that I am retrieving only the first row from the data table using the following statement:
Dim dr As DataRow = dt.Rows(0)
Once we get the handle to that row, we can simply retrieve all the column information and assign it to the text boxes using:
Me.txtEname.text = dr("ename")
Me.txtSal.text = dr("sal")
Me.txtDeptno.text = dr("deptno")
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. If anything does not work, please drop me a line so that I can guide you. The entire solution for this article is freely available in the form of a zip downloadable.
Now, we shall examine an interesting issue -- multiple result sets. Before talking about multiple result sets, let us look at the following stored procedure:
CREATE PROCEDURE dbo.sp_getAll
AS
SELECT * FROM dept
SELECT * FROM emp
RETURN
The above stored procedure is very simple to understand. It simply returns two "sets" of data. The first set is all rows from the table "dept" and the other one is all rows from the table "emp." Since the stored procedure is trying to give back (or return) more than one set (or table) of information, we call it a stored procedure returning multiple result sets.
How about retrieving multiple result sets in .NET? We have two methods for doing this. And now I start with DataAdapter in this section.
The steps will be very similar to the ones I specified in the previous sections. I shall work directly with the code now. Add a new web form (call it "SPMultipleResultSets") with a single button captioned "Retrieve Multiple resultsets" and two data grids (simply "datagrid1" and "datagrid2").
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 Multiple resultsets" 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_getAll"
.Connection = cn
End With
Dim ds As New DataSet
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
da.Dispose()
cmd.Dispose()
cn.Close()
Me.DataGrid1.DataSource = ds.Tables(0)
Me.DataGrid1.DataBind()
Me.DataGrid2.DataSource = ds.Tables(1)
Me.DataGrid2.DataBind()
ds.Dispose()
End Sub
Set the start page, execute your application (by pressing F5) and click on the button "Retrieve Multiple resultsets." Once it executes successfully, you should be able to view the list of all departments and the list of all employees in the two data grids (separately).
Within the above code, I used "dataset" instead of "datatable." A dataset can contain any number of datatables inside it. And thus, we can fetch any number of multiple resultsets as well!
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Microsoft SQL Server 2000. If anything does not work, please drop me line so that I can guide you. The entire solution for this article is freely available in the form of a zip downloadable.
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
MS SQL Server 
ASP.NET Export to Excel and Word using VB.NET and C#
