How to use the same db connection with multiple datareaders?
-
Is it possible to define one db connection only, then define multiple dataReader assoiciated with that connection in ADO.net? If yes, how to do it? Thanks!
In general, it's not wise to do so. In a single threaded application you might orchestrate these DataReaders to use only one connection (that means one at a time, but that's probably not what you want), but in any multi-threaded environment (like ASP.NET) you will get into all kinds of trouble. Rely on the underlying data provider to perform connection-pooling, and open a connection for each operation. For SQL Server you can specify a connectionstring a la: "Server=SVR1;Database=Northwind;...;Pooling=true" This will force the re-use of connections, and makes opening one a lot cheaper. Gertjan Schuurmans Amsterdam The Netherlands
-
In general, it's not wise to do so. In a single threaded application you might orchestrate these DataReaders to use only one connection (that means one at a time, but that's probably not what you want), but in any multi-threaded environment (like ASP.NET) you will get into all kinds of trouble. Rely on the underlying data provider to perform connection-pooling, and open a connection for each operation. For SQL Server you can specify a connectionstring a la: "Server=SVR1;Database=Northwind;...;Pooling=true" This will force the re-use of connections, and makes opening one a lot cheaper. Gertjan Schuurmans Amsterdam The Netherlands
;P = Server=SVR1;Database=Northwind;...; Pooling=true" :laugh: Gertjan Schuurmans Amsterdam The Netherlands
-
Is it possible to define one db connection only, then define multiple dataReader assoiciated with that connection in ADO.net? If yes, how to do it? Thanks!
No you can't have multiple datareaders open at the same time for the same database connection. You might want to think of the following alternatives:
- Open, read, then close each of your datareaders in sequence.
- Execute all of the queries using a single datareader (e.g. "select * from a ; select * from b ; select * from c"). The datareader is able to process multiple resultsets (for many database products).
- Read each of your queries into a DataSet object then do whatever processing you need against the DataSet.
- Open multiple database connections.
Your choice depends upon what processing you are trying to achieve. Hope this helps. Andy Harman
-
No you can't have multiple datareaders open at the same time for the same database connection. You might want to think of the following alternatives:
- Open, read, then close each of your datareaders in sequence.
- Execute all of the queries using a single datareader (e.g. "select * from a ; select * from b ; select * from c"). The datareader is able to process multiple resultsets (for many database products).
- Read each of your queries into a DataSet object then do whatever processing you need against the DataSet.
- Open multiple database connections.
Your choice depends upon what processing you are trying to achieve. Hope this helps. Andy Harman
Thanks for replying! Initially what I did before is kind of as follows: static void main() { .... func1(); .... } static void func1() { .... string connectionstring = "..."; sqlconnection myconnection = new sqlconnection(connectionstring); string sqlstr1 = "..."; sqlcommand mycommand = new sqlcommand(sqlstr1, myconnection); myconnection.open() sqldatareader myreader = mycommand.executereader(); string myvalue = ""; while (myreader.read() { myvalue = myreader["..."].tostring(); processThisValue(myvalue); waitforresult(myvalue, connectionstring); } myreader.close(); myconnection.close(); } static processThisValue(string myvalue) { .......... } static void waitforresult(string inputvalue, string connectionstring) { sqlconnection myconnection2 = new sqlconnection(connectionstring); string sqlstr2 = "select xxx from xxx when xxx=" + inputvalue; sqlcommand mycommand2 = new sqlcommand(sqlstr2, myconnection2); while (true) { myconnection2.open(); sqldatareader mydatareader2 = mycommand2.executereader(); string myvalue2 = ""; while (mydatareader2.read()) { myvalue2 = mydatareader2["..."].toString(); } if (myvalue2 == "...") break; } mydatareader2.close(); myconnection2.close(); } because of the cost for db connection is expensive, so I'm thinking to use one connection for 2 readers, etc. From your point of view, how should I simplify the above process? Thanks!
-
Thanks for replying! Initially what I did before is kind of as follows: static void main() { .... func1(); .... } static void func1() { .... string connectionstring = "..."; sqlconnection myconnection = new sqlconnection(connectionstring); string sqlstr1 = "..."; sqlcommand mycommand = new sqlcommand(sqlstr1, myconnection); myconnection.open() sqldatareader myreader = mycommand.executereader(); string myvalue = ""; while (myreader.read() { myvalue = myreader["..."].tostring(); processThisValue(myvalue); waitforresult(myvalue, connectionstring); } myreader.close(); myconnection.close(); } static processThisValue(string myvalue) { .......... } static void waitforresult(string inputvalue, string connectionstring) { sqlconnection myconnection2 = new sqlconnection(connectionstring); string sqlstr2 = "select xxx from xxx when xxx=" + inputvalue; sqlcommand mycommand2 = new sqlcommand(sqlstr2, myconnection2); while (true) { myconnection2.open(); sqldatareader mydatareader2 = mycommand2.executereader(); string myvalue2 = ""; while (mydatareader2.read()) { myvalue2 = mydatareader2["..."].toString(); } if (myvalue2 == "...") break; } mydatareader2.close(); myconnection2.close(); } because of the cost for db connection is expensive, so I'm thinking to use one connection for 2 readers, etc. From your point of view, how should I simplify the above process? Thanks!
I would recommend that you try the simplest (easiest to code and maintain) way first, then optimise if you find that its performance is not good enough. If your process is used a lot then you should find that the SQL-Server connection pooling will reduce the expense of database connections. If your process is not used a lot then it is not worth the bother of optimising the code. Andy Harman