Multiple Result Sets in ADO.net using SqlClient

Introduction

In today’s world most of the applications are data centric. Every form either windows or web has tons and tons of data to de displayed; the major chunk of the data is read only or for look up purpose. To get data from the database to the client,a good amount of resource are used up for establishing database connection; too many round trip calls to the server and there by increasing the burden on the backend servers and significant increase in network traffic. . Net has many cool features for SQL server 2000 to improve the over all scalability of the applications. We will see one of the cool futures today; retrieve multiple result sets in a single SqlDataReader. This feature is available only for SQL server, why not for Oracle?, well it’s any one’s guess. Anyway the same results can be achieved even for Oracle through other means; which we will discuss some time later. Well. It’s time to get to business. Let us open new ASP.Net and place two DropDownList and a DataGrid.

<form id="Form1" method="post" runat="server">
<asp:DropDownList id="cboCategory" runat="server"></asp:DropDownList>
<asp:DropDownList id="cboOrder" runat="server"></asp:DropDownList>
<asp:DataGrid id="DataGridProd" runat="server"></asp:DataGrid>
</form>

Don't for get to reference name space

using System.Data.SqlClient;

Let us examine the code step by step. Step#1 : Connect to Database Step#2 : Club all the Sql statements in to one string separated by “;” and pass into SQLCommand object Step#3 : Execute the SqlComman and capture the results into Sqldata reader Step#4 : Bind Data to corresponding server controls Step#5 : Use the NextResult()method of DataReader to get the next result set. Step#6 : Close the SqlDataReader and Database connection.

private void Page_Load(object sender, System.EventArgs e)
 {
  if (!IsPostBack){GetData();}
 // Put user code to initialize the page here
 }


 private void GetData()
 {
 //Step#1 : Database Connection
 SqlConnection con=new SqlConnection("data source=home-pc;initial 
 catalog=Northwind;User id="":Password="";);
 con.Open();
 //Step#2 : Pass multiple sql statements
 string sql="select * from categories;
 select * from orders;select top 5 * from products;";
 //Step#3 : Execute Command 
 SqlCommand cmd=new SqlCommand(sql,con);
 SqlDataReader Rd = cmd.ExecuteReader();

 //Step#4 : Categories
 cboCategory.DataSource=Rd;
 cboCategory.DataTextField="CategoryName";
 cboCategory.DataValueField="CategoryID";
 cboCategory.DataBind();
 //Step#5 : Move to next result set
 Rd.NextResult();		

 //Orders
 cboOrder.DataSource=Rd;
 cboOrder.DataTextField="ShipName";
 cboOrder.DataValueField="Orderid";
 cboOrder.DataBind();
 //Move to next result set
 Rd.NextResult();

 //Products
 DataGridProd.DataSource=Rd;
 DataGridProd.DataBind();
 //Step # 6:Close Data reader
 Rd.Close();
 //close connection
 con.Close();
		}

Note: Do not use the CommandBehavior.CloseConnection attribute with ExecuteReader() methord of SqlCommand object.This will cause an error. Each time we use the NextResult() method of SQLDataReader internally commandobject executes the next sql statement and fetches the next result set.

Conclusion

This is one of many things we have been missing for years; .net has cool stuff like this one, to cut short the development time. You can also use Dataset in the similar fashion.Whether to go for Dataset or Datareader depends on the architecture of the project and the business requirement.