How to work with multiple result sets using SqlDataReader?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 19539 red flag

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

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 using Stored Procedure or multiple select statements in one go using SqlDataReader, we can follow 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 above code snippet, we have two GridViews in which we shall bind both result sets.

CODE BEHIND

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

 

protected void Page_Load(object sender, EventArgs e)

{

// get the connection

using (SqlConnection conn = new SqlConnection(_connStr))

{

// write the sql statement to execute

string sql = "SELECT AutoID, FirstName, LastName, Active 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))

{

conn.Open();

// in case of multiple result set you will not be able to specify the

CommandBehavior

// if so you will not be able to get the next result set from the

reader

 

using (SqlDataReader reader = cmd.ExecuteReader())

{

// bind the first resultset

GridView1.DataSource = reader;

GridView1.DataBind();

 

reader.NextResult();

 

// bind the second resultset

GridView2.DataSource = reader;

GridView2.DataBind();

}

conn.Close();

}

}

}

In the code behind, we have executed two SQL statements (sql string variable has two SELECT statements separated by ";") in the SqlDataReader.

The first result set can be accessed by simply calling the reader object however to access the second result set, we need to explicitly call the NextResult method of the reader object.

Notice that we will not be able to specify the CommandBehavior.CloseConnection while executing the reader from the command object in this case (when the reader is returning two result sets), if so it throws following error while trying to access the next result set.

Exception Details: System.InvalidOperationException: Invalid attempt to call NextResult when reader is closed.

OUTPUT

Hope this article was useful, do let me know your comment or feedback.

In case you are looking for returning multiple results set using DataSet, use this article.

Stay tuned for many more articles and do not forget to refer these articles to your friend in need.

Thanks for reading.

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
http://www.snarayan.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

Login to post response

Comment using Facebook(Author doesn't get notification)