Several Recordsets...Close the Connection Each Time?

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I need to get several recordset for which I'm opening a datareader
like so...

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
while(drA.Read())
{
sbL.Append("<tr><td>"+drA["Name"].ToString()+"</td></tr>");
}

This works fine......but when I try for the second recordset like
this....

OleDbCommand rsT = new OleDbCommand("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
while(drT.Read())
{
sbL.Append("<tr><td>"+drT["Name"].ToString()+"</td></tr>");
}

I get an error...I "fixed" it by closing...then re-opening the
connection....but do I need to???

Why can't I open the connection and re-use the connection over and
over until I have everything.......as a matter of fact....I should be
able to re-use the dr also....

Anybody know?

Bob Sweeney
 
Hi,
What you are trying to do is possible in ADO.NET 2.0 with a feature called
MARS(Multiple Active Result Sets).Search in any serach engine for MARS and
you will get plenty of articles on it.

Thanks and regards,
Manish Bafna.
MCP and MCTS.
 
Hi there,

I reckon MARS is only supported by SQL Server 2005 / Oracle (and don't think
one would use OleDbProvider for SQL Server2005 - or based on his code he's
definitely ASP guy :D, and Oracle natively supports MARS so he probably would
not get this exception) Therefore Bob, you just have to close associated
reader before executing next query / stored procedure

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
while(drA.Read())
{
sbL.Append("<tr><td>"+drA["Name"].ToString()+"</td></tr>");
}

// close reader before executing next statement
drA.Close()

OleDbCommand rsT = new OleDbCommand("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
while(drT.Read())
{
sbL.Append("<tr><td>"+drT["Name"].ToString()+"</td></tr>");
}

Additional resolution would be to execute statements in one batch and move
to next result set:

OleDbCommand rsA = new OleDbCommand(
"Select * from Authors; GO; " +
"Select * fromTitles; GO;",cnAccess);
OleDbDataReader dr = rsA.ExecuteReader();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drA["Name"].ToString());
}

dr.NextResult();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drT["Name"].ToString());
}

hope this helps
--
Milosz


Manish Bafna said:
Hi,
What you are trying to do is possible in ADO.NET 2.0 with a feature called
MARS(Multiple Active Result Sets).Search in any serach engine for MARS and
you will get plenty of articles on it.

Thanks and regards,
Manish Bafna.
MCP and MCTS.

Bob said:
I need to get several recordset for which I'm opening a datareader
like so...

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
while(drA.Read())
{
sbL.Append("<tr><td>"+drA["Name"].ToString()+"</td></tr>");
}

This works fine......but when I try for the second recordset like
this....

OleDbCommand rsT = new OleDbCommand("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
while(drT.Read())
{
sbL.Append("<tr><td>"+drT["Name"].ToString()+"</td></tr>");
}

I get an error...I "fixed" it by closing...then re-opening the
connection....but do I need to???

Why can't I open the connection and re-use the connection over and
over until I have everything.......as a matter of fact....I should be
able to re-use the dr also....

Anybody know?

Bob Sweeney
Click to expand...
Click to expand...
 
Forgot to change "drA" / "drT" to "dr" in second snippet, should be:

OleDbCommand rsA = new OleDbCommand(
"Select * from Authors; GO; " +
"Select * fromTitles; GO;",cnAccess);
OleDbDataReader dr = rsA.ExecuteReader();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", dr["Name"].ToString());
}

dr.NextResult();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", dr["Name"].ToString());
}

Regards
--
Milosz


Milosz Skalecki said:
Hi there,

I reckon MARS is only supported by SQL Server 2005 / Oracle (and don't think
one would use OleDbProvider for SQL Server2005 - or based on his code he's
definitely ASP guy :D, and Oracle natively supports MARS so he probably would
not get this exception) Therefore Bob, you just have to close associated
reader before executing next query / stored procedure

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
while(drA.Read())
{
sbL.Append("<tr><td>"+drA["Name"].ToString()+"</td></tr>");
}

// close reader before executing next statement
drA.Close()

OleDbCommand rsT = new OleDbCommand("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
while(drT.Read())
{
sbL.Append("<tr><td>"+drT["Name"].ToString()+"</td></tr>");
}

Additional resolution would be to execute statements in one batch and move
to next result set:

OleDbCommand rsA = new OleDbCommand(
"Select * from Authors; GO; " +
"Select * fromTitles; GO;",cnAccess);
OleDbDataReader dr = rsA.ExecuteReader();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drA["Name"].ToString());
}

dr.NextResult();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drT["Name"].ToString());
}

hope this helps
--
Milosz


Manish Bafna said:
Hi,
What you are trying to do is possible in ADO.NET 2.0 with a feature called
MARS(Multiple Active Result Sets).Search in any serach engine for MARS and
you will get plenty of articles on it.

Thanks and regards,
Manish Bafna.
MCP and MCTS.

Bob said:
I need to get several recordset for which I'm opening a datareader
like so...

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
while(drA.Read())
{
sbL.Append("<tr><td>"+drA["Name"].ToString()+"</td></tr>");
}

This works fine......but when I try for the second recordset like
this....

OleDbCommand rsT = new OleDbCommand("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
while(drT.Read())
{
sbL.Append("<tr><td>"+drT["Name"].ToString()+"</td></tr>");
}

I get an error...I "fixed" it by closing...then re-opening the
connection....but do I need to???

Why can't I open the connection and re-use the connection over and
over until I have everything.......as a matter of fact....I should be
able to re-use the dr also....

Anybody know?

Bob Sweeney
Click to expand...
Click to expand...
Click to expand...
 
Back
Top