sql connection; multiple connections?

[Iwan Petrow]

Hi,

I have an .net windows form application and sql server 2005.
In one of the functions I have one connection for a sqldatareader and
after that I want to use other connections to sql server (do some
things like insert and update statements).
At this moment I create other SqlConnection objects in this way :
SqlConnection newSqlConn=new
SqlConnection(basicSqlConnection.ConnectionString). And this procedure
two or three times.

Is there other possibilities to do this? (If it is possible to avoid so
many SqlConnections)?

Thanks.

 

[Michael Voss]

Iwan Petrow wrote:
[...snip...]

In one of the functions I have one connection for a sqldatareader and
after that I want to use other connections to sql server (do some
things like insert and update statements).

[...snip...]

Why would you want to use another connection ? Using the same connection for
doing both would be fine (as long as you don't need different credentials to
perform selects, inserts and updates, but I'd consider this useless).

Is there other possibilities to do this? (If it is possible to avoid so
many SqlConnections)?

[...snip...]

Continue using your connection until you need to close it ;-)

 

[Rob Banfield]

Iwan,

Quite simply, you won't need to create multiple SQLConnections. The
first time you create your first, VS will open a pool of them for you,
and the next time you need a SQLConnection object, VS will look at the
pool to see if any are currently available. If not, it will dynamically
add more to the pool.

The best way to do all of this cleanly is to use the "Using ()..."
syntax as in this code fragment:

using (SqlConnection sqlConn = new
SqlConnection(AppGlobals.ConnString))
{
sqlConn.Open();

cSelect = " select blah from Yaaah";

SqlDataAdapter sdaLVData = new SqlDataAdapter(cSelect, sqlConn);
DataSet dsLVData = new DataSet();
sdaLVData.Fill(dsLVData, "SerNums");

// Do something!...
foreach (DataRow oRow in dsLVData.Tables["SerNums"].Rows)
{
blahh...
}
sqlConn.Close();
sqlConn.Dispose();

}

Just do a google on SQL and connection and pool, and you'll get lots
more on this.

Happy coding
Robert

 

[Rob Banfield]

PS - forgot to add that you do this anywhere and everywhere you need a
SQLConnection. That's all there is to it!

 

[Iwan Petrow]

I use datareader (one open connection). When I try to use the same open
connection it throws an exception that the connection is opened and is
used by datareader.

 

[Rob Banfield]

Iwan,

Look at the example I gave you - you need this kind of construction
each time you wish to create and use a datareader. Don't worry about
the number of connections - this is all handled automatically by VS
itself. Each time you use a construction like the following:

using (SqlConnection sqlConn = new
SqlConnection(AppGlobals.ConnString))
{
sqlConn.Open();

//Create datareader #1...
//
// use it...
//
// then discard it.

sqlConn.Close();
sqlConn.Dispose();
}

using (SqlConnection sqlConn = new
SqlConnection(AppGlobals.ConnString))
{
sqlConn.Open();

//Create datareader #2...
//
// use it...
//
// then discard it.

sqlConn.Close();
sqlConn.Dispose();
}
, etc.,

you are simply asking VS to give you one of the available connections
in the pool. Nothing is being wasted here, and pooling connections is
the most efficient way of handling things. That's why VS does this for
you...

HTH,
Robert