When do i have to open the connection ?!

  • Thread starter Thread starter Marine
  • Start date Start date
M

Marine

Hi,

My project must connect to a Sql Server quite often to read/write datas.
I am wondering : do i have to open the connection to the Sql Server at the
beginning when the application loads, then
leave the connection open and close it when the application closes ? Or is
it more efficient toopen and close the connection each time i have to use a
datareader, a command etc...

Thanks !
 
Hi Marine,

ado.net is designed to be used this way. Actually, connections are
pooled and even if you think the connection is completely
created/disposed each time you use it, in fact it's still in memory.
The key word is : let ado.net handle this for you.

By the way : if you store the connection object in the
session/application store it has to be marchalled/unmarshalled each time
you acccess it (slow).

Hope it helps.

Marine wrote :
 
My project must connect to a Sql Server quite often to read/write datas.
I am wondering : do i have to open the connection to the Sql Server at the
beginning when the application loads, then
leave the connection open and close it when the application closes ? Or is
it more efficient toopen and close the connection each time i have to use a
datareader, a command etc...
Click to expand...

Open the connection when you need it and close it immediately
afterwards. Connection pooling will take care of the underlying
connection to the database.

Jon
 
My project must connect to a Sql Server quite often to read/write datas.
I am wondering : do i have to open the connection to the Sql Server at
the beginning when the application loads, then leave the connection open
and close it when the application closes ? Or is it more efficient
toopen and close the connection each time i have to use a datareader, a
command etc...
Click to expand...

Hello Marine,

Create the connection object and close the connection after every use.
The Connections to Sql Server are pooled anyways, so there is little
overhead creating new oonnection objects.

Always use the 'using' keyword to ensure you really close/dispose your
connections like so :
using (SqlConnection connection = new SqlConnection(connectionString))
{

connection.Open();
command.Execute();
}

If you don't, you might end up leaking connections.

Ben
 
Hi Marine,

Its a good practice to close the connection once the task has been done.
Try to use Data Application Blocks for much more performance oriented tasks,
as it takes care of most of the things.

Thanks..!
 
Back
Top