SQL Server Performance

When to close or destroy a .NET SqlConnection

Discussion in 'General Developer Questions' started by rhartness, Nov 7, 2006.

  1. rhartness New Member

    I've been developing in .NET for a few years now and it's been quite a while since I've read up on the MS documentation about establishing database connections and retrieving data because it's something I've had to do over and over again for the past few years.

    ADO.NET and the former ADO data models (I think it's was ADO???) are quite different from what I can remember. I remember the days of always opening and closing your connections and GCing them later. I recall ADO.NET trying to change that a little.

    Whether it's right or wrong, I have pretty much ignored handling the open/close state of my connections to my databases from with in the applications I've written because .NET mostly handles all of that for me. Have I been lazy? Probably and now it's come back to bite me in the arse. So, I'm asking a few simple questions that anyone can answer but more than answering these questions I'd like technical explanations as to the 'whys' of doing things a certain way.

    I have a data connection that is static and global as part of a windows forms app. Is that a bad thing? Should I destroy my data connections after each use? I've never done that before and I've never had any issues arise. Why would that be a problem for any reason other than that's just not a good thing to do?

    I also have a complicated updating routine. The updates to the database are pretty straight forward, I pass my parameters to an Add/Update stored procedure and it handles the rest. The C# procedure, however, loops through a bunch of records and calls the SP numerous times on the initial load of the application posting thousands of records. I've found that if I use the same .NET SqlConnection for the SqlCommand within the loop that I can post all of those updates with out a problem but if I try to use that same SqlConnection in my code on another SqlCommand, immediately after the loop, the application crashes. If I explicitily close the connection after my loop and then try to use the data connection it works fine. What would cause this to happen? Once again, technical answers are prefered.

    Thanks guys!
  2. satya Moderator

    The feature of disconnected data and pooled connections in ADO.NET you can perform open/close of connections as needed. Keep your connection string syntax identical.

    I believe ADO.NET is ideal for disconnected data, therefore you are expected to connect to a data source, retrieve or update the data as necessary and then disconnect again until there is another requirement for direct interaction.

    You should check the PERFMON to see how the system is performing during constant process of connecting and disconnecting. Keeping the string syntac identical means this then allows further connections to
    be made faster rather than the delay on the first connection attempt.



    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. rhartness New Member

    Thanks for the response. That makes sense. Considering the fact that my connections strings never change for the db commands, I'll add a few close statements and strategic places and monitor the performance.

    Thanks!

Share This Page