Multiple Data Readers in C#
-
Question: How can I have multiple datareaders writing data in a loop? Example:
For (int i=0; i<column.count;i++)
{
Step 1:
Run the following query to get datareader values:
select [] Column[i], count(Column[i]) AS COUNT
from <Table Name>
group by [] Column[i]Step 2:
From the first row of datareader create a string.
}Expected output when DataReader1 returns 3 rows and DataReader returns 3 rows: Sample: --> Row#1 from first dataReader --> Row#1 from second dataReader --> Row#2 from first dataReader --> Row#2 from second dataReader --> Row#3 from first dataReader --> Row#3 from second dataReader
-
Question: How can I have multiple datareaders writing data in a loop? Example:
For (int i=0; i<column.count;i++)
{
Step 1:
Run the following query to get datareader values:
select [] Column[i], count(Column[i]) AS COUNT
from <Table Name>
group by [] Column[i]Step 2:
From the first row of datareader create a string.
}Expected output when DataReader1 returns 3 rows and DataReader returns 3 rows: Sample: --> Row#1 from first dataReader --> Row#1 from second dataReader --> Row#2 from first dataReader --> Row#2 from second dataReader --> Row#3 from first dataReader --> Row#3 from second dataReader
If you want multiple DataReaders you will need multiple Commands and Connections as well -- a Connection can have only one active Command executing against it at one time.
-
If you want multiple DataReaders you will need multiple Commands and Connections as well -- a Connection can have only one active Command executing against it at one time.
Thanks for your reply. Can I have a list of SqlConnection, SqlDataReader & SqlCommand? Eg: List SQLConnections = new ();
-
Thanks for your reply. Can I have a list of SqlConnection, SqlDataReader & SqlCommand? Eg: List SQLConnections = new ();
Sure you can, but I recommend making a class that holds a Connection, a Command, and a DataReader -- then make a list of those.
-
Sure you can, but I recommend making a class that holds a Connection, a Command, and a DataReader -- then make a list of those.
Thanks a ton.. :)
-
Question: How can I have multiple datareaders writing data in a loop? Example:
For (int i=0; i<column.count;i++)
{
Step 1:
Run the following query to get datareader values:
select [] Column[i], count(Column[i]) AS COUNT
from <Table Name>
group by [] Column[i]Step 2:
From the first row of datareader create a string.
}Expected output when DataReader1 returns 3 rows and DataReader returns 3 rows: Sample: --> Row#1 from first dataReader --> Row#1 from second dataReader --> Row#2 from first dataReader --> Row#2 from second dataReader --> Row#3 from first dataReader --> Row#3 from second dataReader
I think you're trying to solve a problem that doesn't exist. The DataReader portion of the code is very fast (assuming you aren't overusing .ToString() ;). The expense is executing the query itself and getting the data back over the wire. If you want to process the data in different threads, thats a different story. Use ONE DataReader and dump the data into a List. Then use Parallel.ForEach on the List.
-
I think you're trying to solve a problem that doesn't exist. The DataReader portion of the code is very fast (assuming you aren't overusing .ToString() ;). The expense is executing the query itself and getting the data back over the wire. If you want to process the data in different threads, thats a different story. Use ONE DataReader and dump the data into a List. Then use Parallel.ForEach on the List.
Thanks for the information. If I am not wrong, this command is available for .NET 4 and above. If yes, then it might not work for me as I am working on 3.5. Please Advise.
-
I think you're trying to solve a problem that doesn't exist. The DataReader portion of the code is very fast (assuming you aren't overusing .ToString() ;). The expense is executing the query itself and getting the data back over the wire. If you want to process the data in different threads, thats a different story. Use ONE DataReader and dump the data into a List. Then use Parallel.ForEach on the List.
If it's tons of data I wouldn't want to hold it all in memory without good reason. (And it doesn't look like threading.)
-
Thanks for the information. If I am not wrong, this command is available for .NET 4 and above. If yes, then it might not work for me as I am working on 3.5. Please Advise.
Well, you can use multi-threading on the List in 3.5 without the Parallel.ForEach. Parallel.ForEach just makes it trivial and handles everything for you. As Pie mentioned, is it a lot of data? If its not a lot of data, just expensive processing, I'd dump it in a List and multi-thread process the List. If it is a lot of data, are you sure you need all that data at once? If its a lot of data AND you need it all at once, can you limit your app to run on a 64-bit OS? If so, memory won't really be an issue (as long as your machine has enough RAM). If its a lot of data AND you need it all at once AND you can't restrict to a 64-bit OS AND/OR it would be too much data to keep in memory, etc. and you really need to do the multi-threaded reader approach, is the data coming from a table or pre-computed? If so, you could have a paging approach.... have each reader grab x records on its own connection, etc, but that approach will kill performance as calling a SP and spinning up the connection and disposing it is relatively expensive.
-
If it's tons of data I wouldn't want to hold it all in memory without good reason. (And it doesn't look like threading.)
From his original post, it looks like he is trying to implement multi-threaded processing of the table, just in the wrong way.
-
From his original post, it looks like he is trying to implement multi-threaded processing of the table, just in the wrong way.
I considered that, but I don't think so. Of course, the situation is unclear.