Solved

Reuse the same oledb.connection object for each aspx form (global.asax?)

Posted on 2004-08-23
6
709 Views
Last Modified: 2008-03-17
I've been looking for this forever.

It just doesn't feel right to recreate a connection object with the same properties (e.g. connectionstring) for each aspx form...

Is there a way to make one connection object (preferably in designtime) and reuse that instance without needing to create one for every form?

It would be nice if I could stil use this same connection when I need to enter it as a property in the .NET enviroment (for example, you need to associate a connection object with a oledb command etc...)

Any help is appreciated!
0
Comment
Question by:3803
[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
6 Comments
 
LVL 15

Assisted Solution

by:Timbo87
Timbo87 earned 40 total points
ID: 11877052
You can store the connection string in the web.config file.

http://www.dotnetjohn.com/articles/articleid3.aspx
0
 
LVL 3

Assisted Solution

by:jetforce
jetforce earned 100 total points
ID: 11878794
If you use the same connection string with the same username and password, by default you are using connection pooling and this is the best way to do it. Once you destroy the connection, the object is returned to the pool and I think it is put in the pool for 3 or 5 minutes (cannot remember which one). This gives greater performance and scalability.
  I store a connection string with the username and password . The other thing you can use if you are using ODBC is a DSN.

 Hope this helps
0
 
LVL 8

Accepted Solution

by:
shovavnik earned 110 total points
ID: 11880117
I can only add to jetforce's answer that any other alternative, like storing the connection in the Application or, even worse, in the Session, can be extermely detrimental to performance.  The connection pooling is automatic, handled behind the scenes, and guarantees a certain number of available connections for any connection object you need to make.  It also supports multi-threading better, which is vital for web applications and for good database connectivity.

If you don't want to create the connection for each aspx page independantly, you're probably not using a tiered approach.  If you had, for example, a data access layer (a DAL), the creation of the connection object would most likely be hidden from the aspx pages, and optimized for multi-threaded use.  That is, the page itself shouldn't need to create the connection itself, but should rely on the DAL to create the connections, open them ONLY when necessary, close, and destroy them.
0
Independent Software Vendors: 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!

 

Author Comment

by:3803
ID: 11880422
Thanks all, I'll split points later, but I would like some further info if possible.

I read about connection pooling elswhere too, but I don't understand very well.

Jetforce wrote this:
If you use the same connection string with the same username and password, by default you are using connection pooling and this is the best way to do it. Once you destroy the connection, the object is returned to the pool and I think it is put in the pool for 3 or 5 minutes (cannot remember which one). This gives greater performance and scalability.

Yes, the connection on each page has indeed the same connectionstring etc.
Where do these 3 to 5 minutes serve for? Is the connection destroyed (and kept in the pool) when you do connection.close or when you go to another page?

What I understand is this: The connectionobject is kept in memory (the pool) when the user leaves a page. The connectionobject is being retrieved from the pool when a new page that has a connection with the same properties is opened.

If, for example, it takes more 3 minutes to open this page, or the user leaves the site, the connection will be released from the pool.

Is that correct?
0
 
LVL 8

Assisted Solution

by:shovavnik
shovavnik earned 110 total points
ID: 11880691
I think the easiest way to think about it is to distinguish between the connection and the connection object.

The connection is maintained by a system outside of the .net framework.

The connection object is an object exposed by the .net framework that knows how to retrieve connections from the external system.

When you work try to get a connection, all you're doing really is getting an object.  The object goes to the system and requests a connection with certain properties (your connection string) from the system.  If the system finds such an object in the pool (more of a closet with drawers) it returns an available connection, but if it doesn't find one, it opens one up for you.

So basically, if you create and destroy a connection object, and then create a new connection object again, there's a good chance that you'll actually be working with the same connection.

However, the system will produce as many connections as it needs (and can) to answer your requests.  So if you have 5 concurrent database calls, all made by different clients, but having the same connection string, the system will actually produce 5 connections.

Each connection object can remain open for as much time as is specified in the time out property (or whatever it's called), or the CommandTimeOut, or the page ScriptTimeOut, whichever comes first.  When a timeout occurrs or the conenction object is explictly closed by you, the connection becomes available in the pool for other requests for a connection.
0
 

Author Comment

by:3803
ID: 11881382
Thx a lot, hope you guys agree about the points
0

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why use this lambda? 12 98
Query in AngularJS 8 84
history of pages 7 55
In the web.config conection string is there a way to tell it to use windows autertication. 2 37
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

752 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