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.
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)
// Put user code to initialize the page here
private void GetData()
//Step#1 : Database Connection
SqlConnection con=new SqlConnection("data source=home-pc;initial
//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
//Step#5 : Move to next result set
//Move to next result set
//Step # 6:Close Data reader
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.
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.