Solved

Persistent SQL server connection

Posted on 2004-10-20
9
856 Views
Last Modified: 2010-08-05
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
Comment
Question by:RichardFox
[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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 22

Assisted Solution

by:CJ_S
CJ_S earned 50 total points
ID: 12361896
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 12361934
objects = classes
0
 
LVL 8

Assisted Solution

by:daffodils
daffodils earned 50 total points
ID: 12362076
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
Technology Partners: 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!

 
LVL 28

Assisted Solution

by:mmarinov
mmarinov earned 50 total points
ID: 12362373
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
 
LVL 33

Accepted Solution

by:
raterus earned 50 total points
ID: 12362375
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
 
LVL 10

Assisted Solution

by:jnhorst
jnhorst earned 50 total points
ID: 12363168
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
 

Author Comment

by:RichardFox
ID: 12363375
Thanks a lot folks. I have lots of ammo now. I am not sure how to split/award points, though
0
 
LVL 33

Expert Comment

by:raterus
ID: 12363419
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
 
LVL 28

Expert Comment

by:mmarinov
ID: 12363421
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS Deployment problem 5 124
Problem to copy file 14 83
asp.net web app 3 56
Build a string of emails from a gridview 2 28
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses

739 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