Database connection best practice

[dj4uk]

Just wondered if I could get a few opinions on the following.

We have just had a new developer start here and he uses a method to connect/disconnect to a database I've never seen before. Basically he sets the database connection as a global object for the page and then opens it in Page_Load and closes it in Page_Unload. As I've not seen this method before I'm presuming it is not best practice.

Obviously this means he only opens one connection and I know that there is a high overhead when opening and closing database connections. However I'm not sure how this would cope with errors.

Is it better to open and close database connections when needed and be able to use using and try/catch statements?

Your thoughts appreciated.

DJ

[Lord_Rat]

You should open and close as needed.

Another reason to support this is...

What if the page is just handling a button push for a button object that does something like change the value of a label on the page? His code is going to now open a connection to the database and then close it even though that functionality had NOTHING to do with using the database.

Now, if he simply CREATES the object in page load and then actually closes and opens it as needed, that is usually an acceptable way to go.

[Lord_Rat]

And though I hate to say it, keep watching your new coder. What you described is symptomatic of either of:

1) Lazy coding
or
2) Lack of understanding

(Or both)

And he may need some education on proper methodology.

[kleinma]

I agree. Also when you create a DB connection, and then close it, the connection still exists (in closed state) within windows itself until it times out, which is why initial connections to a DB take longer than subsequent ones (even when the connection has been closed and reopened again)

[HoraShadow]

I've actually seen this type of coding before.

Lord Rat's first post about the button has the right example of the why this is not good performance coding practice.

HoraShadow

[MasterBlaster]

Never connect to the database unless you plan on using it.

Also WTH is he connecting to the database directly from a page code behind? Shouldn't that be in the data access layer and only hit when you try to perform an action on one of your classes. Sounds like he needs to take a few more classes in oop.

[Lord_Rat]

Depending on project scope, there may not be a data access layer. That level of coding is usually reserved for larger projects.

[MasterBlaster]

Depending on project scope, there may not be a data access layer. That level of coding is usually reserved for larger projects.

No doubt, but A class/s that at least handles all connections and communication with the database is still a very valuable thing to have regardless of the size of the project. Just an opinion.

[Lord_Rat]

Totally agreed.

Actually I am finishing up one tha I intend to let other people use too.

It handles most stuff "the right way".

Unfortunately there is one thing I want to implement that is kicking my tail.

You know how if you have a Stored Procedure that has the following line:

PRINT 'Some Message'

... that you can see the message in the MESSAGES section of the Query Analyzer? I cannot seem to figure out how to get those back on any SQL object I try to use... =/

Once I figure that bit out Ill be rolling out this code class... =)

[szlamany]

Not sure with .Net, but with regular ADO the PRINT stuff becomes part of the ERRORS collection - and is only available after you process the RECORDSET collection.

We avoid using PRINT in SPROCS and either use OUTPUT parameters or actually create little mini-recordsets with that data.

PRINT 'TEST'

replaced with

SELECT 'TEST'

[Lord_Rat]

Usually, I do the same as well, but I am not always the coder on the SPs.

[MasterBlaster]

I think you're SOL on that one. As far as I know you can only grab data from "raiseerror". I don't think there is a built in way to grab data from "print" in ado.net.

[Lord_Rat]

*not listening*

Nya nya nya nya

=P

You may be right, but I'm not willing to throw in the cards yet on this. =)

[szlamany]

Does this link help?

http://msdn.microsoft.com/library/de...tml/vb03k4.asp

I googled for "ADO.NET "SQL PRINT STATEMENT" - this was one of the top links - there were many...

Please post back with your experience...

[Lord_Rat]

OK I feel bad hijacking this thread, so Ill make a new one, with a response to that question... =)