Pages

Monday, July 27, 2009

How to use Multiple DataReaders in ADO.Net

Most of the folks says that we cannot use one more DataReader when one DataReader is Opened. It is obsolutely correct upto ADO.Net 1.0 version.

With ADO.Net 2.0 we can create DataReader while another DataReader is opened on the same SqlConnection.

Please note that the second DataReader should use the same SqlConnection.
Following is the sample to demonstrate the same (in C#):

static void Main(string[] args)
{

SqlConnection objCon = null;
try
{

string sConString = "Data Source=.\\sqlexpress;Initial Catalog=DB1;Integrated Security=True;MultipleActiveResultSets=true";

SqlDataReader dr1 = null, dr2 = null;

objCon = new SqlConnection();
objCon.ConnectionString = sConString;

SqlCommand objCmd = new SqlCommand();
objCmd.CommandText = "select top 10 * from Page";
objCmd.Connection = objCon;

SqlCommand objCmd1 = new SqlCommand();
objCmd1.Connection = objCon;

objCon.Open();

dr1 = objCmd.ExecuteReader();
while (dr1.Read())
{
Console.WriteLine(dr1.GetInt32(0).ToString());

objCmd1.CommandText = "select top 10 * from PageData Where PageId=" + dr1.GetInt32(0).ToString();

// Creating the Second DataReader
dr2 = objCmd1.ExecuteReader();

while (dr2.Read())
{
Console.WriteLine(dr2.GetInt32(1));
}

// Closing the Data Reader
dr2.Close();
}
// Closing the Data Reader
dr1.Close();

}
catch (Exception objExp)
{
Console.WriteLine("Exception: " + objExp.Message);
}
finally
{
if (objCon != null)
{
objCon.Close();
}
}
}

Please note that in the Connection String we have to add "MultipleActiveResultSets=true" for allowing mutiple DataReaders on the same Connection.

1 comment:

Joy said...

Hey, thanks so much for posting this article.

Thanks! if you want, you can check out my blog for ADO.NET Interview Questions and Answers

ADO.NET Interview Questions and Answers

Thanks
Joya