Open multiple datareader using one connecion

Posted by Thiru under VB.NET on 8/16/2012 | Points: 10 | Views : 18915 | Status : [Member] | Replies : 6
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.




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response