[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 869
  • Last Modified:

Persistent SQL server connection

My boss does not like the fact that in our ASP.NET pages, we bracket each DB query with a connection open and close. In my mind this couple of microseconds in a page load is irrelevant, but he is the boss. He says there is a System object you can use which represents a persistent connection to the server which is always open and does not have to be opened and closed, and is thus more efficient. True? Opinions?
Thanks
0
RichardFox
Asked:
RichardFox
  • 2
  • 2
  • 2
  • +3
5 Solutions
 
CJ_SCommented:
What System object? System is a namespace which holds objects. And you definitely should close your connections. You can however open a connection at page level, and close it when the page is done. Just extend from Page and use that class in your inheritance.
0
 
CJ_SCommented:
objects = classes
0
 
daffodilsCommented:
I am unaware of any such object in System or any other namespace that holds persistent connection to the Server.

An option is to use SqlDataAdpater, instead of explicitly opening and closing connections.
SqlDataAdapater.Fill(query, conn) implicitly opens and closes connections ..
The connection object associated with the "query" must be valid, but it does not need to be open. If the connection is closed before the method is called is called, it is opened to retrieve data, then closed.

    SqlConnection conn = new SqlConnection(connection);
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand(query, conn);
    adapter.Fill(dataset);
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mmarinovCommented:
Hi RichardFox,

you must not leave the connection open becuase the connection pool fill be filled and you will not be able to use the connecitons
you can use auto close connection by sqldataadapter like this

    DataTable dt = new DataTable();
    SqlConnection conn = new SqlConnection(connection);
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.Fill(dt, new SqlCommand("Select statement", conn), CommnadBehaviour.CloseConnection  );

B..M
0
 
raterusCommented:
Teach your boss a lession, the general rule of thumb is to open a connection at the very last second and close it as soon as possible.  You are doing it correctly, he is not.  .NET also uses connection pooling, so you really aren't recreating the connection each time.  Don't settle for "his" way of doing it, because he is wrong.  If he still says "but..", ask him what he plans to do when an exception occurs and the connection is never closed...hmmm.
0
 
jnhorstCommented:
Agree with all of the above...  Hanging connections are probably the single biggest obstacle to a site being able to scale to a large # of users (that and maybe session state).  Sounds like he is micrmanaging the code, so I would go with the DataAdapter suggestion above.  It does what you are doing, but you will not have the apparently politically incorrcet open and close lines of code.

John
0
 
RichardFoxAuthor Commented:
Thanks a lot folks. I have lots of ammo now. I am not sure how to split/award points, though
0
 
raterusCommented:
The general rule of thumb when you don't know how to split points is to give all the points to the expert who's first letter of their name most closely matches yours. :-)
0
 
mmarinovCommented:
i think that all of us gave you something to fill the "hole" - hope that these posts will help you at the discussion with your boss
B..M
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now