I am about to publish my first website, and I am kinda worried about SqlCommand and SqlConnection objects, they take too much space in the memory and might really overwhelm the server.
What's the best method to implement a light and fast SQL query execution method? I know that we need to use 'using' statement for the SqlCommand object in order to garbage collector handle it better, but what about SqlConnection? Should I keep my SqlConnection object in a session storage per user, or make a static SqlConnection object for the whole appdomain?
Should I keep my SqlCommand objects per page or make a SqlCommand for every event and close it with cmd.Dispose(); or not even bother because it will be disposed when it leaves the local section.
I just wanted to have an expert's opinion about this subject.
Thanks in advance
You should use new SqlCommand for each event. And close it after it's completed.
You have to use separate SqlConnection instance, because if you happen to have more than one query executed at the same moment (which is highly possible in web scenario) than you'll get an exception that previous command for this connection didn't complete yet. SqlConnection can support only one command at a time.