Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
edi843
Starting Member
12 Posts |
 Posted - 2009-04-21 : 07:27:19
|
| Maybe a stupid question, however what i want to know is if i have to keep the sql connection open all the time, or i every time i need to execute a command i have to open the connection. The aplication that i am developing is in asp.Net 2008. the database in SQL2008.Thanks i advance |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-21 : 09:20:38
|
| I usually reconnect for every query (via a class).The connection will not be dropped but reset ready for the next connection. There's a timeout before it is dropped (I think 10 mins). When you disconnect it goes back to the connection pool ready for re-use until the timeout.Creating a connection is quite resource intensive but re-using one from the pool is nowhere near as bad.Here's the database access layer I use (only allows SPs).http://www.nigelrivett.net/DOTNET/DotNetDBAccess.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
edi843
Starting Member
12 Posts |
Posted - 2009-04-22 : 02:56:54
|
| Thank you for the reply. I looked at the code and understand it. The only thing i am afraid is the fact that when the connection is open it take some extra time. That is why i thought to keep the connection all the time open. Because i thought that i will lose a lot of time, if the connection have to be opened every time i need it. May be i am wrong. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 05:21:42
|
| With connection pooling the connection is not dropped when you close it but reset. When you next open the connection it is retrieved from the pool which does not take much resources.In fact keeping a connection open can take more resources as you will need more connections in the pool and more new connections.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
amilojko
Starting Member
2 Posts |
Posted - 2009-04-22 : 19:06:28
|
| nr, So you are saying in VB, C#, Access etc.Connections should be opened and closed every time you need a recordset (dataset)?Rather than opening a connecton when you start the application?I am developing an Access ADO application using SQL native client or sqloledb.1I noticed in the activity monitor, that when I open connections every time I need a recordset I can see up to 5-6 separate connections to the SQL server.When I open only one and use the same one for every recordset and keep it open for the lenght of the application, there is only one connection to the server.Isn't it better to have one connection, rather than 5-6?Thanks, |
|
|
|
|
|
|
Sql connection