I have a c# app that I am working on and wish to run a query and then run another query within the output of the ExecuteReader. My question is that can this be done within a single connection or do I have to close and re-open the connection everytime I want to run a new query?
views:
143answers:
5
+1
A:
You can reuse the connection. You may have to change the CommandType, but you don't need to close and reopen the connection. That adds unnecessary overhead.
Randolph Potter
2009-09-21 14:25:56
Would the CommandType not always be Text for queries?MySqlCommand qry = new MySqlCommand("SELECT somthing FROM table1");qry.CommandType = System.Data.CommandType.Text;qry.Connection = conn;MySqlDataReader res = qry.ExecuteReader();
aHunter
2009-09-21 14:51:33
If you're running queries this way, yes it would remain Text.
Randolph Potter
2009-09-21 14:53:22
A:
You can do it with a single connection.
You don't need to open and then close, and the prefered method in this case is to keep the connection open.
David Basarab
2009-09-21 14:26:17
+1
A:
You do not have to close it.
using(connection...) { query1; query2; }
Regards.
Pablo Castilla
2009-09-21 14:30:43
Thanks do you mean like this?using(MySqlConnection conn = new MySqlConnection(global.connectionString)) { MySqlCommand qry1 = new MySqlCommand("SELECT something FROM table1");MySqlDataReader res_qry1= qry1.ExecuteReader();while (res_qry1.Read()) { MySqlCommand qry2= new MySqlCommand("SELECT something FROM table2");MySqlDataReader res_qry2= qry2.ExecuteReader();}
aHunter
2009-09-21 14:47:19
As long as you close the reader when you're done with it, before running the next query, as Guffa pointed out.
Randolph Potter
2009-09-21 14:54:48
A:
You can run one query after the other using the same connection. However, the DataReader uses the connection to read the result, so you have to read the result and close the reader before you can run the next query.
If you would need to run another query for each row in the result, you would first need to read the result into a collection, so that you can close the reader before looping through the result. Alternatively you could open another connection, but it's better to stick to a single connection if possible.
Also consider if you can get the result in a single query using a join. It's better to run a single query than hundreds.
Guffa
2009-09-21 14:34:40
I have to say that is exactly what I thought the DataReader can only be used once for that connection. As a result because you have to have the DataReader to output the result then you have to open and close the connection. Is this the same if you have a DataReader and ExecuteNonQuery?
aHunter
2009-09-21 14:56:25
@Stuart: You can only use a DataReader once anyhow, but you don't need to open a new connection for each DataReader. If you have an open DataReader it's connection is needed to read it's result, so you can't use the connection even for ExecuteNonQuery.
Guffa
2009-09-21 15:09:35
@Guffa: so basically you create the DataReader x = qry.ExecuteReader(); and then can you feed a new query into it example x = qry2.ExecuteReader();?
aHunter
2009-09-21 15:25:27
@Stuart: Yes, you can reuse both the connection and command objects, you just have to take care of the first reader before getting the next. (Well, there is nothing stopping you from just running another query, but the first reader just can't read any more data after that.)
Guffa
2009-09-21 20:57:48
A:
You may also consider using DataTableReader which is a disconnected reader, you can iterate through it's results without having to store another collection in memory requiring twice the iteration.
Jamie Altizer
2009-09-21 16:18:42