Solved

C# , ADO.NET - Better to recreate a SqlCommand object each time it's used or to keep the object around?

Posted on 2013-01-24
7
345 Views
Last Modified: 2013-01-28
I'm working on a C# console app with some classic ADO.NET in which I'm making a ton of calls to a MSSQL database. When I fire up the application, I instantiate a SqlConnection object (we'll call it mySQLConnection) and keep it around until the console app is done. That seems reasonable. My question centers on my SqlCommand object (we'll call it mySQLCommand). Is it better practice to create one mySQLCommand object, use it again and again, and then dump it when the console app is done. Or, is it better practice to create a new instance of SqlCommand each time I need to execute a command?
0
Comment
Question by:jdana
7 Comments
 
LVL 42

Expert Comment

by:sedgwick
Comment Utility
doesn't really matter, garbage collector handles the objects disposal and SqlCommand object is stateless in a way that it keeps no open sessions or scope variables.
so you can safely create SqlCommand object, or use an existing one and update relevant properties each iteration.
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 167 total points
Comment Utility
Best practice actually says that both the connection and the command object should be created each time and not kept open, but it does depend on how the project is structured and what standards you have.

If you are doing everything in the main method then it reasonable to use the same connection and command objects, however if you are calling other methods from the main method then I would create a connection and command object in each method. The main reason for doing this is that our standards say to avoid the use of global variables but it does also enforce good programming practice where you don't have global objects floating around.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
I think it depends on how often you need to execute SQL. If its hours apart, it would make sense to recreate the command and connection. If you are executing sql in a loop or frequently otherwise then it makes sense to use one object rather than recreating it.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 167 total points
Comment Utility
If you were worried about any overhead related to opening a connection to the server, then you can rest a bit more assured that your connections will be pooled in order to reduce such overhead:

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx

Basically, the runtime maintains an open TCP connection to the server. Whenever your code creates a connection object, the pool is searched for an open connection. If one exists, then it is reused; otherwise a new connection will be established, and subsequently added to the pool for future requests.
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 166 total points
Comment Utility
Using global variables should be a last resort idea. These things eat up resources even when they are not in use, and they are a pain to debug. If you have a bug on a global variable, it can come from anywhere in the application. If you have a bug on a local variable, you have to look only in the method in which it is uses and eventually in methods to which it could be passed as a parameter.

For the connection, jacko and kaufmed are right. This is also the method suggested most of the time by Microsoft. In most instances, the connection pooling handles the opening and closing of the physical connection better than you could do it. It also automatically frees connections that are not used regularly, such as when somebody goes out for diner leaving his applications running.

For the command, it depends.

Somebody told that is was not important because the garbage collector handles memory collection. This is only partially true. For optimum performance, you have to help the GC. Recreating hundreds or thousands of time while an application is running requires a lot of work from the GC.

If your command is a fixed command that does not require parameters objects or is called infrequently, then recreating it everytime you need it (except in loops) is usually the best thing. However, if you run it hundreds of times or if it has dozens of parameter objects that need to be recreated each time, then you would help the GC by keeping it in memory and changing only the values of the parameters instead of recreating the objects each time. I would try as much as possible not to keep it as a global variable however. Passing it from A to B through parameters makes maintenance easier because with good documentation, you know exactly where the object is used.
0
 

Author Comment

by:jdana
Comment Utility
Thanks.
0
 

Author Closing Comment

by:jdana
Comment Utility
jacko32 - Thanks for the insightful comment.
kaufmed - Thanks for the link to the pooling article. It was very interesting.
James - Thanks for the great synopsis.

J
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now