Pulling Information using DataAdapter with ADO.NET - How to retrieve multiple result sets from stored procedure using "DataAdapter" in ADO.NET from ASP.NET
(Page 4 of 4 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
