Can we connect two datareader to same data source using single connection at same time?

No, we cann't since once connection to database is opened must be closed before you reopen again .

Editorial / Best Answer

Answered by: Sathiyavathi

  • Mar 24th, 2006


 We can do it in ADO.Net 2.0 as

   In your connection string there is an additional attribute named as MARS

and set it as true. MARS stands for Multiple Active Result Sets.

  Ex :

    string connectionString = "Data Source=MSSQL1;" +
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI" +
    "MultipleActiveResultSets=True";

Showing Answers 1 - 5 of 5 Answers

Shree

  • Mar 17th, 2006
 

No. You will get a runtime error:// System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.//SO, Connection has to be closed before it is used for another DataReader.

  Was this answer useful?  Yes

AD

  • Mar 23rd, 2006
 

You can just close the datareader instead of closing the connection.

  Was this answer useful?  Yes

Karthik

  • Mar 24th, 2006
 

Yes, you can connect two datareader to the same datasource, but one mainthing is close the first datareader before using second one then only it's possible.

  Was this answer useful?  Yes

Sathiyavathi

  • Mar 24th, 2006
 

 We can do it in ADO.Net 2.0 as

   In your connection string there is an additional attribute named as MARS

and set it as true. MARS stands for Multiple Active Result Sets.

  Ex :

    string connectionString = "Data Source=MSSQL1;" +
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI" +
    "MultipleActiveResultSets=True";

It is bit ambiguous, there are two scenerios
(1) want to connect two datareader to single database .
(2) want to connect two datareader to single database using single connection.

In first scenerio, it is possible to connect two datareader to single database using different connection.
But in second scenerio it is not possible to connect two datareader to single database connection

  Was this answer useful?  Yes

yes,we can connect two dataadaters to same datasource using single connection at same time.
There is a technology in ado.net 2.0 called MARS usinng Mars in connection string we can do it.
for eg:
  cn.ConnectionString = "server=(local); database=employee; integrated security=sspi; MultipleActiveResultSets=True";

  Was this answer useful?  Yes

Ravi

  • Sep 16th, 2011
 

Yes, you can connect two datareader to the same datasource, but one main thing is close the first datareader before using second one then only it's possible.

The point is, we can have any number of datareaders to the same datasource, but only one of them could have an active connection at any point of time.

  Was this answer useful?  Yes

Yes, 100% you can connect. help of-- Multiple Active Result Sets (MARS).

more details with Example visit below Microsoft official website. --

http://msdn.microsoft.com/en-us/library/yf1a7f4f%28v=vs.80%29.

Thanks
Rajan Vishwakarma

Code
  1. using System;

  2. using System.Data;

  3. using System.Data.SqlClient;

  4.  

  5. class Class1

  6. {

  7. static void Main()

  8. {

  9.   // By default, MARS is disabled when connecting

  10.   // to a MARS-enabled host such as SQL Server 2005.

  11.   // It must be enabled in the connection string.

  12.   string connectionString = GetConnectionString();

  13.  

  14.   int vendorID;

  15.   SqlDataReader productReader = null;

  16.   string vendorSQL =

  17.     "SELECT VendorId, Name FROM Purchasing.Vendor";

  18.   string productSQL =

  19.     "SELECT Production.Product.Name FROM Production.Product " +

  20.     "INNER JOIN Purchasing.ProductVendor " +

  21.     "ON Production.Product.ProductID = " +

  22.     "Purchasing.ProductVendor.ProductID " +

  23.     "WHERE Purchasing.ProductVendor.VendorID = @VendorId";

  24.  

  25.   using (SqlConnection awConnection =

  26.     new SqlConnection(connectionString))

  27.   {

  28.     SqlCommand vendorCmd = new SqlCommand(vendorSQL, awConnection);

  29.     SqlCommand productCmd =

  30.       new SqlCommand(productSQL, awConnection);

  31.  

  32.     productCmd.Parameters.Add("@VendorId", SqlDbType.Int);

  33.  

  34.     awConnection.Open();

  35.     using (SqlDataReader vendorReader = vendorCmd.ExecuteReader())

  36.     {

  37.       while (vendorReader.Read())

  38.       {

  39.         Console.WriteLine(vendorReader["Name"]);

  40.  

  41.         vendorID = (int)vendorReader["VendorId"];

  42.  

  43.         productCmd.Parameters["@VendorId"].Value = vendorID;

  44.         // The following line of code requires

  45.         // a MARS-enabled connection.

  46.         productReader = productCmd.ExecuteReader();

  47.         using (productReader)

  48.         {

  49.           while (productReader.Read())

  50.           {

  51.             Console.WriteLine("  " +

  52.               productReader["Name"].ToString());

  53.           }

  54.         }

  55.       }

  56.   }

  57.       Console.WriteLine("Press any key to continue");

  58.       Console.ReadLine();

  59.     }

  60.   }

  61.   private static string GetConnectionString()

  62.   {

  63.     // To avoid storing the connection string in your code,

  64.     // you can retrive it from a configuration file.

  65.     return "Data Source=(local);Integrated Security=SSPI;" +

  66.       "Initial Catalog=AdventureWorks;MultipleActiveResultSets=True";

  67.   }

  68. }

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions