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

[Phillip Williams]

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/de...taProvider.asp

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

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

[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
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3D**********************************@microsof t.com...

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/de...us/cpguide/htm
l/cpconConnectionPoolingForSQLServerNETDataProvider. asp
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Larry" wrote:

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

[Phillip Williams]

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?
--
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Larry" wrote:

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
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3D**********************************@microsof t.com...

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/de...us/cpguide/htm
l/cpconConnectionPoolingForSQLServerNETDataProvider. asp

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

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

[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.InnerTe xt))
{
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: ¡*
}
---------------------
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3E**********************************@microsof t.com...

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?
--
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Larry" wrote:

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
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3D**********************************@microsof t.com...

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/de...us/cpguide/htm l/cpconConnectionPoolingForSQLServerNETDataProvider. asp

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

> 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
>
>
>

[Phillip Williams]

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/de...entelement.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" wrote:

I did close the connetions, please check the code.

---------------------
switch ((CommandFunctions)Convert.ToInt32(xmlNode.InnerTe xt))
{
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: ¡Â*
}
---------------------
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3E**********************************@microsof t.com...

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?
--
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Larry" wrote:

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
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3D**********************************@microsof t.com...
> 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/de...us/cpguide/htm l/cpconConnectionPoolingForSQLServerNETDataProvider. asp
>
> HTH,
> Phillip Williams
> http://www.societopia.net
> http://www.webswapp.com
>
>
> "Larry" wrote:
>
> > 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
> >
> >
> >

[larry]

"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:4C**********************************@microsof t.com...

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/de...us/cpgenref/ht
ml/gngrfconnectionmanagementelement.asp
Please tell me what is the Max connection number? my machine.config file has
"<add name = "*" maxconnection = "2" />"
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. ??

That is my best guess.

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

I did close the connetions, please check the code.

---------------------
switch ((CommandFunctions)Convert.ToInt32(xmlNode.InnerTe xt))
{
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: ?-
}
---------------------
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3E**********************************@microsof t.com...

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?
--
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Larry" wrote:

> 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
>
>
> "Phillip Williams" <Ph**************@webswapp.com> wrote in message
> news:3D**********************************@microsof t.com...
> > 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/de...us/cpguide/htm

> l/cpconConnectionPoolingForSQLServerNETDataProvider. asp
> >
> > HTH,
> > Phillip Williams
> > http://www.societopia.net
> > http://www.webswapp.com
> >
> >
> > "Larry" wrote:
> >
> > > 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
> > >
> > >
> > >
>
>
>

[Lucas Tam]

"larry" <la***@abc.com> wrote in news:OsBkAwtpFHA.3960
@TK2MSFTNGP12.phx.gbl:

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. ??

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

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Phillip Williams]

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
"larry" wrote:

"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:4C**********************************@microsof t.com...

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/de...us/cpgenref/ht
ml/gngrfconnectionmanagementelement.asp

Please tell me what is the Max connection number? my machine.config file has
"<add name = "*" maxconnection = "2" />"

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. ??

That is my best guess.

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

I did close the connetions, please check the code.

---------------------
switch ((CommandFunctions)Convert.ToInt32(xmlNode.InnerTe xt))
{
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: ?-
}
---------------------
"Phillip Williams" <Ph**************@webswapp.com> wrote in message
news:3E**********************************@microsof t.com...
> 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?
> --
> Phillip Williams
> http://www.societopia.net
> http://www.webswapp.com
>
>
> "Larry" wrote:
>
> > 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
> >
> >
> > "Phillip Williams" <Ph**************@webswapp.com> wrote in message
> > news:3D**********************************@microsof t.com...
> > > 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/de...us/cpguide/htm > > l/cpconConnectionPoolingForSQLServerNETDataProvider. asp
> > >
> > > HTH,
> > > Phillip Williams
> > > http://www.societopia.net
> > > http://www.webswapp.com
> > >
> > >
> > > "Larry" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

[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/

[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]

On 22 Aug 2005 08:53:14 -0700, "jasonkester" <ja*********@gmail.com>
wrote:

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.

[Usenet User]

On 22 Aug 2005 08:53:14 -0700, "jasonkester" <ja*********@gmail.com>
wrote:

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.