0

i am really Confusion in the SqlConnection object is not closed after use we may get the performance issues regarding to the Connection pooling . i am thinking to do a single sqlConnection object is a static variable with instances in the abstract class .

siva
  • 1
  • 1
    Instead creating a static, long lived, connection, you could create a static connection generating method. That way you would ease its use and you would benefit from connection pooling. – Cleptus Aug 16 '19 at 06:32
  • of course but why don't we go for static keyword . instead of using connection polling , establishing a number of connection objects . if i use a static keyword . i will close the connection after db operation is completed so the connection object is not long lived – siva Aug 16 '19 at 06:57
  • 1
    I have always heard if was not a good idea. A quick search points static/singleton usage of database connections is a bad idea [Is using a singleton for the connection a good idea in ASP.NET website](https://stackoverflow.com/questions/1557592/is-using-a-singleton-for-the-connection-a-good-idea-in-asp-net-website) It has one answer with MS links. – Cleptus Aug 16 '19 at 07:06
  • @bradbury tq . but i have a small confusion instead of using static in asp.net can i user any application like windows , or console application only synchronize process occur like a single thread application. – siva Aug 16 '19 at 08:12

1 Answers1

2

You should wrap it an using block. This will automatically dispose the sqlconnection. See official documentation official documentation 

using(var connection = new SqlConnection("connectionstring")
{ do stuff }

I can't think off a good reason to make a static SqlConnection object. There is no reason to keep to connection live after an operation

M1sterPl0w
  • 193
  • 3
  • 14
  • while we are instance the connection object c# code internally do some function like open the sql session window and connects to the sql server through network . i am not thing about connection opening and close . i am thing to make a single connection object interact with sql server through network when garbage collector dispose the connection object we don't know – siva Aug 16 '19 at 06:36
  • The Garbage Collector will not delete static objects... This question is asked else where on StackOverflow. https://stackoverflow.com/questions/6600093/do-static-members-ever-get-garbage-collected – M1sterPl0w Aug 16 '19 at 06:43
  • ya garbage collector will not dispose the static objects thats why to restrict a multiple instance for the sql connection object i am kept static keyword for the sql connection object .for every application to make a request from client side we may or may not interact with database but most of the cases we interact with database so at the when user logout i will dispose the sqlconnection object . i am thinking for the memory wise it is good practice to keep static keyword . if i keep static keyword for the sql connection what is the drawbacks – siva Aug 16 '19 at 06:53
  • Just the opposite @siva. You will have a SqlConnection with its underlying connection closed, but being static SqlConnection would have some bits in memory because... it is static and calling `new` reserves memory (unless you assign it null after disposing it manually) – Cleptus Aug 16 '19 at 07:14