Hello All, I have a question for the masses. I hope it's not a matter of opinion and there is a defined reason for doing which.

1. Scenario, we have ADO.NET SQLConnection object.

Within the Web.Config file, we can define our connection string and also a good little guy the Pooling parameter. Pooling is great because it pools x-amount of connections so that we don not have to wait for SQLConnection objec to open a brand spanking new one, it simply grabs one from the pool. Well, sorry i got a bit off topic. Now to the real quesiton.

Should a SQLConnection object be opened inside of every method that needs an SQLConnection object, or should we pass it as a parameter to the next method that needs it?

Example:

Public Class foo
Dim cn as new SQL Conneciton("ConnectionString")

Sub Page_Load()
 cn.Open()
 Foo1(cn)
 Foo2(cn)
 cn.dispose()
End Sub

Sub Foo1(byVal _cn as SQLConnectionString)
 ' Do some stuff with the ado connection. Open Datareader, etc...end function

Sub Foo2(byVal _cn as SQLConnectionString)
 ' Do some stuff with the ado connection. Open Datareader, etc...
end function

End Class

OR........

Public Class foo
Sub Page_Load()
 Foo1()
 Foo2()
End Sub

Sub Foo1()
 Dim cn as new SQLConnection("ConnectString")
 cn.Open()
 ' Do some stuff with the ado connection. Open Datareader, etc...end
 cn.Dispose()
function

Sub Foo2()
 cn.Open()
 ' Do some stuff with the ado connection. Open Datareader, etc...end
 cn.Dispose()
end function

End Class

What is the better solution? is there a cost to passing a connection via as a parameter as apposed to new-ing up a new connection in separate methods? thank for your help.

Flyin

Hello,

How many functions are you passing the connection to? I would have to say opening it up only when you need it would be the best solution. As it was told to me in the past, "open it up only when you need it, and close it as soon as you are done with it."

Brian

Dear flyin:
Open connection use many resourcesas Brian said, try ro open it when u need it & then ensure close it!

Always:),
Hovik Melkomian.

thanks alot everyone for your input. I Also have been told by other site to open it as needed then close it right away. Do you know from experience that continuously using the same connection is actually slower or performs worse than opening and closing within a set of methods? I have seen it done that 1 connection takes care of the whole page's db activity then is closed at the end. How do you feel about this?

How many times are you going to have to open/close the connection object in your methods? Are we talking a small amount or a large amount of connection open/close operations?

Here's the way I look at it- If there is a connection pool (which there is) then the cost to open and close is really nothing more than turning on and off a pointer to the open connection. Closing the connection just frees it up for another proccess to use.

Lets be realistic though- how long are we talking about keeping the connection open if you use it on the page? Does the page take 5 seconds to run on 5ms?

Don't overthink this for something that takes fractions of a second, unless you are having a performance issue.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Using next method

Create grobal static class DBCONNECT
and field public sqlconnection

NOW sqlconnection is one object to all module from program

when load data from DB

DBCONNECT.OPEN() // open sqlconnection
// load data
DBCONNECT.CLOSE() // close sqlconnection

// This method using pooling technology SQL SERVER and fast
// Resource of program using is small
// beacuse one sqlconnection object

akorolev10, I don't think this is a wise design decision for a multi-user application. Flyin mentions the Web.Config file so he's talking about a multi-user ASP.NET application.

What happens when you have a static class with one connection object is that all requests can be queued up. For example, when you have an open DataReader object *no* other code can use the same connection as long as the reader is open. So if you're using a DataReader to dump, say, a 100 records to a page, the data access for your other users of the application is blocked until the DataReader is done reading and is closed. This is really bad for performance.

So, in a type of application like an ASP.NET application (multi-user), it's good idea to have a global static connection string, but in your data layer (or wherever you create your connections), the local code should instantiate and open its own connection, do what it needs to do and close the connection again. I usually refer to this as the bankrobber's 3G rule: Go in, Get what you want and Get out..... (No experience in that area though :))

Since the connections are pooled, opening a new connection is indeed often as quick as pointing to an existing connection, so opening and closing a connection shouldn't cause that much performance problems.

Personally, that's the way I do it. Each method is responsible for creating and opening its own connection. However, in certain circumstances (here you go, flyin, matter of opinion, not just a defined reason :)) it can be easier to reuse the connection. Say, for example, you need to update 100 individual objects, each implementing its own Save method. So, you call Save 100 times, and each method creates its own connection, opens it, updates the object and closes the connection again. This causes quite some overhead, and you might find it quicker to reuse the connection.
In that scenario, you could create an overloaded version of the Save method that accepts an (open) connection. This way, your Business or Data code creates one connection, and then passes it 100 times to the Save method of the individual objects.

Your initial Save method can now create its own connection, and then call the overloaded version too, simply passing in the single instance of the connection.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar that is a good call. Because the way i look at it, in a multi-tier, multi-user app, i will be separating data from business and UI logic. So if a connection is opened and used, it will be used amongst methods that all do some type of database connectivity and activity so yes if i am updating records, i will have an initial save method, then overload it with a parameter for a connection and just use that to do some type of batch job. Thanks for all the input everyone!!!