Open multiple datareader using one connecion

Posted by Thiru under VB.NET on 8/16/2012 | Points: 10 | Views : 24732 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

How to open multiple datareader using one connection

Like this:

Public Function CheckActiveReviews()
            Dim objCon As SqlConnection
            Dim objCommand As SqlCommand, objCommand2 As SqlCommand
            Dim objDR As SqlDataReader, objDR2 As SqlDataReader
            Try
                objCon = New SqlConnection("Data Source=TestDatabase;Initial Catalog=TestTable;User ID=TestUser;Password=TestPassword;MultipleActiveResultSets=True")
                objCommand = New SqlCommand
                objCommand.Connection = objCon
                objCommand2 = New SqlCommand
                objCommand2.Connection = objCon
                objCon.Open()
                objCommand.CommandText = "SELECT ID FROM Person WHERE PersonID > 1000"
                objDR = objCommand.ExecuteReader()
                Do While objDR.Read
                    objCommand2.CommandText = "SELECT * FROM Sport WHERE PersonID = @PersonID "
                    objCommand2.Parameters.AddWithValue("@PersonID", objDR("ID"))
                    objDR2 = objCommand2.ExecuteReader
                Loop

            Catch ex As Exception

            End Try

        End Function

I need to do using one connection without opening multiple connection for every datareader.

If its not available in vb.net - how to over come this in any other method.

Expecting your valuable reply.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Gopesh9 on: 8/16/2012 [Member] Starter | Points: 25

0	You can use more than one DataReader in one connection but before using the 2nd DataReader you have to close the 1st DataReader, after that only you can use the 2nd DataReader. G. S. .Net Developer Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 8/17/2012 [Member] Starter | Points: 25

0	Thanks for your reply Gopesh Sharma, As in the below example i need to assign the value from 1st datareader to 2nd datareader so, if i close the 1st one - i can't able to use it in 2nd one right ? Like: cmd = New MySqlCommand(QRY, Con) rdr = cmd.ExecuteReader While rdr.Read cmd1 = New MySqlCommand("Select * from table2 where Fld='" & rdr("fld3") & "'", Con) rdr1 = cmd1.ExecuteReader While rdr1.Read <do required action/things> End While End While It will be great if i get a solution to meet this requirement. I mean by having a loop i have to assign the value from command1 to command2; and command2 to command3.... like that. How to do this in vb.net ? Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Thanks for your reply Gopesh Sharma,

As in the below example i need to assign the value from 1st datareader to 2nd datareader
so, if i close the 1st one - i can't able to use it in 2nd one right ?

Like:

cmd = New MySqlCommand(QRY, Con)

            rdr = cmd.ExecuteReader

            While rdr.Read



                cmd1 = New MySqlCommand("Select * from table2 where Fld='" & rdr("fld3") & "'", Con)

                rdr1 = cmd1.ExecuteReader

                While rdr1.Read

                       <do required action/things>

                End While

            End While

It will be great if i get a solution to meet this requirement.

I mean by having a loop i have to assign the value from command1 to command2;
and command2 to command3.... like that.

How to do this in vb.net ?

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gopesh9 on: 8/17/2012 [Member] Starter | Points: 25

0	See you cant use multiple DataReader using one connection. Inspite of that you can use Datatable object to store data after reading from the DataReader. You can use like this... SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); DataTable dtSchema = dr.GetSchemaTable(); G. S. .Net Developer Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 8/17/2012 [Member] Starter | Points: 25

0	Oh we have to assign data to a datatable and use the same. fine. can you give me a sample 2or3 line code for looping datatable like do until datatable(0).eof vXX=datatable(0).row(x)col(y) datatable(0).movenext loop Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Gopesh9 on: 8/17/2012 [Member] Starter | Points: 25

0	See I am not so familiar with VB.NET so i am writing it in C#, may be it will be same in case of VB SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); DataTable dtSchema = dr.GetSchemaTable(); foreach (DataRow row in dtSchema.Rows) // Loop over the rows. { Console.WriteLine("--- Row ---"); // Print separator. foreach (var item in row.ItemArray) // Loop over the items. { Console.Write("Item: "); // Print label. Console.WriteLine(item); // Invokes ToString abstract method. } } G. S. .Net Developer Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

See I am not so familiar with VB.NET so i am writing it in C#, may be it will be same in case of VB

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 

DataTable dtSchema = dr.GetSchemaTable();



foreach (DataRow row in dtSchema.Rows) // Loop over the rows.

{

     Console.WriteLine("--- Row ---"); // Print separator.

     foreach (var item in row.ItemArray) // Loop over the items.

     {

	Console.Write("Item: "); // Print label.

	Console.WriteLine(item); // Invokes ToString abstract method.

     }

}

G. S.
.Net Developer

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 8/17/2012 [Member] Starter | Points: 25

0	Thanks for your valuable time here Gopesh Sharma, I will take care of rest. Thanks again. Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts