Asp.net ajax update panel sharing a database connection

Question

I have a dropdown box and a literal tag inside an Update Panel. On the selection change event of the dropdown up requery the database and repopulate the literal tag and then call UPdatePanel.Update().

below, is there are way i can avoid having to create a new Oledbconnection each time as this seems slow. Can i reuse and store:

1. The Datasource
2. The connection in the page.

if so, how do i keep this state between calls from the GUI to the server? Here is my selection change code below

protected void cboPeople_SelectedIndexChanged(object sender, EventArgs e)
{
    string dataSource = ConfigurationSettings.AppSettings["contactsDB"];
    var objConn = new OleDbConnection(dataSource);
    string id = People[cboPeople.Text];
    UpdateLiteral(objConn, id);
}

Answer 1

You need to recreate this for each request. You have a a state less server. you never know when or if your client will call back. You do not want to keep an open connection to the database nor could you simply service multiply clients while maintaining one database connection.

To deploy high-performance applications, you must use connection pooling. When you use the .NET Framework Data Provider for OLE DB, you do not have to enable connection pooling because the provider manages this automatically. For more information about how to use connection pooling with the .NET Framework Data Provider for OLE DB, see OLE DB, ODBC, and Oracle Connection Pooling (ADO.NET). From OleDbConnection Class

Answer 2

With .NET is not a good idea to keep your connection alive longer than needs. Good practice would be to put a using statement around it (so it always gets cleaned up):

string dataSource = ConfigurationSettings.AppSettings["contactsDB"];
using(var objConn = new OleDbConnection(dataSource))
{
    string id = People[cboPeople.Text];
    UpdateLiteral(objConn, id);
}

.NET uses connection pooling, which means that when you close/dispose of the connection it doesn't actually close the connection, rather resets it and add it back to the pool. The next time a connection is needed it is used from the pool. So the overhead is not as much as you think and it is not slow. In actual fact you will find that it will use the same connection as long as only one at a time is needed.

The danger with keeping connections open is that they never get closed, and in high demand situations you run out of connections.