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
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38814196
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
ID: 38814244
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
ID: 38814306
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
Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 167 total points
ID: 38814327
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
ID: 38816121
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
ID: 38828945
Thanks.
0
 

Author Closing Comment

by:jdana
ID: 38828951
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 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