Close the connection?

Hi, I've created a simple hit counter in my Session_Start event...

Dim myDataSource As New SqlDataSource
myDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString
myDataSource.UpdateCommand = "UPDATE Stats SET Hits = Hits + 1"
myDataSource.Update()

It works fine but do I need to close the connection after I've finished with it or is this ok?

Thanks for your help.

It's ok, since myDataSouce will go out of scope at the end of your procedure and it (should) clean itself up.

However, if you would be better served by skipping the sqldatasource and going directly to sqlconnection and sqlcommand. It's lighter weight, and would perform slightly better.

|||

Great, thanks a lot Motley. I just needed steering in the right direction!

Come to think of it,I have five seperate SqlDataSource's on another page and keep getting timeouts. Maybe I should try converting everything to the hard coded method.

|||SqlDatasources are really meant for binding to data controls. If you are doing everything in code, it's probably better to just use sqlconnection/sqlcommand. Although, I'm curious if sqldatasources that are instantiated that way are able to cache any content, which may make them useful, but I've never tried it.|||

Motley:

It's ok, since myDataSouce will go out of scope at the end of your procedure and it (should) clean itself up.

Actually, you should go ahead and dispose it. If you don't, when the GC runs, it gets handed over to the finalizer thread. It won't be eligible for GC until after the finalizer finalizes it, and then it's been promoted to the level 2 GC cache. This is a (weak) memory leak.

|||In .NET 2.0 best practice is to wrap connection with Using statement which will call dispose automatically.|||

Caddre:

In .NET 2.0 best practice is to wrap connection with Using statement which will call dispose automatically.

I don't think that vb.net has a using statement. Otherwise I would've suggested it.

|||

The Using statement was added to VB.NET 2.0. Try the link below for details.

http://pluralsight.com/blogs/fritz/archive/2005/04/28/7834.aspx

|||

Actually, the sqldatasource control should be able to cache the results (If you enable it, which it is disabled by default).

Secondly, I checked the source code for the sqldatasource and it closes itself after executing the update. The "using" statement will help free some memory quicker than letting it get cleaned up when the procedure goes out of scope. All in all, you won't see much of a difference in speed by either converting to sqlcommand/connection or by changing to the using statement. My comment was more just an FYI -- incase you ever had to worry about the minor performance differences between the two (and because I hate seeing when people programmatically use the sqldatasource as their only data retrieval method). It's much like watching someone instantiate a textbox control everytime they wanted to store a string.

|||I don't know about VB but in C# the only thing better than a Using statement on classes that implement IDisposable is Dispose Bool and the difference is not minor.