ASP.NET Databound controls and connection pools --> How does it work?

[Tom]

I try to do the following:

Generate a pooled connection in a web application. I use the following code:

In my Global.asax.cs

protected void Application_Start(object sender, EventArgs e)
{
//Creating a global pooled connection object
ConnectionStringSettings DBconn =
ConfigurationManager.ConnectionStrings["MyDatabase"];
SAConnection _conn = new SAConnection(DBconn.ConnectionString);
Application.Lock();
Application["DBConnection"] = (SAConnection)_conn;
Application.UnLock();
}

In the code behind of a web page I have:

protected void Page_Load(object sender, EventArgs e)
{
SAConnection _conn = (SAConnection)Application["DBConnection"];

string commandString = "Select * from MarkenAm";
SACommand command = new SACommand(commandString);

try
{
_conn.Open();
command.Connection = _conn;
SADataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
MarkeDL.DataSource = reader;
MarkeDL.DataBind();
}
finally
{
_conn.Close();
}
}

This works fine but I have to write all the code manually. Is there any
way to tell the designer about my connection pool stored in the
application scope of my web app?? I like to use the click an point
programming feature of the designer, but want all my web pages to use
the same centrally defined connection pool. Is this possible and if so how?

(I wrote tons of web apps in Java, but I am new to ASP.NET)

Thanks for your help

Thomas

 

[bruce barker]

you've coded such that no pooling is effectively used, as you are using
the same connection for all requests. also as you do not lock the
connection during use (by sqlcommands), two page requests that try to
use the connection at the same time will fail.

you should only keep the connection string in application, and create a
connection for each request. this will enable pooling to be be used by
every request.


-- bruce (sqlwork.com)

 

[Tom]

Thanks for that input bruce. So does this mean, that ASP is managing the
pool in the background transparently? In J2EE you have to configure
connection pools in the container (application server) for every
application.

I hope ASP connection pools are also on application context and not on
page context!

Tom