how to keep one connection between two aspx page calls

[Larry]

Hi



I have asp.net programs. I used a very simple data transfer method by using
URLs



¡°First.aspx¡± contents a line to send data to the ¡°second.aspx¡± page, for
example: http://server/path/seond.apsx?data=mydata



There is a customer setting refresh rate in ¡°First.aspx¡± page.

So, if customer set refresh rate to 6 sec (mydata could be different each
time), there are more then 10 different calls (connections) to
¡°second.aspx¡± from the same ¡°first.aspx¡± page in one minute. This gave
me Error 403.9. (too many connections)



Is anyway I can keep the one connetions all the time between the two pages?



thanks

 

[Guest]

Hello Larry:

With connection pooling in .NET you do not need to share the connection
object between web pages (though you can technically store it in the session
object). It might work better if you explicitly closed the connection after
you finished retrieving the data.

You can further read about the connection pooling on MSDN:
http://msdn.microsoft.com/library/d...nectionPoolingForSQLServerNETDataProvider.asp

HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com

 

[Larry]

will it be ok if I use the "connection lifetime" in my connection string?

"Server=mypc;Database=mydata;User ID=sa;Password=ps;Trusted_Connection=True;
Connection Lifetime=5"

disconnect it after 6 sec.
thanks

 

[Guest]

Hello Larry,

You can certainly experiment with the Connection Lifetime values if you find
that it makes a difference. But, are you still getting the "too many
connections" error if you close the connection object directly after the
retrieval of the data is done in every page?

 

[jasonkester]

Trying to keep connections alive between page requests will only
excacerbate your problem. The symptoms you describe lead me to believe
that you're not closing and disposing of your connections properly.
Make sure you explicitly close all connections, and never open and
close them outside a single block of code.

Here is a typical lifecycle for one of your connections:

using (SqlConnection objConn = new SqlConnection(yourConnectionString))
{
objConn.Open();

// build your command, fill a dataset, or whatever

objConn.Close()
}

If your code is missing the using block or the .Close(), you run the
risk of it staying alive longer that you expect it to. Given your
description of the problem, I'd sat that is probably what's happening.


Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com

 

[larry]

I did close the connetions, please check the code.

---------------------
switch ((CommandFunctions)Convert.ToInt32(xmlNode.InnerText))
{
case CommandFunctions.Select:
strSQL = CreateSqlSelect(true);
¡­
if (xdData.DocumentElement == null )
{ ¡­
if (GetConnection())
{
IDataReader dbDataReader = null;
try
{
GetCommand(strSQL);
dbDataReader = dbCommand.ExecuteReader();
while (dbDataReader.Read())
{¡­ }
}
catch (System.Exception e) { ¡­ }
finally
{
if ( dbDataReader != null ) dbDataReader.Close();
dbDataReader = null;
CloseConnection();
}
UpdateCacheList(xnNode.OuterXml);
}
}
break;
case: ¡­
}

 

[Guest]

Well, the code you posted looks ok to me. There is no reason that it would
cause the "too many connections" error.

Look for other possible causes:

1- The other Cases in your Select block: Do any of them do an update using
another connection object?

2- The connectionManagement element of your machine.config and web.config
(if any)
http://msdn.microsoft.com/library/d...ref/html/gngrfconnectionmanagementelement.asp

3- The frequency of this page being hit as compared to the time that the
query takes to complete.


That is my best guess.

HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com

 

[larry]

Well, the code you posted looks ok to me. There is no reason that it would
cause the "too many connections" error.

Look for other possible causes:

1- The other Cases in your Select block: Do any of them do an update using
another connection object?

Yes, I did use UPDATE here in another case statement.

2- The connectionManagement element of your machine.config and web.config
(if any)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/ht
ml/gngrfconnectionmanagementelement.asp

Please tell me what is the Max connection number? my machine.config file has

3- The frequency of this page being hit as compared to the time that the
query takes to complete.

I think it is very hard to measure. ??

 

[Lucas Tam]

I think it is very hard to measure. ??

Run the query in Query Analyzer to see how long it takes to execute...

 

[Guest]

Hello Larry,

The maxconnection number is as the name sounds. For example if your
datareader takes 20 seconds to complete and there are 3 requests on this page
during those 20 seconds then you will definitely get an error because you
only have 2 connections available for this web application. This number
should be decided upon for each application together with your database
administrator.

As for measuring the time of your query, I would simply place Response.Write
(System.DateTime.Now.ToLongTimeString ()) before dbCommand.ExecuteReader()
and after you closed the connection.

Note also the difference between the DataReader and using Dataset and
DataAdapter.Fill method. "While a DataReader is open, the Connection is in
use exclusively by that DataReader. You will not be able to execute any
commands for the Connection, including creating another DataReader, until the
original DataReader is closed." (As quoted from MSDN) whereas the
DAtaAdapter.Fill method shares the connection with other processes "If the
connection is closed before Fill is called, it is opened to retrieve data,
then closed. If the connection is open before Fill is called, it remains
open." (As quoted from the MSDN)

So in summary, you can try a few things to resolve this:
1- Find out the time that the DataReader keeps the connection open,
2- Increase the maxconnection number (after consulting with you DB admin)
3- Use a DataSet and DataAdapter instead of a datareader


Hope this helps.

Phillip Williams
http://www.societopia.net
http://www.webswapp.com

 

[jasonkester]

As I mentioned below, you are not disposing of your connection when you
are finished with it. If you wrap it in a using block, it will be
marked for garbage collection immediately after you close it.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

 

[Usenet User]

As I mentioned below, you are not disposing of your connection when you
are finished with it. If you wrap it in a using block, it will be
marked for garbage collection immediately after you close it.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

Not quite so. If your object uses a connection, simply disposing of it
is not the right way to close a connection. Instead, the connection
should be explicitly closed in the Dispose method of the IDisposable
interface:

public class MyWarrer : IDisposable
{
private SqlConnection _conn = null;
private bool _connIsMine = false;

// Constructor
public MyWrapper( SqlConnection conn )
{
if( conn != null )
{
this._conn = conn;
}
else
{
this._conn = new SqlConnection();
this._connIsMine = true;
}
}

...

// IDisposable implementation - clean up
public void Dispose()
{
if( this._connIsMine
&& this._conn != null )
{
this._conn.Close();
this._connIsMine = false;
}
}
}

Then elsewhere you can use something like this:

using( MyWrapper wrapper = new MyWrapper( null ) )
{
// do you stuff
}

The above code allows you to use multiple wrappers and share the same
connection between them w/o risking leaving it open after the work is
done. The wrapper where the connection is created will be the one who
closes it as well. Normally, this the outermost wrapper in the calling
chain, assuming you are using wrappers from within each other.