SqlConnection, SqlCommand

  • Thread starter Thread starter Donnie Darko
  • Start date Start date
D

Donnie Darko

I'm trying to understand SqlConnection(), SqlCommand()

For example. I use SqlConnection() as a parameter when I create a new
SqlCommand. Then I open SqlConnection(), then I execute the SqlCommand().
At that point I cannot use SqlConnection() again. Unless I .Close() it and
then .Open it right?

Then what is 'connection pooling' ?

Can't I run multiple SqlCommand()s on the same SqlConnection() somehow ?
 
Now there are couple of things here

1) If you open a SqlConnection then you CAN execute multiple different SqlCommand.ExecuteNonQuery and SqlCommand.ExecuteScalar methods.
2) If you open a SqlConnection then you can execute only ONE SqlCommand.ExecuteReader. This is because, unlike the previous methods, Execute reader keeps the data live and hence the connection to the prticular table also is live so only one of them can execute untill you close the SqlConnection or close the SqlDataReader.
3) Connection Pooling is a different conecpt. Creating managed connections to the database are expensive. Hence when Connection Pooling is enabled, and you call Close on the SqlConnection object, although the SqlConnection object is closed, the underlying connection to the database is not closed, but its returned to the pool. When another request for a new connection comes in it utilizes the connection from the pool. This way the cost of creating and deleting connections to the database is saved.

--
Regards,
Saurabh Nandu
AksTech Solutions, reflecting your needs...
[ www.AksTech.com ]
[ www.MasterCSharp.com ]
 
Back
Top