How to work with multiple results set using DataSet?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 21765 red flag
Rating: 5 out of 5  
 1 vote(s)

In this article, we shall learn how to work with multiple results set using DataSet.

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In order to retrieve more than one result sets from the Stored Procedure or multiple select statements at one go
using DataSet, we can use this approach.

Below is the GridView code on my ASPX Page

ASPX PAGE

<asp:GridView ID="GridView1" runat="server" EnableViewState="false" />

<hr />

<asp:GridView ID="GridView2" runat="server" EnableViewState="false" />

In the above code snippet, we have two GridViews.

CODE BEHIND

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

 

protected void Page_Load(object sender, EventArgs e)

{

DataSet dSet = new DataSet();

 

// get the connection

using (SqlConnection conn = new SqlConnection(_connStr))

{

// write the sql statement to execute

string sql = "SELECT AutoID, FirstName, LastName FROM PersonalDetail ORDER BY FirstName; " +

"SELECT AutoId, FileName FROM Files Order By FileName ASC";

// instantiate the command object to fire

 

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

using (SqlDataAdapter ad = new SqlDataAdapter(cmd))

{

ad.Fill(dSet);

 

// 1st result set

GridView1.DataSource = dSet.Tables[0];

GridView1.DataBind();

 

// 2nd result set

GridView2.DataSource = dSet.Tables[1];

GridView2.DataBind();

}

}

}

}

In the code behind, we have used DataSet to execute a SQL statement that has two SELECT statements separated by semi-column (;) (Multiple select statements returns multiple result sets from the database). 

DataSet is a collection of DataTables that can hold multiple result sets, so we have used DataSet here. Instead of the SQL statement, we can also use a stored procedure that can return multiple result set by executing multiple select statements.

All the select statements executed will have a separate DataTable created into the DataSet and we can
access them by using the indexer of the Tables collection (like we have used dSet.Tables[0] and
dSet.Tables[1] to return the 1st and the 2nd result set).

OUTPUT

My output looks like above picture.

Thanks for reading, hope you are liking my series of ADO.NET articles that describes several frequently used solutions of ADO.NET problems. To watch video tutorials of ADO.NET and ASP.NET problem solutions, click here.

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com

Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Ksuresh on: 9/3/2011 | Points: 25
Thanks,

Regards
suresh

Login to post response

Comment using Facebook(Author doesn't get notification)