multiple database connections?

  • Thread starter Thread starter Mortar
  • Start date Start date
M

Mortar

i am binding a datagrid do a sqlcommand, which uses a sql connection.

fine.

if i want to run another query and bind another datagrid, do i have to
create new connections and commands? because i can't seem to get it to
work using just one connection
 
You aren't binding to a Command or a SqlConnection per se. I'm guessing you
are using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
remember is close the connection as soon as you're done - because of pooling
you can create another connection at very little cost
 
yes i am creating a connection and a command, then binding the
datagrid to the command's executeReader.

you suggest that creating another connection is little cost. Is this
because I can't use the existing connection? If I can use it, please
explain how. Thanks.
 
What I'm actually advocating is not reusing the same one. You have the
postback to deal with so go ahead and just create the connection and call
executereader. Justmake sure you close that connection ie using
(SqlConnection cn = new SqlConnection()) so you know it gets disposed of.
 
i know you are advocating creating a new one. What I am wondering is
why? Personal preference or because the same connection cannot
actually be used.

also, there is no postback, i am trying to do this all on the same
page load. I have a connection object, a command object, and 2
datagrids. I simply want to open a connection, run 2 queries to fill 2
grids, then close the connection. The error I get is that when it
tries to fill the second grid, it says there is a dataReader already
open associated with the connection, and it has to be closed first.
Thing is, i'm not using a datareader anywhere.

here is what i would like to do :

SqlConnection con = new SqlConnection([mydatabasedetails]);
SqlCommand cmd = new SqlCommand(SQL, con);
con.Open();

dgResults.DataSource = cmd.ExecuteReader();
dgResults.DataBind();

'now fill 2nd grid (dgResultsTwo):
'not sure how to do this on same connection and command object



con.Close();
 
Hi Mortar,

The line of code:
dgResults.DataSource = cmd.ExecuteReader();

*is* opening a SqlDataReader. You need to close the SqlDataReader
before you can do anything else with the SqlCommand.

--
Scott
http://www.OdeToCode.com/blogs/scott/

i know you are advocating creating a new one. What I am wondering is
why? Personal preference or because the same connection cannot
actually be used.

also, there is no postback, i am trying to do this all on the same
page load. I have a connection object, a command object, and 2
datagrids. I simply want to open a connection, run 2 queries to fill 2
grids, then close the connection. The error I get is that when it
tries to fill the second grid, it says there is a dataReader already
open associated with the connection, and it has to be closed first.
Thing is, i'm not using a datareader anywhere.

here is what i would like to do :

SqlConnection con = new SqlConnection([mydatabasedetails]);
SqlCommand cmd = new SqlCommand(SQL, con);
con.Open();

dgResults.DataSource = cmd.ExecuteReader();
dgResults.DataBind();

'now fill 2nd grid (dgResultsTwo):
'not sure how to do this on same connection and command object



con.Close();




What I'm actually advocating is not reusing the same one. You have the
postback to deal with so go ahead and just create the connection and call
executereader. Justmake sure you close that connection ie using
(SqlConnection cn = new SqlConnection()) so you know it gets disposed of.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Click to expand...
Click to expand...
 
how do I close it when it is created in the way I did (no object
variable)? The only way I could get it to work was using the cmd
object to close the connection (cmd.Connection.Close), then setting
the cmd to the new query, then open the connection again. If there is
a better way of doing it, please let me know.



Hi Mortar,

The line of code:
dgResults.DataSource = cmd.ExecuteReader();

*is* opening a SqlDataReader. You need to close the SqlDataReader
before you can do anything else with the SqlCommand.

--
Scott
http://www.OdeToCode.com/blogs/scott/

i know you are advocating creating a new one. What I am wondering is
why? Personal preference or because the same connection cannot
actually be used.

also, there is no postback, i am trying to do this all on the same
page load. I have a connection object, a command object, and 2
datagrids. I simply want to open a connection, run 2 queries to fill 2
grids, then close the connection. The error I get is that when it
tries to fill the second grid, it says there is a dataReader already
open associated with the connection, and it has to be closed first.
Thing is, i'm not using a datareader anywhere.

here is what i would like to do :

SqlConnection con = new SqlConnection([mydatabasedetails]);
SqlCommand cmd = new SqlCommand(SQL, con);
con.Open();

dgResults.DataSource = cmd.ExecuteReader();
dgResults.DataBind();

'now fill 2nd grid (dgResultsTwo):
'not sure how to do this on same connection and command object



con.Close();




What I'm actually advocating is not reusing the same one. You have the
postback to deal with so go ahead and just create the connection and call
executereader. Justmake sure you close that connection ie using
(SqlConnection cn = new SqlConnection()) so you know it gets disposed of.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
yes i am creating a connection and a command, then binding the
datagrid to the command's executeReader.

you suggest that creating another connection is little cost. Is this
because I can't use the existing connection? If I can use it, please
explain how. Thanks.


On Wed, 13 Oct 2004 19:36:44 -0400, "W.G. Ryan eMVP"

You aren't binding to a Command or a SqlConnection per se. I'm guessing
you
are using cmd.ExecuteReader witht he ASP.NET Web Grid? The main thing to
remember is close the connection as soon as you're done - because of
pooling
you can create another connection at very little cost

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
i am binding a datagrid do a sqlcommand, which uses a sql connection.

fine.

if i want to run another query and bind another datagrid, do i have to
create new connections and commands? because i can't seem to get it to
work using just one connection
Click to expand...
Click to expand...
Click to expand...
 
Hi Mortar:

Here is one of many possible ways you could do it, I think Bill was
alluding to this earlier:

using(SqlConnection connection = new SqlConnection(<connstring>))
{
connection.Open();

SqlCommand command = new SqlCommand();
command.Connection = connection;

command.CommandText = <query1>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid1.DataSource = reader;
DataGrid1.DataBind();
}

command.CommandText = <query2>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid2.DataSource = reader;
DataGrid2.DataBind();
}
}

The using clause ensures the SqlDataReader is properly closed before
it leaves scope.
 
excellent. Thanks.


Hi Mortar:

Here is one of many possible ways you could do it, I think Bill was
alluding to this earlier:

using(SqlConnection connection = new SqlConnection(<connstring>))
{
connection.Open();

SqlCommand command = new SqlCommand();
command.Connection = connection;

command.CommandText = <query1>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid1.DataSource = reader;
DataGrid1.DataBind();
}

command.CommandText = <query2>;
using(SqlDataReader reader = command.ExecuteReader())
{

DataGrid2.DataSource = reader;
DataGrid2.DataBind();
}
}

The using clause ensures the SqlDataReader is properly closed before
it leaves scope.
Click to expand...
 
Back
Top